mysqldump

备份

# 备份所有数据库 --all-databases 也可使用 -A 代替
$ mysqldump -uroot -p123 --all-databases > all.sql;
# 备份指定数据库 --databases 也可使用 -B 代替，可跟多个库名表示同时备份多个数据库
$ mysqldump -uroot -p123 --databases mydb1 mydb2 > mydb.sql;

# 其它选项：
        -S：指定 Socket 文件；
        -P：指定端口；
        -h：指定 MySQL 服务地址；
    --lock-all-tables,-x：执行备份操作前先锁定所有库的所有表；
    --lock-tables,-l：指定备份操作前先锁定指定库的指定表；
    --single-transaction：在一个事务中基于快照执行备份操作，仅可用于支持事务的引擎；
    --events,-E：备份指定数据库相关的所有 event scheduler；
    --routines,-R：备份指定数据库相关的所有存储过程和存储函数；
    --triggers：备份表相关的触发器，可使用 --skip--triggers 指定不备份触发器；
    --master-data=2：备份时记录当前时间点使用的二进制日志及事件位置；
    --flush-logs：锁定表完成后，执行 flush logs 命令做二进制日志滚动；
    --max_allowed_packet：最大接收数据包的大小，备份时超出大小会报错；

注意：

• MyISAM：支持温备，锁定备份库，而后启动备份操作；
• 还原时建议把二进制日志关闭，否则会生成大量的二进制日志；

恢复

下面模拟删除 testdb 库后恢复的操作。

全备：

$ mysqldump -uroot -p123  -B testdb  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/data/backup/full.sql

查看备份时刻的信息：

-- 查看 GTID 相关信息
SET @@GLOBAL.GTID_PURGED='1ab44cc3-83d2-11ea-a51d-000c29b7d4cd:1-12';
-- 查看 pos 号，备份开始时 binlog 位置点信息。
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql.000008', MASTER_LOG_POS=2313;

做一些操作，然后删库，查看全备后生成的日志起点与终点：

-- 起点：1ab44cc3-83d2-11ea-a51d-000c29b7d4cd:13
-- 终点：1ab44cc3-83d2-11ea-a51d-000c29b7d4cd:14
$ mysql -uroot -p123 -e "show binlog events in 'mysql.000008'"|grep -B 1 "drop database testdb"
mysql.000008    2746    Gtid    1       2811    SET @@SESSION.GTID_NEXT= '1ab44cc3-83d2-11ea-a51d-000c29b7d4cd:15'
mysql.000008    2811    Query   1       2909    drop database testdb

截取日志：

$ mysqlbinlog --skip-gtids --include-gtids='1ab44cc3-83d2-11ea-a51d-000c29b7d4cd:13-14' /data/3306/logs/mysql.000008 >/data/backup/bin.sql

恢复：

mysql> set sql_log_bin=0;
-- 恢复全备
mysql> source /data/backup/full.sql;
-- 恢复截取部分
mysql> source /data/backup/bin.sql;
mysql> set sql_log_bin=1;

xtrabackup

1、下载地址：https://www.percona.com/downloads/Percona-XtraBackup-LATEST/；
2、安装：

$ yum install percona-xtrabackup-80-8.0.11-1.el7.x86_64.rpm -y

3、修改 my.cnf 文件，添加配置让 xtrabackup 能找到 socket 文件：

[client]
socket=/tmp/mysql.sock

使用 xtrabackup 备份实际上就是对数据目录下的文件做拷贝。

• InnoDB ： 热备。拷贝 ibdataN,UNDO00N ,ibtmpN ,ibd 。通过截取变化 redo。
• 非InnoDB： FTWRL，全局锁。拷贝非 INNODB 的文件 frm\myi\myd...

只能本地备份。

全备及恢复

备份流程：

---

1、执行下面命令进行全备：

# 默认会自动基于时间生成备份目录，可通过选项 --no-timestamp 关闭该设定
$ innobackupex --user=root --password=123 /data/3306/backup/

2、生成备份文件如下：

# 自动基于备份时间生成目录
$ cd /data/3306/backup/2020-05-12_09-52-45/
$ ls xtrabackup_* | xargs -n1
# 记录 binlog 位置点，截取 binlog 起点位置
xtrabackup_binlog_info
# LSN 信息
#  backup_type：备份类型
#  from_lsn：起始 lsn 号码
#  to_lsn：CKPT-LSN
#  last_lsn：xtrbackup_logfile LSN
xtrabackup_checkpoints
# 总览信息
xtrabackup_info
# 备份期间产生的 redo 信息
xtrabackup_logfile

3、关闭数据库并删库：

$ pkill mysqld
$ rm -rf /data/3306/data/*

4、prepare 准备备份阶段：

# 重用了 CR，自动故障恢复。DWB+redo 前滚和 undo 回滚。
$ innobackupex --apply-log /data/3306/backup/2020-05-12_09-52-45/

5、copy-back 恢复：

# 方法一： cp or mv
$ cp -a /data/backup/xbk/full/*  /data/3306/data/ 

# 方法二： 使用 innobackupex 
# 相当于 cp
$ innobackupex --copy-back /data/backup/xbk/full
# 相当于 mv
$ innobackupex --move-back /data/backup/xbk/full
# 方法三： 直接修改数据目录路径
$ vim /etc/my.cnf
datadir=/data/3306/backup/2020-05-12_09-52-45/

6、赋权限，启动检查数据：

$ chown -R mysql.mysql /data/*
$ systemctl start mysqld

增量备份

xtrabackup 自带增量备份工具，备份原理：

每次增量一般是将最近一次备份作为参照物，会自动读取参照物的 xtrabackup_checkpoints 中的 to_lsn 值与当前 CKPT 的 LSN 作对比，仅备份变化过的 page，备份期间新的数据变化，通过 redo 自动备份，恢复数据时，需要把所有的增量恢复到全备，无法单独通过增量恢复数据。

---

数据模拟

1、备份前数据准备：

mysql> create database xbk charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use xbk
Database changed
mysql> create table full(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into full values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from xbk.full;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

2、模拟周日 23:00 全备：

$ innobackupex --user=root --password=123 /data/3306/backup/

3、模拟周一白天数据变化：

mysql> use xbk;
Database changed
mysql> create table inc1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into inc1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

4、模拟周一 23:00 增量备份：

# --incremental：指定是增量备份
# --incremental-base：指定增量备份的参照物
$ innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/3306/backup/2020-05-12_11-12-06 /data/3306/backup/inc1_`date +%F`

5、模拟周二白天数据变化：

mysql> create table inc2(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into inc2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

6、模拟周二 23:00 增量备份：

# --incremental：指定是增量备份
# --incremental-base：指定增量备份的参照物
$ innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/3306/backup/inc1_2020-05-12 /data/3306/backup/inc2_`date +%F`

7、模拟周三白天数据变化：

mysql> create table inc3(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  inc3 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

8、模拟周三 23:00 增量备份：

# --incremental：指定是增量备份
# --incremental-base：指定增量备份的参照物
$ innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/3306/backup/inc2_2020-05-12 /data/3306/backup/inc3_`date +%F`

9、模拟周四白天数据变化：

mysql> create table inc4(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into  inc4 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

10、周四下午出现数据损坏。如何恢复？

$ pkill mysqld
# 执行这部前确认 binlog 文件不在数据目录
$ rm -rf /data/3306/data/*

恢复流程

此时拥有的数据：

# 备份的数据
$ ls /data/3306/backup/ | xargs -n1
2020-05-12_11-12-06 # 周日全备
inc1_2020-05-12     # 周一增量
inc2_2020-05-12     # 周二增量
inc3_2020-05-12     # 周三增量
# binlog 文件中保存着周三增量后到周四数据损坏期间的数据
$ ls /data/3306/logs/ | xargs -n1
mysql-bin.000006
mysql-bin.000007
mysql-bin.000008
mysql-bin.000009
mysql-bin.000010
mysql-bin.000011
mysql-bin.000012
mysql.index
# 除上述外还有全量的 binlog

查看最后一次（周三增量）增量备份使用的 binlog 文件：

$ cat /data/3306/backup/inc3_2020-05-12/xtrabackup_binlog_info 
mysql.000012    3242    1ab44cc3-83d2-11ea-a51d-000c29b7d4cd:1-17,
60b9d400-93f7-11ea-a2eb-000c29b7d4cd:1-15

思路：

1. 恢复全备；
2. 将周一、周二、周三的增量整合到全备；
3. 从 binlog 中恢复周三增量备份后到周四出现数据损坏期间的数据；

具体步骤如下：

---

1、恢复全备：

# 在恢复全备时如果后续还要在这个全备的基础上合并增量，则需要添加 --redo-only 参数
# 默认情况下的恢复全备会做 redo 和 undo 操作，如果后续需要合并增量，此时做了 undo 则会导致后续的增量的 LSN 不能连续，所以需要添加 --redo-only 参数，直到合并最后一个增量
$ innobackupex --apply-log --redo-only /data/3306/backup/2020-05-12_11-12-06

2、将 inc1 合并到全备中，并且做 prepare 操作：

# 合并 inc1 到全备
$ innobackupex --apply-log --redo-only --incremental-dir=/data/3306/backup/inc1_2020-05-12 /data/3306/backup/2020-05-12_11-12-06

$ cat /data/3306/backup/2020-05-12_11-12-06/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 8463243
last_lsn = 8463252
compact = 0
recover_binlog_info = 0

$ cat /data/3306/backup/inc1_2020-05-12/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 8457233
to_lsn = 8463243
last_lsn = 8463252
compact = 0
recover_binlog_info = 0

# 全备的 to_lsn 和增量的 to_lsn 一致则说明合并成功

3、将 inc2 合并到全备中：

# 合并 inc2 到全备
$ innobackupex --apply-log --redo-only --incremental-dir=/data/3306/backup/inc2_2020-05-12 /data/3306/backup/2020-05-12_11-12-06
# 检查操作同和并 inc1

4、将 inc3 合并到全备中：

# 合并 inc3 到全备，此时不需要指定 --redo-only
$ innobackupex --apply-log --incremental-dir=/data/3306/backup/inc3_2020-05-12 /data/3306/backup/2020-05-12_11-12-06
# 检查操作同和并 inc1

5、将合并后的全备再次 prepare：

$ innobackupex --apply-log /data/3306/backup/2020-05-12_11-12-06

6、恢复到数据目录并修改属主：

$ innobackupex --copy-back /data/3306/backup/2020-05-12_11-12-06/
$ chown -R mysql.mysql /data/3306/data

7、启动数据库（禁止外部连接，因为后续还要恢复 binlog）：

$ /etc/init.d/mysqld start --skip-networking

8、截取 binlog 数据：

$ mysqlbinlog --skip-gtids --start-position=3242  /data/3306/logs/mysql-bin.000012 >/data/3306/backup/bin.sql

9、登入数据库从截取的文件恢复并检查数据：

mysql> set sql_log_bin=0;
mysql> source /data/3306/backup/bin.sql;
mysql> set sql_log_bin=1;
mysql> use xbk;
Database changed
mysql> show tables;
+---------------+
| Tables_in_xbk |
+---------------+
| full          |
| inc1          |
| inc2          |
| inc3          |
| inc4          |
+---------------+
5 rows in set (0.00 sec)
# 清空所有日志，主从中谨慎使用 reset master
mysql> reset master;

10、确认恢复完成后，正常重启：

$ /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
$ systemctl start mysqld;

完事儿后建议立即来一波全备。