MySQL 8 innodb replication 一体化的种种你会不会？

最新推荐文章于 2025-02-15 17:26:03 发布

原创最新推荐文章于 2025-02-15 17:26:03 发布 · 179 阅读

CC 4.0 BY-SA版权

文章标签：

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群（共1620人左右 1 + 2 + 3 + 4） 3群突破 490已关闭自由申请如需加入请提前说明，新人会进4群（200）,另欢迎 OpenGauss 的技术人员加如入。

每人感悟

六人行中的搞笑马修前两天走了，终年54岁，一个时代过去了，来了走了，过好每一天，每一天都是新的开始，都当做是美好的一天。

——————————————————————————————

MySQL搭建主从怎么做，相信在MySQLDBA的手里是一件非常简单的工作，xtrabackup 数据库，然后恢复，在然后做主从配置，启动slave start。

时代变了MySQL 8了，我们不在用这样的方法来建立基于MySQL8的数据库的复制了，那么我们用什么，这样的方式叫什么 innodb replication.这里我们采用的方法通过 adminapi来支持MySQL搭建复制，并进行手动切换主从。

基于这个操作我们需要确认以下的一些前置信息

1 MySQL 的版本建议在8.019以上

2 数据复制需要使用 GTID 技术

3 不能使用复制过滤

4 对于从库的数量在操作中并未进行设置

5 支持手动切换

6 整个操作过程使用 MySQL Shell

7 通过主机设置一台主机可以清晰的辨识自己和其他要进行replication的主机。

在需要进行 /etc/hosts 对 replicaiton 的主机中来进行主机名的设定，如果DNS支持则可以不在此位置进行设定

127.0.0.1 localhost
127.0.0.1 mysql1
192.168.198.100 mysql1
192.168.198.101 mysql2

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
~                                                                                                                                                   
~

下面一步步的进行replication 搭建的的操作，在两个数据库中都建立账号。然后通过mysqlsh 来登陆数据库，这里需要注意我们的命令都是基于js的，所以进入到mysqlshell 后都需要在JS 命令环境下操作。

这里需要注明此次进行replication 搭建的主机为

192.168.198.100

192.168.198.101

[root@localhost][(none)]> create user repl@'%' identified by 'repl';
Query OK, 0 rows affected (10.01 sec)

[root@localhost][(none)]> grant all  on *.*  to repl@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql@mysql1:~$ mysqlsh repl@'192.168.198.100'
Please provide the password for 'repl@192.168.198.100': ****
Save password for 'repl@192.168.198.100'? [Y]es/[N]o/Ne[v]er (default No): yes
MySQL Shell 8.0.34

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'repl@192.168.198.100'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 11 (X protocol)
Server version: 8.0.34 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  192.168.198.100:33060+ ssl  JS > dba.configureReplicaSetInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet...

This instance reports its own address as mysql1:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Configuring instance...
The instance 'mysql1:3306' was configured to be used in an InnoDB ReplicaSet.

这里我们需要注意在进行主机设置中，提示binlog_transaction_dependency_tracking中的配置需要修改，修改成writeset，这里的目的是在具有多线程复制的源服务器中，binlog_transaction_dependency_tracking 指定了源mysql生成依赖信息的方式这样的方式会支持MYSQL 8 采用并行复制，判断那些事务可以进行并行复制，这里并行复制主要采用使用逻辑时间戳的方式，需要replic_parallel_type, slave_parallel_type 均设置为 logcial_clock，其中包含sequence_number , last_committed 中对于事务进行编号，并且通过last_committed 其中指定二进制日志中当与当前事务冲突最近的事务的sequence_number。

其中包含三个值，commit_order, writset , writeset_session

1 commit_order 中第一个事务和第二个事务提交的时间窗口时间是重叠的，则认为这两个事务是独立的，这也是基于事务提交中事务最后一个语句提交结束作为焦点，两个事务在重叠的时间段是无法进行资源的同时的持有，所以我们认为他们更新的行是不相同的。

2 writeset 中是通过commit_order以及事务中每一行都会有自己的计算出的hash 值，如果传输中，发现两个事务的hash值一致，说明有冲突

3 writeset_session 与writeset类似但加入了SESSION 的环节，通过session来判断一个会话中是否出现了事务的冲突。

这里如果使用replicaiton 的方式，则要求这个值最低为writeset,所以在我马上上面进行innodb_replication 的设置中，会判断配置是否可行，如不可行则在具有权限的情况下，对数据库的配置进行修改。

在执行完相关命令后，会产生mysqld-auto文件

mysql@mysql1:~$ cat /data/mysql/mysqld-auto.cnf 
{"Version": 2, "mysql_static_variables": {"slave_parallel_workers": {"Value": "4", "Metadata": {"Host": "", "User": "repl", "Timestamp": 1698630809532860}}, "replica_parallel_workers": {"Value": "4", "Metadata": {"Host": "", "User": "repl", "Timestamp": 1698630809532860}}}, "mysql_dynamic_variables": {"binlog_transaction_dependency_tracking": {"Value": "WRITESET", "Metadata": {"Host": "", "User": "repl", "Timestamp": 1698630809533983}}}}mysql@mysql1:~$

然后我们需要在主库上创建第一个集群，集群的名称为 repl

MySQL  192.168.198.100:33060+ ssl  JS > dba.createReplicaSet('repl')
A new replicaset with instance 'mysql1:3306' will be created.

* Checking MySQL instance at mysql1:3306

This instance reports its own address as mysql1:3306
mysql1:3306: Instance configuration is suitable.

* Checking connectivity and SSL configuration...
* Updating metadata...

ReplicaSet object successfully created for mysql1:3306.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

<ReplicaSet:repl>

搭建中可能有同学会碰到某些错误如下，这个错误的问题在主机名和IP解析一致导致错误

2023-10-30T03:03:50.912684-00:00 14 [ERROR] [MY-013117] [Repl] Replica I/O for channel 'mysqlsh.test': Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it). Error_code: MY-013117
2023-10-30T03:03:50.912755-00:00 14 [Note] [MY-010570] [Repl] Replica I/O thread exiting for channel 'mysqlsh.test', read up to log 'FIRST', position 4

上面的操作主要是针对mysql1 进行replicaiton 操作前的设置，下面还需要对mysql2 来进行replication操作前的设置。这里通过在主节点添加从节的简单的命令来进行，这里采用的主机CLONE的方式来进行，这里需要注意主库从库的数据空间。

MySQL  mysql1:33060+ ssl  JS > repl.addInstance('mysql2:3306')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as mysql2:3306
mysql2:3306: Instance configuration is suitable.

* Checking async replication topology...

* Checking connectivity and SSL configuration...

* Checking transaction state of the instance...

NOTE: The target instance 'mysql2:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'mysql2:3306' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: mysql2:3306 is being cloned from mysql1:3306
** Stage DROP DATA: Completed




** Clone Transfer      FILE COPY  ============================================================    0%  Not Started    PAGE COPY  ============================================================    0%  Not Started    REDO COPY  ============================================================    0%  Not Started** Clone Transfer      FILE COPY  ############################################################  100%  Completed    PAGE COPY  ############################################################  100%  Completed    REDO COPY  ############################################################  100%  Completed
NOTE: mysql2:3306 is shutting down...

* Waiting for server restart... ready 
* mysql2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 73.66 MB transferred in about 1 second (~73.66 MB/s)

** Changing replication source of mysql2:3306 to mysql1:3306
** Waiting for new instance to synchronize with PRIMARY...

** Transactions replicated  ============================================================    0% ** Transactions replicated  ###########################################################=   98% ** Transactions replicated  ############################################################  100% 

The instance 'mysql2:3306' was added to the replicaset and is replicating from mysql1:3306.

* Waiting for instance 'mysql2:3306' to synchronize the Metadata updates with the PRIMARY...

** Transactions replicated  ============================================================    0% ** Transactions replicated  ##########################################################==   97% ** Transactions replicated  ############################################################  100%

MySQL  mysql1:33060+ ssl  JS > repl.status()
{
    "replicaSet": {
        "name": "repl", 
        "primary": "mysql1:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "mysql1:3306": {
                "address": "mysql1:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }, 
            "mysql2:3306": {
                "address": "mysql2:3306", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for source to send event", 
                    "replicationLag": null, 
                    "replicationSsl": "TLS_AES_256_GCM_SHA384 TLSv1.3"
                }, 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

截止到此，MySQL的innodb_replicaset 就搭建完毕了。

这里innodb replicaset 的优点有不少，其中主从切换的便利性的提一下，这里我们进行主从切换，下面的步骤直接将主从库进行切换。

MySQL  mysql1:33060+ ssl  JS > repl.setPrimaryInstance('mysql2:3306')
mysql2:3306 will be promoted to PRIMARY of 'repl'.
The current PRIMARY is mysql1:3306.

* Connecting to replicaset instances
** Connecting to mysql1:3306
** Connecting to mysql2:3306
** Connecting to mysql1:3306
** Connecting to mysql2:3306

* Performing validation checks
** Checking async replication topology...
** Checking transaction state of the instance...

* Synchronizing transaction backlog at mysql2:3306

** Transactions replicated  ============================================================    0% ** Transactions replicated  ############################################################  100% ** Transactions replicated  ############################################################  100% 

* Updating metadata

* Acquiring locks in replicaset instances
** Pre-synchronizing SECONDARIES
** Acquiring global lock at PRIMARY
** Acquiring global lock at SECONDARIES

* Updating replication topology
** Changing replication source of mysql1:3306 to mysql2:3306

mysql2:3306 was promoted to PRIMARY.

 MySQL  mysql1:33060+ ssl  JS > repl.status()
{
    "replicaSet": {
        "name": "repl", 
        "primary": "mysql2:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "mysql1:3306": {
                "address": "mysql1:3306", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for source to send event", 
                    "replicationLag": null, 
                    "replicationSsl": "TLS_AES_256_GCM_SHA384 TLSv1.3"
                }, 
                "status": "ONLINE"
            }, 
            "mysql2:3306": {
                "address": "mysql2:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

截止到目前，MySQL8在innodb replica的搭建和基本维护上早就可以进行二次开发并集中管理大量的主从复制的功能准备，简单的命令就可以快速搭建复制，或进行主从切换。