Russian Version

Appendix. Methods of copying and moving of MySQL databases.

In this application I'd like to shortly discuss general methods of backup and moving of mySQL databases.

Easier and recommended way of data moving is mysqldump utility. You can copy data with help of following command:

$mysqldump dbname [tblname ...] >dump.sql

You can load data in the database with help of command:

$mysql dbname <dump.sql

Specify option --default-character-set=utf8 when you import data if you used mysqldump without options, because this encoding used by mysqldump by default. Be careful! Earlier versions of mysqldump used latin1 by default, therefore, if you have data in encoding different from latin1 use option --default-character-set both when you export and when you import data.

You can read about mysqldump and its options in details at http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html You can read about encoding here: http://dev.mysql.com/doc/refman/5.1/en/charset.html.

mysqldump makes dump of tables in SQL language. It adds queries LOCK TABLES, therefore you can rely on it in data integrity. But in case of large amount of data mysqldump works too slow which can be not acceptable for you.

Alternate method is using plain copy. Simple copy MySQL datadir with means of operating system such as cp. Data in MySQL tables are binary compatible between different versions and platforms, therefore plain copy is perfect solution. But in this case you have to care about data integrity and locking yourself.

Also you can use such OS means like LVM Snapshots, but again you have to care about data integrity and locking.

There are utilities for data copying which use particular storage engine. For example, mysqlhotbackup for MyISAM, InnoDB Hot Backup and xtrabackup for InnoDB.

Back Content Forward



Author 2010 Sveta Smirnova
COPYRIGHT © 2010 S.Smirnova and S. Lasunov
sveta_at_js-client_dot_com