MYSQL 备份恢复
1、备份工具
mysqldump : MDP
自带逻辑备份工具,SQL语句(Create database ,Create table ,Insert),可读性较强,压缩比比较高
相对来说比较慢
xtrabackup :PBK XBK
Percona公司,物理备份,可读性弱,压缩比较低
相对来说较快
3. 企业常见备份策略
(1) MDP定时全备+binlog备份(每天)
(2) XBK定时全备+XBK增量+binlog备份
(3) XBK定时全备+binlog备份
4. mysqldump 应用
4.1 连接参数
-u 用户名
-p 密码
-S 本地sock
-h mysql服务端IP
-P 端口号
4.2 备份专用参数
-A 全库备份
[root@db01 ~]# mkdir -p /backup/mdp
[root@db01 ~]# mysqldump -uroot -p123456 -A >/backup/mdp/full.sql 2>/dev/null
-B 单库或多库备份
[root@db01 ~]# mysqldump -uroot -p123456 -B world test >/backup/mdp/db.sql 2>/dev/null
单表或多表
第一个是库名剩下的无论多少都是表名
针对以下例子：库名world 表名：city country
[root@db01 ~]# mysqldump -uroot -p123456 world city country >/backup/mdp/tab.sql 2>/dev/null
4.3 特殊功能参数
在使用mysqldump 备份时加上以下参数即可，无副作用
-R 过程函数
-E 事件
--triggers 触发器
此参数需加载客户端，意为在备份时，服务端像客户端备份时传输数据的大小。
--max-allowed-packet=128M
[root@db01 /tmp]# mysqldump -uroot -p123456 -A -E -R --triggers >/tmp/full.sql 2>/dev/null
-F 备份时，立即栓先出一个新的二进制文件（每个库会刷出一个二进制文件）。
--master-data=2 参数
(1)自动记录binlog的位置点，以主事的方式记录到备份文件中（一般在文件的第22行）。
(2)自动锁表和解锁。
(3)配合 --single-transaction ，可以实现innodb表的热备
--single-transaction 在备份innodb表时可以快照备份，不会阻塞其他事务的操作。
mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction >/tmp/`date +%F`.sql 2>/dev/null
演练
背景环境：
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。
备份策略：
每天23:00点，计划任务调用mysqldump执行全备脚本
故障时间点：
年底故障演练:模拟周三上午10点误删除数据库，并进行恢复.
思路：
1、停业务，避免数据的二次伤害
2、找一个临时库，恢复周三23：00全备
3、截取周二23：00 --- 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、
5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
5.2 方法二：将误删除的表导出，导入到原生产库
数据恢复实际操作：
模拟数据环境
mysql> create database mdp charset utf8mb4;
Query OK, 1 row affected (0.09 sec)
mysql> use mdp;
Database changed
mysql> create table t1(id int)engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.50 sec)
mysql> insert into t1 values (1),(2),(3),(5);
Query OK, 4 rows affected (0.34 sec)
Records: 4 Duplicates: 0 Warnings: 0
模拟周二全备
[root@db01 /backup]# mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction >/backup/`date +%F`.sql 2>/dev/null
模拟周三上午10点误删前的数据录入
mysql> insert into t1 values (111),(2222),(333),(5555),(444),(666);
Query OK, 6 rows affected (0.10 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into t1 values (111),(2222),(333),(5555),(4444),(6666);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> drop table t1;
恢复流程
(1) 截取日志：
日志的起点位置（全备文件的22行）
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=5719;
二进制中drop前面的位置：
| mysql-bin.000013 | 6336 | Query | 6 | 6449 | use mdp; DROP TABLE t1 /* generated by server */
[root@db01 ~]# mysqlbinlog --start-position=5719 --stop-position=6636 /data/binlog/mysql-bin.000013 >/backup/bak.sql
(2)使用截取的二进制文件和全备进行恢复
恢复前执行如下操作：把此窗口的恢复的二进制日志输出关闭掉（不再进入二进制日志），退出窗口失效。
mysql> set sql_log_bin=0
mysql>
恢复操作及数据导入操作：
mysql> set sql_log_bin=0
mysql> source /backup/2019-09-01.sql;
mysql> source /backup/bak.sql
5.xtrabackup 备份工具使用
5.1软件介绍：
xtrabackup 数据备份是实现物理备份，相当于cp,但是它可以将备份时间内的redo和undo日志一同备份走。
软件下载地址：
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
5.2 xtrabackup软件安装使用。
安装前依赖包：
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
解决依赖关系后直接使用yum安装软件即可：
yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
使用前需要在mysql的配置文件内添加以下参数：
添加参数的目的是因为xtrabackup在使用过程中会读取mysql的sock文件，[client]意为所有客户端；
[client]
socket=/tmp/mysql.sock
具体配置如下（重启mysql生效）：
[root@db01 /server/tools]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
log_error=/data/mysql/data/error.log
binlog_format=row
log_bin=/data/binlog/mysql-bin
expire_logs_days=8
slow_query_log=ON
slow_query_log_file= /data/mysql/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes=ON
transaction_isolation=READ-COMMITTED
[mysql]
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock
5.3xtrabackup使用
5.3.1全备
[root@db02 /server/tools]# innobackupex --user=root --password=123456 /backup/xbk/full
备份时产生文件：
xtrabackup_binlog_info #备份时二进制日志的起点位置
xtrabackup_checkpoints #脏页落盘 将内存中已经提交的事物在备份过程直接写入磁盘，所记录的信息（差9个即为正常）
[root@db02 ]# cat /backup/xbk/full/2019-09-01_21-22-43/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0 #全备是 LSN号码为0
to_lsn = 167051071 #脏页落盘时的LSN号码
last_lsn = 167051080 #redo中的LSN号码，与to_lsn相差9个号码并不是有数据更改，而是软件备份过程中产生的信息，可以忽略
compact = 0
recover_binlog_info = 0
xtrabackup_info #软件备份的信息
xtrabackup_logfile #备份过程中产生的redo日志信息
5.3.2 全备恢复测试
[root@db02 ]# rm -rf /data/mysql/data/* #删除数据目录下的所有文件
备份恢复前的准备：
redo 日志处理
undo 日志处理
使用--apply-log 参数
[root@db02 /]# innobackupex --apply-log /backup/xbk/full/2019-09-01_21-22-43/
恢复:将备份的所有文件直接cp到数据目录下即可
[root@db02 /backup/xbk/full/2019-09-01_21-22-43]# cp -a * /data/mysql/data/
root@db02 /]chown -R mysql.mysql /data/
5.4xtrabackup 常用参数
--no-timestamp #不使用时间戳，直接备份到指定目录下
5.5 xtrabackup 增量备份两个参数
增量的特点就是，基于那天的备份进行增量备份，增量备份前要指明基于那天的数据目录。
--incremental --incremental-vasedir=/backup/full #基础目录
6、Xtrabackup企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量20M-30M
备份策略：
xtrabackup，每周日23:00进行全备，周一到周六23:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
如何恢复？
演练：
6.1 模拟数据环境
模拟原始数据
create database xbk charset utf8mb4;
use xbk
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
周日的全备：
[root@db01 /backup]# innobackupex --user=root --password=123456 --no-timestamp /backup/full
模拟周一的数据
use xbk
insert into t1 values(11),(22),(33);
commit;
周一的增量备份:
[root@db01 /backup]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1
commit;
模拟周二的数据
use xbk
insert into t1 values(111),(222),(333);
commit;
周二的增量备份:
模拟周三删库之前的数据变化
use xbk
insert into t1 values(100),(200),(300);
insert into t1 values(400),(500),(600);
commit;
6.2 数据恢复
xtrabackup 做数据恢复时，从第一次开始，要添加--redo-only，最后一次不需要添加。
6.2.1 处理备份数据
处理原始备份数据
innobackupex --apply-log --redo-only /backup/full/
将周一的增量合并到全备数据中，并处理备份
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full
将周二的增量合并到全备数据中，并处理备份
innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/full
最后整理全备
innobackupex --apply-log /backup/full/
截取二进制日志：
[root@db02 /backup/inc2]# cat /backup/inc2/xtrabackup_binlog_info
mysql-bin.000020 1270
mysql> show binlog events in 'mysql-bin.000020';
mysql-bin.000020 | 1857 | Query | 6 | 1946 | drop database xbk |
mysqlbinlog --start-position=1270 --stop-position=1857 /data/binlog/mysql-bin.000020 >/backup/bin.sql
6.2.2最后恢复
\rm -rf /data/mysql/data/* #因为我用的是drop 删库，所以要情况数据目录重新导入
innobackupex --copy-back full/
chown -R mysql.mysql /data/
mysql>source /backup/bin.sql
