Ways to copy and transfer MySQL and databases.

In this application I would like to highlight the main ways by which you can backup and migrate MySQL databases.

The recommended and easiest way to transfer data is with the mysqldump utility. You can copy data using the following command:

You can load data into the database using the command:

Specify the option --default-character-set = utf8 when importing data, if you used mysqldump with no options, since this is the default encoding it uses. Be careful! Earlier versions of mysqldump used latin1 by default, so if you have data in a different encoding than latin1, use the --default-character-set option when importing and exporting data.

You can read more about mysqldump and its options here: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html You can read more about encodings here: http: //dev.mysql.com/doc /refman/5.1/en/charset.html.

mysqldump dumps tables in SQL query format. It adds LOCK TABLES queries, so you need not worry about data integrity. But in case of large volumes, mysqldump is too slow, which may not suit you.

An alternative is to use simple copying. Just copy MySQL datadir using operating system tools such as cp. Since the data in the MySQL tables are binary compatible between versions and platforms, simple copying is an excellent way out. But in this case, you must take care of the locks manually.

You can also use OS tools such as LVM Snapshots, but again, you will have to take care of blocking manually.

There are utilities for copying data using a specific storage engine. For example, mysqlhotbackup for MyISAM, InnoDB Hot Backup and xtrabackup for InnoDB.