1 MySQL备份和恢复

1.1 备份类型

• 完全备份, 部分备份

          完全备份: 整个数据集, 备份一整个数据库, 或者备份所有的数据库, 看部署情况
          部分备份: 只备份数据子集, 如部分库或表
  

• 完全备份, 增量备份, 差异备份

          增量备份: 仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据, 备份较快, 还原复杂
          差异备份: 仅备份最近一次完全备份以来变化的数据, 备份较慢, 还原简单
  

增量备份:

图片.png

需要有一次完全备份
之后每次都是备份基于上一次完全备份或者上一次增量备份以来, 变化的那一部分数据
对于增量备份的数据恢复, 一定要按照顺序进行恢复, 先恢复最近一次的完全备份, 然后陆续恢复每一次增量备份
因为, 每次增量备份, 备份数据不确实是否有交集, 因此, 一定要按照备份顺序恢复

图片.png

差异备份:

图片.png

需要有一次完全备份
每次差异备份, 都是基于上一次完全备份来备份, 备份的就是上一次完全备份, 到此次备份时, 变化的数据
对于完全备份的数据, 恢复时, 需要先恢复上一次的完全备份, 然后再用最近一次的差异备份来恢复
虽说还原只需要最近一次完全备份和差异备份, 但是此前的差异备份不要删除, 避免期间有误操作, 之后恢复数据还需要之前的差异备份v

图片.png

注意: 二进制文件不应该与数据文件放在同一个磁盘

• 冷, 温, 热备份

冷备: 读, 写操作均不可进行, 数据库停止服务
温备: 读操作可以执行, 但是写操作不可执行, 加全局读锁
热备: 读, 写操作做均可执行

      MyISAM: 温备, 不支持热备
      InnoDB: 都支持. InnoDB支持热备, 因为支持事务, 事务的可重复读隔离级别, 保证在备份的时间段内, 备份的数据是基于同一个时间点的

• 物理备份和逻辑备份

物理备份: 直接复制数据文件进行备份, 与存储引擎有关, 占用较多的空间, 速度快. 直接复制数据目录的文件
逻辑备份: 从数据库中'导出'数据另存到文件而进行备份, 与存储引擎无关, 占用空间少, 速度慢, 可能丢失精度, 利用mysqldump, xtrabackup等其他工具

1.2 备份内容

• 数据, 业务数据库
• mysql数据库, 存放用户信息
• 二进制日志, InnoDB的事务日志
• 用户账户, 权限设置, 程序代码(存储过程, 函数, 触发, 事件调度器)
• 服务器的配置文件

1.3 备份注意要点

• 能容忍最多丢失多少数据
• 备份产生的负载
• 备份过程的时长
• 温备的锁要持续多久
• 恢复数据需要在多长时间内完成
• 需要备份和恢复哪些数据
• 多个数据库进行备份还原时,数据库版本要一致, 避免出现问题

1.4 还原注意

• 做还原测试, 用于测试备份的可用性, 避免由于备份过程出错, 比如断网, 与mysql连接端断开等原因导致备份不完全, 不可用
• 还原演练, 把还原过程写成规范的技术文档

1.5 备份工具

• cp, tar等复制归档工具: 用于物理备份, 适用所有的存储引擎, 只支持冷备, 完全和部分备份
• LVM的快照: 先加读锁, 做完快照后解锁, 几乎热备, 借助文件系统工具进行备份
• mysqldump: 逻辑备份工具, 适用所有存储引擎, 对MyISAM存储引擎进行温备, 支持完全或部分备份; 对InnoDB存储引擎支持热备, 结合binlog达到增量备份, 还未来得及备份的数据, 适用二进制日志做还原, 已经备份好的数据, 用增量备份还原.
• xtrabackup; 由Percona提供支持对InnoDB做热备(物理备份)的工具, 支持完全备份, 增量备份
• MariaDB Backup: 从MariaDB 10.1.26开始集成, 基于Percona XtraBackup 2.3.8实现
• mysqlbackup: 热备份, MySQL Enterprise Edition组件
• mysqlhotcopy: perl语言实现, 几乎冷备, 仅适用于MyISAM存储引擎, 使用lock tables, flush tables 和 cp 或 scp来快速备份数据库

1.6 备份案例

1.6.1 基于LVM的快照备份

1. 请求锁定所有表
mysql> flush tables with read lock;
2. 记录二进制日志文件及事件位置
mysql> flush logs;
mysql> show master status;
mysql -e 'show master status' > /PATH/TO/LOGFILE
3. 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
4. 释放锁
mysql> unlock tables;
5. 挂载快照卷, 执行数据备份
6. 备份完成后, 删除快照卷
7. 制定好策略, 通过原卷备份二进制日志

1.6.2 实战案例: 数据库冷备份和还原

准备环境:

两台MySQL服务器, CentOS 8, 安装最新版MySQL 8.0

1. 10.0.0.51: 作为源数据库, 导入hellodb.sql
2. 10.0.0.52: 作为备份数据库, 关掉源数据库服务, 将源数据库的数据文件拷贝到备份数据库数据目录下, 开启mysql后, 会读取源数据库的数据文件

step1: 开启源数据库的二进制日志

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
log-bin=/data/log/binlog/mysql-binlog   
server-id=1 #如果是MySQL5.7版本, 开启二进制必须指定server-id, 否则无法启动, MySQL8.0不用

[root@mysql-1 ~]#mkdir -pv /data/log/binlog/
[root@mysql-1 ~]#chown -R mysql.mysql /data/log  #必须保证mysql用户对于存放二进制日志的目录有写权限

step2: 开启服务, 导入数据库文件

[root@mysql-1 ~]#systemctl start mysqld
[root@mysql-1 ~]#mysql < hellodb_innodb.sql

step3: 在备份数据库创建好保存二进制日志的目录. 开启二进制.

[root@mysql-2 ~]#mkdir -pv /data/log/binlog 
[root@mysql-2 ~]#chown -R mysql.mysql /data/log/binlog

[root@mysql-2 ~]#vim /etc/my.cnf.d/mysql-server.cnf 
log-bin=/data/log/binlog/mysql-binlog

step4: 源数据库关闭服务, 将配置文件, 二进制文件, 数据目录分别拷贝到备份服务器

# 两台主机需要安装rsync, yum -y install rsync
[root@mysql-1 ~]#systemctl stop mysqld
[root@mysql-1 ~]#rsync -av /etc/my.cnf.d/mysql-server.cnf 10.0.0.52:/etc/my.cnf.d
[root@mysql-1 ~]#rsync -av /var/lib/mysql/ 10.0.0.52:/var/lib/mysql
[root@mysql-1 ~]#rsync -av /data/log/binlog/ 10.0.0.52:/data/log/binlog/

step5: 备份服务器开启mysql服务, 验证数据导入成功

[root@mysql-2 ~]#systemctl start mysqld
[root@mysql-2 ~]#mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

补充:

MyISAM不支持热备份, InnoDB支持热备份, 因为InnoDB支持事务, 靠的是事务的第三个隔离性, 可重复读,保证了即使数据正在被修改, 备份时读取的数据还是开始备份时的状态. 因此, 备份要以事务方式进行

如果通过物理备份, 拷文件, 需要冷备份, 确保没有数据的变化. 采用冷备份, 备份数据库无需关闭二进制. 因为数据是直接通过文件拷贝过去的, 不是在备份服务器执行的命令

数据库的数据文件, 如果启动时,本身没有, 那么会创建, 如果目的路径有相应文件, 会读取, 不会创建新的. 只会读取数据文件, 二进制日志不会读取

2 mysqldump 备份工具

2.1 mysqldump说明

逻辑备份工具

mysqldump是MySQL的客户端命令, 通过mysql协议连接至MySQL服务器进行备份
mysqldump需要在mysql服务启动时使用

三种命令格式

Usage: mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份, 但是数据库本身的属性定义不会备份
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] #支持指定数据库备份, 也支持备份多个数据库, 包含数据库本身定义也会备份
OR     mysqldump [OPTIONS] --all-databases [OPTIONS] #备份所有数据库, 包含数据库本身定义也会备份

mysqldump常用选项

-A, --all-databases #备份所有数据库, 内容包含CREATE DATABASE语句, 也就是数据库定义
-B, --database DB_NAME... #指定备份的数据库, 内容包含CREATE DATABASE语句, 也就是数据库定义
-R, --routines #备份所有存储过程和函数
-E, --events #备份相关的所有event scheduler
--triggers #备份表相关触发器, 默认启用, 用--skip-triggers, 不备份触发器
# -R, -E, --triggers需要看数据库是否有存储过程, 函数, 时间和触发器, 如果没有, 那么无需加这些选项
--default-character-set=utf8 #指定字符集, 需要和数据库服务器的字符集一致
--master-data[=#] #此选项需启用二进制日志
#1. 所备份的数据前加一条记录为CHANGE MASTER TO语句, 非注释, 不指定#, 默认是为1, 适合于主从复制多机使用
#2. 记录为被注释的 -- CHANGE MASTER TO语句, 适合于单机使用
#此选项会自动关闭--lock-tables功能, 自动打开-x | --lock-all-tables功能(除非开启--single-transaction)
-F, --flush-logs #备份前滚动日志, 锁定表完成后, 执行flush logs命令, 生成新的二进制日志文件, 配合-A或-B选项时, 会导致刷新多次数据库. 建议在同一时刻执行转储和日志刷新, 可通过和--single-transaction或-x, --master--data一起使用实现, 此时只刷新一次二进制日志, 这样备份之前的二进制日志在一个文件里, 备份开始后新产生的二进制日志会在新的文件里, 这样可以轻松的实现二进制文件拷贝
--compact #去掉注释, 适合调试, 生产不使用
-d, --no-data #只备份表结构, 不备份数据
-t, --no-create-info #只备份数据, 不备份表结构, 即不备份CREATE TABLES语句. 当需要重新建表, 只保留数据, 而表属性, 字段属性需要重新设定时, 可以使用. 因为备份出来的的
sql语句, 不会包含表创建语句, 可以先重新建表, 然后把sql语句导进去即可
-n, --no-create-db #不备份CREATE DATABASE语句, 可被-A或-B覆盖
--flush-privileges #备份mysql数据库或相关时需要使用, 如果涉及到了权限变化, 那么可以加该选项
-f, --force #忽略SQL错误, 继续执行
--hex-blob #使用十六进制符号转储二进制列, 当有包括binary, varbinary, blob, bit的数据类型的列时使用, 避免乱码
-q, --quick #不缓存查询, 直接输出, 加快备份速度

mysqldump的MyISAM存储引擎相关的备份选项:
MyISAM不支持事务, 只能支持温备, 不支持热备, 所以必须先锁定要备份的库, 而后启动备份操作

-x, --lock-all-tables #加全局读锁, 锁定所有库的所有表, 同时加--single-transaction或--lock-tables选项会关闭此选项功能, 注意: 数据量大时, 可能会导致长时间无法并发访问数据库
-l, --lock-tables #对于需要备份的每个数据库, 在启动备份之前分别锁定其所有表, 默认为on,
--skip-lock-tables选项可禁用, 对备份MyISAM的多个库, 可能会造成数据不一致
#注意: 以上选项对InnoDB表一样生效, 实现温备, 但是不推荐使用

mysqldump的InnoDB存储引擎相关的备份选项:
InnoDB存储引擎支持事务, 可以利用事务的相应隔离级别, 实现热备, 也可以实现温备,但不建议使用

--single-transaction
#此选项InnoDB中推荐使用, 不适用MyISAM, 此选项会开始备份前, 先执行start transaction指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照. 仅适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不保证和其他存储引擎保持一致. 在进行单事务转储时, 要确保有效的转储文件(正确的表内容和二进制日志位置), 没有其他连接应该使用以下语句: alter table, drop table, rename table, truncate tables, 此选项和--lock-tables(此选项隐含提交挂起的事务)选项是相互排斥, 备份大型表时, 建议将--single-transaction和--quick结合一起使用

2.2 生产环境实战备份策略

InnoDB完全备份建议

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > ${BACKUP}/fullback_${BACKUP_TIME}.sql

MyISAM完全备份建议

# myisam不支持事务, 因此只能热备, -x加全局读锁, 只读不写
mysqldump -uroot -p -A -F -E -R --triggers -x --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > ${BACKUP}/fullback_${BACKUP_TIME}.sql

2.3 mysqldump完全备份简单案例

2.3.1 不加任何选项只备份单个库, 或者单个库的多个表

• mysqldump不加任何选项, 备份时只是把库或表的内容显示出来到屏幕. 需要重定向到文件里才行

[15:21:43 root@mysql-1 ~]#mysqldump hellodb
-- MySQL dump 10.13  Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: hellodb
-- ------------------------------------------------------
-- Server version   5.7.31

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

[15:23:29 root@mysql-1 ~]#mysqldump hellodb teachers
-- MySQL dump 10.13  Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: hellodb
-- ------------------------------------------------------
-- Server version   5.7.31

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

将备份内容重定向到文件里

[15:25:19 root@mysql-1 ~]#mysqldump hellodb teachers > /data/hellodb_teachers.sql

• 测试备份恢复

删除hellodb中的teachers表

[15:26:21 root@mysql-1 ~]#mysql  hellodb -e 'drop table teachers'

还原teachers表

[15:32:42 root@mysql-1 ~]#mysql hellodb  < /data/hellodb_teachers.sql 

mysql> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

如果数据库量比较大时, 可以压缩, 比如导出整个数据库

[15:32:48 root@mysql-1 ~]#mysqldump hellodb | gzip > /data/hellodb.sql.gz
[15:35:21 root@mysql-1 ~]#ll /data/hellodb.sql.gz 
-rw-r--r-- 1 root root 1837 Nov 24 15:35 /data/hellodb.sql.gz

[15:38:25 root@mysql-1 ~]#gzip -d /data/hellodb.sql.gz 
[15:39:38 root@mysql-1 ~]#ll /data/
total 16
-rw-r--r--  1 root  root  7646 Nov 24 15:35 hellodb.sql

但是直接用mysqldump hellodb 去备份, 这时导出来的数据是没有指定数据库信息的, 无法对整个数据库进行还原, 只能还原数据库里的表

[15:41:36 root@mysql-1 ~]#mysql < /data/hellodb.sql  
ERROR 1046 (3D000) at line 22: No database selected

需要先手动把数据库创建起来, 再进行还原, 而且新创建的数据可以和源数据库不同名

[15:43:08 root@mysql-1 ~]#mysql -e 'create database hello'
[15:43:20 root@mysql-1 ~]#mysql hello < /data/hellodb.sql 

但是新创建的库, 和源库的属性不一定相等, 即使数据导进去也不一定能用, 因为源数据库的库信息没有保留下来

mysqldump 不加选项的缺点总结:

1. mysqldump不加任何选项, 备份时只是把库或表的内容显示出来到屏幕. 需要重定向到文件里才行

2. 备份时候, mysqldump是陆续给每个表,先加写锁, 保证备份表的时候,没人能修改数据, 表备份完事,再把锁去掉,然后给下一张表加写锁,备份,再解锁,这样就会造成如果表之间有联系, 一个表去掉写锁后发生了修改,另一个表正在被备份, 没有写入新的数据, 这样数据就会有差错, 因此mysqldump必须配合相应选项使用.

mysql> select * from course;
ERROR 1100 (HY000): Table 'course' was not locked with LOCK TABLES

3. 另外备份的时候, 即使指定了备份哪个数据库,内容里也不会有库的信息, 只有表的信息, 因此, 在还原时, 要先把库创建出来, 新的库名可以不一样

4. 然而,  虽然表和表内容, 表属性都记录下来并且能被还原, 库的属性是没记录的

不建议用mysqldump [OPTIONS] database [tables]方式备份数据库, 因为不会记录库信息, 和其他以上原因

2.3.2 --databases|-B选项, 备份单个或者多个数据库

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

mysqldump -B 选项, 会把数据库的属性信息备份下来, 还原时会先根据数据库原属性创建数据库

会记录库的属性,信息, 还原时不需要手动创建数据库, 直接导入文件即可

# 查看源数据库属性

mysql> show create database hellodb;
+----------+-----------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                     |
+----------+-----------------------------------------------------------------------------------------------------+
| hellodb  | CREATE DATABASE `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[15:48:34 root@mysql-1 ~]#mysqldump -B hellodb > /data/hello_B.sql

-- Current Database: `hellodb`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET latin1 */;

[21:42:24 root@mysql-1 ~]#mysql -e 'drop database hellodb'
[21:42:43 root@mysql-1 ~]#mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

还原hellodb数据库

[15:50:34 root@mysql-1 ~]#mysql < /data/hello_B.sql

mysql> show tables from hellodb;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)

2.3.3 --all-databases|-A选项备份所有数据库

mysqldump -A 备份所有数据库, 包括除了information_schema,performance_schema和sys外所有的数据库. 也就是mysql和其余业务数据库都会备份

[21:47:59 root@mysql-1 ~]#mysqldump -A > /data/hellodb_A.sql
[15:56:05 root@mysql-1 ~]#grep -i '^create database' /data/hellodb_A.sql 

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

不建议用-A, 因为会备份所有数据库, 不能选择性备份, 要做到按照不同业务,制定不同的备份计划, 每个数据库备份到独立的文件里

2.3.4 分库完全备份并压缩, 将不同的数据库备份到不同的目录和文件中

注意: 还原数据库时要把二进制日志关掉

方法1:

#!/bin/bash
DB=`mysql -e 'show databases' | grep -Ev 'Database|.*schema|sys'` #这里根据不同的数据库版本, 需要排除除了mysql库和业务数据库意外的其他系统数据库

for db in $DB; do
mysqldump -B $db | gzip > /data/$db.sql.gz
done

[19:30:59 root@mysql-2 ~]#bash /data/scripts/mysql_back1.sh
[19:31:05 root@mysql-2 ~]#ll /data/
total 232
-rw-r--r--  1 root  root    1903 Nov 24 19:31 hellodb.sql.gz
-rw-r--r--  1 root  root  230803 Nov 24 19:31 mysql.sql.gz

方法2:

mysql -e 'show databases' | grep -Ev 'Database|.*schema|sys' | while read db; do mysqldump -B $db | gzip > /data/$db.sql2.gz;done

方法3:

mysql -e 'show databases' | grep -Ev 'Database|.*schema|sys' | sed -nr 's#(.*)#mysqldump -B \1 | gzip > /data/\1.sql3.gz#p' | bash

#利用sed查找替换, 把mysqldump命令整个替换进去, 再把结果传给bash执行
[19:46:01 root@mysql-2 ~]#mysql -e 'show databases' | grep -Ev 'Database|.*schema|sys' | sed -nr 's#(.*)#mysqldump -B \1 | gzip > /data/\1.sql3.gz#p'
mysqldump -B hellodb | gzip > /data/hellodb.sql3.gz
mysqldump -B mysql | gzip > /data/mysql.sql3.gz

方法4: 用sed地址定位, 排除包含Database和sys的行

[19:46:18 root@mysql-2 ~]#mysql -e 'show databases' | sed -nr '/Database|sys|.*schema/!s#(.*)#mysqldump -B \1 | gzip > /data/\1.sql4.gz#p' 
mysqldump -B hellodb | gzip > /data/hellodb.sql4.gz
mysqldump -B mysql | gzip > /data/mysql.sql4.gz
[19:51:45 root@mysql-2 ~]#mysql -e 'show databases' | sed -nr '/Database|sys|.*schema/!s#(.*)#mysqldump -B \1 | gzip > /data/\1.sql4.gz#p' | bash

2.3.5 对二进制日志备份是增量备份还是差异备份?

二进制日志记录的是数据库的每个DML操作, 并且达到一定的条件或者手动触发, 就会生成新的二进制日志文件, 还原过程相当于把数据库之前做的操作, 再重新做一遍, 不过需要把误操作给删除

• 假设, 每天都生成一个二进制日志文件, 那么一周就有7个二进制日志

如果每次备份都是备份当天产生的二进制日志, 那么就是增量备份
如果每次备份都是备份从第一天到当前时间点的所有二进制日志, 那就是差异备份

• 然而, 二进制并不是按照时间去触发生成新的新的二进制日志, 即使用flush logs, 也难保证按照时间去生成新的二进制日志, 除非用mysqlbiglog, 按照每一天的时间点, 导出二进制, 那么这就是增量备份. 如果每次都是把从完全备份时间点开始, 到当前时间点的二进制日志备份出来, 那就是差异备份

如果每周都做一次mysqldump完全备份, 那么之前的二进制日志就可以备份到其他的位置, 保留一段时间后, 就可以删除了

2.4 mysqldump重要选项

• --master-data[=#]

--master-data[=#] #此选项需启用二进制日志
#1. 所备份的数据前加一条记录为CHANGE MASTER TO语句, 非注释, 不指定#, 默认是为1, 适合于主从复制多机使用
#2. 记录为被注释的 -- CHANGE MASTER TO语句, 适合于单机使用
# --master-data 此选项会自动关闭--lock-tables(加锁)功能, 自动打开-x | --lock-all-tables功能(除非开启--single-transaction)
# 因此, 备份过程中, 会对所有表加锁, 导致所有表都无法写入新的数据, 业务会受到影响

• --master-data[=#]选项的作用

记录二进制日志, 和当前时间点的对应关系, 也就是在做完全备份时, 是从二进制日志哪个Pos位置开始备份的. 只有把做完全备份这个时间点的位置记录下来, 才知道从二进制日志哪个Pos位置开始, 是没有做完全备份的数据
CHANGE MASTER TO会记录当前mysqldump执行时, 二进制日志的位置, 那么从该位置往后的就是数据库没备份下来的, 之前的是备份下来的

比如: 当前执行mysqldump时, --master-data记录了二进制位置位Pos=100, 那么Pos=100之后的二进制日志, 就是没有做完全备份的.

• --single-transaction

由于--master-data会在执行备份过程中, 对所有表加锁, 导致无法写入, 影响业务, 因此, 可以使用--single-transaction选项, 表示以事务方式进行备份
事物的可重复读隔离级别和MVCC保证了即使在备份过程中, 有新的数据写入, 整个备份的过程中, 看到的数据也是同一个时间点的, 数据一致, 不会受新写入数据的影响, 即使不加锁, 也没影响

• -F | --flush-logs

-F, --flush-logs #备份前生成新的二进制日志, 锁定表完成后, 执行flush logs命令, 生成新的二进制日志文件, 配合-A或-B选项时, 会导致刷新多次二进制日志, 备份了几个数据库就生成几个二进制日志. 
建议在同一时刻执行转储和日志刷新, 可通过和--single-transaction或-x, --master--data一起使用实现, 此时只刷新一次二进制日志, 这样备份之前的二进制日志在一个文件里, 备份开始后新产生的二进制日志会在新的文件里, 这样可以轻松的实现二进制文件拷贝, 可以把就旧二进制日志直接拷贝走, 因为这部分内容都存在了完全备份里了, 新的内容保留在了新的二进制日志

备份案例: InnoDB完全备份指定数据库到指定目录

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup/hellodb
DB=hellodb
PASS="centos"

mysqldump -uroot -p"$PASS" -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB |  gzip > ${DIR}/${DB}_${TIME}.sql.gz  

# 完全备份文件中的-- CHANGE MASTER TO, 表示, 开启备份时间点, 二进制的日志位置位POS=156, 那么从mysql-binlog.000004文件的156开始, 都是没有做完全备份的
[00:44:53 root@mysql-1 /backup/hellodb]#grep 'CHANGE MASTER TO'  hellodb_2021-06-13_00-31-39.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000004', MASTER_LOG_POS=156;

备份后, 生成的sql文件就是该数据库知道change master to位置的完全备份
-F --single-transaction会刷新日志, 生成新的日志文件, 新的二进制日志就是从备份时间点开始新增的数据
因为, 除了新的二进制日志文件, 其他的二进制日志内容都已经包含在了完全备份的文件里, 可以拷贝走, 单独保存
可以每天做一次完全备份, 生成新的二进制日志, 一旦某一时间出现错误, 那么先停止服务, 比如只允许本地登录, 或者添加防火墙策略. 恢复上一次的完全备份, 然后修改二进制日志文件, 删除错误的语句, 再导入完全备份后新的二进制日志, 即可恢复数据

备份案例:InnoDB完全备份所有数据库到指定的目录

#!/bin/bash
  
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=centos
DB=`mysql -uroot -p"$PASS" -e 'show databases' | grep -Ev "^Database|.*schema|sys"`                                                                              


[ -d "$DIR" ] || mkdir $DIR

for db in $DB; do
    [ -d ${DIR}/${db} ] || mkdir ${DIR}/${db}
    mysqldump -uroot -p"$PASS" -F -B $db --single-transaction --master-data=2 --default-character-set=utf8 -q &> /dev/null | gzip > ${DIR}/${db}/${db}_${TIME}.sql.gz
done

2.5 利用二进制日志恢复误删的表

部署环境

yum -y install mysql-server

开启二进制

[01:13:16 root@mysql-1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
log-bin=/data/mysql/log/mysql-bin

创建二进制存放目录

[01:13:29 root@mysql-1 ~]#mkdir -pv /data/mysql/log
mkdir: created directory '/data/mysql'
mkdir: created directory '/data/mysql/log'

[01:14:05 root@mysql-1 ~]#chown -R mysql.mysql /data/mysql/

[01:14:35 root@mysql-1 ~]#systemctl enable --now mysqld

导入hellodb数据库

[01:28:21 root@mysql-1 ~]#mysql < hellodb_innodb.sql
[01:28:17 root@mysql-1 ~]#ll /data/mysql/log/
total 20
-rw-r----- 1 mysql mysql   179 Jun 13 01:14 mysql-bin.000001
-rw-r----- 1 mysql mysql 10752 Jun 13 01:26 mysql-bin.000002 # yum安装的8.0版本, 开启二进制后会生成两个文件
-rw-r----- 1 mysql mysql    66 Jun 13 01:14 mysql-bin.index

#可以看到二进制日志存放到了mysql-bin.000002里, 并且记录到了10752

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |    10752 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

实验背景

image.png

案例说明: 每天凌晨00:00做完全备份, 周一白天正常修改数据, 晚上17:30, students表被误删除, 之后teachers表做了修改, 18:00时发现了students表被删除了

恢复过程

1. 先利用前一天的完全备份, 恢复到周一凌晨00:00点的状态
2. 把二进制日志中,删除表的操作删除
3. 用二进制恢复到18:00

step1: 模拟完全备份

[22:00:30 root@mysql-1 ~]#mkdir /backup
[22:00:40 root@mysql-1 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /backup/allbackup_`date +%F_%T`.sql

#由于加了-F刷新日志, 因此会生成新的00003二进制文件, 意味着00003文件保存着这次完全备份后的二进制日志, 刚刚导入hellodb的二进制日志全部存放在000002文件里
[22:01:09 root@mysql-1 ~]#ll /data/mysql/log
total 24
-rw-r----- 1 mysql mysql   179 Nov 24 21:31 mysql-bin.000001
-rw-r----- 1 mysql mysql 10799 Nov 24 22:01 mysql-bin.000002
-rw-r----- 1 mysql mysql   156 Nov 24 22:01 mysql-bin.000003 #新的二进制日志文件
-rw-r----- 1 mysql mysql    87 Nov 24 22:01 mysql-bin.index

查看完全备份文件

[22:01:25 root@mysql-1 ~]#ll /backup/
total 1056
-rw-r--r-- 1 root root 1078502 Nov 24 22:01 allbackup_2020-11-24_22:01:06.sql

step2: 模拟白天17:30之前的数据更新

mysql> use hellodb;
Database changed


mysql> insert students (name,age,gender) value ("haha",18,'M');
Query OK, 1 row affected (0.00 sec)

mysql> insert students (name,age,gender) value ("lala",20,'F');
Query OK, 1 row affected (0.00 sec)

step3: 模拟17:30, 删除student表

mysql> drop table students;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| teachers          |
| toc               |
+-------------------+
6 rows in set (0.00 sec)

step4: 17:30后, 继续修改teachers表

mysql> insert teachers (name,age,gender) values ('zhao',19,'M');
Query OK, 1 row affected (0.01 sec)

mysql> insert teachers (name,age,gender) values ('qian',18,'F');
Query OK, 1 row affected (0.00 sec)

step5: 发现故障后, 需要暂停业务, 通过防火墙或者只允许本地socket登录

下面恢复数据

step1: 找到二进制日志的位置

先查看二进制日志, 可以看到00003文件数据增加了, 但是实际情况是不知道二进制具体位置的, 需要根据完全备份来判断完全备份备份到的二进制位置

[22:10:59 root@mysql-1 ~]#ll /data/mysql/log
total 24
-rw-r----- 1 mysql mysql   179 Nov 24 21:31 mysql-bin.000001
-rw-r----- 1 mysql mysql 10799 Nov 24 22:01 mysql-bin.000002
-rw-r----- 1 mysql mysql  1582 Nov 24 22:08 mysql-bin.000003
-rw-r----- 1 mysql mysql    87 Nov 24 22:01 mysql-bin.index

#查看完全备份文件
[22:11:19 root@mysql-1 ~]#grep '^-- CHANGE MASTER' /backup/allbackup_2020-11-24_22\:01\:06.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=156;
#这里可以看到上一次完全备份, 备份到了00003文件的位置ID156, 也就是说该文件156以后的都是新增的二进制日志, 如果00003以后还有其他文件, 那么要把这些都合并在一起, 一起重新导入

step2: 将新增的二进制日志内容导出来

[22:11:22 root@mysql-1 ~]#mysqlbinlog --start-position=156 /data/mysql/log/mysql-bin.000003 > /backup/inc.sql
#此时导出来的数据, 就是完全备份后的增量备份
#如果除了00003还有其他的二进制文件, 那么要一并导出, 假如还有00004文件, 那么需要追加到/backup/inc.sql文件里
#mysqlbinlog  /data/mysql/log/mysql-bin.000004 >> /backup/inc.sql
#除了00003外, 其余文件的所有内容都是新增的二进制日志内容, 因此无需判断位置

step3: 将此前删除students表的语句从二进制备份中删除

#如果导出来的增量备份过大, 可以使用grep先把SQL语句过滤出来, 然后用sed去删除, 一定要确保删的是对的SQL语句
[22:19:02 root@mysql-1 ~]#grep -i 'drop' /backup/inc.sql 
DROP TABLE `students` /* generated by server */

step4: 定位到正确的需要删除的语句后, 用sed删除

[22:19:13 root@mysql-1 ~]#sed -i.bak '/DROP TABLE `students`/d' /backup/inc.sql #sed -i.bak把增量备份文件先做个备份
[22:20:31 root@mysql-1 ~]#grep -i 'drop' /backup/inc.sql  #确保错误的语句已经被删除

step5: 关闭数据库二进制

mysql> set sql_log_bin=off;

step6: 导入完全备份

mysql> source /backup/allbackup_2020-11-24_22:01:06.sql

step7: 导入修改后的增量备份

mysql> source /backup/inc.sql

step8: 开启二进制

mysql> set sql_log_bin=on;

还原完成, 检查数据库

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |  #students表还原成功
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.01 sec)

|    26 | haha          |  18 | M      |    NULL |      NULL |
|    27 | lala          |  20 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
|   5 | zhao          |  19 | M      |
|   6 | qian          |  18 | F      |
+-----+---------------+-----+--------+
#新增的数据都在

如果删除的是一个数据库, 那么在mysqlbinlog导出的文件中, 要把从删库命令到最后的所有数据都删除
总结: 还原的过程, 就是利用上一次的完全备份, 先还原到完全备份那个时间点, 这样所有在完全备份之后的操作, 就相当于没执行, 因为完全备份还原, 会把所有表重新创建, 插入完全备份那个时间点的数据, 之后再根据完全备份的文件里的CHANGE MASTER TO定位到此次完全备份备份到了哪个二进制文件的哪个位置id, 之后把从这个位置id开始的所有二进制文件都导出来到同一个文件里, 那么这个文件就包含了从完全备份到发现故障时的所有操作,之后把错误的执行命令删除, 然后先关闭二进制日志, 先导入完全备份, 再倒入二进制的增量备份,再开启二进制即可还原数据

这种还原方式有一定的风险, 按照上面的案例, students表是在17:30被删除的, 18:00发现了故障, 假如18:10把表恢复了,那么恢复的students表利用完全备份和二进制恢复后也是处于删除时17:30的状态, 而其余表是18:00时的状态, 因为其余表是没有被删除的, 它们的状态是18:00发现故障暂停服务前的状态, 这就造成了students表的时间点和其他表不一样,如果数据之间有关联就会产生问题, 造成数据不同步

注意要点: 导入完全备份和增量备份前, 需要停止二进制服务, 同时整个业务都要暂停访问, 避免有用户还在连接数据库

建议: 每周做一次完全备份, 把整个数据库的内容都拷出来, 备份时用-F每次备份完都刷一下二进制日志, 这样新生成的二进制日志就是新的内容, 另外每天做一次差异备份, 把从上次完全备份后生成的新的所有的二进制日志都备份,这样每天都备份了最新的二进制信息, 这样一旦出现问题, 可以利用上一次完全备份, 和最新的所有的二进制差异备份, 去进行数据恢复. 这里备份的二进制信息指的是, 把二进制文件通过mysqlbinlog导出到一个文件里.

如果做增量备份, 那么需要每次把之前备份过的内容剔除, 把剩下的做备份, 计算比较麻烦, 不如每次都做差异备份, 把上一次完全备份后生成的二进制文件都拷出来, 利用增量复制工具比如rsync, 只去复制发生变化的文件, 这样就不用每次都把之前复制过的二进制文件再复制一次, 可以加快复制速度. 通过增量备份工具, 最终实现还是增量备份的效果