MySQL InnoDB Cluster

JM·2021년 6월 15일
InnoDB Clustermysql
2

MySQL

목록 보기
6/10

✔ MySQL InnoDB Cluster 란?

  • 3개 이상의 MySQL 서버 인스턴스로 구성되며 고 가용성 및 확장 기능을 제공
  • MySQL Group Replication을 기반으로 하여 자동 멤버십 관리, 내결함성, 자동 장애 조치 등과 같은 기능을 제공
  • 일반적으로 하나의 primary 인스턴스 (읽기-쓰기)와 여러 secondary 인스턴스 (읽기 전용)가 있는 single-primary 모드로 실행
  • 모든 인스턴스가 primary인 multi-primary 모드로 사용 가능

✔ MySQL InnoDB Cluster 설치 방법


🔨 설치 환경

  • OS : CentOS Linux release 7.9.2009 (Core)
  • DB : MySQL 8.0.23, MySQL Shell 8.0.23, MySQL Router 8.0.23

📌 Download
      https://dev.mysql.com/downloads/mysql/
      https://dev.mysql.com/downloads/shell/
      https://dev.mysql.com/downloads/router/

🔨 설치 전 준비

1. MySQL 설치

📌 MySQL 설치 방법

2. MySQL Shell 설치

# tar -xf mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local
# cd /usr/local
# mv mysql-shell-8.0.23-linux-glibc2.12-x86-64bit mysql-shell
# chown -R mysql.dba mysql-shell

# su - mysql
$ vi ~/.bash_profile
MySQL_Shell=/usr/local/mysql-shell/bin
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MySQL_Shell

$ source ~/.bash_profile
$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/mysql/.local/bin:/home/mysql/bin:/usr/local/mysql-shell/bin

3. MySQL Router 설치

# tar -xf mysql-router-8.0.23-linux-glibc2.12-x86_64.tar.xz -C /usr/local
# cd /usr/local
# mv mysql-router-8.0.23-linux-glibc2.12-x86_64 mysql-router
# chown -R mysql.dba mysql-router

# su - mysql
$ vi ~/.bash_profile
MySQL_Router=/usr/local/mysql-router-bin
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MySQL_Router

$ source ~/.bash_profile
$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/mysql/.local/bin:/home/mysql/bin:/usr/local/mysql-router/bin

🔨 클러스터 구성

1. MySQL Shell 계정 생성

mysql> CREATE USER 'shell'@'MySQL-S1' IDENTIFIED BY 'passwd';
mysql> CREATE USER 'shell'@'MySQL-S2' IDENTIFIED BY 'passwd';
mysql> CREATE USER 'shell'@'MySQL-S3' IDENTIFIED BY 'passwd';

mysql> GRANT ALL ON *.* TO 'shell'@'MySQL-S1' WITH GRANT OPTION;
mysql> GRANT ALL ON *.* TO 'shell'@'MySQL-S2' WITH GRANT OPTION;
mysql> GRANT ALL ON *.* TO 'shell'@'MySQL-S3' WITH GRANT OPTION;

2. 서버 구성 설정

$ mysqlsh --log-level=DEBUG3

 MySQL  JS > dba.checkInstanceConfiguration('shell@MySQL-S1:3306')
Please provide the password for 'shell@MySQL-S1:3306': ******
Save password for 'shell@MySQL-S1:3306'? [Y]es/[N]o/Ne[v]er (default No): 
Validating MySQL instance at MySQL-S1:3306 for use in an InnoDB cluster...

This instance reports its own address as MySQL-S1: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.

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                       |
| enforce_gtid_consistency               | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                              | OFF           | ON             | Update read-only variable and restart the server |
| server_id                              | 1             | <unique ID>    | Update read-only variable and restart the server |
| slave_parallel_type                    | DATABASE      | LOGICAL_CLOCK  | Update the server variable                       |
| slave_preserve_commit_order            | OFF           | ON             | Update the server variable                       |
+----------------------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server.
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"
        }, 
        {
            "action": "server_update+restart", 
            "current": "OFF", 
            "option": "enforce_gtid_consistency", 
            "required": "ON"
        }, 
        {
            "action": "server_update+restart", 
            "current": "OFF", 
            "option": "gtid_mode", 
            "required": "ON"
        }, 
        {
            "action": "server_update+restart", 
            "current": "1", 
            "option": "server_id", 
            "required": "<unique ID>"
        }, 
        {
            "action": "server_update", 
            "current": "DATABASE", 
            "option": "slave_parallel_type", 
            "required": "LOGICAL_CLOCK"
        }, 
        {
            "action": "server_update", 
            "current": "OFF", 
            "option": "slave_preserve_commit_order", 
            "required": "ON"
        }
    ], 
    "status": "error"
}
 MySQL  JS > dba.configureInstance('shell@MySQL-S1:3306')
Please provide the password for 'shell@MySQL-S1:3306': ******
Save password for 'shell@MySQL-S1:3306'? [Y]es/[N]o/Ne[v]er (default No): 
Configuring MySQL instance at MySQL-S1:3306 for use in an InnoDB cluster...

This instance reports its own address as MySQL-S1: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                       |
| enforce_gtid_consistency               | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                              | OFF           | ON             | Update read-only variable and restart the server |
| server_id                              | 1             | <unique ID>    | Update read-only variable and restart the server |
| slave_parallel_type                    | DATABASE      | LOGICAL_CLOCK  | Update the server variable                       |
| slave_preserve_commit_order            | OFF           | ON             | Update the server variable                       |
+----------------------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Configuring instance...
The instance 'MySQL-S1:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at MySQL-S1:3306 was restarted.
🔍 나머지 두개 노드도 동일하게 진행

3. 클러스터 생성

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

Validating instance configuration at MySQL-S1:3306...

This instance reports its own address as MySQL-S1:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'MySQL-S1:33061'. Use the localAddress option to override.

Creating InnoDB cluster 'myCluster' on 'MySQL-S1: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:myCluster>

4. 클러스터 인스턴스 추가

MySQL  MySQL-S1:3306 ssl  JS > cluster.addInstance('shell@MySQL-S2:3306')

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

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster 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): 
Validating instance configuration at MySQL-S2:3306...

This instance reports its own address as MySQL-S2:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'MySQL-S2: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: MySQL-S2:3306 is being cloned from mysql-s1: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  ============================================================    0%  In Progress    
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: MySQL-S2:3306 is shutting down...

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

State recovery already finished for 'MySQL-S2:3306'

The instance 'MySQL-S2:3306' was successfully added to the cluster.

 MySQL  MySQL-S1:3306 ssl  JS > cluster.addInstance('shell@MySQL-S3:3306')

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

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster 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): 
WARNING: The instance 'MySQL-S3:3306' is only read compatible with the cluster, thus it will join the cluster in R/O mode.
Validating instance configuration at MySQL-S3:3306...

This instance reports its own address as MySQL-S3:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'MySQL-S3: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.
Incremental state recovery is now in progress.

* Waiting for distributed recovery to finish...
NOTE: 'MySQL-S3:3306' is being recovered from 'MySQL-S1:3306'
* Distributed recovery has finished

The instance 'MySQL-S3:3306' was successfully added to the cluster.

4. 클러스터 상태 확인

 MySQL  MySQL-S1:3306 ssl  JS > cluster.status()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "MySQL-S1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "MySQL-S1:3306": {
                "address": "MySQL-S1:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.23"
            }, 
            "MySQL-S2:3306": {
                "address": "MySQL-S2:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.23"
            }, 
            "MySQL-S3:3306": {
                "address": "MySQL-S3:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "MySQL-S1:3306"
}

🔨 MySQL Router 구성

1. Bootstrap

$ mysqlrouter --bootstrap shell@MySQL-S1:3306 -d /mysql/router
Please enter MySQL password for shell: 
# Bootstrapping MySQL Router instance at '/mysql/router'...

- 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 /mysql/router/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'myCluster'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /mysql/router/mysqlrouter.conf

the cluster 'myCluster' 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:64460
- Read/Only Connections:  localhost:64470

2. MySQL Router 실행

$ cd /mysql/router/
$ ./start.sh 
$ PID 22120 written to '/mysql/router/mysqlrouter.pid'
logging facility initialized, switching logging to loggers specified in configuration

🔨 InnoDB Cluster 접속

[Read/Write]
$ mysql -u app -p -h 'router-ip' -P 6446

[Read Only]
$ mysql -u app -p -h 'router-ip' -P 6447
profile
JM
오픈소스 DB엔지니어
이전 포스트

MySQL 8.0의 새로운 기능

다음 포스트

MySQL Clone Plugin

0개의 댓글