mysql shell 配置 ReplicaSet
用户权限
在所有节点执行
GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'root'@'%' WITH GRANT OPTION;
创建用户
在所有节点执行
mysqlsh -S /data/mysql/mysql.sock root@localhost
dba.configureReplicaSetInstance('root@node01:3306', {clusterAdmin: "'rsadmin'@'%'"})
MySQL localhost JS > dba.configureReplicaSetInstance('root@node01:3306', {clusterAdmin: "'rsadmin'@'%'"})
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet...
This instance reports its own address as node01: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.
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
Cluster admin user 'rsadmin'@'%' created.
Configuring instance...
The instance 'node01:3306' was configured to be used in an InnoDB ReplicaSet.
调整参数
vi /data/mysql/conf/my.cnf
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = 1
创建Replicat set集群
#以rsadmin用户登录
mysqlsh -S /data/mysql/mysql.sock rsadmin@localhost
#创建ReplicaSet:rs01
var rs = dba.createReplicaSet("rs01")
添加节点
在node01上执行，如instance未重启需手工启动（Waiting for server restart… ready）
var rs = dba.getReplicaSet()
rs.addInstance('node02:3306')
添加输出日志
MySQL localhost JS > rs.addInstance('node02:3306')
Adding instance to the replicaset...
* Performing validation checks
This instance reports its own address as node02:3306
node02: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 'node02:3306' determined that it contains transactions that do not originate from the replicaset, which must be discarded before it can join the replicaset.
node02:3306 has the following errant GTIDs that do not exist in the replicaset:
3f5ba01d-f934-11ee-bb8e-080027e6c610:1-8
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of node02: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: node02:3306 is being cloned from node01:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: node02:3306 is shutting down...
* Waiting for server restart... ready
* node02:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.64 MB transferred in 4 sec (18.16 MB/s)
** Changing replication source of node02:3306 to node01:3306
** Waiting for new instance to synchronize with PRIMARY...
** Transactions replicated ############################################################ 100%
The instance 'node02:3306' was added to the replicaset and is replicating from node01:3306.
** Transactions replicated ############################################################ 100%
检查Replicaset状态
#在主库登录
mysqlsh -S /data/mysql/mysql.sock rsadmin@localhost
var rs = dba.getReplicaSet()
You are connected to a member of replicaset 'rs01'.
rs.status();
# 在从节点登录
mysqlsh -S /data/mysql/mysql.sock rsadmin@localhost
var rs = dba.getReplicaSet()
\sql show replica status\G
MySQL localhost JS > rs.status();
{
"replicaSet": {
"name": "rs01",
"primary": "node01:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"node01:3306": {
"address": "node01:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"node02:3306": {
"address": "node02: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"
},
"node03:3306": {
"address": "node03: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"
}
}
