云原生|kubernetes|部署MySQL一主多从复制集群（基于GTID的复制）

标签：binlog slave kubernetes 一主多复制 master Master 日志

前言：

一，

MySQL的主从复制优点如下：

数据更安全：做了数据冗余，不会因为单台服务器的宕机而丢失数据
性能大大提升：一主多从，不同用户从不同数据库读取，性能提升
扩展性更优：流量增大时，可以方便的增加从服务器，不影响系统使用
负载均衡：一主多从相当于分担了主机任务，做了负载均衡。

那么在实操之前，我们还是需要了解一下主从复制的原理：

二，

主从复制的原理：

MySQL的复制功能用三个线程来实现：

主库:Binlog Dump线程

从库:I/O线程和SQL线程



1.用户提交数据的修改，然后Master主库把所有数据库变更写进Binary Log二进制日志。

主库通过Binlog Dump线程把二进制日志内容推送给Slave从库，从库被动接收数据，不是主动获取，除非是新建连接。

2.在从库上执行START SLAVE语句时，已经使用CHANGE MASTER TO语句配置好复制信息，从库会创建一个I/O线程，该线程连接到主库并请求主库为其发送所需的二进制日志。

从库I/O线程与主库Binlog Dump线程成功建立连接后，从库I/O线程接收主库Binlog Dump线程发送的二进制日志，并将它们写入从库本地的Relay Log中继日志文件。

3.从库SQL线程读取并解析中继日志中的内容，按照读取的顺序进行回放，二进制日志中存放的事务顺序就是主库中事务的提交顺序，并将数据变更写入本地数据库文件中，这样就实现了数据在主从数据库实例之间的同步。

三，

主从复制的两种方式

传统复制，也可以称为基于二进制日志文件和位置的复制，在从库中配置复制时，要求指定从库中获取的二进制日志文件(binlog file)和位置(binlog position)，也就是基于Binlog+Position的复制。

GTID的复制，新的事务复制方法，利用GTID可以自动在主库寻找需要复制的二进制日志记录，因此不需要关心日志文件或位置，极大地简化了许多常见的复制任务，也就是基于GTID的复制。

MySQL 5.6之前只支持传统复制，即基于二进制日志文件和位置的复制，在5.6及之后的版本中，出现了基于GTID的复制，而在复制方面，MySQL的版本是向后兼容的（也就是MySQL5.6后可以传统复制也可以GTID复制，看心情咯）

四，

MySQL主从复制的几个关键参数：

这里说的关键参数基本都是和binlog有关的

（1）

sync_binlog

sync_binlog参数来控制数据库的binlog刷到磁盘上去。
默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。
这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。

（2）

log-slave-updates

log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的。如果需要配置互为主从，那么，此参数需要配置。

（3）

binlog_format

此参数是binlog日志的格式，格式分为三种：STATEMENT模式（SBR），ROW模式（RBR），MIXED模式（MBR）

binlog_format = STATEMENT

每一条修改数据的sql语句会记录到binlog中。

优点：

并不需要记录每一行的数据变化，减少了binlog日志量，节约IO，提高性能。

缺点：

在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

binlog_format = ROW

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。

优点：

不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

缺点：

会产生大量的日志，尤其是alter table的时候会让日志暴涨。

binlog_format = MIXED

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

在MySQL 5.7.7之前，默认的二进制日志采用statement格式。

在MySQL 5.7.7及更高的版本中，默认的二进制变更为row格式。

建议使用binlog_format = MIXED ，这样可以做到性能和负载的平衡。

（4）

relay_log_recovery = 1

当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。

默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

（5）

enforce_gtid_consistency = TRUE

当使用此参数的时候，事务、存储过程、存储函数和触发器内不支持CREATE TEMPORARY TABLE和DROP TEMPORARY TABLE语句，

不过可以在这些对象之外执行CREATE TEMPORARY TABLE和DROP TEMPORARY TABLE语句，当需要使用autocommit = 1时自动提交。

（6）

使用GTID时不支持使用系统变量sql_slave_skip_counter来跳过事务，也就是说，当使用了 gtid_mode=ON这个参数的时候，不可以使用SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 一般报错如下：

1858 - sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

一，

开启GTID

主节点：192.168.217.16:3311 从节点1：192.168.217.17:3311 从节点2：192.168.217.23:3311

主节点：

主要是设置三个参数，enforce_gtid_consistency = TRUE或者enforce_gtid_consistency = ON都可以

gtid_mode = ON
enforce_gtid_consistency = TRUE
binlog_format = ROW

从节点

也是设置三个参数，enforce_gtid_consistency = TRUE或者enforce_gtid_consistency = ON都可以：

gtid_mode = ON
enforce_gtid_consistency = TRUE
binlog_format = ROW

MySQL 5.7.9 及之后的版本新增了一张InnoDB存储引擎的mysql.gtid_executed表来持久化GTID信息（如果没记错的话），因此，MySQL的版本应该使用MySQL5.7.9以后的哦。

二，

从节点执行以下命令：

这里解释一下change命令，复制操作可以使用专有账号也可以使用root用户，使用专有用户需要在master节点创建用户，命令如下：

CREATE USER 'slave'@'192.168.217.17' IDENTIFIED WITH mysql_native_password BY '123456';
 
CREATE USER 'slave'@'192.168.217.23' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.217.17';
 
GRANT REPLICATION slave ON *.* TO 'slave'@'192.168.217.23';

在从节点执行change命令：

stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.217.16',MASTER_PORT=3311,MASTER_USER='slave',MASTER_PASSWORD='123456,master_auto_position=1;

使用root用户不需要创建用户，直接使用即可：

在从节点执行change命令：

stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.217.16',MASTER_PORT=3311,MASTER_USER='root',MASTER_PASSWORD='123456,master_auto_position=1;

三，

启动slave

在从节点启动slave并查看slave的状态（这里最好是使用MySQL的cmd命令，Navicat查看的话，结果会很长，不利于观察）：

start slave;
show slave status \G;

正确的输出应该是这样的；

MySQL [(none)]> show slave status \G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    11
Current database: *** NONE ***

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.217.16
                  Master_User: root
                  Master_Port: 3311
                Connect_Retry: 60
              Master_Log_File: mysql_binlog.000013
          Read_Master_Log_Pos: 194
               Relay_Log_File: node3-relay-bin.000014
                Relay_Log_Pos: 413
        Relay_Master_Log_File: mysql_binlog.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 194
              Relay_Log_Space: 879
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 56116
                  Master_UUID: ca9907c5-5dd3-11ed-9029-000c29320c0d
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: ca9907c5-5dd3-11ed-9029-000c29320c0d:1-19
            Executed_Gtid_Set: ae4751af-5d31-11ed-8ed7-000c29701212:1-3,
ca9907c5-5dd3-11ed-9029-000c29320c0d:1-22
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

可以看到有两个YES，表示主从复制状态正常，如果有错误， Last_IO_Error和Last_SQL_Error将会有日志，根据日志排查即可。

四，

测试环节

执行查询语句，可以看到各个节点都有一个表gtid_executed，此表内持久化存储了所有的GTID

select * from mysql.gtid_executed;

例如，从节点的17服务器，执行以上查询的结果：

云原生|kubernetes|部署MySQL一主多从复制集群（基于GTID的复制）_服务器

master节点的查询结果：

云原生|kubernetes|部署MySQL一主多从复制集群（基于GTID的复制）_数据库_02

slave节点出现的错误：

Last_SQL_Error: Error 'Can't drop database 'mytest'; database doesn't exist' on query. Default database: 'mytest'. Query: 'drop database mytest'

解决方案：

停止slave，登陆从节点，新建数据库mytest，在启动slave。

附一：

主从复制的重置

如果主从复制出现错误，从 Last_IO_Error和Last_SQL_Error中找到的日志并不能解决错误的话，那么，重置主从复制是一个比较快速的方式。

RESET MASTER

删除所有index file 中记录的所有binlog 文件，将日志索引文件清空，创建一个新的日志文件，这个命令通常仅仅用于第一次用于搭建主从关系的时的主库，

注意

reset master 不同于purge binary log的两处地方

1 reset master 将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件起始值从000001 开始，然而purge binary log 命令并不会修改记录binlog的顺序的数值。

2 reset master 不能用于有任何slave 正在运行的主从关系的主库。因为在slave 运行时刻 reset master 命令不被支持，reset master 将master 的binlog从000001 开始记录,slave 记录的master log 则是reset master 时主库的最新的binlog,从库会报错无法找的指定的binlog文件，因此，在执行此命令的时候，所有的slave需要停止。

RESET SLAVE

reset slave 将使slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。

使用reset slave之前必须使用stop slave 命令将复制进程停止。

注所有的relay log将被删除不管他们是否被SQL thread进程完全应用(这种情况发生于备库延迟以及在备库执行了stop slave 命令),存储复制链接信息的master.info文件将被立即清除,如果SQL thread 正在复制临时表的过程中，执行了stop slave ，并且执行了reset slave，这些被复制的临时表将被删除。

RESET SLAVE ALL

在 5.6 版本中 reset slave 并不会清理存储于内存中的复制信息比如 master host, master port, master user, or master password,也就是说如果没有使用change master 命令做重新定向，执行start slave 还是会指向旧的master 上面。

当从库执行reset slave之后,将mysqld shutdown 复制参数将被重置。

在5.6.3 版本以及以后使用使用 RESET SLAVE ALL 来完全的清理复制连接参数信息。

附二，

关于两个文件的持久化

在MySQL 5.6.2之前，slave记录的master信息以及slave应用binlog的信息存放在文件中，即master.info与relay-log.info，5.6.2版本之后，可以将这两个文件持久化到数据库内

对应的表分别为mysql.slave_master_info与mysql.slave_relay_log_info，且这两个表均为innodb引擎表。：

数据库主配置文件mysqld字段内添加这两个参数（先停止slave，在添加下面的参数，然后重启MySQL服务）：

master-info-repository  = TABLE    

relay-log-info-repository = TABLE

可以看到，添加这两个参数后，在从节点可以查看到此表不是空的了，里面存储了master信息以及slave应用binlog的信息，例如：

select * from mysql.slave_master_info;

云原生|kubernetes|部署MySQL一主多从复制集群（基于GTID的复制）_云原生_03

设置relay_log_info_repository和master_info_repository设置为TABLE可以提高数据库本身或者所在主机意外终止之后crash recovery的能力（这两张表是innodb表，可以保证crash之后表中的位置信息不丢失），且可以保证数据一致性，算是一个小优化吧。

标签：binlog,slave,kubernetes,一主多,复制,master,Master,日志
From： https://blog.51cto.com/u_15966109/6082651