物理备份(Xtrabackup)
Xtrabackup安装
#下载epel源wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-6.repo#安装依赖yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL#下载Xtrabackupwget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
备份方式(物理备份)
1)对于非innodb表(比如myisam)是直接锁表cp数据文件,属于一种温备。
2)对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。
3)备份时读取配置文件/etc/my.cnf
1.全量备份
#全备[root@db01 data]# innobackupex --user=root --password=1 /backup#避免时间戳,自定义路径名[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full#查看备份路径中的内容[root@db01 backup]# ll /backup/full#记录binlog文件名和binlog的位置点-rw-r----- 1 root root 21 Aug 16 06:23 xtrabackup_binlog_info #备份时刻,立即将已经commit过的内存中的数据页刷新到磁盘#备份时刻有可能会有其他数据写入,已备走的数据文件就不会再发生变化了#在备份过程中,备份软件会一直监控着redo和undo,一旦有变化会将日志一并备走-rw-r----- 1 root root 117 Aug 16 06:23 xtrabackup_checkpoints#备份类型(全备) backup_type = full-backuped #从哪个日志版本号开始 from_lsn = 0 #备份到哪个日志版本号 to_lsn = 1025046494 #最新的日志版本号 last_lsn = 1025046494 compact = 0 recover_binlog_info = 0#备份汇总信息-rw-r----- 1 root root 485 Aug 16 06:23 xtrabackup_info#备份的redo文件-rw-r----- 1 root root 2560 Aug 16 06:23 xtrabackup_logfile
全备的恢复
准备备份
将redo进行重做,已提交的写到数据文件,未提交的使用undo回滚,模拟CSR的过程
[root@db01 full]# innobackupex --user=root --password=1 --apply-log /backup/full
恢复备份步骤
前提1:被恢复的目录是空的
前提2:被恢复的数据库的实例是关闭的
#1. 停库[root@db01 full]# /etc/init.d/mysqld stop#2. 进入mysql目录[root@db01 full]# cd /application/mysql#删除data目录(在生产中可以备份一下)[root@db01 mysql]# rm -fr data/#3.先做redo和undo 模拟CSR [root@db01 mysql]# innobackupex --user=root --password=1 --apply-log /backup/2019-11-15_00-35-57#4.恢复data 在my.cnf里面必须有datadir [root@db01 mysql]# innobackupex --copy-back /backup/2019-11-15_00-35-57#授权[root@db01 mysql]# chown -R mysql.mysql /application/mysql/data/#启动MySQL[root@db01 mysql]# /etc/init.d/mysqld start
注意:不要时间戳就加上--no-timestamp
2.增量备份及恢复
备份方式
1)基于上一次备份进行增量
2)增量备份无法单独恢复,必须基于全备进行恢复
3)所有增量必须要按顺序合并到全备当中
#1.不使用之前的全备,执行一次全备[root@mysql-db01 ~]# innobackupex --user=root --password=1 --no-timestamp /backup/full
先模拟数据变化
mysql> create database inc1;mysql> use inc1mysql> create table inc1_tab(id int);mysql> insert into inc1_tab values(1),(2),(3);mysql> commit;mysql> select * from inc1_tab;
第一次增量备份
[root@db01 ~]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/inc1参数说明:--incremental:开启增量备份功能--incremental-basedir:上一次备份的路径
再次模拟数据变化
mysql> create database inc2;mysql> use inc2mysql> create table inc2_tab(id int);mysql> insert into inc2_tab values(1),(2),(3);mysql> commit;
inn第二次增量备份
[root@db01 ~]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/inc1/ /backup/inc2
增量恢复
#破坏数据mysql> drop database world;Query OK, 3 rows affected (0.04 sec)mysql> drop database world1;Query OK, 1 row affected (0.03 sec)mysql> drop database test;Query OK, 5 rows affected (0.03 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || backup || binlog || db01 || db02 || db1 || linux50 || mysql || performance_schema || shell || testa |+--------------------+11 rows in set (0.00 sec)[root@db01 full]# /etc/init.d/mysqld stopShutting down MySQL.... SUCCESS! [root@db01 full]# rm -fr /application/mysql/data/*
准备备份
**1.增量备份无法单独恢复,必须基于全备进行恢复 **
**2.所有增量必须要按顺序合并到全备当中 **
**3.分步骤进行--apply-log **
第一步:在全备中apply-log时,只应用redo,不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only /backup/full/backup_type = log-applied from_lsn = 0 to_lsn = 1025119769
第二步:合并inc1合并到full中,并且apply-log,只应用redo,不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full/from_lsn = 0 to_lsn = 1025140919
第三步:合并inc2合并到full中,redo和undo都应用
[root@db01 ~]# innobackupex --apply-log --incremental-dir=/backup/inc2/ /backup/full/from_lsn = 0 to_lsn = 1025168949
第四步:整体full执行apply-log,redo和undo都应用,模拟一次CSR
[root@db01 mysql]# innobackupex --apply-log /backup/full/
第五步: copy back
[root@db01 ~]# innobackupex --copy-back /backup/full/[root@db01 ~]# chown -R mysql.mysql /application/mysql/data/[root@db01 ~]# /etc/init.d/mysqld start
第六步:测试查看
[root@db01 backup]# mysql -uroot -p1mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || backup || binlog || db01 || db02 || db1 || linux50 || mysql || performance_schema || shell || test || testa || world || world1 |+--------------------+14 rows in set (0.00 sec)
3.差异备份及恢复
优点:
**1.备份的时候,方便 **
2.恢复的时候,也方便
缺点:
**重复数据多,占用磁盘空间大 **
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp /backup/full_1 backup_type = full-backuped from_lsn = 0 to_lsn = 1025176220
1).第一次差异备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_1 /backup/chayi1 [root@db01 backup]# cat chayi1/xtrabackup_checkpoints backup_type = incremental from_lsn = 1025176220 to_lsn = 1025202474
2) .第二次差异备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_1 /backup/chayi2 backup_type = incremental from_lsn = 1025176220 to_lsn = 1025208985
3) .第三次差异备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_1 /backup/chayi3
4) .第四次差异备份
[root@db01 backup]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_1 /backup/chayi4 backup_type = incremental from_lsn = 1025176220 to_lsn = 1025234244
恢复数据
#1.停数据库 [root@db01 backup]# /etc/init.d/mysqld stop #2.删除data目录或者备份 [root@db01 backup]# rm -fr /application/mysql/data/* #3.模拟CSR合并数据 full_1 + chayi1 + chayi2 + chayi3 + chayi4 1)full_1只做redo 不做undo [root@db01 backup]# innobackupex --apply-log --redo-only /backup/full_1/ 2)将chayi4合并到full_1 redo undo 都做 [root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/chayi4 /backup/full_1/ 3)将full_1 redo undo 都做 [root@db01 backup]# innobackupex --apply-log /backup/full_1/ #4.copy back [root@db01 backup]# innobackupex --copy-back /backup/full_1/ [root@db01 backup]# chown -R mysql.mysql /application/mysql/data/
4.实战:企业级增量恢复实战
背景:
某大型网站,mysql数据库,数据量500G,每日更新量100M-200M
备份策略:
xtrabackup,每周六0:00进行全备,周一到周五及周日00:00进行增量备份。
故障场景:
周三下午2点出现数据库意外删除表操作。
如何恢复?
思路:
步骤:
1.创建数据
mysql> create database mysqldump;mysql> use mysqldumpmysql> create table mysqldump(id int);[root@db01 ~]# sh insert.shmysql> select count(*) from mysqldump.mysqldump;+----------+| count(*) |+----------+| 105 |+----------+
2.全备
[root@db01 ~]# innobackupex --user=root --password=1 --no-timestamp /backup/full_`date +%F`[root@db01 ~]# ll /backup/total 4drwxr-x--- 18 root root 4096 Nov 14 19:49 full_2019-11-14
3.模拟增量数据
模拟写数据脚本[root@db01 ~]# vim insert.sh#!/bin/bash while true;do mysql -uroot -p1 -e 'insert into mysqldump.mysqldump values(1);commit;' sleep 2 done[root@db01 ~]# sh insert.sh
4.增备
[root@db01 ~]# date +%F -s '+1day'2019-11-15[root@db01 ~]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full_`date +%F -d "1 day ago"` /backup/inc_`date +%F`[root@db01 ~]# ll /backup/total 8drwxr-x--- 18 root root 4096 Nov 14 19:49 full_2019-11-14drwxr-x--- 18 root root 4096 Nov 15 19:51 inc_2019-11-15
5.模拟增量数据
[root@db01 ~]# sh insert.shmysql> select count(*) from mysqldump.mysqldump;+----------+| count(*) |+----------+| 127 |+----------+
6.增备
[root@db01 ~]# date +%F -s '+1day'2019-11-16[root@db01 ~]# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/inc_`date +%F -d "1 day ago"` /backup/inc_`date +%F`[root@db01 ~]# ll /backup/total 12drwxr-x--- 18 root root 4096 Nov 14 19:49 full_2019-11-14drwxr-x--- 18 root root 4096 Nov 15 19:51 inc_2019-11-15drwxr-x--- 18 root root 4096 Nov 16 19:52 inc_2019-11-16
7.模拟增量数据
[root@db01 ~]# sh insert.shmysql> select count(*) from mysqldump.mysqldump;+----------+| count(*) |+----------+| 134 |+----------+
8.到时候 周三下午14点:模拟故障删库,删表
mysql> drop database linux50;Query OK, 6 rows affected (0.05 sec)mysql> drop database backup;Query OK, 2 rows affected (0.03 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || binlog || db1 || mysql || performance_schema || shell || test || world |+--------------------+8 rows in set (0.00 sec)
9.恢复步骤
1)找到drop语句执行的位置点 , 23:00之后到删库的用bin_log恢复
#取最后一个增备的终点,也就是要恢复的起点[root@db01 ~]# cat /backup/inc_2019-11-16/xtrabackup_binlog_info mysql-bin.0000012238#结束位置点[root@db01 ~]# mysql -uroot -p1 -e "show binlog events in 'mysql-bin.000001'"|grep -i 'drop database'mysql-bin.0000013356Query16695drop database mysqldump#截取二进制[root@db01 tmp]#mysqlbinlog -uroot -p123 --start-position=2238 --stop-position=3356 /application/mysql/data/mysql-bin.000017 > /tmp/inc.sql[root@db01 ~]# ll /tmp/-rw-r--r-- 1 root root 5876 Nov 16 19:55 inc.sql
2)停库
#停库[root@db01 ~]# /etc/init.d/mysqld stop#进入mysql目录[root@db01 ~]# cd /application/mysql#清空data目录(在生产中可以备份一下)[root@db01 mysql]# rm -rf data/*[root@db01 mysql]# mv /application/mysql/data/ /tmp/
3)在全备full中 apply-log时,只应用redo,不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only /backup/full_2019-11-14
4)合并inc_2019-11-15合并到full中,并且--apply-log,只应用redo,不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-11-15 /backup/full_2019-11-14
5)合并inc_2019-11-16合并到full中,redo和undo都应用
[root@db01 ~]# innobackupex --apply-log --incremental-dir=/backup/inc_2019-11-16 /backup/full_2019-11-14
6)整体full执行apply-log,redo和undo都应用
[root@db01 mysql]# innobackupex --apply-log /backup/full_2019-11-14
7)copy-back
[root@db01 ~]# innobackupex --copy-back /backup/full_2019-11-14[root@db01 ~]# chown -R mysql.mysql /application/mysql/data/[root@db01 ~]# /etc/init.d/mysqld startmysql> select count(*) from mysqldump.mysqldump;+----------+| count(*) |+----------+| 127 |+----------+
8)恢复binlog数据
#不记录二进制日志mysql> set sql_log_bin=0;#恢复增量数据#方法一:mysql> source /tmp/inc.sql#方法二:[root@db02 mysql]# mysql -uroot -p1 < /tmp/inc.sql#查看表mysql> select count(*) from mysqldump.mysqldump;+----------+| count(*) |+----------+| 134 |+----------+
示例:
备份策略是每天一次全备,每小时一次增备
crontab
00 00 * * * 全备的脚本
00 */1 * * * 增备的脚本
合并数据的脚本
#全备增备的脚本[root@db01 ~]# vim beifen.sh#!/bin/bash date=`date +%F-%H`date1=`date +%F-%H |awk -F '-' '{print $NF}'`date2=`date +%F-%H -d '-1hour'`dir=backupif [ ! -d /$dir ];then mkdir /$dirfiif [ $date1 -eq 0 ];then innobackupex --user=root --password=1 --no-timestamp /$dir/full_${date} &>/dev/nullelif [ $date1 -eq 1 ];then innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/$dir/full_${date2} /$dir/inc1_${date} &>/dev/nullelse innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/$dir/inc1_${date2} /$dir/inc1_${date} &>/dev/nullfi[root@db01 ~]# crontab -l#每天凌晨全备,隔一个小时增备数据库0 0 * * * /usr/bin/sh /root/zengbei.sh &> /dev/null[root@db01 ~]# ll /backup/total 4drwxr-x--- 11 root root 297 Nov 17 00:00 full_2019-11-17-00drwxr-x--- 11 root root 4096 Nov 17 01:04 inc1_2019-11-17-01[root@db01 ~]# cat /backup/full_2019-11-17-00/xtrabackup_checkpoints backup_type = full-backupedfrom_lsn = 0to_lsn = 15630976last_lsn = 15630976compact = 0recover_binlog_info = 0[root@db01 ~]# cat /backup/inc1_2019-11-17-01/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 15630976to_lsn = 15630976last_lsn = 15630976compact = 0recover_binlog_info = 0