How to back up MySQL database?

Question

What do I need to consider when backing up a database with millions of records? Are there any tools (possibly bundled with a MySQL server) that I could use?

Answer 1

Depending on your requirements, there are several options that I used myself:

• If you do not need hot backups, remove the db server and back up at the file system level, i. e. using tar, rsync or the like.
• If you need a database server to continue working, you can start with the mysqlhotcopy (perl script) tool , which locks table backups and allows you to select individual tables and databases.
• If you want the backup to be portable, you can use mysqldump , which creates SQL scripts to recreate the data, but which is slower than mysqlhotcopy
• If you have a copy of db at a specific point in time, you can also just keep binels (starting from that point in time) somewhere safe. This can be very easy to do and does not interfere with the server, but it may not be the fastest to recover, and you must be sure not to miss some of the logs.

Methods that I have not tried, but it makes sense to me:

• , ZFS LVM, , , . db , . . FLUSH TABLES WITH READ LOCK (, , UNLOCK TABLES )

:

• "-" , . , , , binlog .
• , MySQL , .

Answer 2

Mysqlhotcopy - ​​ , MyISAM, .

mysqldump - , ( , InnoDB, . --single-transaction).

, , XtraBackup: http://www.percona.com/docs/wiki/percona-xtrabackup:start

Answer 3

mysqldump MyISAM; , , 5-10 .

InnoDB, - , .

MyISAM, , MySQL mysqldump .

Answer 4

export phpMyAdmin. phpMyAdmin - - MySQL.

Answer 5

, mysqldump - .