MySql教程
MySQL 8.0.32 InnoDB ReplicaSet 配置和手动切换
本文主要是介绍MySQL 8.0.32 InnoDB ReplicaSet 配置和手动切换，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
1.环境准备
主库：192.168.137.4 mytest3 从库：192.168.137.5 mytest4 MySQL： 8.0.32
2.配置 ReplicaSet 实例
启动 mysql shell
# mysqlsh --uri root@localhost --socket=/abce/mysql_data/mysql.sock MySQL Shell 8.0.32 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 Classic session to 'root@/abce%2Fmysql_data%2Fmysql.sock' Fetching schema names for auto-completion... Press ^C to stop. Your MySQL connection id is 9 Server version: 8.0.32-24 Percona Server (GPL), Release 24, Revision e5c6e9d2 No default schema selected; type \use <schema> to set one. MySQL localhost JS >
配置实例
这里会新建专门用于管理 ReplicaSet 的账号。过程需要用户手动输入，会自动修改一些 ReplicaSet 需要的参数配置。
MySQL localhost JS > dba.configureReplicaSetInstance() The instance 'mytest3:3306' belongs to an InnoDB ReplicaSet. Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet... This instance reports its own address as mytest3: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. ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster. 1) Create remotely usable account for 'root' with same grants and password 2) Create a new admin account for InnoDB ReplicaSet with minimal required grants 3) Ignore and continue 4) Cancel Please select an option [1]: 2 Please provide an account name (e.g: icroot@%) to have it created with the necessary privileges or leave empty and press Enter to cancel. Account Name: rsadmin@% Password for new account: ********* Confirm password: ********* 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 'mytest3:3306' was configured to be used in an InnoDB ReplicaSet. MySQL localhost JS >
更新的参数会被持久化写入文件 mysqld-auto.cnf 中，放在 mysql 的数据目录下。
# more mysqld-auto.cnf
{"Version": 2, "mysql_static_variables": {"slave_parallel_workers": {"Value": "4", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1703209767354675}}, "replica_parallel_workers": {"Value": "4", "Metadata": {"Host": "localhost", "User": "root
", "Timestamp": 1703209767354675}}}, "mysql_dynamic_variables": {"binlog_transaction_dependency_tracking": {"Value": "WRITESET", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1703209767357369}}}}
#
要确保这一步骤在所有节点上都执行成功。集群的管理账号要在所有节点上都一致。
执行完上面的操作，mysql 实例会自动重启，让配置生效。当然也可以提前就创建要一个专门的用户，按照后面的操作步骤完成后续工作。
3.切换到新建的集群管理用户
MySQL localhost JS > \connect rsadmin@mytest3 Creating a session to 'rsadmin@mytest3' Please provide the password for 'rsadmin@mytest3': ********* Save password for 'rsadmin@mytest3'? [Y]es/[N]o/Ne[v]er (default No): y Fetching schema names for auto-completion... Press ^C to stop. Closing old connection... Your MySQL connection id is 12 (X protocol) Server version: 8.0.32-24 Percona Server (GPL), Release 24, Revision e5c6e9d2 No default schema selected; type \use <schema> to set one. MySQL mytest3:33060+ ssl JS >
4.创建 ReplicaSet
MySQL mytest3:33060+ ssl JS > dba.createReplicaSet('MyReplicaSet')
A new replicaset with instance 'mytest3:3306' will be created.
* Checking MySQL instance at mytest3:3306
This instance reports its own address as mytest3:3306
mytest3:3306: Instance configuration is suitable.
* Updating metadata...
ReplicaSet object successfully created for mytest3:3306.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.
<ReplicaSet:MyReplicaSet>
MySQL mytest3:33060+ ssl JS >
5.检查 ReplicaSet 的状态
MySQL mytest3:33060+ ssl JS > replicaset = dba.getReplicaSet()
You are connected to a member of replicaset 'MyReplicaSet'.
<ReplicaSet:MyReplicaSet>
MySQL mytest3:33060+ ssl JS >
MySQL mytest3:33060+ ssl JS > replicaset.status()
{
"replicaSet": {
"name": "MyReplicaSet",
"primary": "mytest3:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mytest3:3306": {
"address": "mytest3:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
MySQL mytest3:33060+ ssl JS >
6.增加从节点
添加新节点有两种方式：
(1)clone：使用在线实例快照做克隆，对于全新安装方式推荐使用这种。如果 DNS 配置不正确，或者 hosts 文件没有配置，克隆会失败。
(2)增量：依赖 mysql 的复制和日志应用，如果事务比较少，这种方式会比较快
MySQL mytest3:33060+ ssl JS > replicaset.addInstance('mytest4:3306')
Adding instance to the replicaset...
* Performing validation checks
This instance reports its own address as mytest4:3306
mytest4:3306: Instance configuration is suitable.
* Checking async replication topology...
* Checking transaction state of the instance...
WARNING: A GTID set check of the MySQL instance at 'mytest4:3306' determined that it contains transactions that do not originate from the replicaset, which must be discarded before it can join the replicaset.
mytest4:3306 has the following errant GTIDs that do not exist in the replicaset:
a135dcac-9964-11ee-a4f2-005056b0f2b2:1-7
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of mytest4:3306 with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
Please select a recovery method [C]lone/[A]bort (default Abort): 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: mytest4:3306 is being cloned from mytest3:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
* Clone process has finished: 74.37 MB transferred in about 1 second (~74.37 MB/s)
** Changing replication source of mytest4:3306 to mytest3:3306
** Waiting for new instance to synchronize with PRIMARY...
** Transactions replicated ############################################################ 100%
The instance 'mytest4:3306' was added to the replicaset and is replicating from mytest3:3306.
* Waiting for instance 'mytest4:3306' to synchronize the Metadata updates with the PRIMARY...
** Transactions replicated ############################################################ 100%
MySQL mytest3:33060+ ssl JS >
至此 ReplicaSet 创建结束。
7.查看集群状态
检查 InnoDB ReplicaSet 的状态
使用 ReplicaSet.status() 操作查看 ReplicaSet 的相关信息。ReplicaSet.status() 操作支持扩展选项，可获取不同级别的详细信息。例如
·ReplicaSet.status({extended:0})： 提供常规级别的详细信息。除了非默认或意外的复制设置和状态外，只包含有关实例和复制状态的基本信息。
·ReplicaSet.status({extended:1})： 将扩展设置为 1 时，将包括元数据版本、服务器 UUID、复制信息（如滞后和工作线程）、用于推导实例状态的原始信息、应用者队列大小、防止意外写入的系统变量值等。
·ReplicaSet.status({extended:2})： 将扩展设置为 2 包括与复制相关的重要配置设置，如加密连接等。
MySQL mytest3:33060+ ssl JS > replicaset.status()
{
"replicaSet": {
"name": "MyReplicaSet",
"primary": "mytest3:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mytest3:3306": {
"address": "mytest3:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"mytest4:3306": {
"address": "mytest4: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
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
MySQL mytest3:33060+ ssl JS >
--其它查询演示
MySQL mytest3:33060+ ssl JS > replicaset.status({extended:0})
{
"replicaSet": {
"name": "MyReplicaSet",
"primary": "mytest3:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mytest3:3306": {
"address": "mytest3:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"mytest4:3306": {
"address": "mytest4: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
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
MySQL mytest3:33060+ ssl JS > replicaset.status({extended:1})
{
"metadataVersion": "2.1.0",
"replicaSet": {
"name": "MyReplicaSet",
"primary": "mytest3:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mytest3:3306": {
"address": "mytest3:3306",
"fenceSysVars": [],
"fenced": false,
"instanceRole": "PRIMARY",
"mode": "R/W",
"serverUuid": "a1356bf9-9964-11ee-9a9d-005056b01ad7",
"status": "ONLINE"
},
"mytest4:3306": {
"address": "mytest4:3306",
"fenceSysVars": [
"read_only",
"super_read_only"
],
"fenced": true,
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierQueuedTransactionSet": "",
"applierQueuedTransactionSetSize": 0,
"applierState": "ON",
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Waiting for an event from Coordinator",
"applierWorkerThreads": 4,
"coordinatorState": "ON",
"coordinatorThreadState": "Replica has read all relay log; waiting for more updates",
"receiverStatus": "ON",
"receiverThreadState": "Waiting for source to send event",
"receiverTimeSinceLastMessage": "00:00:06.330290",
"replicationLag": null,
"source": "mytest3:3306"
},
"serverUuid": "a135dcac-9964-11ee-a4f2-005056b0f2b2",
"status": "ONLINE",
"transactionSetConsistencyStatus": "OK"
}
},
"type": "ASYNC"
}
}
MySQL mytest3:33060+ ssl JS > replicaset.status({extended:2})
{
"metadataVersion": "2.1.0",
"replicaSet": {
"name": "MyReplicaSet",
"primary": "mytest3:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mytest3:3306": {
"address": "mytest3:3306",
"fenceSysVars": [],
"fenced": false,
"instanceRole": "PRIMARY",
"mode": "R/W",
"serverUuid": "a1356bf9-9964-11ee-9a9d-005056b01ad7",
"status": "ONLINE"
},
"mytest4:3306": {
"address": "mytest4:3306",
"fenceSysVars": [
"read_only",
"super_read_only"
],
"fenced": true,
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierQueuedTransactionSet": "",
"applierQueuedTransactionSetSize": 0,
"applierState": "ON",
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Waiting for an event from Coordinator",
"applierWorkerThreads": 4,
"coordinatorState": "ON",
"coordinatorThreadState": "Replica has read all relay log; waiting for more updates",
"options": {
"connectRetry": 60,
"delay": 0,
"heartbeatPeriod": 30,
"retryCount": 86400
},
"receiverStatus": "ON",
"receiverThreadState": "Waiting for source to send event",
"receiverTimeSinceLastMessage": "00:00:10.062587",
"replicationLag": null,
"source": "mytest3:3306"
},
"serverUuid": "a135dcac-9964-11ee-a4f2-005056b0f2b2",
"status": "ONLINE",
"transactionSetConsistencyStatus": "OK"
}
},
"type": "ASYNC"
}
}
MySQL mytest3:33060+ ssl JS >
8.数据测试
主节点插入数据
# mysqlsh --uri root@localhost --socket=/abce/mysql_data/mysql.sock
MySQL Shell 8.0.32
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 Classic session to 'root@/abce%2Fmysql_data%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 58
Server version: 8.0.32-24 Percona Server (GPL), Release 24, Revision e5c6e9d2
No default schema selected; type \use <schema> to set one.
MySQL localhost JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
MySQL localhost SQL > create abce;
ERROR: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'abce' at line 1
MySQL localhost SQL > create database abce;
Query OK, 1 row affected (0.0035 sec)
MySQL localhost SQL > use abce
Default schema set to `abce`.
Fetching global names, object names from `abce` for auto-completion... Press ^C to stop.
MySQL localhost abce SQL > CREATE TABLE if not exists abce.student(stndid int primary key auto_increment,stdname varchar(100),phone int,classid int);
Query OK, 0 rows affected (0.0128 sec)
MySQL localhost abce SQL > INSERT abce.student(stdname,phone,classid) values('Aslam',123456977,7);
Query OK, 1 row affected (0.0052 sec)
MySQL localhost abce SQL > select * from student;
+--------+---------+-----------+---------+
| stndid | stdname | phone | classid |
+--------+---------+-----------+---------+
| 1 | Aslam | 123456977 | 7 |
+--------+---------+-----------+---------+
1 row in set (0.0004 sec)
MySQL localhost abce SQL > select @@hostname;
+------------+
| @@hostname |
+------------+
| mytest3 |
+------------+
1 row in set (0.0006 sec)
MySQL localhost abce SQL >
在从节点查看
# mysqlsh --uri root@localhost --socket=/abce/mysql_data/mysql.sock MySQL Shell 8.0.32 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 Classic session to 'root@/abce%2Fmysql_data%2Fmysql.sock' Fetching schema names for auto-completion... Press ^C to stop. Your MySQL connection id is 28 Server version: 8.0.32-24 Percona Server (GPL), Release 24, Revision e5c6e9d2 No default schema selected; type \use <schema> to set one. MySQL localhost JS > \sql Switching to SQL mode... Commands end with ; Fetching global names for auto-completion... Press ^C to stop. MySQL localhost SQL > use abce Default schema set to `abce`. Fetching global names, object names from `abce` for auto-completion... Press ^C to stop. MySQL localhost abce SQL > select * from student; +--------+---------+-----------+---------+ | stndid | stdname | phone | classid | +--------+---------+-----------+---------+ | 1 | Aslam | 123456977 | 7 | +--------+---------+-----------+---------+ 1 row in set (0.0009 sec) MySQL localhost abce SQL > select @@hostname; +------------+ | @@hostname | +------------+ | mytest4 | +------------+ 1 row in set (0.0004 sec) MySQL localhost abce SQL >
9.手动切换
ReplicaSet.setPrimaryInstance() 可以切换主节点。为了切换安全，mysql shell 必须要能访问所有节点。根据 ReplicaSet 启动的 MySQL 路由器实例会自动开始将读写客户端重定向到新的主服务器。
# mysqlsh --uri root@localhost --socket=/abce/mysql_data/mysql.sock
MySQL Shell 8.0.32
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 Classic session to 'root@/abce%2Fmysql_data%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 65
Server version: 8.0.32-24 Percona Server (GPL), Release 24, Revision e5c6e9d2
No default schema selected; type \use <schema> to set one.
MySQL localhost JS >
MySQL localhost JS > \connect rsadmin@mytest3
Creating a session to 'rsadmin@mytest3'
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 66 (X protocol)
Server version: 8.0.32-24 Percona Server (GPL), Release 24, Revision e5c6e9d2
No default schema selected; type \use <schema> to set one.
MySQL mytest3:33060+ ssl JS > replicaset = dba.getReplicaSet()
You are connected to a member of replicaset 'MyReplicaSet'.
<ReplicaSet:MyReplicaSet>
MySQL mytest3:33060+ ssl JS > replicaset.status()
{
"replicaSet": {
"name": "MyReplicaSet",
"primary": "mytest3:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mytest3:3306": {
"address": "mytest3:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"mytest4:3306": {
"address": "mytest4: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
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
MySQL mytest3:33060+ ssl JS > replicaset.setPrimaryInstance('mytest4:3306')
mytest4:3306 will be promoted to PRIMARY of 'MyReplicaSet'.
The current PRIMARY is mytest3:3306.
* Connecting to replicaset instances
** Connecting to mytest3:3306
** Connecting to mytest4:3306
** Connecting to mytest3:3306
** Connecting to mytest4:3306
* Performing validation checks
** Checking async replication topology...
** Checking transaction state of the instance...
* Synchronizing transaction backlog at mytest4:3306
** 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 mytest3:3306 to mytest4:3306
mytest4:3306 was promoted to PRIMARY.
MySQL mytest3:33060+ ssl JS > replicaset.status()
{
"replicaSet": {
"name": "MyReplicaSet",
"primary": "mytest4:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mytest3:3306": {
"address": "mytest3: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
},
"status": "ONLINE"
},
"mytest4:3306": {
"address": "mytest4:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
MySQL mytest3:33060+ ssl JS >
强制切换
如果无法连接到主节点，也无法使用 ReplicaSet.setPrimaryInstance() 安全地执行切换到新主节点。可以强制故障转移，强制故障转移是一种潜在的破坏性操作，必须谨慎使用。
如果最后已知的主库仍可访问，ReplicaSet.forcePrimaryInstance() 操作就会失败
10.安装启动mysqlrouter
MySQL Router 的 bootstrap 模式支持 failover，这里将 MySQL Router 安装在 192.168.137.4上，如果资源充足的话，建议将MySQL Router安装在单独的一台服务器上。
用 mysqlrouter 用户引导启动 mysqlrouter
# mysqlrouter --bootstrap rsadmin@mytest3 --user mysqlrouter
Please enter MySQL password for rsadmin:
# Bootstrapping system MySQL Router instance...
- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf
Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'
# MySQL Router configured for the InnoDB ReplicaSet 'MyReplicaSet'
After this MySQL Router has been started with the generated configuration
$ /etc/init.d/mysqlrouter restart
or
$ systemctl start mysqlrouter
or
$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
InnoDB ReplicaSet 'MyReplicaSet' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
## MySQL X protocol
- Read/Write Connections: localhost:6448
- Read/Only Connections: localhost:6449
router 配置好后就可以通过 router 连接到数据库了。
这篇关于MySQL 8.0.32 InnoDB ReplicaSet 配置和手动切换的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
部署MySQL集群项目实战：新手入门教程11-04
-
如何部署MySQL集群资料：新手入门指南11-04
-
MySQL集群项目实战：新手入门指南11-02
-
初学者指南：部署MySQL集群资料11-02
-
部署MySQL集群教程：新手入门指南11-01
-
如何部署MySQL集群：新手入门教程11-01
-
部署MySQL集群学习：新手入门教程11-01
-
部署MySQL集群入门：新手必读指南11-01
-
BinLog入门：新手必读的MySQL二进制日志指南10-23
-
Binlog入门：MySQL数据库的日志管理指南10-23
-
MySQL数据库入门教程：从安装到基本操作10-22
-
MySQL读写分离入门教程：轻松实现数据库性能提升10-22
-
MySQL分库分表入门教程10-22
-
MySQL慢查询的诊断与优化指南10-22
-
MySQL索引入门教程：快速理解与应用指南10-22
栏目导航
