第1章 MySQL物理备份工具xtrabackup
1.1 xtrabackup介绍
Percona公司的备份工具，性能比较高，物理备份工具。
1.1.1 xtrabackup特点
xtrabackup是物理备份工具，在同级数据量基础上都要比逻辑备份性能好的多，特别是在数据量比较大的时候体现更加明显。
1.1.2 xtrabackup备份方式
- 拷贝数据文件
- 拷贝数据页
1.1.3 xtrabackup备份原理
- 对于Innodb表，可以实现热备
- 在数据库还有修改操作的时刻直接将数据文件中的数据页备份，此时备份的数据对于当前MySQL来讲是不一致的（各个文件的LSN号码不一致）
- 将备份过程中的redo和undo一并备份
- 只要保证备份的数据页和LSN能和redo的LSN匹配，将来恢复的就是一致的数据
- 对于Myisam表，实现自动锁表拷贝文件
1.2 安装xtrabackup
1.2.1 安装依赖库
[[email protected] ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo [[email protected] ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
1.2.2 下载xtrabackup
提示：官网下载地址：https://www.percona.com/downloads/XtraBackup/LATEST/
[[email protected] ~]# cd /server/tools/ [[email protected] tools]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
1.2.3 安装xtrabackup
[[email protected] tools]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
1.3 备份命令
1.3.1 全库备份
依赖于/etc/my.cnf文件（basedir、datadir、socket等）：
[[email protected] ~]# innobackupex --user=root --password=123456 --use-memory=32M --no-timestamp /backup/xfull
- 命令说明：
- --user：指定登录MySQL的用户
- --password：指定登录用户的密码
- --use-memory：指定备份时使用内存的大小
- --no-timestamp：备份文件不加时间戳标记
- /backup/xfull：备份文件的路径
1.3.2 增量备份
可以使用binlog作为增量
自带的增量备份时基于上次备份后的变化的数据页，还要备份在备份过程中的undo和redo变化
[[email protected] ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xfull /backup/xinc1
- 命令说明：
- --incremental：开启增量备份开关
- --incremental-basedir：指定基于增量的备份文件
- /backup/xinc1：增量备份的目录路径
1.4 xtrabackup全库备份还原案例
1.4.1 全备备份
[[email protected] ~]# mkdir -p /backup/xfull [[email protected] ~]# innobackupex --user=root --password=123456 --use-memory=32M --no-timestamp /backup/xfull
1.4.2 恢复数据前的准备(合并xtabackup_log_file和备份的物理文件)
[[email protected] ~]# innobackupex --apply-log --use-memory=32M /backup/xfull
1.4.3 关闭数据库
[[email protected] ~]# /etc/init.d/mysqld stop # 或强制停止数据库 Kill -9 pid
1.4.4 模拟数据损坏
[[email protected] ~]# mv /usr/local/mysql/data/ /tmp/
1.4.5 数据库还原
1.4.5.1 方法一：使用cp命令
[[email protected] ~]# mkdir -p /usr/local/mysql/data [[email protected] ~]# cp -a /backup/xfull/* /usr/local/mysql/data/ [[email protected] ~]# chown -R mysql.mysql /usr/local/mysql/data/
1.4.5.2 方法二：使用--copy-back
[[email protected] ~]# innobackupex --copy-back /backup/xfull/ [[email protected] ~]# chown -R mysql.mysql /usr/local/mysql/data/
1.4.6 启动并查看还原信息
[[email protected] ~]# /etc/init.d/mysqld start mysql> show databases; +------------------------------+ | Database | +------------------------------+ | information_schema | | #mysql50#2017-11-23_09-39-48 | | leon | | mysql | | performance_schema | | test | | world | +------------------------------+ 7 rows in set (0.22 sec)
1.5 xtrabackup增量备份还原案例
1.5.1 进行全库备份
基于4.4上的备份。
1.5.2 模拟数据库数据变化
# 第一次增量备份前操作 mysql> use leon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into test values(7,'gongzhu',28); Query OK, 1 row affected (0.39 sec) mysql> update test set name='dawang' where id=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | leon | 18 | | 2 | shadow | 20 | | 3 | shuaige | 26 | | 4 | xiaofang | 19 | | 5 | dawang | 28 | | 6 | guowang | 35 | | 7 | gongzhu | 28 | +----+----------+------+ 7 rows in set (0.00 sec)
# 第二次增量备份前操作 mysql> use leon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into test values(8,'xiaoming',30); Query OK, 1 row affected (0.13 sec) mysql> update test set name='xiaodi' where id=5; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | leon | 18 | | 2 | shadow | 20 | | 3 | shuaige | 26 | | 4 | xiaofang | 19 | | 5 | xiaodi | 28 | | 6 | guowang | 35 | | 7 | gongzhu | 28 | | 8 | xiaoming | 30 | +----+----------+------+ 8 rows in set (0.10 sec)
1.5.3 进行增量备份
1.5.3.1 第一次增量备份
[[email protected] ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xfull /backup/xinc1
1.5.3.2 第二次增量备份
[[email protected] ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xinc1 /backup/xinc2
1.5.4 合并备份文件
1.5.4.1 合并全备文件
[[email protected] ~]# innobackupex --apply-log --use-memory=32M --redo-only /backup/xfull/
1.5.4.2 合并第一次增量备份到全备中（一致性合并）
[[email protected] ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/xinc1/ /backup/xfull/
1.5.4.3 合并最后一次增量到全备中
[[email protected] ~]# innobackupex --apply-log --incremental-dir=/backup/xinc2/ /backup/xfull/
提示：只有最后一次备份合并时不需要使用--redo-only参数，其他增量备份要依次使用--redo-only参数和全备合并后方可进行数据库还原。
例如：周日做全备；周一到周六每天做上一天增量备份：
- xfull --apply-log --redo-only 保证last-lsn=周一增量开始lsn
- xinc1 合并周一的增量到全备，并apply-log --redo-only 保证last-lsn=周二增量开始lsn
- xinc2 合并周二的增量到全备，并apply-log --redo-only 保证last-lsn=周三增量开始lsn
- xinc3 ...
- xinc4 ...
- xinc5 ...
- xinc6 合并周六的增量到全备，--apply-log 准备恢复即可
1.5.4.4 查看全备状态
[[email protected] xfull]# cat xtrabackup_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 6854459 last_lsn = 6854459 compact = 0 recover_binlog_info = 0
1.5.5 关闭数据库
[[email protected] ~]# /etc/init.d/mysqld stop # 或强制停止数据库 Kill -9 pid
1.5.6 模拟数据损坏
[[email protected] ~]# mv /usr/local/mysql/data/ /tmp/
1.5.7 数据库还原
[[email protected] ~]# innobackupex --copy-back /backup/xfull/ [[email protected] ~]# chown -R mysql.mysql /usr/local/mysql/data/
1.5.8 启动并查看还原信息
[[email protected] ~]# /etc/init.d/mysqld start mysql> use leon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | leon | 18 | | 2 | shadow | 20 | | 3 | shuaige | 26 | | 4 | xiaofang | 19 | | 5 | xiaodi | 28 | | 6 | guowang | 35 | | 7 | gongzhu | 28 | | 8 | xiaoming | 30 | +----+----------+------+ 8 rows in set (0.11 sec)
第2章 Xtrabackup企业级增量备份实战
2.1 企业环境模拟
- 背景：某大型网站，mysql数据库，数据量500G，每日更新量100M-200M
- 备份策略：xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。
- 故障场景：周三下午2点出现数据库意外删除表操作，大约10G。
2.2 备份策略设计
Xtrabackup原生支持全备和增量备份的功能：一般可以采取每周一次全备，其他时间增量备份：
- 每周六0:00进行全备：00 00 * * 6 mysql_backup_full.sh
- 周一到周五及周日00:00进行增量备份：00 00 * * 0-5 mysql_backup_inc.sh
2.3 恢复思路与流程
2.3.1 恢复思路
- 断开所有应用
- 检查备份是否存在
- 快速恢复数据
2.3.2 恢复流程
- 准备上周六的全备（--apply-log --redo-only）
- 合并周日、周一、周二（--apply-log --redo-only），周三（--apply-log）增量
- 在测试库恢复以上数据，数据的目前状态是周三凌晨时的数据
- 需要恢复的数据状态是周三下午两点左右误删表之前的数据状态：从凌晨开始的binlog恢复到删除之前的那个events的position
- 导出删除的表恢复到生产库，验证数据可用性和完整性
- 启用应用并连接数据库
2.4 需要考虑的问题
- 恢复需要多长时间？（与恢复+验证+意外情况有关）
- 业务需要停止多长时间？
- 数据恢复时出了问题怎么办？
2.5 模拟操作
2.5.1 模拟生产环境
2.5.1.1 模拟星期六全量备份
[[email protected] ~]# mkdir -p /backup/{full,inc1,inc2} [[email protected] ~]# innobackupex --user=root --password=123456 --use-memory=32M --no-timestamp /backup/full
2.5.1.2 模拟星期六白天的操作
mysql> use leon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into test values(9,'outman',45); Query OK, 1 row affected (0.20 sec) mysql> insert into test values(10,'outgirl',46); Query OK, 1 row affected (1.61 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
2.5.1.3 模拟星期日增量备份
[[email protected] ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1/
2.5.1.4 模拟星期日白天操作
mysql> use leon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into test values(11,'outman2',46); Query OK, 1 row affected (0.14 sec) mysql> insert into test values(12,'outgirl2',47); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
2.5.1.5 模拟星期一增量备份
[[email protected] ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2/
2.5.1.6 模拟星期一误删除操作
mysql> use leon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into test values(13,'outman3',47); Query OK, 1 row affected (0.14 sec) mysql> insert into test values(14,'outgirl3',48); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> drop table test; Query OK, 0 rows affected (0.02 sec) mysql> show tables; Empty set (0.00 sec)
2.5.2 关闭数据库并备份二进制日志
# 关闭数据库 [[email protected] ~]# /etc/init.d/mysqld stop # 备份二进制日志 [[email protected] ~]# mkdir /tmp/bin_log [[email protected] ~]# cp /usr/local/mysql/data/mysql-bin.* /tmp/bin_log
2.5.3 合并数据库
[[email protected] ~]# innobackupex --apply-log --use-memory=32M --redo-only /backup/full/ [[email protected] ~]# innobackupex --apply-log --use-memory=32M --redo-only --incremental-dir=/backup/inc1/ /backup/full/ [[email protected] ~]# innobackupex --apply-log --use-memory=32M --incremental-dir=/backup/inc2/ /backup/full/
2.5.4 分析binglog文件
[[email protected] ~]# cat /backup/inc2/xtrabackup_binlog_info mysql-bin.000001 944 [[email protected] ~]# mysqlbinlog --start-position=944 /tmp/bin_log/mysql-bin.000001 ...省略部分输出内容... # at 1326 #171123 12:07:18 server id 1 end_log_pos 1357 CRC32 0xca9ce38e Xid = 61 COMMIT/*!*/; # at 1357 #171123 12:07:28 server id 1 end_log_pos 1474 CRC32 0xe50ca0c1 Query thread_id=1 exec_time=0 error_code=0 use `leon`/*!*/; SET TIMESTAMP=1511410048/*!*/; DROP TABLE `test` /* generated by server */ /*!*/; ...省略部分输出内容...
2.5.5 导出误删表之前的操作
[[email protected] ~]# mysqlbinlog --start-position=944 --stop-position=1357 /tmp/bin_log/mysql-bin.000001 > /backup/inc_binlog.sql
2.5.6 删除数据库所有数据
[[email protected] ~]# rm -rf /usr/local/mysql/data/
提示：生产环境建议使用mv命令。
2.5.7 还原数据库
[[email protected] ~]# innobackupex --copy-back /backup/full/ [[email protected] ~]# chown -R mysql.mysql /usr/local/mysql/data/
2.5.8 启动数据库并还原二进制日志文件
# 启动数据库 [[email protected] ~]# /etc/init.d/mysqld start # 还原二进制日志文件 mysql> source /backup/inc_binlog.sql
2.5.9 查看恢复结果
mysql> use leon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | leon | 18 | | 2 | shadow | 20 | | 3 | shuaige | 26 | | 4 | xiaofang | 19 | | 5 | xiaodi | 28 | | 6 | guowang | 35 | | 7 | gongzhu | 28 | | 8 | xiaoming | 30 | | 9 | outman | 45 | | 10 | outgirl | 46 | | 11 | outman2 | 46 | | 12 | outgirl2 | 47 | | 13 | outman3 | 47 | | 14 | outgirl3 | 48 | +----+----------+------+ 14 rows in set (0.00 sec)
第3章 扩展内容
3.1 单表恢复
对于整体数据库容量很大，但是误删的表相对整体备份而言很小的情况，xtrabackup提供了导出单表数据的功能，可以使用此功能快速还原误删的表。
3.2 依赖条件
需要有全库备份。
3.3 恢复测试
3.3.1 导出表
导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了：
[[email protected] ~]# innobackupex --apply-log --export /backup/full/
此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器：
[[email protected] ~]# find /backup/full/ -type f -name "*.exp" /backup/full/mysql/innodb_index_stats.exp /backup/full/mysql/innodb_table_stats.exp /backup/full/mysql/slave_worker_info.exp /backup/full/mysql/slave_master_info.exp /backup/full/mysql/slave_relay_log_info.exp /backup/full/test/t2.exp /backup/full/test/t1.exp /backup/full/test/t3.exp /backup/full/leon/test.exp /backup/full/world/country.exp /backup/full/world/countrylanguage.exp /backup/full/world/city.exp
3.3.2 创建表
要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：
mysql> create table test ( -> id int(4) not null auto_increment, -> name varchar(20), -> age int(3), -> primary key (id) -> ) engine=innodb; Query OK, 0 rows affected (1.24 sec)
3.3.3 删除表空间
mysql> alter table leon.test discard tablespace; Query OK, 0 rows affected (0.39 sec)
3.3.4 复制表空间
将来自于导出表的服务器的table表的table.ibd和table.exp文件复制到当前服务器的数据目录：
[[email protected] ~]# cp /backup/full/leon/{test.exp,test.ibd} /usr/local/mysql/data/leon/ [[email protected] ~]# chown -R mysql.mysql /usr/local/mysql/data/
3.3.5 导入表
mysql> alter table leon.test import tablespace;
3.3.6 验证导入结果
mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | leon | 18 | | 2 | shadow | 20 | | 3 | shuaige | 26 | | 4 | meinv | 19 | | 5 | wangzi | 28 | | 9 | outman | 45 | | 10 | outgirl | 46 | | 11 | outman2 | 46 | | 12 | outgirl2 | 47 | +----+----------+------+ 9 rows in set (0.01 sec)
我的微信
如果有技术上的问题可以扫一扫我的微信
