List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 1 1999 4:13pm
Subject:Re: Export/import
View as plain text  
Peter Hicks wrote:
> 
> Folks,
> 
> I've tried in vain to get a simple, straightforward, command or script
> to dump the contents (data, and table structures) from a MySQL
> database on one server, to a text file, and then to re-create the
> database on another sever.
> 
> Using mysql_dump and mysqlimport doesn't seem to work reliably -
>  mysql_dump doesn't appear to escape all special characters, so I
> get a whole host of errors when importing.
> 
> Can someone point me in the direction of a script to do this?
> 
> Cheers,
> 
> Peter.

Hi Peter

mysqldump does escape all special characters, but a tab is not considered a special
character. As mysqlimport per default uses tab delimited columns you can have problems on
reading strings (not only BLOB's) with tabs inside.

Because of this I use the following two commands:
mysqldump --quick --complete-insert --extended-insert databaseName >database.sql
mysql --host=other.mysql.com newDatabase <database.sql

Example:
mysql> CREATE TABLE bin (extra_chars TINYBLOB,id TINYINT);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO bin (id, extra_chars) VALUES
    -> (0,'abcdefg'),(1,'abc\'defg'),(2,'abc\"defg'),(3,'abc\\defg')
    -> ,(4,'abc\0defg'),(5,'abc\ndefg'),(6,'abc\tdefg');
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bin;
+-------------+------+
| extra_chars | id   |
+-------------+------+
| abcdefg     |    0 |
| abc'defg    |    1 |
| abc"defg    |    2 |
| abc\defg    |    3 |
| abc         |    4 |
| abc
defg    |    5 |
| abc   defg    |    6 |
+-------------+------+
7 rows in set (0.00 sec)

Mack>mysqldump --quick --complete-insert --extended-insert -hcomnt0 -uroot test bin

# MySQL dump 5.13
#
# Host: comnt0    Database: test
#--------------------------------------------------------
# Server version        3.22.16-gamma

#
# Table structure for table 'bin'
#
CREATE TABLE bin (
  extra_chars tinyblob,
  id tinyint(4)
);

#
# Dumping data for table 'bin'
#

INSERT INTO bin (extra_chars, id) VALUES
('abcdefg',0),('abc\'defg',1),('abc\"defg',2),('abc\\defg',3),('abc\0defg',4),('abc\ndefg',5),('abc
   defg',6);

Tschau
Christian

Thread
Export/importPeter Hicks1 Jun
  • Re: Export/importChristian Mack1 Jun
    • Re: Export/importMichael Widenius3 Jun
  • Re: Export/importChristian Mack4 Jun
    • Re: Export/importMichael Widenius5 Jun