MySQL Data backup restore

Mysqldump

Backup tool mysqldump, it is recommended to use the data below 10g for backup, is a logical backup of the MySQL database.
MyISAM Storage Engine: Support Win Bei, lock table when backing up;
InnoDB Storage Engine: Supports Win Bei and hot spares;
Parameter description

-X,--lock-all-tables: Locks All tables of all libraries, reads locks;
-L,--lock-tables: Locks All tables in the specified library;
--single-transaction: Create a transaction to perform a backup based on this snapshot; only scope InnoDB do hot standby
-R,--routines: stored procedures and storage functions;
--triggers Trigger
-E,--events events
--flush-logs: Log refresh is done when the table is locked
--master-data[=#]
|----1: recorded as change MASTER to statement, this statement is not commented;
|----2: recorded as change MASTER to statement, this statement is commented; used to record

A full backup restore
1. Backup

[[email protected] data]# mysqldump  -uroot -hlocalhost -p -l -R --triggers -E --master-data=2 --flush-logs  -B  hellodb > hellodb-fullback-$(date +%F).sql

2. View the binary log

Restore time:--flush-logs, here as long as the view from the beginning of the file can be, each time after scrolling from 154 onwards, there are 4,123 two operations, 4 rollback, 123 is an empty command
Change MASTER to master_log_file= ' master-bin.000023 ', master_log_pos=154;

3. Export Binary Log

[[email protected] data]# cp /www/data/mysql/log-bin/master-bin.00002{3,4} ./[[email protected] data]# mysqlbinlog  master-bin.000023 >tmp.sql   #如有有必要，参考mysqlbinlog[[email protected] data]# mysqlbinlog  master-bin.000024 >>tmp.sql

4. Restore data

mysql> set @@session.sql_log_bin=OFF;mysql> source /root/data/hellodb-fullback-2018-06-20.sqlmysql> source /root/data/tmp.sqlmysql> set @@session.sql_log_bin=ON;

Xtrabackup

Xtrabackup is a MySQL database backup tool provided by Percona, an open source tool capable of hot and incremental backups of InnoDB and XTRADB databases, for MyISAM, only for Win Bei, and for MyISAM when using incremental backups is actually a full backup. Xtrabackup is easy to use and powerful. Xtrabackup Features: (1) The backup process is fast and reliable, (2) The backup process does not interrupt the executing transaction, (3) can save disk space and traffic based on functions such as compression, (4) automatically implement backup inspection; (5) Fast restore speed;

Installation

[[email protected] ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm[[email protected] ~]# yum install percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm

Full-scale backup (supported by various engines)

1, full-scale backup

[[email protected] data]# innobackupex --defaults-file=/www/server/mysql/my.cnf --user=root  --password=111111 -Hlocalhost   --socket=/tmp/mysql.sock   ./

2, Backup Data View

#用户增量合并信息[[email protected] 2018-06-20_14-11-46]# cat xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 886153284last_lsn = 886153293compact = 0recover_binlog_info = 0#可以用于还原二进制数据[[email protected] 2018-06-20_14-11-46]# cat xtrabackup_binlog_infomaster-bin.000028   10232

3, prepare data, rollback uncommitted data (Restore on another machine)

[[email protected] data]# innobackupex --apply-log 2018-06-20_14-11-46/

4, restore the data flow is probably to copy the backup data into the MySQL data directory

[[email protected] data]# rm -fr /www/data/mysql/data/*   #注意尽量不要删二进制日志#把准备的好的数据复制到mysql数据目录下[[email protected] data]# innobackupex --defaults-file=/www/server/mysql/my.cnf --copy-back  2018-06-20_14-11-46/[[email protected] data]# chown -R mysql.mysql /www/data/mysql/data/

Incremental backup (only action InnoDB table)

Backup process
All-in-1 (incremental-basedir= full), Increment 2 (incremental-basedir= increment 1), Delta 3 (incremental-basedir= increment 2)

1. On the full backup above, create an incremental

[[email protected] data]# innobackupex --defaults-file=/www/server/mysql/my.cnf --user=root  --password=111111 -Hlocalhost   --socket=/tmp/mysql.sock --incremental  ./    --incremental-basedir=2018-06-20_14-11-46/

2. View the last location of the binary log for the last incremental backup

[[email protected] data]# cat 2018-06-20_15-05-08/xtrabackup_binlog_infomaster-bin.000028   11092

3. Export all the binary data (possibly multiple) after the last backup

[[email protected] log-bin]# mysqlbinlog -j 11092 master-bin.000028 -vv --base64-output=decode-rows[[email protected] log-bin]#  mysqlbinlog -j 11092 master-bin.000028 >/root/data/bin.log

4. Combined increment

Incremental merge process: At the root of the whole amount, in order, the increments are merged into the whole amount in a number of times.
The transaction during the merge process,--redo-only represents the merge, does not roll back, the last time remember not to add, let it roll back those uncommitted transactions

[[email protected] data]# innobackupex --apply-log --redo-only 2018-06-20_14-11-46/

Last merge (do not roll back, if not last, add--redo-only)

[[email protected] data]# innobackupex --apply-log  2018-06-20_14-11-46 --incremental-dir=2018-06-20_15-05-08/

5, the incremental merger is complete, the first full volume is the true total quantity (copy data to MySQL data directory)

[[email protected] data]# innobackupex --defaults-file=/www/server/mysql/my.cnf --copy-back 2018-06-20_14-11-46/

6. Last execution of binary log, incremental part not executed

mysql> set @@session.sql_log_bin=OFF;mysql> source /root/data/bin.log;mysql> set @@session.sql_log_bin=ON;

MySQL Data backup restore