MySQL Backup and Recovery

Backup of the database can prevent the loss of data caused by unexpected situations such as server downtime, disk corruption, RAID card corruption, and so on.

Backup tool for MySQL:
Mysqldump
Ibbackup
Replication
LVM Snapshot
or third-party tools Xtrabacup, etc.

Backup Methods for Databases

According to the backup method partition:

1: Hot Spare

2: Cold Standby

According to the backup file partition:

1: Logical Backup: mysqldump and select * into outfile and so on.

2: Physical backup: Use tools such as Ibbackup, xtrabackup to copy physical files.

According to the backup content divided into:

1: Full backup: A backup of the whole library.

2: Incremental backup: A backup of a partial change library. Generally done via binary log

PS: Incremental backup is done by checking the last checkpoint LSN per page, if it is greater than the previously fully-prepared LSN, then back up the page, otherwise, skip. This is the backup principle of xtrabackup, which greatly reduces the time of backup and recovery in the case of large data volumes.

For InnoDB storage engines, it is best to use the--single-transaction option when backing up with mysqldump to ensure data consistency

Backup All libraries:

[Email protected] mysql]# mysqldump-uroot-p--all-databases >/root/backupdata/20140918_alldb.sql

　

Backing up some libraries

[Email protected] backupdata]# mysqldump-uroot-p--databases mysql test >/root/backupdata/20140918_2db.sql

　　

Ensure data consistency

--single-transaction to ensure consistency of backup data

--lock-tables (-L) generally applies to myisam storage engines

　

--master-data is mainly used to build a replication

When--master-data = 1: Dump file record Change master

When--master-data = 2: Dump file Comment Change Master

MySQL Backup and Recovery