Mysql 8 Innodbcluster – Cluster 구성

by · Published · Updated

Mysql 8 Innodbcluster 구성

이전글 – Mysql 8 Innodbcluster – Install Mysql – Umount Blog

Mysql 8 Innodbcluster 구성을 하기 위해서 작성합니다. Mysql 8 Innodbcluster 구성을 위해 서버는 3대를 사용하였습니다.

Software

OS : Rocky 8.6 minimal

Mysql : 8.0.29

Innodbcluster 구성

1. Setting Firewall

다른 노드를 확인해서 포트 3306, 33061 열어 둡니다.

2. Download & Install Mysqlshell

Cluster를 구성하기 위해 MysqlShell을 다운로드하고 설치합니다.

wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.29-1.el8.x86_64.rpm
dnf install -y mysql-shell-8.0.29-1.el8.x86_64.rpm

3. Create Shell User ( All Node )

Mysqlshell이 사용할 User를 생성 및 권한을 줍니다.

create user '<shelluser>'@'<maindb1 IP>' identified by '<password>';
create user '<shelluser>'@'<maindb2 IP>' identified by '<password>';
create user '<shelluser>'@'<maindb3 IP>' identified by '<password>';

grant all on *.* to '<shelluser>'@'<maindb1 IP>' with grant option;
grant all on *.* to '<shelluser>'@'<maindb2 IP>' with grant option;
grant all on *.* to '<shelluser>'@'<maindb3 IP>' with grant option;

flush privileges;

4. Pre-setting innodbcluster ( All Node )

모든 Node에서 Cluster를 생성하기 전에 my.cnf 설정이 제대로 되어 있는지 점검 합니다..

[root@maindb001 ~]# mysqlsh --log-level=debug3
MySQL Shell 8.0.29

Copyright (c) 2016, 2022, 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.
 MySQL  JS > dba.checkInstanceConfiguration('<shelluser>@<Node IP>')
Please provide the password for '<shelluser>@<Node IP>': ******************************
Save password for '<shelluser>@<Node IP>'? [Y]es/[N]o/Ne[v]er (default No): 
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as <Node IP>:3306

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

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 |
+----------------------------------------+---------------+----------------+----------------------------+

NOTE: Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update", 
            "current": "COMMIT_ORDER", 
            "option": "binlog_transaction_dependency_tracking", 
            "required": "WRITESET"
        }
    ], 
    "status": "error""
}
 MySQL  JS > dba.configureInstance('<shelluser>@<Node IP>')
Please provide the password for '<shelluser>@<Node IP>': ******************************
Save password for '<shelluser>@<Node IP>'? [Y]es/[N]o/Ne[v]er (default No): 
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as <Node IP>:3306

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 '<Node IP>:3306' was configured to be used in an InnoDB cluster.

5. Create Cluster ( Primary Main Node )

Cluster를 생성합니다. 

 MySQL  JS > \c <shelluser>@<Node IP>
Creating a session to '<shelluser>@<Node IP>'
Please provide the password for '<shelluser>@<Node IP>': ******************************
Save password for '<shelluser>@<Node IP>'? [Y]es/[N]o/Ne[v]er (default No): 
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 25 (X protocol)
Server version: 8.0.29 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  <Node IP>:33060+ ssl  JS > cluster = dba.createCluster('<Cluster Name>')
A new InnoDB cluster will be created on instance '<Node IP>:3306'.

Validating instance configuration at <Node IP>:3306...

This instance reports its own address as <Node IP>:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '<Node IP>:33061'. Use the localAddress option to override.

Creating InnoDB cluster '<Cluster Name>' on '<Node IP>:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

<Cluster:<Cluster Name>>

6. Join Cluster ( Primary Main Node )

나머지 Node를 Cluster에 조인합니다.

 MySQL  <Node IP>:33060+ ssl  JS > cluster.addInstance('<shelluser>@< Add Node IP>')

WARNING: A GTID set check of the MySQL instance at '<shelluser>@< Add Node IP>' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

< Node IP >:3306 has the following errant GTIDs that do not exist in the cluster:
< GTID >:1-11

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of 10.0.100.206:3306 with a physical snapshot from an existing cluster 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
Validating instance configuration at < Node IP >:3306...

This instance reports its own address as < Node IP >:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '< Node IP >:33061'. Use the localAddress option to override.

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: < Node IP >:3306 is being cloned from < Node IP >:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: < Node IP >:3306 is shutting down...

* Waiting for server restart... ready 
* < Node IP >:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 214.81 GB transferred in 50 min 28 sec (70.94 MB/s)

State recovery already finished for '< Node IP >:3306'

The instance '< Node IP >:3306' was successfully added to the cluster.

 MySQL  < Node IP >:33060+ ssl  JS >

7. Cluster 상태 확인

모든 Node가 Cluster에 정상적으로 조인되었는지 확인 합니다.

 MySQL  < MainDB 1 IP >:33060+ ssl  JS > cluster.status()
{
    "clusterName": "< Cluster Name >", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "< MainDB 1 IP >:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "< MainDB 1 IP >:3306": {
                "address": "< MainDB 1 IP >:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.29"
            }, 
            "< MainDB 2 IP >:3306": {
                "address": "< MainDB 2 IP >:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.29"
            }, 
            "< MainDB 3 IP >:3306": {
                "address": "< MainDB 3 IP >:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.29"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "< MainDB 1 IP >:3306"
}
 MySQL  < MainDB 1 IP >:33060+ ssl  JS >

Tags:

You may also like...

Subscribe
Notify of
guest

이 사이트는 스팸을 줄이는 아키스밋을 사용합니다. 댓글이 어떻게 처리되는지 알아보십시오.

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x