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

    前言：

    一，

    MySQL的主从复制优点如下：

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

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

    二，

    主从复制的原理：

    1. MySQL的复制功能用三个线程来实现：
    2.  
    3. 主库:Binlog Dump线程
    4.  
    5. 从库:I/O线程和SQL线程
    6.  
    7.  
    8.  
    9. 1.用户提交数据的修改，然后Master主库把所有数据库变更写进Binary Log二进制日志。
    10.  
    11. 主库通过Binlog Dump线程把二进制日志内容推送给Slave从库，从库被动接收数据，不是主动获取，除非是新建连接。
    12.  
    13. 2.在从库上执行START SLAVE语句时，已经使用CHANGE MASTER TO语句配置好复制信息，从库会创建一个I/O线程，该线程连接到主库并请求主库为其发送所需的二进制日志。
    14.  
    15. 从库I/O线程与主库Binlog Dump线程成功建立连接后，从库I/O线程接收主库Binlog Dump线程发送的二进制日志，并将它们写入从库本地的Relay Log中继日志文件。
    16.  
    17. 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都可以

    1. gtid_mode = ON
    2. enforce_gtid_consistency = TRUE
    3. binlog_format = ROW

    从节点

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

    1. gtid_mode = ON
    2. enforce_gtid_consistency = TRUE
    3. binlog_format = ROW

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

    二，

    从节点执行以下命令：

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

    1. CREATE USER 'slave'@'192.168.217.17' IDENTIFIED WITH mysql_native_password BY '123456';
    2.  
    3. CREATE USER 'slave'@'192.168.217.23' IDENTIFIED WITH mysql_native_password BY '123456';
    1. GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.217.17';
    2.  
    3. GRANT REPLICATION slave ON *.* TO 'slave'@'192.168.217.23';

    在从节点执行change命令：

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

     

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

    在从节点执行change命令：

    1. stop slave;
    2. 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查看的话，结果会很长，不利于观察）：

    1. start slave;
    2. show slave status \G;

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

    1. MySQL [(none)]> show slave status \G;
    2. ERROR 2006 (HY000): MySQL server has gone away
    3. No connection. Trying to reconnect...
    4. Connection id:    11
    5. Current database: *** NONE ***
    6.  
    7. *************************** 1. row ***************************
    8.                Slave_IO_State: Waiting for master to send event
    9.                   Master_Host: 192.168.217.16
    10.                   Master_User: root
    11.                   Master_Port: 3311
    12.                 Connect_Retry: 60
    13.               Master_Log_File: mysql_binlog.000013
    14.           Read_Master_Log_Pos: 194
    15.                Relay_Log_File: node3-relay-bin.000014
    16.                 Relay_Log_Pos: 413
    17.         Relay_Master_Log_File: mysql_binlog.000013
    18.              Slave_IO_Running: Yes
    19.             Slave_SQL_Running: Yes
    20.               Replicate_Do_DB: 
    21.           Replicate_Ignore_DB: 
    22.            Replicate_Do_Table: 
    23.        Replicate_Ignore_Table: 
    24.       Replicate_Wild_Do_Table: 
    25.   Replicate_Wild_Ignore_Table: 
    26.                    Last_Errno: 0
    27.                    Last_Error: 
    28.                  Skip_Counter: 0
    29.           Exec_Master_Log_Pos: 194
    30.               Relay_Log_Space: 879
    31.               Until_Condition: None
    32.                Until_Log_File: 
    33.                 Until_Log_Pos: 0
    34.            Master_SSL_Allowed: No
    35.            Master_SSL_CA_File: 
    36.            Master_SSL_CA_Path: 
    37.               Master_SSL_Cert: 
    38.             Master_SSL_Cipher: 
    39.                Master_SSL_Key: 
    40.         Seconds_Behind_Master: 0
    41. Master_SSL_Verify_Server_Cert: No
    42.                 Last_IO_Errno: 0
    43.                 Last_IO_Error: 
    44.                Last_SQL_Errno: 0
    45.                Last_SQL_Error: 
    46.   Replicate_Ignore_Server_Ids: 
    47.              Master_Server_Id: 56116
    48.                   Master_UUID: ca9907c5-5dd3-11ed-9029-000c29320c0d
    49.              Master_Info_File: /var/lib/mysql/master.info
    50.                     SQL_Delay: 0
    51.           SQL_Remaining_Delay: NULL
    52.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    53.            Master_Retry_Count: 86400
    54.                   Master_Bind: 
    55.       Last_IO_Error_Timestamp: 
    56.      Last_SQL_Error_Timestamp: 
    57.                Master_SSL_Crl: 
    58.            Master_SSL_Crlpath: 
    59.            Retrieved_Gtid_Set: ca9907c5-5dd3-11ed-9029-000c29320c0d:1-19
    60.             Executed_Gtid_Set: ae4751af-5d31-11ed-8ed7-000c29701212:1-3,
    61. ca9907c5-5dd3-11ed-9029-000c29320c0d:1-22
    62.                 Auto_Position: 1
    63.          Replicate_Rewrite_DB: 
    64.                  Channel_Name: 
    65.            Master_TLS_Version: 
    66. 1 row in set (0.01 sec)

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

    四，

    测试环节

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

    select * from mysql.gtid_executed;

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


                   

     master节点的查询结果：

    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服务）：

    1. master-info-repository  = TABLE    
    2.  
    3. relay-log-info-repository = TABLE

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

    select * from mysql.slave_master_info;

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

     

  • 相关阅读:
    VM Tools安装过程
    MPEG-NTA-NI 甲氧基聚乙二醇-氮川三乙酸-镍
    Twincat-Simulink 出现问题总结
    基于MOOC的FPGA硬件类课程远程实验教学平台设计
    ubuntu如何开启22端口支持ssh访问
    CentOS 7设置固定IP地址
    Java--MybatisPlus入门；与Mybatis区别；简单使用（一）
    day02论文学习：能够使大语言模型产生带有引用的文章
    【C++】类和对象(上)
    十四、Django框架使用
  • 原文地址：https://blog.csdn.net/alwaysbefine/article/details/127938119