Mysql copy data between databases

Sequel Pro

I often use Sequel Pro when I’m getting up to speed on the data model for a project or when I just want to debug in a more visual way than with the mysql command-line client. It’s a free OS X application that lets you inspect and manage MySQL databases. I also find it very useful for making small changes to the data while I develop and test web apps.

Quickly Copy Data Between Databases

I recently needed a way to copy a few dozen records from one camp to another. I tried using the “SELECT…INTO OUTFILE” method but ran into a permissions issue with that approach. Using mysqldump was another option but that seemed like overkill in this case—​I only needed to copy a few records from a single table. At this point I found a really neat and helpful feature in Sequel Pro: Copy as SQL INSERT

I simply selected the records I wanted to copy and used the “Copy as SQL INSERT” feature. The SQL insert statement I needed was now copied to the system clipboard and easily copied over to the other camp and imported via the mysql command-line client.

Bundles

The Sequel Pro website describes Bundles which extend the functionality in various ways—​including copying data as JSON. Very handy stuff. Many thanks to the developers of this fine software. If you’re on OS X, be sure to give it a try.

database environment mysql tools

This post's content

• Exporting MySQL database to a dump file
• Secure the backup file
• Transfer the backup file
• Import MySQL dump to new server
• Validate imported data in new server
• Another export & import option
• Important Notes

Migrating a MySQL database usually requires only few simple steps, but can take quite some time, depending on the amount of data you would like to migrate.

The following steps will guide through how to export the MySQL database from the old server, secure it, copy it to the new server, import it successfully and make sure the data is there.

Exporting MySQL database to a dump file

Oracle provides a utility named mysqldump which allows to easily export the database structure and data to an SQL dump file. Use the following command:

mysqldump -u root -p --opt [database name] > [database name].sql

Few notes:

• We're using the --single-transaction flag to avoid a database lock while exporting the data. It will allow you to continue updating data in your old database while exporting the dump file. Please note though, that new data that will be updated after the exporting process already started, won't be available in the exported dump file.
• Make sure to replace [database name] with your actual database name before running the command.
• Make sure to enter your user credentials instead of "user" and "Password" in the command. Make sure the user has permissions to backup the database.

Secure the backup file

In most cases, an organization's data is its most critical asset. Therefore, we do not want database backups laying around in our servers unprotected, as they can mistakenly leak or even worse, get stolen by hackers.

Therefore, at the first chance you get, let's compress and encrypt the file and delete the original file. To encrypt the file to a compressed file in Linux operating systems, use this command:

zip --encrypt dump.zip db.sql

You will be prompted to enter a password before the compression starts.

Transfer the backup file

Now that we have an encrypted dump file, let's transfer it over the network to the new server, using SCP:

scp /path/to/source-file [email protected]:/path/to/destination-folder/

Import MySQL dump to new server

Now that we have the backup file on the new server, let's decrypt and extract it:

unzip -P your-password dump.zip

Once the file is imported, remember to delete the dump file both for storage and security reasons.
To import the file, use the following command:

mysql -u root -p newdatabase < /path/to/newdatabase.sql

Validate imported data in new server

Now that we have the database imported on the new server, we need a way to make sure that the data is actually there and we didn't lose anything.
We recommend to start with running this query on both the old and new databases and compare the results.
The query will count the rows on all tables, which will provide an indication on the amount of data in both databases.

SELECT 
    TABLE_NAME, 
    TABLE_ROWS 
FROM 
    `information_schema`.`tables` 
WHERE 
    `table_schema` = 'YOUR_DB_NAME';

In addition, we recommend to check for MIN and MAX records of columns in the tables, to make sure the data itself is valid and not only the amount of data.
Also, before migrating the application itself, we recommend to redirect one application instance to the new database and confirm that everything is working properly.

Another export & import option

We kept this option to the end, as we do not really recommend working with it.
It seems to be a lot easier, as it will export, transfer the dump file and import the data to the new database, all in one command.
The downside though is that if the network link dies, you need to start over.
Therefore, we believe it's less recommended to work with this command, especially with large database.
If you would like to try it anyway, use this command:

mysqldump -u root -pPassword --all-databases | ssh [email protected]_host.host.com 'cat - | mysql -u root -pPassword'

Important Notes

• Make sure have both MySQL servers installed with the same official distribution and version. Otherwise, you'll need to follow the upgrade instructions from MySQL's website.
• Make sure you have enough space in your old server to hold the dump file and the compressed file (2 x db_size => free).
• Make sure you have enough space in your new server to hold the encrypted dump file, the decrypted dump file and the imported database (3 x db_size => free).
• If you ever considered just moving the datadir from one database to another, please don't. You do not want to mess with the internal structure of the database, as it's very likely to be an invitation for trouble in the future.
• Do not forget to configure important flags such as innodb_log_file_size in your new server's configurations. Forgetting to update the configuration according to the new server's specifications might result in serious performance issues.

Enjoy your new server!