User Comments

If you have ISAM table files (written under a different OS) you want to use and absolutely cannot access the old system - and back them up properly (with mysqldump) - you (probably) can get away with rebuilding the index (See "4.5.6.9 How To Repair Tables - Stage 3") and then issuing isamchk -o. So, it's not impossible, as some sites *ahem* claim.

The mysqldump / mysqlimport method does not work as described above if you have InnoDB tables with defined foreign keys. However, I was able to still use the dump files by ordering the table creates from the top of the relational hierarchy to the bottom. The same had to be done with the import side, as the foreign key constraints fail otherwise. If there's a switch you can set to avoid this, post it here as an additional comment. (My database only had 7 tables, so it wasn't too bad - for larger databases, though, it could get pretty hairy.)

An easy way to move a number of databases from one host to another is:

$ mysqldump -uSRC_USER -pSRC_PWD -a --databases DATBASE1 DATABASE2 DATABASE3 | ssh TARGET_USER@TARGET_HOST "mysql -
uTARGET_USER -pTARGET_PWD"

I use Windows 2000 Pro, and MySQL 4.1.4 Gamma. When I tried this command, shown above:

shell> mysqldump --quick db_name | gzip > db_name.contents.gz

It did not work. I get an error that gzip is not a valid command. What did work:

shell> mysqldump -u root -p --quick db_name > db_name.contents.txt

This places the file whereever your prompt was when you ran the command.

Before going further, I'm not a deep unix expert, so take my comments with caution. For mysqldump to work, DUMPDIR must be writable by the mysql user. A 'select into outfile' query is executed to get the result set for the *.txt file containing dumped table data, and that file has to be written by the mysql user.

For example, on unix systems if you specify /home/well/mysqlbkup as DUMPDIR, then /home/well must be readable by the mysql user and /home/well/mysqlbkup must be writable by the mysql user.

When mysqldump creates the *.txt file containing the actual table data, that file is world readable on a unix system.

These instructions do not work in MySQL 4.1:

--------------------

First, create the directory for the output files and dump the database:

shell> mkdir DUMPDIR
shell> mysqldump --tab=DUMPDIR db_name

Then transfer the files in the DUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there:

shell> mysqladmin create db_name # create database
shell> cat DUMPDIR/*.sql | mysql db_name # create tables in database
shell> mysqlimport db_name DUMPDIR/*.txt # load data into tables

--------------------

1) mysqlimport does NOT support wildcards. Doing

mysqlimport db_name DUMPDIR/*.txt

Results in:

mysqlimport: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* IGNORE 0 LINES' at line 1, when using table: *

You must use the complete filename:

mysqlimport db_name DUMPDIR/table1.txt
mysqlimport db_name DUMPDIR/table2.txt
mysqlimport db_name DUMPDIR/table3.txt
etc...

-----

2) mysqlimport expects paths to be relative to the data directory for the given database. Thus you should specify the absolute path to each .txt that you want to import.

mysqlimport db_name ./table1.txt # this will not work

mysqlimport db_name /somedir/someotherdir/andanother/table1.txt # this will work

-----

3) cat DUMPDIR/*.sql | mysql db_name # create tables in database

This (obviously) does not work in Windows XP. Instead of "cat", you can use "type", but if you do "type *.sql", type will print filenames, which will result in SQL errors. Instead, do this:

copy *.sql all.sql
type all.sql | mysql db_name # create tables in database

Daniel,

I had the same problem you did with InnoDb tables and foreign key constraints. Here's a fix:

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name
mysql> SET FOREIGN_KEY_CHECKS = 1;

These commands are obviously from the mysql> command line, but you may be able to insert them into your dump file. I haven't tried it yet from the dump file, so I don't know. Works like a charm from the mysql> command line though.

Alex

While trying to make a dump, you might encounter an error that looks like this:

[mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

It's because the mysql user doesn't have the right permissions to write to that dir.
a simple chmod should fix the problem

Regarding Brandon Stout's comment above about the dump-to-zip-file not working, I'm guessing that to dump to the zip file, you need to

a) have the zip utility in question installed and
b) specify the path to the zip utility after the pipe delimiter

I tried this with winzip but couldn't quite get it figured out.

As a suggestion for MySQL documentation writers, I think Brandon's comment and a lot of my personal headbanging as a newbie come from the documentation's not having a clearly stated set of documentation conventions. Experienced folks may know that a referenced exe is not part of the mysql code, but newbies may not. I know some of the vagueness of the documentation has to do with there being different specifics for different shells etc. but a few more putting down of assumptions might be very much appreciated by your newbie userbase. As an example, if a path is needed you might write (path)mysqldump, and also ideally develop a page where documentation conventions such as "(path)" are spelled out in detail.

*PS big thanks to MySQL documentation writers/developers. I know this excellent server software and documentation are free, for which I am thankful! I'm just hoping to remind developers about days way, way in the past, when you were in Newbie Forest at dark...

**PPS Regarding Alex's tip above about setting foreign key checks=0, I tried "set foreign_key_checks = 0;" in a dump file before the first "table stucture" and "set foreign_key_checks = 1;" after the last "dumping data for table x" in my dump file and I didn't get any foreign key errors (using PHPMyadmin) when creating my database on a new server.

Brandon Stout and Frances D:
You need to have gzip (GNU zip, not WinZip or others) in your search path. See http://www.gzip.org/ for information on gzip and to download gzip executables for Windows.


Vladimir Kornea:
mysqlimport does not need to support wildcards, because a decent shell will support using a wildcard character for globbing of filenames. If you enter "mysqlimport db_name DUMPDIR/*.txt" in bash, for example, bash will create a child process to run "mysqlimport db_name DUMPDIR/table1.txt DUMPDIR/table2.txt DUMPDIR/table3.txt". Judging by your post, it looks like cmd.exe treats the '*' character literally instead of expanding it. The solution is to use a better shell, such as bash, on Windows.

"cat DUMPDIR/*.sql | mysql db_name" *will* work on Windows XP if you use bash instead of cmd.exe and have a "cat" executable in your search path.


Frances D:
The MySQL reference manual is not a tutorial on the usage of a command interpreter, as stated in the second paragraph of section 1.1. Newbies can refer to plenty of other documentation for such information.

you can, as it says, move tables from a database to another machine by simply copying the .frm .MYD & .MYI files.

i don't know about other platforms, but on linux, when the files arrive it seems to be essential to ensure that they are owned by mysql & in group mysql (& probably have reasonable permissions) otherwise, when you try to make any changes to the table (eg alter table disable keys) mysql reports:

ERROR 1036: Table '<table-name>' is read only

you'd think that you could just change the owner etc at this point & it would cure the problem, but it doesn't (or at least, it didn't for me).

Just change the owner and group to mysql. I had the same problem and this fixed it. Also, the files should only be chmod 660.

About FOREIGN_KEY_CHECKS, just quoting manual, now "mysqldump automatically includes a statement in the dump output to set FOREIGN_KEY_CHECKS to 0"

Thierry B.

as mysqlimport doesn't support wildcards, the solution could be

find /usr/mysql-transfer/dmpdmp -type f -name "*.txt" -exec mysqlimport dbname {} \; -print

Instead of the 'mysqlimport' command line, I found it much easier (in Windows) to read the dump result file into MySQL Query Browser. It's best to rename your dump file with a ".sql" suffix. Then in the Browser, use File/Open Script and select the file. Finally, press "Continue" at the top and (hopefully) the script will do its thing.
If there is a problem found, the script stops immediately and the text can be modified before going on using Continue. I, for instance, had a foreign key duplicate name that I easily corrected and proceeded without further problem.

Add your own comment.