Xtrabackup2.4.8 backup, restore, restore Mysql5.7.19 real

Environment: CentOS 6.7 + Mysql 5.7.19 + xtraback 2.4.8

Innobackupex Common parameters:--user=user Specifies the backup user, if not specified, for the current system user--password=passwd specifying the backup user password--Port=port specifying the database port--Defaults-group=group-name When using multiple instances--Host=host Specifies the host for the backup, which can be a remote database server--apply-log rollback Log--database Specifies the databases that need to be backed up, separated by spaces between multiple databases--Defaults-file specifying the configuration file for MySQL--copy-back Copy the backup data back to its original location--incremental incremental backup, followed by the path to incremental backup--Incremental-basedir=directory Incremental backup Use the same directory that points to the last incremental backup--incremental-dir=directory Incremental backup is used to consolidate incremental backups to full volume to specify a full-standby path--redo-only to consolidate incremental backups--rsync speeds up local file transfers for the NON-INNODB database engine. Not shared with--stream--the backup file generated by No-timestamp is not a timestamp directory.

Example Group 1: fully-prepared restore

#数据目录:/data/MySQL $mkdir-pv/backup/3306/20171107/full #本地 Single-instance whole-volume backup $ innobackupex--defaults-file=/ETC/MY.CNF--user=root--password=123456--socket=/tmp/mysql.sock--no-timestamp--parallel=4/backup/3306/20171107/full# Restore $ Innobackupex--apply-log/backup/3306/20171107/full$ service mysqld stop $MV/data/mysql/data/mysql_bak_201711071 # #备份原数据目录 $mkdir-pv/data/mysql$ Innobackupex--copy-back/backup/3306/20171107/full$Chown-R mysql:mysql/data/mysql$ Service mysqld start

Example Group 2: incremental Restore

#数据目录:/data/MySQL $mkdir-pv/backup/3306/20171107/{full,inc1,inc2,inc3} #本地 single-instance full-volume backup $ innobackupex--defaults-file=/ETC/MY.CNF--user=root--password=123456--socket=/tmp/mysql.sock--no-timestamp--parallel=4/backup/3306/20171107/full# Local Single instance incremental backup 1$ MySQL-s/tmp/mysql.sock-uroot-p123456-e"CREATE TABLE TPCCDB.TB2 (col1 varchar);"$ mysql-s/tmp/mysql.sock-uroot-p123456-e"INSERT into TPCCDB.TB2 values (' 1 '), (' 2 '), (' 3 '), (' First insert end ');"$ Innobackupex--defaults-file=/ETC/MY.CNF--user=root--password=123456--socket=/tmp/mysql.sock--no-timestamp--parallel=4--incremental-basedir=/backup/3306/20171107/full--incremental/backup/3306/20171107/inc1# Local Single instance incremental backup 2$ MySQL-s/tmp/mysql.sock-uroot-p123456-e"INSERT into TPCCDB.TB2 values (' 4 '), (' 5 '), (' 6 '), (' Second insert End ');"$ Innobackupex--defaults-file=/ETC/MY.CNF--user=root--password=123456--socket=/tmp/mysql.sock--no-timestamp--parallel=4--incremental-basedir=/backup/3306/20171107/inc1--incremental/backup/3306/20171107/inc2# Local Single instance incremental backup 3$ MySQL-s/tmp/mysql.sock-uroot-p123456-e"INSERT into TPCCDB.TB2 values (' 7 '), (' 8 '), (' 9 '), (' Third insert End ');"$ Innobackupex--defaults-file=/ETC/MY.CNF--user=root--password=123456--socket=/tmp/mysql.sock--no-timestamp--parallel=4--incremental-basedir=/backup/3306/20171107/INC2--incremental/backup/3306/20171107/inc3 #还原 $ innobackupex--apply-log--redo-only/backup/3306/20171107/full$ Innobackupex--apply-log--redo-only/backup/3306/20171107/full--incremental-dir=/backup/3306/20171107/inc1$ Innobackupex--apply-log--redo-only/backup/3306/20171107/full--incremental-dir=/backup/3306/20171107/inc2$ Innobackupex--apply-log/backup/3306/20171107/full--incremental-dir=/backup/3306/20171107/inc3 $ Innobackupex--apply-log/backup/3306/20171107/full$ service mysqld stop $MV/data/mysql/data/mysql_bak_201711072 # #备份原数据目录 $mkdir-pv/data/MySQL $ innobackupex--copy-back/backup/3306/20171107/full$Chown-R mysql:mysql/data/mysql$ service mysqld start$ MySQL-s/tmp/mysql.sock-uroot-p123456-e"select * from TPCCDB.TB2;"

Example Group 3: Full & fixed point & misoperation recovery

#数据目录:/data/MySQL $mkdir-pv/backup/3306/20171107/{FULL,INC1,INC2} #本地 single-instance full-volume backup $ innobackupex--defaults-file=/ETC/MY.CNF--user=root--password=123456--socket=/tmp/mysql.sock--no-timestamp--parallel=4/backup/3306/20171107/full# Local Single instance incremental backup 1$ MySQL-s/tmp/mysql.sock-uroot-p123456-e"CREATE TABLE TPCCDB.TB3 (col1 varchar);"$ mysql-s/tmp/mysql.sock-uroot-p123456-e"INSERT into TPCCDB.TB3 values (' 1 '), (' 2 '), (' 3 '), (' First insert end ');"$ Innobackupex--defaults-file=/ETC/MY.CNF--user=root--password=123456--socket=/tmp/mysql.sock--no-timestamp--parallel=4--incremental-basedir=/backup/3306/20171107/full--incremental/backup/3306/20171107/inc1# Local Single instance incremental backup 2$ MySQL-s/tmp/mysql.sock-uroot-p123456-e"INSERT into TPCCDB.TB3 values (' 4 '), (' 5 '), (' 6 '), (' Second insert End ');"$ Innobackupex--defaults-file=/ETC/MY.CNF--user=root--password=123456--socket=/tmp/mysql.sock--no-timestamp--parallel=4--incremental-basedir=/backup/3306/20171107/inc1--incremental/backup/3306/20171107/inc2$ MySQL-s/tmp/mysql.sock-uroot-p123456-e"INSERT into TPCCDB.TB3 values (' 7 '), (' 8 '), (' 9 '), (' Third insert End ');"# #二进制日志恢复 $ mysql-s/tmp/mysql.sock-uroot-p123456-e"flush logs;"$ mysql-s/tmp/mysql.sock-uroot-p123456-e"INSERT into TPCCDB.TB3 values (' 10 '), (' 11 '), (' 12 '), (' Fourth insert End ');"$ mysql-s/tmp/mysql.sock-uroot-p123456-e"flush logs;"$ mysql-s/tmp/mysql.sock-uroot-p123456-e"INSERT into TPCCDB.TB3 values (' 13 '), (' 14 '), (' 15 '), (' Fifth insert end ');"#恢复 $ Innobackupex--apply-log--redo-only/backup/3306/20171107/full$ Innobackupex--apply-log--redo-only/backup/3306/20171107/full--incremental-dir=/backup/3306/20171107/inc1$ Innobackupex--apply-log/backup/3306/20171107/full--incremental-dir=/backup/3306/20171107/inc2$ Innobackupex--apply-log/backup/3306/20171107/full$Cat/backup/3306/20171107/inc2/Xtrabackup_binlog_info # #获取最后一次备份的LSN # # MySQL-bin.000004        1707$ ll-t/data/mysql/mysql-bin*# #查看有多少个需要导入的二进制文件-rw-r-----1MySQL MySQL461Nov7  .: A/data/mysql/mysql-bin.000006-rw-r-----1MySQL MySQL485Nov7  .: +/data/mysql/mysql-bin.000005-rw-r-----1MySQL MySQL2035Nov7  .: -/data/mysql/mysql-bin.000004$ mysqlbinlog--start-position=1707/data/mysql/mysql-bin.000004-VV >>/tmp/Load.sql $ mysqlbinlog/data/mysql/mysql-bin.000005-VV >>/tmp/Load.sql $ mysqlbinlog/data/mysql/mysql-bin.000006-VV >>/tmp/load.sql # #完全恢复#$ mysqlbinlog--stop-position=407/data/mysql/mysql-bin.000006-VV >>/tmp/load.sql # #指定点恢复#$ mysqlbinlog--stop-position=407/data/mysql/mysql-bin.000006-VV >>/tmp/load.sql # #误删除恢复 (stop before mis-operation) #$ Mysqlbinlog--start-position=1108/data/mysql/mysql-bin.000006-VV >>/tmp/load.sql # #误删除恢复 (continue after misoperation) $ service mysqld Stop $MV/data/mysql/data/mysql_bak_201711073 # #备份原数据目录 $mkdir-pv/data/MySQL $ innobackupex--copy-back/backup/3306/20171107/full$Chown-R mysql:mysql/data/mysql$/usr/local/mysql/bin/mysqld Start--skip-... (Relational binary log, requires only Super permissions to log on) $ MySQL-uroot-P123456mysql> source/tmp/load.sql $ service mysqld stop$/usr/local/mysql/bin/mysqld start

Xtrabackup2.4.8 backup, restore, restore Mysql5.7.19 real