GreatSQL8.0.32-25使用GTID搭建主从复制的一些注意事项

GreatSQL是由开放原子开源基金会孵化的金融级开源数据库项目，全面支持ARM、x86、loongArch、SW-64等多样性计算架构。GreatSQL 数据库是一款开源免费数据库，可在普通硬件上满足金融级应用场景，具有高可用、高性能、高兼容、高安全等特性，可作为 MySQL 或 Percona Server for MySQL 的理想可选替换。从去年开始学习GreatSQL开源数据库，这款数据库在对mysql兼容性方面绝对是无出其右者。如果大家平时学习MySQL数据库，GreatSQL是一个不错的选择。

前几天用GreatSQL社区积累的金币换了一本书，名为《数据生态MySQL复制技术与生产实践》，书本的作者罗小波老师和沈刚老师介绍了关于MySQL复制技术的主要应用和技术原理。如果大家要学习MySQL，这本书是必读之书，但这本书也有一个不足之处，就是没有介绍组复制技术的原理和应用。这个不足之处，刚好GreatSQL社区有详细介绍。

今天介绍一下GreatSQL8.0.32-25使用GTID搭建主从复制过程中所遇到一些问题。首先我们看一下主从节点的配置文件，

主节点的配置文件

[root@sdw3:/root]# cat /etc/my.cnf

# Percona Server template configuration

#

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove the leading "# " to disable binary logging

# Binary logging captures changes between backups and is enabled by

# default. It's default setting is log_bin=binlog

# disable_log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

#

# Remove leading # to revert to previous value for default_authentication_plugin,

# this will increase compatibility with older clients. For background, see:

# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin

# default-authentication-plugin=mysql_native_password

enforce_gtid_consistency=true

gtid_mode=on

server_id=1

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

从上面的配置文件看，enforce_gtid_consistency=true

gtid_mode=on

server_id=1

这三项是主节点是必要配置的。server_id如果要配置传统的主从复制，这个也是要配置的。

从节点的配置文件内容如下：

[root@sdw2 ~]# cat /etc/my.cnf

# Percona Server template configuration

#

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove the leading "# " to disable binary logging

# Binary logging captures changes between backups and is enabled by

# default. It's default setting is log_bin=binlog

# disable_log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

#

# Remove leading # to revert to previous value for default_authentication_plugin,

# this will increase compatibility with older clients. For background, see:

# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin

# default-authentication-plugin=mysql_native_password

enforce_gtid_consistency=true

gtid_mode=on

log_slave_updates=true

server_id=2

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

以下四项是必须要配置的

enforce_gtid_consistency=true

gtid_mode=on

log_slave_updates=true

server_id=2。

上面主从节点配置完成后，一定要记得重启数据库使配置生效。

下面我把自己配置时遇到问题列举出来，

1、Last_IO_Error: error connecting to master ‘root@192.168.200.40:3306’ - retry-time: 60 retries: 1 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

遇到这种情况，是因为默认使用的是密码插件caching_sha2_password，我们要把之前在主节点创建的复制用户密码插件改为mysql_native_password，如下所示

alter user rep@'192.168.200.%' identified  with mysql_native_password by 'Good@2019’。

然后再在从节点上执行 stop slave；

start slave;

show slave status\G查看是不是还有其它报错信息，主从复制是否正常工作。

2、Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘d393d2e3-9b61-11e5-82bf-141877342ba0:171661170’ at master log mysql-bin.000063, end_log_pos 171661170. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

这种错误因为某个事务阻塞所致，我们只要跳过这个事务就行了。

Stop slave;

Set @@SESSION.GTID_NEXT=’ d393d2e3-9b61-11e5-82bf-141877342ba0:171661170’

Begin;

Commit;

Set @@SESSION.GTID_NEXT = AUTOMATIC;

Start slave;

再次查看从库状态，恢复正常即解决

Show slave status\G

关于第二个问题的处理方法，很多关于MYSQL的著作都有相关的介绍，比如《MySQL精英实战》一书有明确介绍。

现在很多开源数据库比如PG数据库是每年推出一个新版本，推出的时间大约在下半年9月左右。MySQL数据库推出的新版本速度更快，现在都推出到MySQL Community Server 9.5.0 Innovation了。

  GreatSQL 开源社区也于推出了GreatSQL 8.4.4-4，相比于GreatSQL8.0，新版本在复制技术上面使用的命令也有了不少变化。

任何一个新版本数据库推出时，自带了很多技术特性，这些特性可能DBA不太容易用到，但对数据库开发人员可能非常有用，这些技术特性有可能会在关键时刻决定数据库的生死。