一、数据库数据备份进阶方式一：利用命令参数 –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.

示例说明：

# 进行数据库单库备份操作

[root@db01-51 ~]# mysqldump -uroot -p123456 -B zq  --single-transaction --set-gtid-purged=OFF >/database_backup/zq.sql

二、数据库数据备份进阶方式二：利用命令参数 –master-data=2(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[=#]参数作用：

在备份期间，会将备份后的binlog位置点信息存储到备份文件中

--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;

 在这种情况下，全局读锁只在备份开始时占用很短的时间

do not 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@master ~]# mysqldump -uroot -p123456 --master-data=2 --single-transaction -A -B >/tmp/bak.sql

-- -B 表示在备份时添加use语句信息

# 进阶方式数据备份（压缩备份）

[root@master ~]# mysqldump -uroot -p123456 --master-data=2 --single-transaction -A -B|gzip >/tmp/bak.sql.gz

-- 压缩数据解压命令

[root@master ~]# gzip -d /tmp/bak.sql.gz

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

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

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

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

序号	参数信息	官方说明	解释说明	参考链接
01	-R	Dump stored routines (functions and procedures)	表示进行数据库存储过程备份	https://www.runoob.com/w3cnote/mysql-stored-procedure.html
02	-E	Dump events	表示进行数据库事件信息备份	https://blog.csdn.net/JokerLJG/article/details/128701993
03	–triggers	Dump triggers for each dumped table.	表示进行触发器信息备份	https://blog.51cto.com/u_15061951/4326095

-R参数涉及名词解释说明：

• 存储过程：等价于数据库中的脚本信息，脚本可以完成一系列数据库的逻辑操作，相应前端程序代码文件进行调用即可

-E参数涉及名词解释说明：

• 事件信息：可以设置数据库的计划任务，根据事件信息对数据库中的数据进行相应操作

–triggers参数涉及名词解释说明：

• 特殊的存储过程，是根据特定的事件信息，自动触发或执行的存储过程

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

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

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

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

# 数据库数据信息备份过程（全备）
[root@master ~]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/database_backup/full_`date +%F`
[root@master ~]# ll /database_backup/
-rw-r--r-- 1 root root 51254551 11月 26 00:47 full_2022-11-26
# 数据库数据备份信息查看
[root@master ~]# vim /database_backup/full_2022-11-26.sql
-- 表示在进行数据恢复操作时，会将gtid1-6的事件信息删除掉，因为在之前备份数据中已经有了1-6的事件数据信息；
-- 因此，从GTID的编号来看，可以从编号7事件开始进行数据增量恢复；
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '9d14be39-6423-11ed-bb21-000c2996c4f5:1-6';
-- 输出信息表示增量数据的临界点在binlog.000013日志文件的1312位置，同时是备份结束时的位置点；
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000013', MASTER_LOG_POS=1312;