Tuesday, October 20, 2009

Transfer MySQL database

One way to transfer database from one server to an other is to use dump file created by mysqldump. However, on the new server to restore the dump file, MySQL may complains this:

ERROR 1046 (3D000) at line xx: No database selected

To fix this problem, insert following 2 lines in bold before the "DROP TABLE IF EXISTS 'address'", in following example, of the dump file before restoring the database:

CREATE DATABASE databasename;
USE databasename


The modified dump file will create a new database and select it, then restore the dump file to this new database without problem.

The command syntax to backup/restore MySQL database as root are shown below:

Backup: mysqldump -u root -p databasename > dumpfilename

Restore: mysql -u root -p < dumpfilename

Note: the user has to enter the password for above commands.

No comments: