mysql 逻辑备份实战（mysqldump）进阶

mysql 逻辑备份实战（mysqldump）进阶

上文中已经提到mysqldump命令工具基本的数据库和数据表的备份方式，除了以上说明的参数用法，还有其他备份参数信息；

同样可以实现进阶方式的数据库信息备份保存：

数据库数据备份进阶方式一：利用命令参数 --single-transaction

 

这个参数的用法作用可以利用一个形象的例子去理解：比如在某个时刻班主任希望统计班级同学的数量情况，那么该如何统计准确呢？

方法一：

形象说明：锁门封闭统计，禁止人员在教室内外随意走动，取班级人数变化的静止状态的学生数量；

真实应用：锁表封闭备份，禁止数据库程序进行数据更新操作，实现静止锁表状态进行数据备份；（一般选择半夜操作）

方法二：

形象说明：瞬时拍照统计，允许人员在教室内外随意走动，但是会根据拍照时刻人员数量进行统计；

真实应用：瞬时节点备份，允许数据库程序进行数据更新操作，只把备份操作瞬间已有数据备份；

因此，利用--single-transaction参数进行数据备份，就等价于在备份的时候给数据库的数据拍了照，备份时候数据库可以继续更新；

命令参数官方信息详细解读：

- 对于InnoDB存储引擎的表，将会利用MVCC中的一致性快照进行备份；
- 在备份数据期间不要出现DDL操作语句信息，如果出现DDL操作语句，将会导致备份数据不一致；

 

--single-transaction 
Creates a consistent snapshot by dumping all tables in a single transaction. 
通过在单个事务中备份所有表时，会创建一致性快照
Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); 
仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB）
对于InnoDB，会利用MVCC中一致性快照进行备份；

the dump is NOT guaranteed to be consistent for other storage engines. 
这种方式的备份不能保证与其他存储引擎一致
While a --single-transaction dump is in process, to ensure a valid dump file 
当--single-transaction参数应用在备份进程中时，确保备份文件的有效性
(correct table contents and binary log position), 
含有正确的表内容和binlog日志位置点
no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them. 
在进行备份数据期间，不要出现相关DDL的操作信息，导致备份数据不一致；
Option automatically turns off --lock-tables.

 

数据库数据备份进阶方式二：利用命令参数 --master-data=2

 

数据备份痛点：在进行数据库全备+binlog恢复数据时，如何进行binlog的临界点（起点）截取操作？

在备份数据的时候会记录binlog日志位置点到备份文件中，这个位置点是上一次全备之后新增数据的临界点；

在未来数据库服务出现异常时，会先恢复全备的数据信息，然后恢复binlog日志临界点之后的数据信息；

在指定日志位置点进行备份的时候，生成的操作日志语句如下：

 

CHANGE MASTER TO MASTER_LOG_FILE='binlog.000011', MASTER_LOG_POS=2335;
-- binlog.000011 表示临界点之后的文件信息
-- 2335表示全备进行时的位置点信息       binlog.000001  binlog.000002   binlog00003

令参数官方信息详细解读：

• 利用此参数功能，可以实现自动记录位置点信息；

• 利用此参数功能，可以实现自动添加全局读锁（GRL）功能（在配合--single-transaction参数使用时，可以减少锁时间）；

--master-data[=#]   
This option is deprecated and will be removed in a future version. Use source-data instead. 
 此选项已弃用，将在以后数据库服务的版本中删除，请使用source-data代替此参数使用；
 
 --source-data[=#]   
This causes the binary log position and filename to be appended to the output. 
这个参数会导致binlog日志位置点信息和文件名信息会附加到输出中，即附件到备份文件中。

If equal to 1, will print it as a CHANGE MASTER command; 
如果数值等于1，将输出显示change master的命令信息；
if equal to 2, that command will be prefixed with a comment symbol. 
如果数值等于2，该命令将以注释符号作为前缀

This option will turn --lock-all-tables on, unless --single-transaction is specified too 
这个参数在使用时，将会自动开启--lock-all-tables参数功能，除非也指定了--single-transaction参数信息；
(in which case a global read lock is only taken a short time at the beginning of the dump; 
 在这种情况下，全局读锁只在备份开始时占用很短的时间
don't forget to read about --single-transaction below). 
不要忘记阅读一下--single-transaction参数功能说明
In all cases, any action on logs will happen at the exact moment of the dump. 
在所有情况下，日志上的任何操作都将在备份的确切时刻发生
Option automatically turns --lock-tables off.
参数将自动关闭 --lock-tables参数功能

 

备份数据进阶方式实践：

# 进阶方式数据备份（不压缩备份）
[root@xiaoQ ~]# mysqldump -uroot -pbaimei123 --master-data=2 --single-transaction -A -B >/tmp/bak.sql
-- -B 表示在备份时添加use语句信息

# 进阶方式数据备份（压缩备份）
[root@xiaoQ ~]# mysqldump -uroot -pbaimei123 --master-data=2 --single-transaction -A -B|gzip >/tmp/bak.sql.gz
[root@xiaoQ ~]# gzip -d /tmp/bak.sql.gz
-- 压缩数据解压命令

 

数据库数据备份进阶方式三：利用命令参数 -R -E --triggers

 

模拟时间-某周周二晚零点，企业数据库管理员进行一次数据库服务数据全备操作**

 

[root@xiaoQ ~]# mysqldump -uroot -pbaimei123 -B mdb --master-data=2 --single-transaction -R -E --triggers >/databases_backup/baimei_`date +%F`.sql

以上mysqldump备份中的特殊参数说明：

 

数据库数据备份进阶方式四：利用命令参数 --max_allowed_packet=64M

此参数表示允许进行传输的数据包大小，在某些时候如果备份的数据为大表数据，需要调整此参数信息；

如果没有正确的设置此参数信息，可能会导致备份大表数据时，会出现数据备份失败的情况；

结合以上参数信息，进行标准化数据备份操作：

# 数据库数据信息备份过程（全备）
[root@xiaoQ-01 ~]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/database_backup/full_`date +%F`
[root@xiaoQ-01 ~]# ll /database_backup/
-rw-r--r-- 1 root root 51254551 11月 26 00:47 full_2022-11-26

# 数据库数据备份信息查看
[root@xiaoQ-01 ~]# vim /database_backup/full_2022-11-26.sql 
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '9d14be39-6423-11ed-bb21-000c2996c4f5:1-6';
-- 表示在进行数据恢复操作时，会将gtid1-6的事件信息删除掉，因为在之前备份数据中已经有了1-6的事件数据信息；
-- 因此，从GTID的编号来看，可以从编号7事件开始进行数据增量恢复；
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000013', MASTER_LOG_POS=1312;
-- 输出信息表示增量数据的临界点在binlog.000013日志文件的1312位置，同时是备份结束时的位置点；

 

欢迎来撩 ： 汇总all