Transfering mysql Data using mysqldump

In the last post I showed a script for truncating all the tables in a mysql database. In this post I will now show some command line techniques for loading the truncated database with data from another database. For this task you can use the myqldump command. For example to dump all the data from a database to a text file you can use a command like this:

mysqldump -u user_name -p database_name > dump.sql

view raw gistfile1.sh hosted with ❤ by GitHub

Executing this command with prompt the user for a password, and then dump the data from database_name out to a text file called dump.sql. The -t switch makes it so that it only dumps data, and doesn't dump DDL statements that delete and recreate the tables before loading the data. You may or may not want to use this switch depending on your situation. This dumped data can now be loaded into another database, such as one we truncated with mysqltrunc, with the following command:

cat dump.sql | mysql -u user_name -p database_name

view raw gistfile1.sh hosted with ❤ by GitHub

Remember that because of the -t option used previously, the dump.sql doesn't contain DDL command to drop and recreate the tables, thus your target database needs to have the same schema or the dump will fail. Once again you may or may not want to use the -t switch depending on your situation.

ssh allows you to execute commands remotely. This can allow you to quickly transfer data from a database in a remote server. For example:

ssh some.server 'mysqldump -u user_name -p remote_database_name' | mysql -u user_name -p local_database_name

view raw gistfile1.sh hosted with ❤ by GitHub

With these techniques you should be able to do things like transfer production data to your local computer to replicate a problem with a minimal amount of effort.