关于MySQL备份、主从同步的一些问题_mysql修改冷备命令

1. 备份

在工作中，经常会遇到需要对mysql数据库进行冷备的业务场景，以下脚本内容特针对冷备定制。

创建备份用户：

mysql> create user backup@'%' identified by 'Tta_eZt7XKr9';
Query OK, 0 rows affected (0.00 sec)
​
mysql> grant all on *.* to backup@'%';
Query OK, 0 rows affected (0.00 sec)

制定备份脚本：

#!/bin/bash
MYSQLUSER=backup
PASSWD='Tta_eZt7XKr9'
BACKUPDIR=/data/mysql/backup/data
BACKUPFILE=mysql_bak_all_`date +%Y%m%d`.sql.gz
DATA_10=mysql_bak_all_`date -d "-10 day" +%Y%m%d`.sql.gz
 
#判断备份文件存储目录是否存在，否则创建该目录 
if [ ! -d $BACKUPDIR ];then 
mkdir -p "$BACKUPDIR" 
fi 
 
#备份开始
cd $BACKUPDIR
mysqldump --parallel-schemas=0 --single-transaction --set-gtid-purged=OFF --add-drop-database -h 192.168.10.32 -u$MYSQLUSER -p$PASSWD -A |gzip > $BACKUPFILE
 
# 删除10天前的备份文件
cd $BACKUPDIR
find ./ -type f -name mysql_bak_all_*.sql.gz | xargs rm -f 
 
echo `date`: "mysqldump is OK!"

2. set-gtid-purged

2024年6月7日，记录一下备份参数中的--set-gtid-purged

先看一下帮助文档中关于该参数的嗯解释：

  --set-gtid-purged[=name] 
                      Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible
                      values for this option are ON, COMMENTED, OFF and AUTO.
                      If ON is used and GTIDs are not enabled on the server, an
                      error is generated. If COMMENTED is used, 'SET
                      @@GLOBAL.GTID_PURGED' is added as a comment. If OFF is
                      used, this option does nothing. If AUTO is used and GTIDs
                      are enabled on the server, 'SET @@GLOBAL.GTID_PURGED' is
                      added to the output. If GTIDs are disabled, AUTO does
                      nothing. If no value is supplied then the default (AUTO)
                      value will be considered.

大致意思是该参数会决定是否在备份的输出文件中添加'SET @@GLOBAL.GTID_PURGED'，可以选择的参数为ON、COMMENTED、OFF和AUTO。

在数据库没有开启GTID的情况下，设置为ON，导出语句会报错，终止导出。

在数据库没有开启GTID的情况下，设置为OFF，此时这个导出参数对导出不做任何操作。

在数据库没有开启GTID的情况下，设置为AUTO，此时这个导出参数对导出不做任何操作。

在数据库开启GTID的情况下，设置为AUTO，导出文件中会包含SET @@SESSION.SQL_LOG_BIN= 0语句和'SET @@GLOBAL.GTID_PURGED'语句。

在数据库开启GTID的情况下，设置为ON，导出文件中会包含SET @@SESSION.SQL_LOG_BIN= 0语句和'SET @@GLOBAL.GTID_PURGED'语句。

在数据库开启GTID的情况下，设置为OFF，导出文件中不包含SET @@SESSION.SQL_LOG_BIN= 0语句和'SET @@GLOBAL.GTID_PURGED'语句。

在数据库开启GTID的情况下，设置为COMMENTED，导出的文件中会包含一个已经注释的'SET @@GLOBAL.GTID_PURGED'。

如果没有明确指定--set-gtid-purged的值，那么他将采用默认的AUTO值。

那么mysqldump时–set-gtid-purged参数到底有什么意义呢？

经过实验对比，该参数=on时比=off时多了如下内容：

# 文件开头下面几行
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
 
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '65ecd4fb-1e49-11ef-bb2c-0242ac110302:1-28413';
 
# 接近文件末尾几行
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

多出的这几行意味着什么呢？简单来说是以下两点。

1、导入时，目标库不再产生binlog；

2、导入时，目标库会记录源库导出时候的GTID值。

那么在什么时候该用ON，什么时候该用OFF呢？用几个常见的运维场景来解释一下。

场景一：

MySQL已经做好了主从同步，此时需要从其他某个MySQL数据库中导出数据到该主从当中。

此时mysqldump需要用--set-gtid-purged=OFF。因为导入主从时主库必须记录日志，这样才能同步到下游的从库。

场景二：

现有一个MySQL数据库，需要构建一个从库。

此时从现有MySQL中导出时需要用--set-gtid-purged=ON。因为新的从库必须知道目前全量的数据是在主库的哪个位置，也就是GTID_PURGED，这样才能保证同步的正常。

场景三：

日常数据迁移，例如从A库导入到B库。

此时推荐使用--set-gtid-purged=OFF。因为两个没有关联的库之间需要分别记录自己的GTID和binlog。使用ON反而可能会出现一些意外的问题。

3. reset master & reset slave

在主从复制的运维中，经常会用的到两个命令，reset master和reset slave，这里记录一下自己对这两个命令的理解。

3.1. reset master

删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。通常用于第一次进行搭建主从库时，进行主库binlog初始化工作；对于正在使用 GTID 的实例，reset master会清除掉系统变量gtid_purged和gtid_executed的值。

reset master会删除所有的二进制日志，而purge binary logs是一种基于时间点的删除。reset master将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件，起始值从000001 开始，然而purge binary logs命令并不会修改记录binlog的点位数值。

在正常的主从复制环境中，如果在master上执行reset master，结果是不可预测的。但使用purge binary logs语句删除binlog没多大影响（前提是，删除的binlog中的events已经传输到slave上）。

3.2. reset slave

删除从库的master.info和relay-log.info文件，并删除所有的relay log，然后重新生成一个新的relay log，即使relay log中还有SQL没有被SQL线程apply完。

但是reset slave有个问题，它虽然删除了上述文件，但内存中的change master信息并没有删除，此时，可直接执行start slave，但因为删除了master.info和relay-log.info，它会从头开始接受主的binlog并应用。

在基于GTID同步的从库中，该语句不会更改gtid_executed、 gtid_purged以及表mysql.gtid_executed的值 。如果您需要重置 GTID 执行历史记录，需要使用reset master命令。

3.3. reset slave all

相对于reset slave，reset slave all还会删除内存中的连接信息，这个时候，执行start slave会报错，需要重新配置主从同步关系。