MySQL 备份系列（1）-- 备份方案总结性梳理

原创

80民工 2021-04-09 21:38:00 ©著作权

文章标签 MySQL 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者80民工的原创作品，请联系作者获取转载授权，否则将追究法律责任

mysql数据库备份有多么重要已不需过多赘述了，废话不多说！以下总结了mysql数据库的几种备份方案：

一、binlog二进制日志通常作为备份的重要资源，所以再说备份方案之前先总结一下binlog日志~~
1.binlog日志内容
1）引起mysql服务器改变的任何操作。
2）复制功能依赖于此日志。
3）slave服务器通过复制master服务器的二进制日志完成主从复制，在执行之前保存于中继日志(relay log)中。
4）slave服务器通常可以关闭二进制日志以提升性能。

2.binlog日志文件的文件表现形式
1）默认在安装目录下，存在mysql-bin.00001, mysql-bin.00002的二进制文件（binlog日志文件名依据my.cnf配置中的log-bin参数后面的设置为准）
2）还有mysql-bin.index用来记录被mysql管理的二进制文件列表
3）如果需要删除二进制日志时，切勿直接删除二进制文件，这样会使得mysql管理混乱。

3.binlog日志文件查看相关mysql命令
1）SHOW MASTER STATUS ; 查看正在使用的二进制文件
MariaDB [(none)]> SHOW MASTER STATUS ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 245 | | |
+------------------+----------+--------------+------------------+
2）FLUSH LOGS; 手动滚动二进制日志
MariaDB [(none)]> FLUSH LOGS;
MariaDB [(none)]> SHOW MASTER STATUS ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 245 | | |
+------------------+----------+--------------+------------------+
滚动以后，mysql重新创建一个新的日志mysql-bin.000004
3）SHOW BINARY LOGS 显示使用过的二进制日志文件
MariaDB [(none)]> SHOW BINARY LOGS ;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30373 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 288 |
| mysql-bin.000004 | 245 |
4）SHOW BINLOG EVENTS 以表的形式查看二进制文件
命令格式：SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000001' \G;
*************************** 99. row ***************************
Log_name: mysql-bin.000001
Pos: 30225
Event_type: Query
Server_id: 1
End_log_pos: 30354
Info: use `mysql`; DROP TEMPORARY TABLE `tmp_proxies_priv` /* generated by server */

4.MySQL二进制文件读取工具mysqlbinlog
命令格式：mysqlbinlog [参数] log-files
有以下四种参数选择：
--start-datetime
--stop-datetime
--start-position
--stop-position
[root@test-huanqiu ~]# mysqlbinlog --start-position 30225 --stop-position 30254 mysql-bin.000001
截取一下结果：
# at 30225
#151130 12:43:35 server id 1 end_log_pos 30354 Querythread_id=1exec_time=0error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1448858615/*!*/;
SET @@session.pseudo_thread_id=1/*!*/

根据以上截取结果第二行，进行解释二进制日志内容
1）时间点： 151130 12:43:35
2）服务器ID： server id 1
服务器ID主要用于标记日志产生的服务器，主要用于双主模型中，互为主从，确保二进制文件不会被相互循环复制
3）记录类型： Query
4) 线程号： thread_id = 1
5) 语句的时间戳和写入二进制日志文件的时间差； exec_time=0
6) 事件内容
7）事件位置 #at 30225
8) 错误代码 error_code=0
9) 事件结束位置 end_log_pos也就是下一事件开始的位置

5.二进制日志格式
由bin_log_format={statement|row|mixed}定义
1）statement: 基于语句，记录生成数据的语句
缺点在于如果当时插入信息为函数生成，有可能不同时间点执行结果不一样，
例如： INSERT INTO t1 VALUE (CURRENT_DATE());
2）row: 基于行数据
缺点在于，有时候数据量会过大
3）mixed：混合模式，又mysql自行决定何时使用statement, 何时使用row 模式

6.二进制相关参数总结
1）log_bin = {ON|OFF}
还可以是个文件路径，自定义binlog日志文件名，使用“log_bin=“或“log-bin=“都可以，主要用于控制全局binlog的存放位置和是否开启binlog日志功能。
比如：log_bin=mysql-bin 或者 log-bin=mysql-bin，这样binlog日志默认会和mysql数据放在同一目录下。
2） log_bin_trust_function_creators
是否记录在
3） sql_log_bin = {ON|OFF}
会话级别是否关闭binlog，如果关闭当前会话内的操作将不会记录
4） sync_binlog 是否马上同步事务类操作到二进制日志中
5） binlog_format = {statement|row|mixed} 二进制日志的格式，上面单独提到了
6） max_binlog_cache_size =
二进制日志缓冲空间大小，仅用于缓冲事务类的语句；
7） max_binlog_stmt_cache_size =
语句缓冲，非事务类和事务类共用的空间大小
8） max_binlog_size =
二进制日志文件上限，超过上限后则滚动
9) 删除二进制日志
命令格式：PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
MariaDB> PURGE BINARY LOGS TO 'mysql-bin.010';
MariaDB> PURGE BINARY LOGS BEFORE '2016-11-02 22:46:26';
建议：切勿将二进制日志与数据文件放在一同设备；可以将binlog日志实时备份到远程设备上，以防出现机器故障进行数据恢复；

二、接下来说下binlog二进制日志备份和恢复
1.为什么做备份：
（1）灾难恢复
（2）审计，数据库在过去某一个时间点是什么样的
（3）测试

2.备份的目的：
（1）用于恢复数据
（2）备份结束后，需要周期性的做恢复测试

3.备份类型：
（1）根据备份时，服务器是否在线
1）冷备(cold backup)：服务器离线，读写操作都不能进行
2）温备份：全局施加共享锁，只能读不能写
3）热备(hot backup)：数据库在线，读写照样进行
（2）根据备份时的数据集分类
1）完全备份(full backup)
2）部分备份(partial backup)
（3）根据备份时的接口
1）物理备份（physical backup）：直接复制数据文件，打包归档
特点：
不需要额外工具，直接归档命令即可，但是跨平台能力比较差；如果数据量超过几十个G，则适用于物理备份
2）逻辑备份(logical backup)：把数据抽取出来保存在sql脚本中
特点：
可以使用文本编辑器编辑；导入方便，直接读取sql语句即可；逻辑备份恢复时间慢，占据空间大；无法保证浮点数的精度；恢复完数据库后需要重建索引。
（4）根据备份整个数据还是变化数据
1）全量备份 full backup
2）增量备份 incremental backup
在不同时间点起始备份一段数据，比较节约空间；针对的是上一次备份后有变化的数据，备份数据少，备份快，恢复慢
3）差异备份 differential backup
备份从每个时间点到上一次全部备份之间的数据，随着时间增多二增多；比较容易恢复；对于很大的数据库，可以考虑主从模型，备份从服务器的内容。针对的是上一次全量备份后有变化的数据，备份数据多，备份慢，恢复快。
（5）备份策略，需要考虑因素如下
备份方式
备份实践
备份成本
锁时间
时长
性能开销
恢复成本
恢复时长
所能够容忍丢失的数据量
（6）备份内容
1）数据库中的数据
2）配置文件
3）mysql中的代码：存储过程，存储函数，触发器
4）OS 相关的配置文件，chrontab 中的备份策略脚本
5）如果是主从复制的场景中：跟复制相关的信息
6）二进制日志文件需要定期备份，一旦发现二进制文件出现问题，需马上对数据进行完全备份

(7)Mysql最常用的三种备份工具：
1）mysqldump：
通常为小数据情况下的备份
innodb：热备，温备
MyISAM, Aria: 温备
单线程备份恢复比较慢
2）Xtrabackup（通常用innobackupex工具）：
备份mysql大数据
InnoDB热备，增量备份；
MyISAM温备，不支持增量，只有完全备份
属于物理备份，速度快；
3）lvm-snapshot：
接近于热备的工具：因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁；
使用cp、tar等工具进行物理备份；
备份和恢复速度较快；
很难实现增量备份，并且请求全局需要等待一段时间，在繁忙的服务器上尤其如此；

除此之外，还有其他的几个备份工具：
-->mysqldumper: 多线程的mysqldump
-->SELECT clause INTO OUTFILE '/path/to/somefile' LOAD DATA INFILE '/path/from/somefile'
部分备份工具，不会备份关系定义，仅备份表中的数据；
逻辑备份工具，快于mysqldump，因为不备份表格式信息
-->mysqlhotcopy: 接近冷备，基本没用

mysqldump工具基本使用
1. mysqldump [OPTIONS] database [tables…]
还原时库必须存在，不存在需要手动创建
--all-databases:　备份所有库
--databases db1 db2 ...: 备份指定的多个库，如果使用此命令，恢复时将不用手动创建库。或者是-B db1 db2 db3 ....
--lock-all-tables：请求锁定所有表之后再备份，对MyISAM、InnoDB、Aria做温备
--lock-table: 对正在备份的表加锁，但是不建议使用，如果其它表被修改，则备份后表与表之间将不同步
--single-transaction: 能够对InnoDB存储引擎实现热备；
启动一个很大的大事物，基于MOCC可以保证在事物内的表版本一致
自动加锁不需要，再加--lock-table, 可以实现热备
备份代码：
--events: 备份事件调度器代码
--routines: 备份存储过程和存储函数
--triggers：备份触发器
备份时滚动日志：
--flush-logs: 备份前、请求到锁之后滚动日志；
方恢复备份时间点以后的内容
复制时的同步位置标记：主从架构中的，主服务器数据。效果相当于标记一个时间点。
--master-data=[0|1|2]
0: 不记录
1：记录为CHANGE MASTER语句
2：记录为注释的CHANGE MASTER语句

2. 使用mysqldump备份大体过程：
1) 请求锁：--lock-all-tables或使用–singe-transaction进行innodb热备；
2) 滚动日志：--flush-logs
3) 选定要备份的库：--databases
4) 记录二进制日志文件及位置：--master-data=
FLUSH TABLES5 WITH READ LOCK;

3. 恢复：
恢复过程无需写到二进制日志中
建议：关闭二进制日志，关闭其它用户连接；

4. 备份策略：基于mysqldump
备份：mysqldump+二进制日志文件；（“mysqldump >”）
周日做一次完全备份：备份的同时滚动日志
周一至周六：备份二进制日志；
恢复：（“mysql < ”）或在mysql数据库中直接执行“source sql备份文件;”进行恢复。如果sql执行语句比较多，可以将sql语句放在一个文件内，将文件名命名为.sql结尾，然后在mysql数据库中使用"source 文件.sql;"命令进行执行即可！
完全备份+各二进制日志文件中至此刻的事件

5. 实例说明：
参考：Mysql备份系列（2）--mysqldump备份（全量+增量）方案操作记录

lvm-snapshot：基于LVM快照的备份
1.关于快照：
1）事务日志跟数据文件必须在同一个卷上；
2）刚刚创立的快照卷，里面没有任何数据，所有数据均来源于原卷
3）一旦原卷数据发生修改，修改的数据将复制到快照卷中，此时访问数据一部分来自于快照卷，一部分来自于原卷
4）当快照使用过程中，如果修改的数据量大于快照卷容量，则会导致快照卷崩溃。
5）快照卷本身不是备份，只是提供一个时间一致性的访问目录。

2.基于快照备份几乎为热备：
1）创建快照卷之前，要请求MySQL的全局锁；在快照创建完成之后释放锁；
2）如果是Inoodb引擎，当flush tables 后会有一部分保存在事务日志中，却不在文件中。因此恢复时候，需要事务日志和数据文件
但释放锁以后，事务日志的内容会同步数据文件中，因此备份内容并不绝对是锁释放时刻的内容，由于有些为完成的事务已经完成，但在备份数据中因为没完成而回滚。因此需要借助二进制日志往后走一段

3.基于快照备份注意事项：
1）事务日志跟数据文件必须在同一个卷上；
2）创建快照卷之前，要请求MySQL的全局锁；在快照创建完成之后释放锁；
3）请求全局锁完成之后，做一次日志滚动；做二进制日志文件及位置标记(手动进行)；

4.为什么基于MySQL快照的备份很好？
原因如下几点：
1）几乎是热备在大多数情况下，可以在应用程序仍在运行的时候执行备份。无需关机，只需设置为只读或者类似只读的限制。
2）支持所有基于本地磁盘的存储引擎它支持MyISAM, Innodb, BDB，还支持 Solid, PrimeXT 和 Falcon。
3）快速备份只需拷贝二进制格式的文件，在速度方面无以匹敌。
4）低开销只是文件拷贝，因此对服务器的开销很细微。
5）容易保持完整性想要压缩备份文件吗？把它们备份到磁带上，FTP或者网络备份软件 -- 十分简单，因为只需要拷贝文件即可。
6）快速恢复恢复的时间和标准的MySQL崩溃恢复或数据拷贝回去那么快，甚至可能更快，将来会更快。
7）免费无需额外的商业软件，只需Innodb热备工具来执行备份。

快照备份mysql的缺点：
1）需要兼容快照 -- 这是明显的。
2）需要超级用户(root) 在某些组织，DBA和系统管理员来自不同部门不同的人，因此权限各不一样。
3）停工时间无法预计，这个方法通常指热备，但是谁也无法预料到底是不是热备 -- FLUSH TABLES WITH READ LOCK 可能会需要执行很长时间才能完成。
4）多卷上的数据问题如果你把日志放在独立的设备上或者你的数据库分布在多个卷上，这就比较麻烦了，因为无法得到全部数据库的一致性快照。不过有些系统可能能自动做到多卷快照。

5.备份与恢复的大体步骤
备份：
1）请求全局锁，并滚动日志
mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS;
2）做二进制日志文件及位置标记(手动进行)；
[root@test-huanqiu ~]# mysql -e 'show master status' > /path/to/orignal_volume
3）创建快照卷
[root@test-huanqiu ~]# lvcreate -L -s -n -p r /path/to/some_lv
4）释放全局锁
5）挂载快照卷并备份
6）备份完成之后，删除快照卷

恢复：
1）二进制日志保存好；
提取备份之后的所有事件至某sql脚本中；
2）还原数据，修改权限及属主属组等，并启动mysql
3）做即时点还原
4）生产环境下，一次大型恢复后，需要马上进行一次完全备份。

备份与恢复实例说明：
环境，实现创建了一个test_vg卷组，里面有个mylv1用来装mysql数据，挂载到/data/mysqldata

备份实例：
1. 创建备份专用的用户，授予权限FLUSH LOGS 和 LOCK TABLES
MariaDB > GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'lvm'@'192.168.1.%' IDENTIFIED BY 'lvm';
MariaDB > FLUSH PRIVILEGES;

2. 记录备份点
[root@test-huanqiu ~]# mysql -ulvm -h192.168.1.10 -plvm -e 'SHOW MASTER STATUS' > /tmp/backup_point.txt

3. 创建快照卷并挂载快照卷
[root@test-huanqiu ~]# lvcreate -L 1G -s -n lvmbackup -p r /dev/test_vg/mylv1
[root@test-huanqiu ~]# mount -t ext4 /dev/test_vg/lvmbackup /mnt/

4. 释放锁
[root@test-huanqiu ~]# mysql -ulvm -h192.168.98.10 -plvm -e 'UNLOCK TABLES'
做一些模拟写入工作
MariaDB [test]> create database testdb2

5. 复制文件
[root@test-huanqiu ~]# cp /data/mysqldata /tmp/backup_mysqldata -r

6. 备份完成卸载，删除快照卷
[root@test-huanqiu ~]# umount /mnt
[root@test-huanqiu ~]# lvmremove /dev/test_vg/lvmbackup

还原实例：
假如整个mysql服务器崩溃，并且目录全部被删除

1. 数据文件复制回源目录
[root@test-huanqiu ~]# cp -r /tmp/backup_mysqldata/* /data/mysqldata/
MariaDB [test]> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| mysqldata |
| openstack |
| performance_schema |
| test |
+--------------------+
此时还没有testdb2，因为这个是备份之后创建的，因此需要通过之前记录的二进制日志

2. 查看之前记录的记录点。向后还原
[root@test-huanqiu ~]# cat /tmp/backup_point.txt
FilePositionBinlog_Do_DBBinlog_Ignore_DB
mysql-bin.000001245
[root@test-huanqiu ~]# mysqlbinlog /data/binlog/mysql-bin.000001 --start-position 245 > tmp.sql
MariaDB [test]> source /data/mysqldata/tmp.sql
MariaDB [test]> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| mysqldata |
| openstack |
| performance_schema |
| test |
| testdb2 |
+--------------------+
8 rows in set (0.00 sec)
testdb2 已经被还原回来。

具体实例说明，参考：Mysql备份系列（4）--lvm-snapshot备份mysql数据(全量+增量）操作记录

使用Xtrabackup进行MySQL备份：

参考：Mysql备份系列（3）--innobackupex备份mysql大数据(全量+增量）操作记录

--------------------------------------------------------------------------------------
关于备份和恢复的几点经验之谈

备份注意：
1. 将数据和备份放在不同的磁盘设备上；异机或异地备份存储较为理想；
2. 备份的数据应该周期性地进行还原测试；
3. 每次灾难恢复后都应该立即做一次完全备份；
4. 针对不同规模或级别的数据量，要定制好备份策略；
5. 二进制日志应该跟数据文件在不同磁盘上，并周期性地备份好二进制日志文件；

从备份中恢复应该遵循步骤：
1. 停止MySQL服务器；
2. 记录服务器的配置和文件权限；
3. 将数据从备份移到MySQL数据目录；其执行方式依赖于工具；
4. 改变配置和文件权限；
5. 以限制访问模式重启服务器；mysqld的--skip-networking选项可跳过网络功能；
方法：编辑my.cnf配置文件，添加如下项：
skip-networking
socket=/tmp/mysql-recovery.sock
6. 载入逻辑备份（如果有）；而后检查和重放二进制日志；
7. 检查已经还原的数据；
8. 重新以完全访问模式重启服务器；
注释前面第5步中在my.cnf中添加的选项，并重启；