linux运维、架构之路-MySQL备份与恢复(四)

一、备份方式

①逻辑备份(文件表示:SQL语句)

②物理备份(数据文件的二进制副本)

③基于快照的备份

④基于复制的备份

二、备份工具

①mysqldump:原生自带的逻辑备份工具

②mysqlbinlog:实现binlog备份的原生态命令

③xtrabackup:precona公司开发的性能很高的物理备份工具

三、Mysqldump

1、常用参数

-A, --all-databases      #备份所有库
-B 增加建库（create）及"use库"的语句，可以接多个库名，同时备份多个库
-B 库1 库2
-d                       #仅备份表结构
-t                       #仅备份数据
--compact                #减少无用数据输出(调试)
-R, --routines	         #备份存储过程和函数数据
   --triggers	         #备份触发器数据
-F，--flush-logs         #刷新binlog日志
--master-data=1|2        #告诉备份时刻的binlog位置
-x, --lock-all-tables    #锁所有备份表
-l,  --lock-tables       #锁单表

2、参数使用详解

①全库备份

mysqldump -uroot -p123456 -A >/backup/full.sql

②备份多个库

mysqldump -uroot -p123456 -B oldboy oldgirl>/backup/oldboy_oldgirl.sql

③分库备份

for name in `mysql -uroot -p123456 -e "show databases;"|sed 1d`
do
 mysqldump -uroot -p'123456' -B $name
done

④单表备份

mysqldump -uroot -p123456 oldboy test>/backup/oldboy_test.sql

⑤多表备份

mysqldump -uroot -p123456 oldboy 表1 表2 表3 …… /backup/oldboy_test.sql

⑥分库分表备份

#!/bin/sh
Myuser=root
Mypass=123456
Mycmd="mysql -u$Myuser -p$Mypass"
Mydump="mysqldump -u$Myuser -p$Mypass -x -F -R"
Dblist=`$Mycmd -e "show databases;"|sed '1,2d'|egrep -v "_schema|mysql"`
for database in $Dblist
do
  Tablist=`$Mycmd -e "show tables from $database;"|sed 1d`
  for table in $Tablist
  do
  mkdir -p /tmp/${database}
  $Mydump $database $table|gzip >/tmp/${database}/${table}_$(date +%F).sql.gz
  done
done

⑦压缩备份

mysqldump -uroot -p123456  -B --master-data=2 oldboy|gzip >/backup/oldboy.sql.gz

⑧innodb引擎备份命令

mysqldump -uroot -p123456 -A -B -R --triggers --master-data=2 --single-transaction|gzip >/backup/all.sql.gz

⑨适合多引擎混合备份

mysqldump -uroot -p123456 -A -B -R --triggers --master-data=2|gzip >/backup/alL_$(date +%F).sql.gz

3、mysqldump备份恢复实战案例

===================================================

环境：正在运行的网站，mysql数据库，数据量25G，日业务增量10-15M

[root@db ~]# cat /etc/redhat-release 
CentOS release 6.9 (Final)
[root@db ~]# uname -r
2.6.32-696.el6.x86_64
[root@db ~]# getenforce 
Disabled
[root@db ~]# hostname -I
172.19.5.56 172.16.1.56
[root@db ~]# mysql -V
mysql  Ver 14.14 Distrib 5.6.36, for linux-glibc2.5 (x86_64) using  EditLine wrapper

备份方式：每天晚上00:00，计划任务调用mysqldump执行全备脚本

mysqldump -uroot -p123456 -A -B -R --triggers --master-data=2 |gzip >/backup/all_$(date +%F_%T).sql.gz

故障时间点：第二天上午10点，某开发人员误删除了一个表

如何恢复？

=====================================================

解题思路：

1、使用测试库，恢复全备 source
2、恢复从00:00到10点之间的binlog
    a.截取00：00到10点这段 binlog在测试库恢复
    b.导出删除表
3、将删除的表进行恢复到生产库

①模拟数据

create database oldboy;#创建oldboy库
use oldboy;
create table test( id int(4) not null,name char(20) not null);#在oldboy库中创建test表
insert into test values(1,"oldboy");#test表中插入数据
insert into test values(2,"oldgirl");
insert into test values(3,"inca");
insert into test values(4,"zuma");
insert into test values(5,"kaka");
insert into test values(6,"老男孩");

mysql> select * from oldboy.test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | 老男孩    |
+----+-----------+

②模拟全备之后增量数据

mysql -e "use oldboy;insert into test values(7,'bingbing');"
mysql -e "use oldboy;insert into test values(8,'xiaoting');"
mysql -e "select * from oldboy.test;"
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | 老男孩    |
|  7 | bingbing  |
|  8 | xiaoting  |
+----+-----------+

③误删除数据

[root@db ~]# date -s "2018/01/23 10:00"
mysql> use oldboy;
Database changed
mysql> drop table test;
mysql> show tables;
Empty set (0.00 sec)

④开始恢复准备

iptables -I INPUT -p tcp --dport 3306 ! -s 172.19.5.56 -j DROP #非172.19.5.56禁止访问数据库3306端口

收集恢复所需数据

cp -a /data/mysql/mysql-bin.* /backup/   #copy所有的binlog日志到/backup下面，binlog指定在哪里看配置文件即可
gzip -d all_2018-01-22_00\:00\:05.sql.gz #解压晚上00:00点的全备
[root@db backup]# sed -n '22p' all_2018-01-22_00\:00\:05.sql #提取出binlog位置点
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=658960;

mysqlbinlog -d oldboy --start-position=658960 mysql-bin.000002 -r bin.sql #截取binlog位置点
[root@db backup]# ll /backup/ #收集到最终全备及增量恢复所需的数据
total 1952
-rw-r--r-- 1 root  root   656383 2018-01-22 00:00 all_2018-01-22_00:00:05.sql
-rw-rw---- 1 root  root     9864 2018-01-23 09:17 bin.sql
-rw-rw---- 1 mysql mysql     143 2018-01-22 10:54 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1317215 2018-01-23 09:02 mysql-bin.000002

⑤恢复全备

mysql <all_2018-01-22_00\:00\:05.sql
[root@db backup]# mysql -e "select * from oldboy.test;"
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | 老男孩    |
+----+-----------+

⑥恢复增量数据

[root@db backup]# mysql<bin.sql 
[root@db backup]# mysql -e "select * from oldboy.test;"
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | 老男孩    |
|  7 | bingbing  |
|  8 | xiaoting  |
+----+-----------+

恢复数据完毕，调整iptables允许用户访问，截取多个binlog问题

mysqlbinlog -d oldboy --start-position=339 mysql-bin.000001 mysql-bin.0000002 -r bin.sql

四、XtraBackup

xtrabackup：是一款基于InnoDB的在线热备工具，具有开源，免费，支持在线热备，占用磁盘空间小，能够非常快速地备份与恢复mysql数据库，(备份时不影响数据读写)

1、工作原理

2、安装部署

①添加yum源安装依赖

wget -O /etc/yum.repos.d/epel.repo  http://mirrors.aliyun.com/repo/epel-6.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

 ②下载安装xtrabackup

wget https://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
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

3、XtraBackup备份恢复实战

①全备

innobackupex --no-timestamp /backup/full

由于备份命令依赖于/etc/my.cnf文件中basedir  datadir  socket，如配置文件中未配置，需在命令行指定

[client]
user = root
password = 123456

[mysqld]
log-bin=/data/mysql/mysql-bin
character-set-server = utf8
basedir = /application/mysql/
datadir = /application/mysql/data/
port = 3306
socket = /tmp/mysql.sock

②应用日志到全备中(预处理数据)

innobackupex --apply-log /backup/full/

③模拟数据损坏

rm -fr /application/mysql/data/*
pkill mysql

④恢复全备数据

innobackupex --copy-back /backup/full/
chown -R mysql.mysql /application/mysql-5.6.36/ #这里重新授权注意一下，有时候发现授权软链接不管用

⑤查看恢复结果

[root@db ~]# mysql -e "select * from oldboy.test;"
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | 老男孩    |
|  7 | bingbing  |
|  8 | xiaoting  |
+----+-----------+

innobackupex命令常用参数说明

--defaults-file
#指明服务器的配置文件，此参数必须作为innobackupex的第一个参数，否则报错
--user        #备份的用户名
--password    #指明备份用户名的密码
/backup       #备份的目录
--apply-log   #指明为重做日志
--copy-back   #指明为恢复
--slave-info
#备份从库的show slave status信息，仅用于在备份从库时使用
--no-lock
#不锁表，仅适用于存储引擎为innodb，并且不在乎备份位置点时使用
--no-timestamp#不按时间生成备份目录

4、XtraBackup增量备份恢复实战

=================================================

场景：xtrabackup全备+增量

备份策略：定时任务周日晚上00点全备，周一到周六都是基于上一天的增量备份

故障场景：周三的时候，下午两点，某开发小菜鸟误删除了一张test表，大于2G

解决思路：

①准备上周日全备，并--apply-log --redo-only
②合并增量，周一 、周二 --apply-log --redo-only 周三 --apply-log
③在测试库恢复以上数据，数据的目前状态应该周三凌晨1:00
④需要恢复的数据状态是，下午2点钟左右，删除test表之前的数据状态，从1点开始的binlog恢复到删除之前的那个events的position
⑤导出删除的表test，恢复到生产库，验证数据可用性、完整性。
⑥启动应用连接数据库

==================================================

①模拟案例

mkdir /backup/inc{1,2} -p #创建增量备份的目录
innobackupex --no-timestamp /backup/full/ #周日全备

#模拟插入数据#
insert into test values(9,'outman');
insert into test values(10,'man');

②周一增量备份

innobackupex --incremental --no-timestamp --incremental-basedir=/backup/full/ /backup/inc1

③再次模拟插入数据

insert into test values(11,'110');
insert into test values(12,'120');

④周二增量备份

innobackupex --incremental --no-timestamp --incremental-basedir=/backup/inc1 /backup/inc2

⑤再次模拟数据变化

insert into test values(130,'newdata1');
insert into test values(140,'newdata2');

此时表中内容为

mysql> select * from test;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | oldboy    |
|   2 | oldgirl   |
|   3 | inca      |
|   4 | zuma      |
|   5 | kaka      |
|   6 | 老男孩    |
|   7 | bingbing  |
|   8 | xiaoting  |
|   9 | outman    |
|  10 | man       |
|  11 | 110       |
|  12 | 120       |
| 130 | newdata1  |
| 140 | newdata2  |
+-----+-----------+
14 rows in set (0.00 sec)

⑥故障场景模拟：下午2点误删除test表

mysql> use oldboy;
Database changed
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
ERROR 1146 (42S02): Table 'oldboy.test' doesn't exist

故障恢复：

①全备进行apply-log,暂时不需要undo回滚,只做redo

innobackupex --apply-log --redo-only /backup/full

②将周一的inc1合并到全备当中去,暂时不需要undo回滚,只做redo

innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full

③将周二的inc2合并到全备当中去,undo和redo都apply

innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/full

④最终把所有合并后的备份集合做apply

innobackupex --apply-log /backup/full

⑤截取周二增量备份inc2之后产生的binlog,截取到delete之前,即可恢复故障

确认binlog起点

[root@db ~]# cat /backup/inc2/xtrabackup_binlog_info
mysql-bin.000017    1022

截取drop操作之前的binlog

mysqlbinlog --start-position=1022 --stop-position=1484 /data/mysql/mysql-bin.000017 >/backup/incbinlog.sql

使用命令查看binlog的event

mysql> show binlog events in 'mysql-bin.000017';
+------------------+------+-------------+-----------+-------------+-----------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                      |
+------------------+------+-------------+-----------+-------------+-----------------------------------------------------------+
| mysql-bin.000017 |    4 | Format_desc |         1 |         120 | Server ver: 5.6.36-log, Binlog ver: 4                     |
| mysql-bin.000017 |  120 | Query       |         1 |         203 | BEGIN                                                     |
| mysql-bin.000017 |  203 | Query       |         1 |         316 | use `oldboy`; insert into test values(9,'outman')         |
| mysql-bin.000017 |  316 | Xid         |         1 |         347 | COMMIT /* xid=37 */                                       |
| mysql-bin.000017 |  347 | Query       |         1 |         430 | BEGIN                                                     |
| mysql-bin.000017 |  430 | Query       |         1 |         541 | use `oldboy`; insert into test values(10,'man')           |
| mysql-bin.000017 |  541 | Xid         |         1 |         572 | COMMIT /* xid=38 */                                       |
| mysql-bin.000017 |  572 | Query       |         1 |         655 | BEGIN                                                     |
| mysql-bin.000017 |  655 | Query       |         1 |         766 | use `oldboy`; insert into test values(11,'110')           |
| mysql-bin.000017 |  766 | Xid         |         1 |         797 | COMMIT /* xid=57 */                                       |
| mysql-bin.000017 |  797 | Query       |         1 |         880 | BEGIN                                                     |
| mysql-bin.000017 |  880 | Query       |         1 |         991 | use `oldboy`; insert into test values(12,'120')           |
| mysql-bin.000017 |  991 | Xid         |         1 |        1022 | COMMIT /* xid=58 */                                       |
| mysql-bin.000017 | 1022 | Query       |         1 |        1105 | BEGIN                                                     |
| mysql-bin.000017 | 1105 | Query       |         1 |        1222 | use `oldboy`; insert into test values(130,'newdata1')     |
| mysql-bin.000017 | 1222 | Xid         |         1 |        1253 | COMMIT /* xid=77 */                                       |
| mysql-bin.000017 | 1253 | Query       |         1 |        1336 | BEGIN                                                     |
| mysql-bin.000017 | 1336 | Query       |         1 |        1453 | use `oldboy`; insert into test values(140,'newdata2')     |
| mysql-bin.000017 | 1453 | Xid         |         1 |        1484 | COMMIT /* xid=78 */                                       |
| mysql-bin.000017 | 1484 | Query       |         1 |        1605 | use `oldboy`; DROP TABLE `test` /* generated by server */ |
+------------------+------+-------------+-----------+-------------+-----------------------------------------------------------+
20 rows in set (0.00 sec)

停库，备份binlog日志，本实例中binlog日志单独存放在/data/mysql目录下，无需备份

[root@db ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@db ~]# rm -fr /application/mysql/data/*

恢复xtrabackup数据

innobackupex --copy-back /backup/full/
chown -R mysql.mysql /application/mysql-5.6.36/
/etc/init.d/mysqld start

恢复binlog

mysql> set sql_log_bin=0; #临时不记录binlog
Query OK, 0 rows affected (0.00 sec)

mysql> source /backup/incbinlog.sql
Query OK, 0 rows affected (0.00 sec)

⑥故障恢复完毕

mysql> select * from oldboy.test;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | oldboy    |
|   2 | oldgirl   |
|   3 | inca      |
|   4 | zuma      |
|   5 | kaka      |
|   6 | 老男孩    |
|   7 | bingbing  |
|   8 | xiaoting  |
|   9 | outman    |
|  10 | man       |
|  11 | 110       |
|  12 | 120       |
| 130 | newdata1  |
| 140 | newdata2  |
+-----+-----------+
14 rows in set (0.00 sec)