Xtrabackup 备份恢复

Xtrabackup 备份恢复

​ mysql 5.1 – xtrabackup2.0
​ mysql5.6 --xtrabackup 2.2
​ mysql5.7 --xtrabackup2.4
​ mysql8.0 --xtrabackup8.0

mysql 5.7以下版本，可以采用percona xtrabackup 2.4版本
mysql 8.0以上版本，可以采用percona xtrabackup 8.0版本

一、创建备份用户

#创建备份用户，并赋予相应的权限
CREATE USER 'bkuser'@'%' IDENTIFIED BY 'bkuser';

grant reload,lock tables,replication client,create tablespace,super,process on *.* to 'bkuser'@'%';

flush privileges;

二、全量备份恢复

1、全量备份

#export PATH=$PATH:/home/mysql/tools/usr/local/xtrabackup/bin
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf --user=bkuser --password=bkuser --socket=/home/mysql/mysql.sock --no-timestamp /home/mysql/tmp/xtrbk/one

2、全量恢复

①恢复日志
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf  --apply-log /home/mysql/tmp/xtrbk/one

②停库
mysqladmin -uroot -p -S /home/mysql/mysql.sock shutdown

③删除 datadir 和 innodb_log_group_home_dir
innodb_log_group_home_dir  = /home/mysql/mysqllog/innodb
datadir                    = /home/mysql/mysqldata

rm -rf /home/mysql/mysqllog/innodb/
rm -rf /home/mysql/mysqldata/

④将备份数据复制到数据文件目录中
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf --copy-back /home/mysql/tmp/xtrbk/one

三、增量备份恢复

1、增量备份

①创建基础备份one
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf --user=bkuser --password=bkuser --socket=/home/mysql/mysql.sock --no-timestamp /home/mysql/tmp/xtrbk/one

②增加数据并创建增量备份one_incre
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf --user=bkuser --password=bkuser --socket=/home/mysql/mysql.sock --incremental --incremental-basedir=/home/mysql/tmp/xtrbk/one --no-timestamp /home/mysql/tmp/xtrbk/one_incre

③增加数据并创建增量备份two_incre
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf --user=bkuser --password=bkuser --socket=/home/mysql/mysql.sock --incremental --incremental-basedir=/home/mysql/tmp/xtrbk/one_incre --no-timestamp /home/mysql/tmp/xtrbk/two_incre

2、增量恢复

①恢复基础备份（一定要加 --redo-only 参数，该参数只应用 xtrabackup 日志中已经提交的事务数据，不回滚还未提交的数据）
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf  --apply-log --redo-only /home/mysql/tmp/xtrbk/one

②将增量备份one_incre应用到基础备份one
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf  --apply-log --redo-only /home/mysql/tmp/xtrbk/one --incremental-dir=/home/mysql/tmp/xtrbk/one_incre

③将增量备份two_incre应用到基础备份one
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf  --apply-log --redo-only /home/mysql/tmp/xtrbk/one --incremental-dir=/home/mysql/tmp/xtrbk/two_incre

④最后进行一次 apply 操作，回滚未提交的数据
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf  --apply-log /home/mysql/tmp/xtrbk/one

⑤停库
mysqladmin -uroot -p -S /home/mysql/mysql.sock shutdown

⑥删除 datadir 和 innodb_log_group_home_dir
innodb_log_group_home_dir  = /home/mysql/mysqllog/innodb
datadir                    = /home/mysql/mysqldata

rm -rf /home/mysql/mysqllog/innodb
rm -rf /home/mysql/mysqldata

⑦将备份数据复制到数据文件目录中
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf --copy-back /home/mysql/tmp/xtrbk/one

四、克隆slave

①备份slave
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf --user=bkuser --password=bkuser --socket=/home/mysql/mysql.sock --slave-info --safe-slave-backup --no-timestamp /home/mysql/tmp/xtrbk/cloneslave

②在slave主机上进行还原
/home/mysql/tools/usr/local/xtrabackup/bin/innobackupex --defaults-file=/home/mysql/my.cnf  --apply-log --redo-only /home/mysql/tmp/xtrbk/cloneslave

③传送到新的 newslave主机上并复制到指定目录
scp -R /home/mysql/tmp/xtrbk/cloneslave newslave@'192.168.136.130':/home/mysql/tmp/xtrbk/cloneslave
cp -r /home/mysql/tmp/xtrbk/cloneslave/* ~/mysqldata

④复制slave my.cnf 到 newslave 并修改 server_id
scp /home/mysql/my.cnf new@'192.168.136.130':/home/mysql/my.cnf

⑤在master 创建同步账号
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.136.130' IDENTIFIED BY 'repl';

⑥查看 xtrabackup_slave_info
[newslave@localhost cloneslave]$ cat xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000002', MASTER_LOG_POS=2056;

⑦启库并登陆
/home/mysql/mysql/bin/mysqld --defaults-file=/home/mysql/my.cnf &
mysql -uroot -p -S /home/mysql/mysql.sock

⑧进行 change master to
change master to master_host='192.168.136.131',
master_user='repl',
master_password='repl',
master_log_file='mysql-binlog.000002',
master_log_pos=2056;

⑨启动同步
start slave;