Mysql备份恢复

• 基础备份参数

# -A备份所有数据库
[root@db01 backup]# mysqldump -uroot -p123 -A >/backup/full.sql

# -B指定数据库备份
[root@db01 backup]# mysqldump -uroot -p123 -B world oldguo wordpress >/backup/db.sql

# 指定数据库下的表备份，world数据库下的city和country表
[root@db01 backup]# mysqldump -uroot -p123 world city country > /backup/tab.sql

# -d只导出表结构 不导出数据
[root@db01 backup]# mysqldump -d -u root -p 123 数据库名> xxx.sql 

# 导出数据不导出结构 
[root@db01 backup]# mysqldump　-t　数据库名　-uroot　-p　>　xxx.sql　 

# 导出指定表的结构
[root@db01 backup]# mysqldump　-uroot -p123 -B 数据库名 --tables 表名　-d >　xxx.sql　　 

# --ignore-table备份时排除某张表或多张表
[root@db01 backup]# mysqldump  -uroot -p123 数据库名称 --ignore-table=数据库名.表名1 --ignore-table=数据库名.表名2 > 自定义名字.sql

• 常用备份

# 常用不锁表,压缩备份
[root@db01 backup]# mysqldump --databases 数据库名称 -uroot -p'tst-cloud@088~' --single-transaction --master-data=2 | gzip > /data/mysql_backup/数据库名称.sql.gz

# 常用压缩恢复
[root@db01 backup]# gunzip < backupfile.sql.gz | mysql -uroot -p123 数据库名

• 特殊备份参数

-R：备份存储过程和函数
-E：备份事件
--triggers：备份 触发器

--master-data=2 *****
(1) 记录备份时刻的binlog信息
(2) 自动锁表
不加--single-transaction ,温备份
加了--single-transaction,对于InnoDB表不锁表备份(快照备份)

--single-transaction *****
对于InnoDB的表,进行一致性快照备份,不锁表.

• 恢复案例

4. 恢复案例

4.1 背景环境：
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。
4.2 备份策略：
每天23:00点，计划任务调用mysqldump执行全备脚本
4.3 故障时间点：
年底故障演练:模拟周三上午10点误删除数据库.
4.4 思路：
1、停业务，挂维护页,避免数据的二次伤害
2、找一个临时库，恢复周二23：00全备
3、截取周二23：00  --- 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、 
    5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
    5.2 方法二：将误删除的表导出，导入到原生产库
6、开启业务
处理结果：经过20分钟的处理，最终业务恢复正常

4.5 故障模拟演练
4.5.1 准备数据
create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /backup/*

4.5.2 周二 23：00全备

mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz


4.5.3 模拟周二 23：00到周三 10点之间数据变化
use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;

4.5.4 模拟故障,删除表(只是模拟，不代表生产操作)
drop database backup;

4.6 恢复过程
4.6.1 准备临时数据库（多实例3307）
systemctl start mysqld3307
4.6.2 准备备份
（1）准备全备：
cd /backup
gunzip full_2018-10-14.sql.gz 
（2）截取二进制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=753;
753
1519 
mysqlbinlog --skip-gtids   --start-position=753 --stop-position=1519  /data/binlog/mysql-bin.000002 >/backup/bin.sql


4.6.3 恢复备份到临时库
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2019-07-15.sql
source /backup/bin.sql

4.6.4 将故障表导出并恢复到生产
mysqldump   -S /data/3307/mysql.sock -B  backup  >/backup/bak.sql
mysql -uroot -p123 
set sql_log_bin=0
source /backup/bak.sql;

5. 练习：
1、创建一个数据库 oldboy
2、在oldboy下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据

需求，跳过第六步恢复表数据


6. 扩展参数  ***
在构建主从时,使用AUTO/ON
--set-gtid-purged=AUTO/ON

仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF  

SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';

--max_allowed_packet=128M  控制的是备份时传输数据包的大小.

mysqldump -uroot -p123 -A  -R  --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz


7. 物理备份-XBK

7.1 安装依赖包：
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
7.2 下载软件并安装
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

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.el7.x86_64.rpm

7.3 innobackupex 使用 
7.3.1 备份核心理念
1. 针对非InnoDB,进行锁表备份,copy所有的非innoDB表文件
2. 针对InnoDB表,立即触发CKPT,copy所有InnoDB表相关的文件(ibdata1,ibd,frm).
并且将备份过程中产生,新的数据变化的部分redo一起备份走
3. 在恢复时,xbk会调用InnoDB引擎的CSR过程,将数据和redo的LSN追平,然后进行一致性恢复.

7.3.2 备份过程
(1) 全备
[root@db01 backup]# innobackupex  --user=root --password=123 --no-timestamp /backup/full
(2) 利用全备进行恢复
1. 
[root@db01 ~]# pkill mysqld
2. 
[root@db01 ~]# \rm -rf /data/mysql/data/*
3. *****
[root@db01 ~]# innobackupex --apply-log /backup/full/
4. 
[root@db01 full]# cp -a /backup/full/* /data/mysql/data/
5. 
[root@db01 full]# chown -R mysql.mysql /data/mysql/data/*
6. 
[root@db01 full]# /etc/init.d/mysqld start