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的表,进行一致性快照备份,不锁表.

恢复案例

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

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