mysql备份工具命令xtra_第49章 MySQL主流备份工具之XtraBackup详解

49.2

图 49-1

图 49-3

图 49-4

图 49-5

49.3　命令行选项

# 该解密命令可以同时解密一个目录下的所有加密文件，解密之后删除加密文件，当然也可以不删除

[root@localhost~]# for i in `find . -iname "*\.xbcrypt"`; do xbcrypt –d\ --encrypt-key-file=/data/backups/keyfile --encrypt-algo=AES256 < $i > $(dirname\ $i)/$(basename $i .xbcrypt) && rm $i; done

# 或者可以直接使用如下命令来解密(注意：innobackupex --decrypt=选项会删除加密文件，解密文件被直接放到与加密文件相同的目录下)

[root@localhost~]# innobackupex --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK\ +Yzfs" /data/backups/2015-03-18_08-31-35/

49.4.1　完全备份与恢复

[root@localhost~]# mkdir /data/backup/test_backup

[root@localhost~]# innobackupex --defaults-file=/home/ mysql/conf/my1.cnf --user=admin\ --password=password --no-timestamp /data/backup/test_backup/

......

170523 17:49:48 completed OK! # 看到类似于这样的输出信息表示备份执行成功

[root@localhost test_backup]# innobackupex --apply-log ./

......

170523 17:57:52 completed OK! # 看到类似于这样的输出信息表示--apply-log操作执行成功

[root@localhost test_backup]# killall mysqld

[root@localhost test_backup]# rm -rf /data/mysqldata1/ {innodb_log,innodb_ts,mydta,undo}/*

[root@localhost~]# innobackupex --defaults-file= /home/mysql/conf/my1.cnf --copy-back ./

......

170523 18:05:34 completed OK! # 看到类似于这样的输出信息表示--copy-back操作执行成功

[root@localhost test_backup]# chown mysql.mysql /data -R

[root@localhost test_backup]# mysqld_safe --defaults-file=/home/mysql/conf/my1.cnf &

[root@localhost test_backup]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uqogir_env\ -p'password'

......

mysql> show databases;

......

49.4.2　增量备份与恢复

1．完全备份和增量备份

[root@localhost test_backup]# mkdir /data/backup/test_backup2

[root@localhost test_backup]# innobackupex --defaults-file =/home/mysql/conf/my1.cnf\ --user=admin --password=password --no-timestamp /data/backup/test_backup2

......

170523 18:23:11 completed OK!

mysql> create database test;

Query OK, 1 row affected (0.01 sec)

mysql> use test;

Database changed

mysql> create table test(id int auto_increment not null primary key,name varchar(20));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(name) values('test1');

Query OK, 1 row affected (0.01 sec)

......

mysql> select * from test;

......

4 rows in set (0.00 sec)

[root@localhost test_backup]# innobackupex --defaults-file =/home/mysql/conf/my1.cnf\ --user=admin --password=password --no-timestamp –incremental -basedir=/data/backup/\ test_backup2 --incremental /data/backup/incremental_one

......

170523 18:32:25 completed OK!

mysql> insert into test(name) values('test5');

Query OK, 1 row affected (0.00 sec)

......

mysql> select * from test;

......

8 rows in set (0.00 sec)

[root@localhost test_backup]# innobackupex --defaults-file =/home/mysql/conf/my1.cnf\ --user=admin --password=password --no-timestamp --incremental-basedir=/data/backup/\ incremental_one --incremental /data/backup/incremental_two

......

170523 18:37:01 completed OK!

[root@localhost test_backup]# innobackupex --defaults-file=/home/mysql/conf/my1.cnf\ --user=admin --password=password --no-timestamp --incremental-basedir=/data/backup/\ test_backup2 --incremental /data/backup/incremental_third

2．增量备份恢复

[root@localhost test_backup]# killall mysqld

......

[root@localhost test_backup]# rm -rf /data/mysqldata1/{innodb_log,innodb_ts,mydata,\ undo}/*

[root@localhost test_backup]# cd /data/backup/test_backup2

# 为了后续演示的需要，先对完全备份目录进行备份

[root@localhost test_backup2]# cp -ar /data/backup/test_backup2 /data/backup/test_backup2.bak

# 执行完全备份应用前滚日志，不应用回滚日志，这里可以使用--use-memory选项指定用于崩溃恢复的内存大小为1GB，避免占用过多的内存

[root@localhost test_backup2]# innobackupex --apply-log --redo-only --use-memory=1G ./

......

170523 18:48:57 completed OK!

# 在完全备份的基础上执行第一次增量备份--apply-log操作

[root@localhost test_backup2]# innobackupex --apply-log --redo-only --use-memory=1G\ /data/ backup/test_backup2 --incremental-dir=/data/backup/incremental_one

......

170523 18:49:32 completed OK!

# 在执行了--apply-log操作的第一次增量备份的完全备份目录中再对第二次增量备份执行该操作，因为是最后一次增量备份，所以不需要加--redo-only选项。请注意，不加--redo-only选项时会有创建redo日志的动作，但是却创建到了第二次增量备份的目录下

[root@localhost test_backup2]# innobackupex --apply-log --use-memory=1G /data/backup/\ test_backup2 --incremental-dir=/data/backup/incremental_two

......

170523 18:51:07 completed OK!

[root@localhost test_backup2]# ls -lh

......

[root@localhost test_backup2]# ls -lh /data/backup/ incremental_two/ib_logfile1

......

# 现在对完全备份目录再执行一次--apply-log操作，就会在该目录下生成redo log文件

[root@localhost test_backup2]# innobackupex --apply-log --use-memory=1G /data/backup/\ test_backup2

......

170523 18:54:40 completed OK!

[root@localhost test_backup2]# ls -lh

......

[root@localhost test_backup2]# innobackupex --defaults-file=/home/mysql/conf/my1.cnf\ --copy-back ./

......

170523 18:56:26 completed OK!

[root@localhost test_backup2]# chown mysql.mysql /data/ -R

[root@localhost test_backup2]# mysqld_safe --defaults-file=/home/mysql/conf/my1.\ cnf &

......

[root@localhost test_backup2]# mysql --defaults-file =/home/mysql/conf/my1.cnf\ -uqogir_env -p'password'

......

mysql> use test

mysql> select * from test;

......

8 rows in set (0.01 sec)

49.4.3　基于时间点的恢复

1．执行完全备份、binlog备份

[root@localhost~]# ll /archive/mysqldata1/binlog/

......

# 先创建一个用于备份的目录

[root@localhost~]# mkdir /data/backup/binlogserver/

[root@localhost~]# cd /data/backup/binlogserver/

# 启动binlog server

[root@localhost~]# mysqlbinlog --host=10.10.30.241 --password=password --user=admin\ --read-from-remote-server --raw --stop-never mysql-bin.000001 &

# 查看binlog server备份的binlog文件

[root@localhost binlogserver]# ll

total 4

-rw-r----- 1 root root 123 May 25 17:47 mysql-bin.000001

# 先创建完全备份目录，或者清空已有的目录

[root@localhost binlogserver]# mkdir /data/backup/full -p

# 执行完全备份

[root@localhost binlogserver]# innobackupex --defaults-file =/home/mysql/conf/my1.cnf\ --user=admin --password=password --no-timestamp /data/backup/ full/

......

170525 17:48:01 completed OK!

[root@localhost binlogserver]# cd /data/backup/full/

[root@localhost full]# cat xtrabackup_binlog_info

mysql-bin.000001 154 2016f827-2d98-11e7-bb1e-00163e407cfb:1-1878711,

402872e0-33bd-11e7-8e8d-00163e4fde29:1-180732,

4e5fb89f-3fa9-11e7-9e0c-00163e4fde29:1-10,

5fe70ca9-3fab-11e7-bc48-00163e4fde29:1-60,

8440023c-3f9f-11e7-8f52-00163e4fde29:1-10

# 为了在后续的恢复过程中制造点“小插曲”，在插入数据前先刷新binlog

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql> use test;

Database changed

mysql> show tables;

......

2 rows in set (0.00 sec)

mysql> select * from test;

......

8 rows in set (0.00 sec)

mysql> delete from test where id in (2,4,6,8);

Query OK, 4 rows affected (0.00 sec)

mysql> select * from test;

......

4 rows in set (0.00 sec)

# 为了在后续的恢复过程中制造点“小插曲”，在插入数据前先刷新binlog

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(name) values('test9'), ('test10'),('test11'),('test12');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test;

......

8 rows in set (0.00 sec)

mysql> drop table test;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

......

1 row in set (0.00 sec)

mysql> show binary logs;

......

3 rows in set (0.00 sec)

2．基于时间点的恢复步骤演示

# 在恢复服务器上创建或清空/data/backup/full目录

[root@localhost ~]# rm -rf /data/backup/full/

[root@localhost ~]# mkdir /data/backup/full -p

# 将备份服务器(备份binlog的服务器)上的备份数据打包并使用scp命令传送

[root@localhost full]# tar zcf backup.tar.gz *

[root@localhost full]# ls -lh backup.tar.gz

-rw-r--r-- 1 root root 2.1G May 25 18:13 backup.tar.gz

[root@localhost full]# scp backup.tar.gz 10.10.30.217:/data/backup/full/

# 将备份服务器上的binlog server的备份文件打包并发送到恢复服务器上

[root@localhost binlogserver]# tar zcf binlog.tar.gz *

[root@localhost binlogserver]# scp binlog.tar.gz 10.10.30.217:/data/backup/full/

# 停止MySQL

[root@localhost ~]# killall mysqld

[root@localhost ~]# cp -ar /data/backup/full/ /data/backup/full.bak

[root@localhost ~]# cd /data/backup/full

[root@localhost full]# ll

......

# 解压缩备份包

[root@localhost full]# tar xf backup.tar.gz

# 对备份目录执行--apply-log操作，并使用--copy-back选项将恢复之后的数据文件复制到相应的数据目录下

[root@localhost full]# innobackupex --apply-log --use-memory =1G ./

......

170525 18:26:36 completed OK!

[root@localhost full]# rm -rf /data/mysqldata1/{innodb_ts, innodb_log,mydata,undo,relaylog,\ tmpdir,binlog}/*

[root@localhost full]# innobackupex --defaults-file=/home/mysql/conf/my1.cnf --copy-back ./

......

170525 18:30:20 completed OK!

# 启动MySQL并登录数据库查看数据

[root@localhost full]# chown mysql.mysql /data/ -R

[root@localhost full]# mysqld_safe --defaults-file=/home/mysql/conf/my1.cnf --skip-\ slave-start --user=mysql &

[root@localhost full]#

[root@localhost full]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uqogir_env\ -p'password'

......

mysql> use test

Database changed

mysql> show tables;

......

2 rows in set (0.00 sec)

mysql> select * from test;

......

8 rows in set (0.01 sec)

# 解压备份binlog

[root@localhost full]# tar xvf binlog.tar.gz

......

# 执行解析，带上之前完全备份中的binlog pos位置mysql-bin.000001 pos 154

[root@localhost full]# mysqlbinlog -vv --start-position=154 mysql-bin.000001 mysql-bin.\ 000002 mysql-bin.000003 > a.sql

mysql> use test;

Database changed

mysql> source /data/backup/full/a.sql;

......

mysql> select * from test;

......

8 rows in set (0.00 sec)

mysql> show binary logs;

......

1 row in set (0.00 sec)

[root@localhost binlog2sql]# ./binlog2sql.py -h 10.10.30.217 -uadmin –ppassword\ --start-file='mysql-bin.000001' --start-position=583 --stop-position=887

......

[root@localhost binlog2sql]# ./binlog2sql.py -h 10.10.30.217 -uadmin –ppassword\ --start-file='mysql-bin.000001' --start-position=583 --stop-position=887 --flashback

......

mysql> DELETE FROM `test`.`test` WHERE `id`=23 AND `name`='test12' LIMIT 1; #start 583 end 887 time 2017-05-25 17:50:02

Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM `test`.`test` WHERE `id`=21 AND `name`='test11' LIMIT 1; #start 583 end 887 time 2017-05-25 17:50:02

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

......

6 rows in set (0.00 sec)

[root@localhost binlog2sql]# mysqldump -h 10.10.30.217 -uadmin –ppassword –single\ -transaction --master-data=2 --set-gtid-purged=OFF test test > test.sql

[root@localhost binlog2sql]# scp test.sql 10.10.30.241:/tmp

mysql> use test

Database changed

mysql> source /tmp/test.sql;

Query OK, 0 rows affected (0.00 sec)

......

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;

......

6 rows in set (0.00 sec)

49.4.4　搭建主从复制架构

1．传统复制

[root@localhost~]# innobackupex --defaults-file=/home/mysql/conf/my1.cnf --user=admin\ --password=password --no-timestamp --stream='tar' ./ | ssh root@10.10.30.250 "cat - >\ /data/backup/backup_`date +%Y%m%d`.tar"

# 看到最后输出信息类似于170228 19:00:21 completed OK!，则表示备份成功。注意：在传统复制模式下，如果slave_parallel_workers参数不为0，则使用XtraBackup 2.4.x进行备份时无法使用--slave-info选项，报错：The --slave-info option requires GTID enabled for a multi-threaded slave.。这就意味着在传统复制架构中，如果从库启用了多线程复制，2.4.x版本的innobackupex命令就无法使用--slave-info选项，不能使用--slave-info选项就意味着该备份不能用于搭建新的从库(因为在备份中没有对应主库的binlog pos信息)

[root@localhost~]# scp /home/mysql/conf/my1.cnf root@10.10.30.250:/tmp/

[root@localhost backup]# cd /data/backup/

[root@localhost backup]# ll

......

[root@localhost backup]# tar xf backup_20170508.tar

[root@localhost backup]# innobackupex --defaults-file=backup-my.cnf --use-memory=1G\ --apply-log /data/backup/

......

# 看到最后输出信息类似于170228 19:14:31 completed OK!，则表示--apply-log操作执行成功

：

[root@localhost backup]# cat xtrabackup_binlog_info

mysql-bin.000001 154

# 关闭当前运行的MySQL

[root@localhost~]# killall mysqld

# 清除当前的datadir、redo日志、共享表空间、binlog、undo日志

[root@localhost~]# rm -rf /archive/mysqldata1/binlog/* /data/mysqldata1/{innodb_log,\ innodb_ts,undo, mydata}/*

# 执行--move-back操作

[root@localhost~]# innobackupex --defaults-file=/tmp/my1.cnf --move-back /data/backup/

# 看到最后输出信息类似于170228 19:37:48 completed OK!，则表示--apply-log操作执行成功

# 修改宿主、属组

[root@localhost~]# chown mysql.mysql /data -R

[root@localhost~]# mysqld_safe --defaults-file=/home/mysql/conf/my1.cnf --user=mysql &

# 确定MySQL是否运行

[root@localhost~]# pgrep mysqld

[root@localhost~]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uadmin –ppassword\ -e "change master to master_host='10.10.30.241',master_user='qfsys',master_password=\ 'password',master_port=3306, master_connect_retry=10,master_log_file='mysql-bin.000001',\ master_log_pos=154;start slave;"

# 查看I/O线程和SQL线程的状态是否为Yes

[root@localhost~]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uadmin –ppassword\ -e "show slave status\G"

......

2．GTID复制

[root@localhost backup]# cat xtrabackup_binlog_info

mysql-bin.000003 2830 2016f827-2d98-11e7-bb1e-00163e407cfb:1-7

# 如果有多个GTID值，那么把第一行的最后一个字段和后边的行拼接成一个gtid_slave_pos值，比如

[root@localhost backup]# cat xtrabackup_binlog_info

mysql-bin.000003 2830 2016f827-2d98-11e7-bb1e-00163e407cfb:1-7,

a4d2a7dc-2026-11e7-bb68-00163e407cfc:1-4,

a4d2a7dc-2026-11e7-bb68-00163e407cfd:1-4

# 拼接为

2016f827-2d98-11e7-bb1e-00163e407cfb:1-7,a4d2a7dc-2026-11e7-bb68-00163e407cfc:1-4,a4d2a7dc-2026-11e7-bb68-00163e407cfd:1-4

[root@localhost~]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uadmin –ppassword\ -e "reset master;set global gtid_purged='2016f827-2d98-11e7-bb1e-00163e407cfb:1-7';change\ master to master_host= '10.10.30.241',master_user='qfsys',master_password='password',\ master_port=3306, master_connect_retry=10,master_auto_position=1;start slave;"

# 查看I/O线程和SQL线程的状态是否为Yes

[root@localhost~]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uadmin –ppassword\ -e "show slave status\G"

......

49.4.5　克隆从库

[root@localhost~]# innobackupex --defaults-file=/home/mysql/conf/my1.cnf --slave-info\ --user=admin --password=password --no-timestamp --stream=tar ./ | ssh 10.10.30.217 "cat - >\ /data/backup/backup_`date +%Y%m%d`.tar"

......

170526 09:59:43 completed OK!

[root@localhost backup]# ll

......

[root@localhost backup]# mkdir recovery

[root@localhost backup]# cp -ar backup_20170526.tar recovery/

[root@localhost backup]# cd recovery/

[root@localhost recovery]# tar xf backup_20170526.tar

[root@localhost recovery]# cat xtrabackup_slave_info # 加了--slave-info选项时，innobackupex在备份时会额外多生成一个xtrabackup_slave_info文件，在该文件中记录了一条CHANGE MASTER TO语句，以及使用set global gtid_purged=''的形式记录了当前数据对应于主库(不是从库)的GTID SET(如果没有启用GTID，则这里记录的CHANGE MASTER TO语句中带有备份数据对应于主库的binlog pos位置)，这些信息是后续指向主库的复制位置

mysql> SET GLOBAL gtid_purged='2016f827-2d98-11e7-bb1e-00163e407cfb:1-1878711, 402872e0-\ 33bd-11e7-8e8d-00163e4fde29:1-180732, 4e5fb89f-3fa9-11e7-9e0c-00163e4fde29:1-10,5fe70ca9\ -3fab-11e7-bc48-00163e4fde29:1-60, 799ef59c-4126-11e7-83ce-00163e407cfb:1-59154, 8440023c\ -3f9f-11e7-8f52-00163e4fde29:1-10';

mysql> CHANGE MASTER TO MASTER_AUTO_POSITION=1

[root@localhost recovery]# innobackupex --apply-log --use-memory=1G ./

......

170526 10:32:53 completed OK!

[root@localhost recovery]# killall mysqld

[root@localhost recovery]# rm -rf /data/mysqldata1/{innodb_ts,innodb_log,mydata,\ undo,relaylog,tmpdir,binlog}/*

[root@localhost recovery]# innobackupex --defaults-file=/home/mysql/conf/my1.cnf\ --copy-back ./

......

170526 10:35:53 completed OK!

[root@localhost recovery]# chown mysql.mysql /data/ -R

# 启动mysqld。注意：由于数据是从其他从库备份的，复制配置信息都会被一并备份，直接启动该实例会自动启动复制线程，所以这里需要加上--skip-slave-start选项

[root@localhost recovery]# mysqld_safe --defaults-file=/home/mysql/conf/my1.cnf --user\ =mysql --skip-slave-start &

# 如果未加--skip-slave-start选项启动实例，则可能会报出如下错误(因为mysqld在启动时已经初始化了一个从000001开始的中继日志，而备份数据中的mysql.slave_relay_log_info表记录的是数据源从库的中继日志位置，这样就可能会造成冲突，从而导致启动复制时发生错误)

mysql> start slave;

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

mysql> reset slave all;

Query OK, 0 rows affected (0.01 sec)

# 先清理数据文件中原始的GTID 信息，避免在设置gtid_purged变量的值时报错：ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

mysql> reset master;

Query OK, 0 rows affected (0.02 sec)

mysql> SET GLOBAL gtid_purged='2016f827-2d98-11e7-bb1e-00163e407cfb:1-1878711, 402872e0-33bd-11e7-8e8d-00163e4fde29:1-180732, 4e5fb89f-3fa9-11e7-9e0c-00163e4fde29:1-10, 5fe70ca9-3fab-11e7-bc48-00163e4fde29:1-60, 799ef59c-4126-11e7-83ce-00163e407cfb:1-59154, 8440023c-3f9f-11e7-8f52-00163e4fde29:1-10';

Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO master_host='10.10.30.241',master_user='qfsys',master_ password='password',master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

......

[root@localhost recovery]# killall mysqld

[root@localhost recovery]# mysqld_safe --defaults-file=/home/mysql/conf/my1.cnf --user\ =mysql &