IT狗们，你的MySQL要升级了！

IT敢客 2018-07-03

311

MySQL 可算是非常热门的一款数据库了，不过我还是比较更喜欢 Oracle 了！题外话，哈哈

这里我要说的并不是最新的 MySQL 8 哦，我要说的是 MySQL5.7！为什么我要这么鼓励大家去升级 MySQL 呢？因为相比之前的 MySQL 版本，我真是对它有许多诟病之处不满。记得之前看过的一篇文章写的是各大数据库之间的对比，MySQL 还是有些处于下风的。可惜的是我现在找不到那篇文章了，无法分享给大家看看，好有一个深刻的认知理解。

这里强烈推荐大家升级 Mysql 到 5.7 版本或者说 MySQL-5.7.7 之后的版本。最主要的原因是GTID，虽然GTID 是 MySQL-5.6.5 就开始支持了，MySQL-5.6.10 后开始完善。MySQL 5.6 版本开启 GTID 模式，但是必须打开参数 log_slave_updates，简单来说就是必须在从机上再记录一份二进制日志。这样的无论对性能还是存储的开销，无疑会相应的增大。而 MySQL 5.7 版本开始无需在 GTID 模式下启用参数 log_slave_updates，其中最重要的原因在于 5.7 在 mysql 库下引入了新的表 gtid_executed。也就是说 MySQL5.7 版本后才有了 mysql.gtid_executed 表。binlog_gtid_simple_recovery=TRUE(MySQL5.7.7 及以上默认)只迭代 mysql-bin.index 的首行和末行所对应的 binlog，gtid_executed 和 gtid_purged 的值就是取这两个 binlog 中的 Previous_gtids_log_event/Gtid_log_event 计算，当然 gtid_executed 变量的值还要结合 mysql.gtid_executed。这些所有和 GTID 有关系的功能演进，表明了 MySQL 有绝对的优势相对于之前的老版本。对于大家可以细读之前的两篇文章深刻理解 gtid_executed 和 gtid_purged和MySQL 主从复制与 GTID 复制原理，如果想了解的更加详细清楚，就去看下简书上 Mysql 5.7 Gtid 内部学习十篇系列文章，写的很好。看完以上，就会明白为什么需要升级到 MySQL-5.7.7 之后的版本了。

主要来说，还是对于 MySQL 的备份与恢复有巨大帮助。我们知道 Oracle 里面的 RMAN 备份与恢复机制有多牛逼，可以恢复到故障点之前的指定时间，这样有效能减少生产损失。MySQL 的GTID现在也具备这样的功能。深刻理解 gtid_executed 和 gtid_purged里面的实验就说明了这几点。

这里拿 MySQL-5.7.7 以上版本做一个小小的总结：恢复一个 MySQL，需要两个重要的参数gtid_executed 和gtid_purged，如何获取这个 gtid_executed 和 gtid_purged 的值，我们需要找到最新的 bin-log 和最旧的 bin-log，这里 MySQL 已经非常方便的处理了，在 mysql–bin.index 文件的首行和末行对应的就是 oldest（最旧）和 newest（最新），从最新的 bin-log 里面我们找到 gtids_in_binlog，然后数据库中找 gtid_executed(select * from mysql.gtid_executed)他们的并集（合集）就是 gtid_executed；然后再最旧的 bin-log 里面找到 Previous_gtids_log_event，然后 gtid_purged 的值就是 gtid_purged=gtid_executed-(gtids_in_binlog –Previous_gtids_log_event)；总结就是 gtid_executed 变量取的是 gtids_in_binlog 和 mysql.gtid_executed 的并集（合集），gtid_purged 的值就是 gtid_purged=gtid_executed-(gtids_in_binlog –Previous_gtids_log_event)。下面是一个简单的示例计算。

gtids_in_binlog：1–507528
oldest Previous_gtids_log_event：1–507510
gtids_in_binlog_not_purged：gtids_in_binlog – Previous_gtids_log_event = 507511–507528
gtid_purged 变量：gtid_executed – gtids_in_binlog_not_purged = (1–507528) – (507511–507528) = 1–507510

简书上有篇文章说的 Gtid 带来的运维改变还是非常有实用价值的，有以下 5 点。

如何跳过一个事务
mysqldump 导出行为的改变
5.7 中搭建基于 Gtid 的主从
5.7 中 Gtid 的主从的切换
5.7 中在线改变 Gtid 模式

一、如何跳过一个事务

和传统基于位置的主从不同，如果从库报错我们需要获得从库执行的最后一个事务，方法有如下：

show slave status \G 中的 Executed_Gtid_Set。
show global variables like ‘%gtid%’; 中的 gtid_executed 。
show master status;中的 Executed_Gtid_Set。

然后构建一个空事务如下：

stop slave ;set gtid_next='4a6f2a67-5d87-11e6-a6bd-000c29a879a3:34'; begin;commit;set gtid_next='automatic'; start slave ;

如果是多个如下：

stop slave ;set gtid_next='89dfa8a4-cb13-11e6-b504-000c29a879a3:3'; begin;commit;set gtid_next='89dfa8a4-cb13-11e6-b504-000c29a879a3:4'; begin;commit;set gtid_next='automatic'; start slave ;

二、 mysqldump 导出行为的改变

使用 mysqldump 受到选项 set-gtid-purged=AUTO 的影响，假如我们在 Gtid 开启和关闭的情况下使用如下语句导出数据：

mysqldump --single-transaction --master-data=2 -R -E --triggers --all-databases

在 Gtid 开启的情况下会多如下设置：

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; -- -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED='ec9bdd78-a593-11e7-9315-5254008138e4:1-105';

为什么要这么设置呢？因为如果做基于 Gtid 的主从，是否生成 binlog 就意味着在导入数据的时候是否基于本地数据库生成新的 Gtid 事务，显然这是不合理的，所以将 SQL_LOG_BIN 设置为 0 是必须的。接着 GTID_PURGED 被设置为备份时刻已经执行过的 Gtid 事务，如前文第五节源码剖析设置 GTID_PURGED 会设置三个地方的 Gtid 如下:

mysql.gtid_executed 表
gtid_purge 变量
gtid_executed 变量

看起来是合理的，但是如果这里忽略了整个 mysql.gtid_executed 表是 innodb 表，导入过程中某些版本(已知 percona 5.7.14，5.7.17)会重新删除和建立，因此通过 GTID_PURGED 设置的 mysql.gtid_executed 表会重新改变，重启数据库后需要读取 mysql.gtid_executed 表可能获得错误 Gtid 集合导致复制错误。
当然也可以使用 –set-gtid-purged=OFF 选项来告诉 mysqldump 不需要加入 SQL_LOG_BIN= 0 和 GTID_PURGED，但是初始化搭建基于 Gtid 的主从一定不要设置为 OFF。下面是这个选项的含义。

--set-gtid-purged[=name] Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible values for this option are ON, OFF and AUTO. If ON is used and GTIDs are not enabled on the server, an error is generated. 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.

三、5.7 中搭建基于 Gtid 的主从

这里存在一个注意点，我们还是直接说步骤

注意主备库必须开启 Gtid 和设置好 server_id

enforce_gtid_consistency = ON gtid_mode = ON server_id = 9910 binlog_format = row

同时主备库都开启 binlog 如果不设置级联从库，从库不要设置 log_slave_updates 参数。
这是最合理的设置。

建立复制用户

CREATE USER 'repl'@'%' IDENTIFIED BY 'test123'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' ;

导出数据

mysqldump --single-transaction --master-data=2 -R -E --triggers --all-databases > test.sql

从库导入数据
source 即可。
从库执行 reset master 语句
这一步主要防止 gtid_executed 被更改过。这个问题在在 percona 5.7.14 5.7.17 存在但是在 percona 5.7.15 5.7.19 又不存在。所以为了安全还是执行下面的两步。

reset master；

提取 GTID_PURGED，并且执行
使用 head -n 40 命令可以快速的得到比如我这里的

-- -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED='ec9bdd78-a593-11e7-9315-5254008138e4:1-21';

备库执行

SET @@GLOBAL.GTID_PURGED='ec9bdd78-a593-11e7-9315-5254008138e4:1-21';

语句即可，完成本部分 mysql.gtid_executed 表会重构。

使用 MASTER_AUTO_POSITION 建立同步

change master to master_host='192.168.99.41', master_user='repl', master_password='test123', master_port=3310, MASTER_AUTO_POSITION = 1;

启动 slave

start slave

四、5.7 中 Gtid 的主从的切换

切换中必须要确认从库(新主库)没有做过本地的事务，如果做过，否则切换主库(新从库)需要拉取这一部分的 Gtid 事务，如果这些 binlog 已经不存在了那么势必会报错。这种情况下还是从建从库吧。那么我们来说正常的切换步骤。

从库(新主库)

stop slave; reset slave all;

主库(新从库)

change master to master_host='192.168.99.40', master_user='repl', master_password='test123', master_port=3310, MASTER_AUTO_POSITION = 1; start slave;

实际就这么简单，从库(新主库)会生成自己的 Gtid 事务，新主库接受到后执行即可。此时会出现如下有两个 server_uuid 对应的 Gtid，如下的 gtid_executed

mysql> show global variables like '%gtid%'; +----------------------------------+-------------------------------------------------------------------------------------+| Variable_name | Value | +----------------------------------+-------------------------------------------------------------------------------------+ | binlog_gtid_simple_recovery | ON || enforce_gtid_consistency | ON | | gtid_executed | 31704d8a-da74-11e7-b6bf-525400a7d243:1-9, ec9bdd78-a593-11e7-9315-5254008138e4:1-25 || gtid_executed_compression_period | 1000 | | gtid_mode | ON || gtid_owned | | | gtid_purged | ec9bdd78-a593-11e7-9315-5254008138e4:1-25 || session_track_gtids | OFF | +----------------------------------+-------------------------------------------------------------------------------------+

总的说来如果要作为的切换的从库，不要在从库本地做任何事务。如果确实要做比如加索引等不影响数据的操作可以是使用如下：

mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> create index test_jjj on jjj(id); Query OK, 0 rows affected (0.42 sec) Records: 0 Duplicates: 0 Warnings: 0

这样也是不会增加本地 Gtid 的。

五、在线修改 Gtid 模式

这是 5.7.6 以后实现的功能其主要依赖了我们前面分析的 Previous gtid Event 以及参数 gtid_mode 新加入的 2 个值。我们具体来看看 gitd_mode 各个值的含义：

OFF(0): Both new and replicated transactions must be anonymous.(生成的是匿名事务，slave 也只能应用匿名事务)
OFF_PERMISSIVE:(1) New transactions are anonymous. Replicated transactions can be either
anonymous or GTID transactions.(生成的是匿名事务，slave 可以应用匿名和 GTID 事务)
ON_PERMISSIVE(2): New transactions are GTID transactions. Replicated transactions can be either
anonymous or GTID transactions.(生成的是 GTID 事务，slave 可以应用匿名和 GTID 事务)
ON(3): Both new and replicated transactions must be GTID transactions(生成的是 GTID 事务，slave 也只能应用 GTID 事务)

注意每次修改值必然导致一次 binlog 的切换，如果发生 binlog 删除也能够依托 Previous gtid Event 快速准确的找到 gtid_purged(Gtid_state.lost_gtids)。

在线启动

主库/从库执行

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

确定事务都支持 gtid，不会在 err log 中出现警告如下：
2017-02-26T22:35:24.322055Z 55 [Warning] Statement violates GTID consistency: CREATE TABLE … SELECT.

主库/从库执行

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

主库/从库执行

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

生成的是匿名事务，slave 可以应用匿名和 GTID 事务

主库/从库执行

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

生成的是 GTID 事务，slave 可以应用匿名和 GTID 事务

主库/从库执行

确定已经没有匿名的事务

SHOW GLOBAL STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

同时确认从库
Retrieved_Gtid_Set
Executed_Gtid_Set
正常增长

到这一步实际上 gtid 事务已经开始使用了。

主库/从库执行

SET @@GLOBAL.GTID_MODE = ON;

从库执行

stop slave; CHANGE MASTER TO MASTER_AUTO_POSITION = 1; start slave;

主库/从库执行
修改配置文件 my.cnf，将参数的更改加入到配置文件

在线关闭

从库执行

stop slave;

记录从库执行状态值

Exec_Master_Log_Pos: 7631438Relay_Master_Log_File: bin_log.000016

执行

CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = 'bin_log.000016', MASTER_LOG_POS = 7631438 start slave;

主库/从库执行

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

生成的是 GTID 事务，slave 可以应用匿名和 GTID 事务

主库/从库执行

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

生成的是匿名事务，slave 可以应用匿名和 GTID 事务

从库执行

等待从库
Retrieved_Gtid_Set
Executed_Gtid_Set
不再变动。
完成这一步实际上 GTID 事务已经没有生成和应用了

主库/从库执行

SET @@GLOBAL.GTID_MODE = OFF;

主库/从库执行

修改配置文件 my.cnf，将参数的更改加入到配置文件

六、innobackupex 下GTID主从的配置

这里我们用 innobackupex 来讲一下GTID主从的配置。和上面 mysqldump 一样，都要保持生产不停服务，不锁表的情况下作的，mysqldump 加了参数–single-transaction，虽然说不锁表，但是还是有锁表的可能。另外获取需要的 gtid_purged 值也不是很方便。innobackupex 还是很方便的，它的原理是拷贝文件，不生成 sql，所以要安全很多。下面将操作步骤。

1、主从服务器都用 dbbak 账号建立备份目录
主 ip：192.168.3.25
备 ip：192.168.2.15

mkdir -p /home/dbbak/backup/full
chown -R dbbak:dbbak /home/dbbak/backup

2、主库服务器免密登陆备库服务器

ssh-keygen
ssh-copy-id -p 1022 dbbak@192.168.2.15

3、主库备份数据
主库 root 用户执行：

innobackupex --defaults-file=/etc/my.cnf --host=192.168.3.25 --user=root --password=123456tAA --stream=tar /home/dbbak/backup/full|ssh -p 1022 dbbak@192.168.2.15 cat ">"/home/dbbak/backup/full/$(date +%Y-%m-%d_%H_%M_%S).tar

4、备库服务器停止 mysql 进程

service mysqld stop

5、备库服务器将备份文件解压
root 操作

cd /home/dbbak/backup/full
mkdir 2018-05-23_17_10_51
mv *.tar 2018-05-23_17_10_51
cd 2018-05-23_17_10_51
tar xvf *.tar
rm *.tar

6、备库服务器删除原来的数据库目录重建

rm -rf /home/data/mysqldb
mkdir -p /home/data/mysqldb
innobackupex --defaults-file=/etc/my.cnf --host=192.168.2.15 --user=root --password=123456 --apply-log /home/dbbak/backup/full/2018-05-23_17_10_51

7、备库服务器备份恢复和启动服务：

innobackupex --defaults-file=/etc/my.cnf --host=192.168.2.15 --user=root --password=123456 --copy-back /home/dbbak/backup/full/2018-05-23_17_10_51
chown -R mysql.mysql /home/data/mysqldb
service mysqld start

8、主库服务器创建同步用户：
master:
grant replication slave,replication client on *.* to replicate@’%’ identified by ‘123456’;
flush privileges;

9、备库服务器上找到需要的 gtid_purged 值

more /home/dbbak/backup/full/2018-05-23_17_10_51/xtrabackup_binlog_info
mysql-bin.000003 77982114 09794eee-5d60-11e8-be67-005056bc0a89:1-68237,
2d9ee028-5d95-11e8-bfc2-00163e007f7c:1,
9c819762-541c-11e8-8201-00163e007f7c:1-926963,
b51a6fb1-5d91-11e8-bfab-00163e007f7c:1,
c3d6fddc-3729-11e8-853c-005056bc0a89:1-584247,
ded0ef2e-5d93-11e8-bfba-00163e007f7c:1

记录下 Executed_Gtid_Set 值

10、备库上:
填入 gtid_purged 值(上面的 Executed_Gtid_Set)

SET @@GLOBAL.gtid_purged='09794eee-5d60-11e8-be67-005056bc0a89:1-68237,2d9ee028-5d95-11e8-bfc2-00163e007f7c:1,9c819762-541c-11e8-8201-00163e007f7c:1-926963,b51a6fb1-5d91-11e8-bfab-00163e007f7c:1,c3d6fddc-3729-11e8-853c-005056bc0a89:1-584247,ded0ef2e-5d93-11e8-bfba-00163e007f7c:1';
change master to master_host='192.168.3.25', master_user='replicate',master_password='123456', master_auto_position=1;
flush privileges;
start slave;
show slave status\G
show processlist;

11、如有主键冲突，在 my.cnf 里写入以下 2 个参数忽略，重启 mysql

slave-skip-errors = 1032
slave-skip-errors = 1062

mysql

文章转载自IT敢客，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。