MySQL common backup and recovery Solutions

MySQL has the following common backup solutions: mysqldump + binloglvm + binlogxtrabackup. In this example, the data in the database is empty. Next start mkdiroptbackup # create Backup Directory mkdir-pdata3309 {data, binlog} cdusrlocalmysqlscriptsmysql_install_db -- usermysql-

MySQL has the following common backup solutions: mysqldump + binlog lvm + binlog xtrabackup. In this example, the data in the database is empty. Start mkdir/opt/backup # create the backup Directory mkdir-p/data/3309/{data, binlog} cd/usr/local/mysql/scripts/mysql_install_db -- user = mysql-

There are three common MySQL backup solutions:

Mysqldump + binlog

Lvm + binlog

Xtrabackup

In this example, the data in the database is empty for convenience demonstration. Start now

Mkdir/opt/backup # create the backup Directory mkdir-p/data/3309/{data, binlog} cd/usr/local/mysql/scripts/mysql_install_db -- user = mysql -- datadir =/data/3309/data/-- basedir =/usr/local/mysql/chown mysql. mysql-R/data/3309/cp support-files/my-small.cnf/data/3309/my. cnf # provides the configuration file vim/data/3309/my. cnf # edit the configuration file [client] # password = your_passwordport = 3309 socket =/tmp/mysql. sock4 # The MySQL server [mysqld] port = 3309 socket =/tmp/mysql. sock4skip-external-lockingkey_buffer_size = bytes = 1Mtable_open_cache = 4sort_buffer_size = bytes = 2Kthread_stack = 128 Kdatadir =/data/3309/dataserver-id = 3309log-bin =/data/3309/binlog/mysql-bin # binary log location binlog_format = mixed # binary log format log-error =/data/3309/mysql-err # error log location innodb_file_per_table = 1 # create a tablespace file sync_binlog for each new data table = 1 # When writing binary logs, synchronize to the disk cp/data/3309/my. cnf/opt/backup/# backup configuration file mysqld_safe -- defaults-file =/data/3309/my. cnf & # Start mysql

1. Use mysqldump to completely back up mysql and binary log backup for Incremental Backup

For mysqldump options, see http://wangweiak47.blog.51cto.com/2337362/1589304

1.1 provide analog data

Mysql-S/tmp/mysql. sock4 # connect to mysqlmysql> use test; Database changedmysql> create table test (id int (2), comment char (30); # create a table Query OK, 0 rows affected (0.34 sec) mysql> insert into test values (1, 'yun zhonghe'); # insert data Query OK, 1 row affected (0.16 sec)

1.2 full backup:

mysqldump -S /tmp/mysql.sock4 -A -B -F -x --events --triggers --routines --master-data=2 > /opt/backup/all_data-`date +%F--%U`.sql

1.3 simulated data changes

Mysql> insert into test values (2, 'yun zhonghe2'); # insert a data record. Query OK, 1 row affected (0.11 sec)

1.4 Incremental Backup

Mysqladmin-S/tmp/mysql. sock4 flush-logs # scroll the binary log before Incremental backup. Cp 'cat/data/3309/binlog/mysql-bin.index | tail-n 2 | head-n 1'/opt/backup/# Back up binary logs

1.5 data corruption

rm -rf /data/3309/killall mysqld

1.6 recovery

The preceding steps are provided and copied directly for use.

Mkdir-p/data/3309/{data, binlog} cd/usr/local/mysql/scripts/mysql_install_db -- user = mysql -- datadir =/data/3309/data/-- basedir =/usr/local/mysql/cp/opt /backup/my. cnf/data/3309/chown mysql. mysql-R/data/3309/mysqld_safe -- defaults-file =/data/3309/my. cnf & mysql-S/tmp/mysql. sock4 # connect to mysql to view data mysql> set SQL _log_bin = 0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'SQL _ log_bin'; +- -------------- + ------- + | Variable_name | Value | + --------------- + ------- + | SQL _log_bin | OFF | + --------------- + ------- + 1 row in set (0.00 sec) mysql> source/opt/backup/all_data-2015-10-01 -- 39. SQL mysql> select * from test. test; # Missing Data + ------ + ------------- + | id | comment | + ------ + ------------- + | 1 | yun zhonghe | + ------ + ------------- + 1 row in set (0.00 sec) mysql> sources/tmp/incres-1. SQL; mysql> Select * from test. test; # Data recovered + ------ + -------------- + | id | comment | + ------ + -------------- + | 1 | yun zhonghe | 2 | yun zhonghe2 | + ------ + ------------ + 2 rows in set (0.00 sec) mysql> set SQL _log_bin = 1; # Turn on the binary record switch.

1.7 conclusion: mysqldump is suitable for scenarios with a small amount of data. It has many options and is flexible to use. Its disadvantage is that once the data volume is too large, it is time-consuming and labor-consuming.

2. Use lvm for full backup.

Lvm snapshot volume principle reference

Http://baike.baidu.com/link? Url = iEpO_zm_AbHbk-ijQa8jNcFRoPPG2NdTYb_cRoQ7mjQb_ag9g-fL7yHPXV7Atp2j3J0L5xYynM9KKLIwycW_S _

2.1 data continues with the previous start.

2.2 full backup.

Ensure that the data inventory is placed on the logical volume group.

2.2.1 first lock the table and scroll logs

Mysql> flush table with read lock; Query OK, 0 rows affected (0.02 sec) mysql-S/tmp/mysql. sock4-e 'show master status; '>/backup/master.info # record the current log information mysqladmin-S/tmp/mysql. sock4 flush-logs # Rolling logs

2.2.2 create a snapshot

lvcreate -s -n snap_data -L 500M /dev/vg_node5/mylv_data  Logical volume "snap_data" created

2.2.3 unlock table

mysql> unlock tables;  Query OK, 0 rows affected (0.00 sec)

2.2.4 copy snapshot volume data to the backup directory

mkdir -p /backup/lvmmount /dev/vg_node5/snap_data /mnt/lvmcp -R /mnt/3309/* /backup/lvm

2.2.5 deleting a snapshot

umount /mntlvremove /dev/mapper/vg_node5-snap_dataDo you really want to remove active logical volume snap_data? [y/n]: y  Logical volume "snap_data" successfully removed

2.3 Incremental backup. You only need to copy the binlog to the backup directory at regular intervals.

2.4. You only need to copy the files in the backup directory.

Killall mysqldrm-rf/data/3309/cp-R/backup/lvm/data/3309/chown mysql. mysql-R/data/3309/mysqld_safe -- defaults-file =/data/3309/my. cnf & ss-tnl | grep 330 LISTEN 0 50 *: 3309 *: * mysql> select * from test. test; # data not changed + ------ + -------------- + | id | comment | + ------ + ------------ + | 1 | yun zhonghe | 2 | yun zhonghe2 | + ------ + -------------- +

2.5 Summary:

The physical backup speed of lvm is faster than that of mysqldump, And the implementation is relatively simple, which is a good choice.

Disadvantage: The data directory must be stored in the lvm volume group.

3. Use xtrabackup for hot backup.

Go to the official website to download and install the corresponding rpm package.

For more information, see:

Http://www.cnblogs.com/Amaranthus/archive/2014/08/19/3922570.html

3.1 create a user with minimum Permissions

mkdir /backup/xtrabackupmysql> grant RELOAD, LOCK TABLES, REPLICATION CLIENT on *.* to 'bkuser'@'localhost' identified by '123456';mysql> flush privileges;

3.2 full hot standby.

When innobakupex is used for backup, it will call xtrabackup to back up all InnoDB tables and copy all the files related to table structure definition (. frm), and files related to MyISAM, MERGE, CSV, and ARCHIVE tables. Files related to triggers and database configuration information are also backed up. These files are saved to a time command directory.

Innobackupex -- user = bkuser -- password = 123456 -- socket =/tmp/mysql. sock4 -- defaults-file =/data/3309/my. cnf/backup/xtrabackup/# help related options. # Nnobackupex: completed OK! # This option indicates that the backup is complete. Ls/backup/xtrabackup/2015-10-01_17-00-13

(1) xtrabackup_checkpoints -- Backup Type (such as full or incremental), backup status (such as whether it is already in prepared status), And LSN (log serial number) range information;

Each InnoDB page (usually 16 KB) contains a log serial number, that is, the LSN. The LSN is the system version number of the entire database system. The LSN related to each page can indicate how the page has changed recently.

(2) xtrabackup_binlog_info -- the binary log file currently in use by the mysql server and the location of the binary log event until the moment of backup.

(3) xtrabackup_binlog_pos_innodb -- the current position of the binary log file used for InnoDB or XtraDB tables.

(4) xtrabackup_binary -- The xtrabackup executable file used in backup;

(5) backup-my.cnf-the configuration option information used by the BACKUP command;

3.3 provide data changes.

mysql> insert into test.test values (3,'yun zhonghe3');Query OK, 1 row affected (0.03 sec)

3.4 Incremental Backup

innobackupex --user=bkuser --password=123456 --socket=/tmp/mysql.sock4 --defaults-file=/data/3309/my.cnf --incremental /backup/xtrabackup/ --incremental-basedir=/backup/xtrabackup/2015-10-01_17-00-13/

3.5 simulate data corruption

rm -rf /data/3309/killall mysqld

3.6 Incremental Backup Recovery

innobackupex --apply-log --redo-only /backup/xtrabackup/2015-10-01_17-00-13/innobackupex --apply-log --redo-only /backup/xtrabackup/2015-10-01_17-00-13/ --incremental-dir=/backup/xtrabackup/2015-10-01_17-15-00/

# Note: if there are multiple instances, you still need to back up the configuration file, and binlog directory is required at startup.

Mkdir-p/data/3309/{data, binlog} chown mysql. mysql-R/data/cp/opt/backup/my. cnf/data/3309/innobackupex -- copy-back/backup/xtrabackup/2015-10-01_17-00-13/-- defaults-file =/opt/backup/my. cnf # restore data.

3.6 start database viewing.

Mysqld_safe -- defaults-file =/data/3309/my. cnf & mysql-S/tmp/mysql. sock4mysql> select * from test. test; # data repair is completed. + ------ + -------------- + | Id | comment | + ------ + -------------- + | 1 | yun zhonghe | 2 | yun zhonghe2 | 3 | yun zhonghe3 | + ------ + -------------- + 3 rows in set (0.01 sec)

Summary:

Xtrabacup has the following features.

(1) The backup process is fast and reliable;

(2) The backup process will not interrupt ongoing transactions;

(3) Saving disk space and traffic based on compression and other functions;

(4) automatic backup check;

(5) Fast Restoration;

Therefore, we recommend that you use xtrabackup for backup and restoration.