10. MySQL

目录

• 逻辑备份工具 - mysqldump
  • 普通参数
  • 高级参数
  • 案例: 从删库到磁盘损坏
    • 模拟故障
    • 故障恢复
• 物理备份工具 - xtrabackup(XBK)
  • 备份方式
  • 1. 简单的全备示例
    • 根据全备恢复数据
  • 2.增量备份和恢复
    • 准备环境:
    • 根据增量备份恢复数据
      • 1. 数据恢复准备工作:
      • 2. 根据全备恢复数据：恢复到周二晚23点
      • 3.根据binlog恢复数据：周二晚23点~删库之前

备份的类型

• 热备：即在线备份，也就是在数据库正常运行时，进行数据备份，并且能够一致性恢复(只能是InnoDB存储引擎)，对业务影响非常小。
• 温备：锁表备份，只能查询不能修改(myisam存储引擎)，影响业务中的写入操作。
• 冷备：关闭数据库，在数据库没有任何变更的情况下，进行数据备份，业务停止。

备份方式及备份工具介绍

• 逻辑备份工具，基于SQL语句进行备份：
  • mysqldump+mysqlbinlog：
    • 优点：软件自带，无需安装；备份出来的是文本类型的SQL，可读性高，便于备份处理；压缩比高，节省磁盘空间。
    • 缺点：依赖于数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行转储，比较消耗资源，数据量大的话，效率比较低。
    • 建议：100G以内的数据量级，可以使用mysqldump，超过TB以上，如果使用mysqldump的话，搭配分布式系统也行。
• 物理备份工具，基于磁盘数据文件备份：
  • xtrabackup(XBK):percona，第三方。
    • 优点：类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高。
    • 缺点：可读性差；压缩比低，需要更多的磁盘空间。
    • 建议：数据量级在TB以上使用。
  • MySQL企业版(MySQL Enterprise Backup，MEB)备份工具。

备份策略

备份方式：

• 全备，全量备份，备份所有数据。
• 增量，备份变化的数据。

备份周期：根据数据量设计备份周期：

• 数据量较大，例如周日全备，周一到周六增量备份。
• 数据量小的话，每天全备都行。

逻辑备份工具 - mysqldump

mysqldump是MySQL数据库自带的客户端备份工具

创建一个用于存储备份数据的目录:

[root@cs ~]# mkdir -p /data/mysql/3306/backup
[root@cs ~]# chown -R mysql:mysql /data/mysql/3306/backup

mysqldump备份中常用的参数(不完全)：

P	Description	必加参数
-A	备份全部数据库数据
-B	备份指定数据库
--triggers	备份触发器	Yes
--routines,-R	备份函数和存储过程	Yes
-events,-E	备份事件	Yes
-F	备份时，自动为每个数据库都刷新一个binlog日志文件
--master-data=2	当--master-data=2时，将以注释的方式将position号和binlog的日志文件写入到备份文件中去	Yes
--single-transaction	该参数针对于InnoDB存储引擎，实现了快照备份(也可以称之为热备)，特点就是备份时不会锁表	Yes
--set-gtid-purged=OFF	进行备份时，是否同时备份gtid，默认值是ATUO，等价于ON
--max-allowed-packet	备份时，设置从mysqld接收和发送包的大小

普通参数

A : 全备参数

[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A > /data/mysql/3306/backup/full.sql

# 如果开一个mysql,可以不用指定socket -S 
[root@cs ~]# mysqldump -uroot -p123 -A > /data/mysql/3306/backup/full.sql

# 查看大小
[root@cs ~]# du -sh /data/mysql/3306/backup/*
158M	/data/mysql/3306/backup/full.sql

B : 备份指定库

备份MySQL数据库中的指定库(一个或多个库，多个库以空格分隔)到本地指定文件中：

[root@cs ~]# mysqldump -uroot -p123 -B db1 db2 > /data/mysql/3306/backup/db.sql

备份指定表

别分指定表，也就是只备份指定数据库下的指定表(一个或多个表,空格分隔)：

[root@cs ~]# mysqldump -uroot -p123 -B db1 t1 t2 > /data/mysql/3306/backup/ts.sql

高级参数

1.用户针对某些表或者自定义的触发器和函数之类"程序"该怎么办？-- 备份时,无脑加下面三个参数

• --triggers：触发器。
• --routines，简写-R，存储过程和函数。
• --events，简写-E，事件(调度器)。

例如:

[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A --triggers -R -E >/data/mysql/3306/backup/full.sql

2.--master-data, 当--master-data=2时，将以注释的方式将position号和binlog的日志文件写入到备份文件中去：-- 备份时无脑加

例如:

[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A --triggers -R -E --master-data=2 >/data/mysql/3306/backup/full.sql

# 前几行文件中会有position号和binlog的日志文件
[root@cs ~]# head -n 30 /data/mysql/3306/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

3.--single-transaction -- 备份时无脑加

该参数针对于InnoDB存储引擎，实现了快照备份(也可以称之为热备)，特点就是备份时不会锁表，备份时加上这个参数就行。

4.--set-gtid-purged

在开启GTID后，进行备份时，是否同时备份gtid，它有三个值：

• 默认的--set-gtid-purged=auto，等价于on，主要应用于主从复制环境中。
• --set-gtid-purged=OFF，可以在日常的备份中，只回复数据。

5.-F

备份时，自动为每个数据库都刷新一个binlog日志文件。

案例: 从删库到磁盘损坏

模拟故障

1. 模拟昨晚23点的全备

[root@cs ~]# mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction >/data/mysql/3306/backup/full.sql

2. 模拟白天的数据变化

create database b4;
use b4
create table a1(id int);
insert into a1 values(1),(2);

3. 有个家伙，手一抖，删库了

drop database b4;

4. 手一抖，又把磁盘搞坏了

# 模拟磁盘损坏
[root@cs backup]# rm -rf /data/mysql/3306/data/*

现在，请你根据现有全备+binlog将数据恢复到删除之前的时间节点。

故障恢复

1. 确认二进制日和全备文件是否存在

[root@cs 3306]# ll /data/mysql/3306/logs/binlog/
total 12
-rw-r-----. 1 mysql mysql 201 May 14 15:16 mysql-bin.000001
-rw-r-----. 1 mysql mysql 893 May 14 15:34 mysql-bin.000002
-rw-r-----. 1 mysql mysql  92 May 14 15:16 mysql-bin.index
[root@cs 3306]# ll /data/mysql/3306/backup/fu*
-rw-r--r--. 1 root root 60702743 May 14 15:29 backup/full.sql

2. 恢复前的准备工作

把数据库从新搭起来：

# 查看进程
[root@cs backup]# netstat -nlp|grep 330        
# 1.杀死现在的mysql进程
[root@cs backup]# pkill mysqld
[root@cs backup]# netstat -nlp|grep 330 

# 2.清空数据库原因数据
[root@cs data]# rm -rf /data/mysql/3306/data/*
[root@cs data]# ll /data/mysql/3306/data/
total 0

# 3.初始化数据库
[root@cs data]# mysqld --initialize-insecure  --user=mysql --basedir=/opt/software/mysql --datadir=/data/mysql/3306/data

# 4.恢复原来的用户密码
[root@cs data]# mysqladmin -uroot -p password 123

3. 先根据全备恢复数据到昨天23点

# 关闭开启binlog日志
set sql_log_bin=0;
source /data/mysql/3306/backup/full.sql
set sql_log_bin=1;

4. 根据二进制日志恢复从昨天23点到今天数据库删库之前的数据

定位pos的起点,终点

起始position号：

[root@cs ~]# head -n 40 /data/mysql/3306/backup/full.sql 
...
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
# 日志文件时mysql-bin.000002, 起点pos为154

终点position号:

3306 [world]>show binlog events in "mysql-bin.000002";
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.20-log, Binlog ver: 4                             |
| mysql-bin.000002 | 123 | Previous_gtids |         6 |         154 |                                                                   |
| mysql-bin.000002 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT= '95f21511-b12d-11eb-9b52-000c29b5bdfc:1' |
| mysql-bin.000002 | 219 | Query          |         6 |         307 | create database b4                                                |
| mysql-bin.000002 | 307 | Gtid           |         6 |         372 | SET @@SESSION.GTID_NEXT= '95f21511-b12d-11eb-9b52-000c29b5bdfc:2' |
| mysql-bin.000002 | 372 | Query          |         6 |         465 | use `b4`; create table a1(id int)                                 |
| mysql-bin.000002 | 465 | Gtid           |         6 |         530 | SET @@SESSION.GTID_NEXT= '95f21511-b12d-11eb-9b52-000c29b5bdfc:3' |
| mysql-bin.000002 | 530 | Query          |         6 |         600 | BEGIN                                                             |
| mysql-bin.000002 | 600 | Table_map      |         6 |         643 | table_id: 330 (b4.a1)                                             |
| mysql-bin.000002 | 643 | Write_rows     |         6 |         688 | table_id: 330 flags: STMT_END_F                                   |
| mysql-bin.000002 | 688 | Xid            |         6 |         719 | COMMIT /* xid=4763 */                                             |
| mysql-bin.000002 | 719 | Gtid           |         6 |         784 | SET @@SESSION.GTID_NEXT= '95f21511-b12d-11eb-9b52-000c29b5bdfc:4' |
| mysql-bin.000002 | 784 | Query          |         6 |         870 | drop database b4                                                  |
| mysql-bin.000002 | 870 | Stop           |         6 |         893 |                                                                   |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
14 rows in set (0.01 sec)

# 终点在删库之前,pos为719

截取日志,恢复数据：

# 截取日志
[root@cs ~]# mysqlbinlog --start-position=154 --stop-position=719 --skip-gtids /data/mysql/3306/logs/binlog/mysql-bin.000002 > /tmp/b2.sql
[root@cs ~]# ll /tmp/b*
-rw-r--r--. 1 root root 2158 May 14 15:57 /tmp/b2.sql

# 恢复数据,关闭开启binlog日志
set sql_log_bin=0;
source /tmp/b2.sql
set sqlin=1;

物理备份工具 - xtrabackup(XBK)

三方工具下载安装

xtrabackup是Perl语言开发，所以还需要配置相关环境和依赖：

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

# 如果没有wget,就安装wget
yum install -y wget

然后下载安装即可：

# 下载或上传RPM包
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
# 使用yum安装,可以安装没有的相关依赖
yum install -y percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

# 判断是否安装成功
[root@cs ~]# innobackupex --version

必要的修改

xtrabackup在执行innobackupex命令时，会自动使用MySQL的socket文件，但默认它去找'/var/lib/mysql/mysql.sock'文件，而我们的socket文件在/tmp下，所以，还需要对MySQL的客户端进行一些参数配置：

[root@cs ~]# vim /etc/my.cnf

[client]
socket=/tmp/mysql.sock

无需重启MySQL服务。

备份方式

物理备份

xtrabackup采取的备份方式类似于cp命令，直接进行物理拷贝数据文件，与此同时，也会拷贝走undo log和redo log。

• 对于非InnoDB表来说，如myisam表，它在备份时会先锁表，然后cp数据文件，这种形式属于温备的备份方式。
• 对于InnoDB表(支持事务的)来说，不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，算是热备的备份方式。

面试题：xtrabackup在InnoDB表备份的恢复流程

1. xtrabackup备份执行的瞬间,立即触发ckpt，将已提交的数据脏页，从内存刷写到磁盘，并记录此时的LSN号
2. 备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走，也就是checkpoint LSN之后的日志
3. 在恢复之前，模拟Innodb"自动故障恢复"的过程，将redo（前滚）与undo（回滚）进行应用
4. 恢复过程是cp备份到原来数据目录下

1. 简单的全备示例

全备命令:

# 遇事不决，记得help
innobackupex --help

# 1.备份出的文件目录名称是日期形式的
[root@cs ~]# innobackupex --user=root --password=123 /data/mysql/3306/backup/xbk
[root@cs ~]# ll /data/mysql/3306/backup/xbk

# 2.自定义备份目录名full  --no-timestamp
[root@cs ~]# innobackupex --user=root --password=123 --no-timestamp /data/mysql/3306/backup/xbk/full

备份产生的目录，我们称之为备份集。

xtrabackup_binlog_info:文件记录的是备份时的二进制日志的position号和GTID号(如果有的话)。

[root@cs full]# cat xtrabackup_binlog_info
mysql-bin.000003	194	98ddc71a-b12d-11eb-b85f-000c29b6f740:1-10

xtrabackup_checkpoints

[root@cs full]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 484039725
last_lsn = 484039734
compact = 0
recover_binlog_info = 0

各字段：

• backup_type：备份类型，默认是全备。
• from_lsn：备份时的lsn号从哪个位置开的:
  • 全备，从0开始的。
  • 如果是增量备份，该值是last_lsn减9，也就是和to_lsn相等。
• to_lsn：当备份命令开始执行时，记录的lsn号。
• last_lsn：备份结束时的lsn号。如果备份过程中，数据没有变更，那么last_lsn和to_lsn号差9(MySQL5.6这两个是一致的，但5.7中，备份自己使用了9个lsn号，所以二者差9)。

xtrabackup_info : 文件是备份时的各种信息的汇总。

根据全备恢复数据

模拟数据库全崩然后根据全备进行数据恢复

# 搞崩数据库
[root@cs ~]# pkill mysqld
# 磁盘损坏,清空数据
[root@cs ~]# rm -rf /data/mysql/3306/data/*
# 查看进程
[root@cs ~]# netstat -lnp|grep 330

• 在恢复之前，模拟Innodb"自动故障恢复"的过程，将redo（前滚）与undo（回滚）进行应用

那这里，xtrabackup也有相应的参数帮我们来做这件事：

[root@cs ~]# innobackupex --apply-log /data/mysql/3306/backup/xbk/full/

# 然后进行备份恢复就好了
[root@cs ~]# innobackupex --copy-back /data/mysql/3306/backup/xbk/full/

# 查看恢复数据
[root@cs ~]# ll /data/mysql/3306/data/*

但有个问题，就是你执行恢复命令的时候，使用的是root用户，所以，再启动MySQL之前，还需要进行授权：改成mysql

[root@cs ~]# chown -R mysql:mysql /data/mysql/3306/data/*

然后,重启服务,

[root@cs ~]# systemctl start mysqld

# 查看是否恢复数据数据
[root@cs ~]# mysql -uroot -p123 -e "show databases;"

2.增量备份和恢复

增量备份依赖全备。如现在的备份规则是周一到周六是增量备份，周日是全备

准备环境:

# 为了后续方便，删除之前的全备数据
rm -rf /data/mysql/3306/backup/xbk/*
# 将binlog日志清空
[root@cs ~]# mysql -uroot -p123
mysql> reset master;
mysql> show master status; -- 查看当前使用的binlog日志文件

1. 模拟周日之前的数据变更,并模拟上周日晚23点的全备

# 模拟数据变更 mysql>
create database y1 charset utf8;
use y1
create table a1(id int);
insert into a1 values(1),(2),(3);

# 模拟周日全备 [root@cs ~]#
innobackupex --user=root --password=123 --no-timestamp /data/mysql/3306/backup/xbk/full/
ll /data/mysql/3306/backup/xbk/ # 查看备份

2.模拟周一的数据变化,做增备

# 模拟数据变更 mysql>
create database y2 charset utf8;
use y2
create table a2(id int);
insert into a2 values(1),(2),(3);

# 模拟周一增备 [root@cs ~]#
innobackupex --user=root --password=123 --no-timestamp --incremental /data/mysql/3306/backup/xbk/inc1 --incremental-basedir=/data/mysql/3306/backup/xbk/full
ll /data/mysql/3306/backup/xbk/ # 查看备份

• --incremental表示开启增量备份。
• /data/mysql/3306/backup/xbk/inc1是增量备份到指定目录。
• --incremental-basedir=/data/mysql/3306/backup/xbk/full表示，当前增量基于哪个全量日志。

3.模拟周二的数据变化,做增备

# 模拟数据变更 mysql>
create database y3 charset utf8;
use y3
create table a3(id int);
insert into a3 values(1),(2),(3);
# 模拟周二增备,基于周一数据做备份 [root@cs ~]#
innobackupex --user=root --password=123 --no-timestamp --incremental /data/mysql/3306/backup/xbk/inc2 --incremental-basedir=/data/mysql/3306/backup/xbk/inc1
ll /data/mysql/3306/backup/xbk/ # 查看备份

这里也要确认备份是否成功，查看列出了各自的checkpoints文件，注意观察几个lsn号的关系

4.到了周三的下午两点，数据库就又坏了....但在坏之前，还有数据变更

# 模拟数据变更 mysql>
create database y4 charset utf8;
use y4
create table a4(id int);
insert into a4 values(1),(2),(3);

# 模拟数据库损坏，这里模拟删除某个数据库
drop database y1;

根据增量备份恢复数据

首先：

1. 增量备份不能单独恢复。
2. 增量必须按照备份顺序合并到全备中，然后再根据全备恢复。
3. 所有备份都需要进行apply-log过程。
4. 需要注意的是，除了最后一个增量备份不需要加--redo-only，其他备份都需要加。

1. 数据恢复准备工作:

# 1. 按照顺序合并各个增量备份文件到全备文件中，除了最后一个增量备份文件不需要加--redo-only之外，其他都要加这个参数
# 整理周日的
innobackupex --apply-log --redo-only /data/mysql/3306/backup/xbk/full/

# 整理周一的
innobackupex --apply-log --redo-only --incremental-dir=/data/mysql/3306/backup/xbk/inc1 /data/mysql/3306/backup/xbk/full/

# 整理周二的,不需要加--redo-only
innobackupex --apply-log --incremental-dir=/data/mysql/3306/backup/xbk/inc2 /data/mysql/3306/backup/xbk/full/

# 最后对全备数据进行apply-log，现在的全备数据到周二晚上23点了
innobackupex --apply-log /data/mysql/3306/backup/xbk/full/


# 2. 截取周二23点到周三删库之前的binlog日志，根据position或者GTID都行
# 起点，在周二(前一天)的增量备份中：xtrabackup_binlog_info 
[root@cs ~]# cat /data/mysql/3306/backup/xbk/inc2/xtrabackup_binlog_info 
mysql-bin.000001	1903	2489f640-b6b2-11eb-8cca-000c29b5bdfc:1-9

# 终点,注意，uuid替换为你自己的uuid
show binlog events in "mysql-bin.000001";

# 截取日志文件
mysqlbinlog --skip-gtids --include-gtids="2489f640-b6b2-11eb-8cca-000c29b5bdfc:10-12" /data/mysql/3306/logs/binlog/mysql-bin.000001 >/tmp/inc2_bin.sql

2. 根据全备恢复数据：恢复到周二晚23点

# 1.清空MySQL的数据目录
[root@cs ~]# pkill mysqld
[root@cs ~]# rm -rf /data/mysql/3306/data/*
[root@cs ~]# ll /data/mysql/3306/data/

# 2.然后进行备份恢复就好了,授权
[root@cs ~]# innobackupex --copy-back /data/mysql/3306/backup/xbk/full/
[root@cs ~]# chown -R mysql:mysql /data/mysql/3306/data/*
[root@cs ~]# ll /data/mysql/3306/data/

# 3.重启服务
[root@cs ~]# systemctl start mysqld

3.根据binlog恢复数据：周二晚23点~删库之前

[root@cs ~]# mysql -uroot -p123

-- 开启关闭binlog日志,恢复数据
set sql_log_bin=0;

source /tmp/inc2_bin.sql;

set sql_log_bin=1;