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:



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:



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:



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.

Truncate all Tables in a MySQL Database

UPDATE: I've written a new post with an updated script that encourages safer password handling. You should go there instead.

Here is a handy script to truncate all the tables in a MySQL database. I use this sometimes to wipe out an entire database before loading in some production data to replicate a customer problem:



This script isn't perfect -- it should really prompt the user for his password so that these passwords don't get into bash history logs. In a later post I'm going to talk about using this command in conjunction with some command line MySQL command to load a remote database locally for testing.