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
/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;