OS : Rocky Linux 8.8 64bit
MySQL : Percona for MySQL 8.0.34
Replica Set : 1(Soure), 2(Replica)
Server
node1(DB) : 10.64.70.21
node2(DB) : 10.64.70.22
이번 포스팅은 기존 Replication의 불편함(유저 생성, 초기 구성 등)을 개선한 MySQL 8.0의 신기능인 ReplicaSet입니다.
InnoDB Cluster와 유사하게 MySQL Shell로 구성하며, 부트스트래핑 또한 지원하여 복제가 간단합니다.
(예시)
MySQL 10.64.70.21:33061 ssl JS > a.status()
{
"replicaSet": {
"name": "replicaSet",
"primary": "vm2-1:33061",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"vm2-1:33061": {
"address": "vm2-1:33061",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"vm2-2:33061": {
"address": "vm2-2:33061",
"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": "ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2"
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}ReplicaSet은 기존 Replication에서 명명하던 Master <-> Slave 혹은 Source <-> Replica 구조가 아닌, InnoDB Cluster와 같이 Primary <-> Secondary로 명명합니다.
또한, GTID-Based 복제 구성이기에 GTID 지원이 필요하며, 자동 페일오버는 지원하지 않습니다.
여기서 강력한 기능을 볼 수 있는데요, 바로 기존 복제와는 다르게 간단한 커맨드로 수동 페일오버가 가능합니다.
MySQL 10.64.70.21:33061 ssl JS > a.setPrimaryInstance('vm2-2:33061')
vm2-2:33061 will be promoted to PRIMARY of 'replicaSet'.
The current PRIMARY is vm2-1:33061.
* Connecting to replicaset instances
** Connecting to vm2-1:33061
** Connecting to vm2-2:33061
** Connecting to vm2-1:33061
** Connecting to vm2-2:33061
* Performing validation checks
** Checking async replication topology...
** Checking transaction state of the instance...
* Synchronizing transaction backlog at vm2-2:33061
** 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 vm2-1:33061 to vm2-2:33061
vm2-2:33061 was promoted to PRIMARY.
MySQL 10.64.70.21:33061 ssl JS > a.status()
{
"replicaSet": {
"name": "replicaSet",
"primary": "vm2-2:33061",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"vm2-1:33061": {
"address": "vm2-1:33061",
"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": "ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2"
},
"status": "ONLINE"
},
"vm2-2:33061": {
"address": "vm2-2:33061",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}(어떻게 보면 InnoDB Cluster의 반쪼가리 기능인 것 같습니다...)
모든 작업은 MySQL Shell로 이루어집니다.
Configuration
- ReplicaSet 어드민 계정 생성 (각 노드)
- 지정한 Primary 노드에서 ReplicaSet 생성
- rs.addInstance()로 Secondary 추가
MySQL Shell이 설치되어 있다고 가정 하에 GRANT CREATE USER 권한과 적절한 ADMIN 권한이 있는 계정으로 Shell에 접속합니다.
root@vm2-1 ~]# mysqlsh -ushell -p -S /tmp/mysql_8.0.sock
Please provide the password for 'shell@/tmp%2Fmysql_8.0.sock': ***
Save password for 'shell@/tmp%2Fmysql_8.0.sock'? [Y]es/[N]o/Ne[v]er (default No): y
MySQL Shell 8.0.34-commercial
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 'shell@/tmp%2Fmysql_8.0.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 297
Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85
No default schema selected; type \use <schema> to set one.
MySQL localhost JS >root 계정으로 해당 과정을 진행해도 되지만, 하나의 서버에서 여러개의 서버를 접속하기 위해 shell 이라는 특정 계정으로 진행했습니다. (grant all priivleges)
다음으로 ReplicaSet을 구성하기 위해 해당 계정이 적절한 권한이 있는지 Check 합니다.
MySQL localhost JS > dba.checkInstanceConfiguration()
Validating local MySQL instance listening at port 33061 for use in an InnoDB cluster...
ERROR: The account 'shell'@'%' is missing privileges required to manage an InnoDB cluster:
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 'shell'@'%' WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'shell'@'%' 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 'shell'@'%' 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 'shell'@'%' 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 'shell'@'%' WITH GRANT OPTION;
For more information, see the online documentation.
Dba.checkInstanceConfiguration: The account 'shell'@'%' is missing privileges required to manage an InnoDB cluster. (RuntimeError)root 등 슈퍼 계정으로 접근해 위 권한을 부여합니다.
MySQL localhost JS > \connect root@/tmp%2Fmysql_8.0.sock
Creating a session to 'root@/tmp%2Fmysql_8.0.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 309
Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85
No default schema selected; type \use <schema> to set one.
MySQL localhost JS >
MySQL localhost JS >
MySQL localhost JS > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost SQL > 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 'shell'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.0027 sec)
MySQL localhost SQL > GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'shell'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.0017 sec)
MySQL localhost SQL > 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 'shell'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.0019 sec)
MySQL localhost SQL > 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 'shell'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.0016 sec)
MySQL localhost SQL > 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 'shell'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.0021 sec)ReplicaSet 전용 Admin 계정을 생성합니다. (각 노드 적용)
dba.configureReplicaSetInstance('shell@/tmp%2Fmysql_8.0.sock', {clusterAdmin:"'rsadmin'@'10.64.70.%'"})
Configuring local MySQL instance listening at port 33061 for use in an InnoDB ReplicaSet...
This instance reports its own address as vm2-1:33061
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.
The instance 'vm2-1:33061' is valid to be used in an InnoDB ReplicaSet.
Disabled super_read_only on the instance 'vm2-1:33061'
Creating user rsadmin@10.64.70.%.
Account rsadmin@10.64.70.% was successfully created.
Successfully enabled parallel appliers.
MySQL localhost:33060+ ssl JS >ReplicaSet을 생성합니다.
MySQL localhost:33060+ ssl JS > var rs = dba.createReplicaSet('replicaSet')
A new replicaset with instance 'vm2-1:33061' will be created.
* Checking MySQL instance at vm2-1:33061
This instance reports its own address as vm2-1:33061
vm2-1:33061: Instance configuration is suitable.
* Checking connectivity and SSL configuration...
* Updating metadata...
ReplicaSet object successfully created for vm2-1:33061.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.
MySQL localhost:33060+ ssl JS > rs.status()
{
"replicaSet": {
"name": "replicaSet",
"primary": "vm2-1:33061",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"vm2-1:33061": {
"address": "vm2-1:33061",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}addInstance() 함수로 Secondary 서버를 추가합니다.
MySQL localhost:33060+ ssl JS > rs.addInstance('vm2-2:33061')
Adding instance to the replicaset...
* Performing validation checks
This instance reports its own address as vm2-2:33061
vm2-2:33061: Instance configuration is suitable.
* Checking async replication topology...
* Checking connectivity and SSL configuration...
* Checking transaction state of the instance...
WARNING: A GTID set check of the MySQL instance at 'vm2-2:33061' determined that it contains transactions that do not originate from the replicaset, which must be discarded before it can join the replicaset.
vm2-2:33061 has the following errant GTIDs that do not exist in the replicaset:
6a16e862-763b-11ee-ae38-0800275ac39e:1-5
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of vm2-2:33061 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: vm2-2:33061 is being cloned from vm2-1:33061
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY #####################################################======= 88% In Progress
PAGE COPY ============================================================ 0% Not Started
REDO COPY ============================================================ 0% Not Started
NOTE: vm2-2:33061 is shutting down...
* Waiting for server restart... ready
* vm2-2:33061 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 2.22 GB transferred in 49 sec (45.31 MB/s)
** Changing replication source of vm2-2:33061 to vm2-1:33061
** Waiting for new instance to synchronize with PRIMARY...
** Transactions replicated ############################################################ 100%
The instance 'vm2-2:33061' was added to the replicaset and is replicating from vm2-1:33061.
* Waiting for instance 'vm2-2:33061' to synchronize the Metadata updates with the PRIMARY...
** Transactions replicated ############################################################ 100%여기서 강력한 기능을 볼 수 있는데요, 현재는 Clone 옵션을 선택하여 처음부터 데이터를 가져오지만, 복제 후 바이너리 로그를 제거하지 않았거나, 단독적으로 바이너리 로그를 작성하지 않았다면 Incremental 방식으로 추가된 트랜잭션만 가져올 수 있습니다.
(현재는 당연히 구성을 단독적으로 하였기에 Clone 방식을 사용합니다.)
ReplicaSet 상태를 확인합니다.
MySQL localhost:33060+ ssl JS > rs.status()
{
"replicaSet": {
"name": "replicaSet",
"primary": "vm2-1:33061",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"vm2-1:33061": {
"address": "vm2-1:33061",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"vm2-2:33061": {
"address": "vm2-2:33061",
"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": "ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2"
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}비교적 간단하게 Replication을 구성할 수 있었습니다.
기존 Replication과 비교하여 기본적으로 4 Threads Parallel Replication이 적용되고, 앞서 언급한 것 처럼 Primary(Master) 노드 변경과 Secondary 추가가 아주 쉽게 될 수 있습니다.
다음 포스팅은 ReplicaSet의 단점인 페일오버 미지원을 Orchestrator로 구현하도록 하겠습니다.
날 것의 기술 '불'로그