Import and export SQL tables

You should not use phpmyadmin to export big tables from mysql. You may get partial exports because of php server timeouts.

Best way is to connect via SSH to the server and use mysqldump to export the dataset. Then you can download it and transfer it to the new server. There you could use the mysql command to import the dataset.

After the export you should always open the sql file with an editor and verify that the last line contains the following text pattern:

-- Dump completed on [date time]

A partial export will result in data loss if it does unnoticed.

Export from old database:

mysqldump --allow-keywords --opt -u USERNAME -p DATABASE > backup.sql

Import to new database:

mysql -u USERNAME -p DATABASE < backup.sql

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s