Mariadb에서는 Galera cluster가 있다면 Mysql에서는 InnoDB cluster가 있습니다.
▶ InnoDB Cluster 요구사항
▶ 테스트 환경
Hostname
|
IP
|
version
|
jh-my001
|
192.168.100.40
|
DB1
|
jh-my002
|
192.168.100.41
|
DB2
|
jh-my003
|
192.168.100.42
|
DB3
|
router
|
192.168.100.53
|
Router
|
▶ Mysql 설치
Mysql Enterprise Edition 설치파일 다운로드
https://login.oracle.com:443/oam/server/obrareq.cgi?encquery%3DZEf9YpJx7yefFPAIx5RkQz%2B8N1Oz4di2RXJOEpufXKj%2BP7TXADXdA8EL2kmaCUcbo7yhF%2BJfopjxiCLGr2iEWyKW3gPsypzmaO%2BsQZYNmesui4%2BYmBxct9lTVnOk%2FfJNdf6UqueXGppxf3WyJ%2FWNkqCmb0LeKAw5zk7VncpOsQF%2FMBuAHjKjFKDc4djvGxDl6Jl2kxOKBLXQFwoZ%2Fi0x4hoiklhLu1sGD%2BTFNIOb80H13uiGk5hbN45FDcHsUGDxJBj3zIPrJOHB%2B%2BNAcO4VYv6CCkfsS1ihkFFrhJbw5imGDl%2BwiWkNeMAgT9x1p%2FcitvPVec1szTvyMFKSI%2BRtkJ3RF32k%2FLzvYZ0D8unOcPc%3D%20agentid%3Dedelivery-extprod%20ver%3D1%20crmethod%3D2&ECID-Context=1.005rjDyAEguFo2KimTctkJ0002qn009TuQ%3BkXjE
login.oracle.com:443
![](https://blog.kakaocdn.net/dn/D2meN/btrB58ItdSt/4oWLAGOZ776DNEqSa5yWAK/img.png)
▶ hosts 등록(all)
[root@jh-my001 ~]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.100.40 jh-my1
192.168.100.41 jh-my2
192.168.100.42 jh-my3
▶ DB 설치(all)
[root@jh-my001 ~]# groupadd mysql
[root@jh-my001 ~]# useradd -g mysql mysql
[root@jh-my001 ~]# mkdir -p /data/mysql
[root@jh-my001 ~]# mkdir -p /data/data
[root@jh-my001 ~]# mkdir -p /data/log/relay-log
[root@jh-my001 ~]# chown -R mysql.mysql /data
[root@jh-my001 ~]# unzip V1020514-01.zip
[root@jh-my001 ~]# tar -xvf mysql-commercial-8.0.29-el7-x86_64.tar.gz
[root@jh-my001 ~]# mv mysql-commercial-8.0.29-el7-x86_64/* /data/mysql/
[root@jh-my001 ~]# mv mysql-commercial-8.0.29-el7-x86_64/* /data/mysql/
[root@jh-my001 ~]# cd /data/mysql/bin/
[root@jh-my001 ~]# mv mysql-commercial-8.0.29-el7-x86_64/* /data/mysql/
[root@jh-my001 bin]# ./mysqld --initialize --basedir=/data/mysql
[root@jh-my001 ~]# vi ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/data/mysql/bin
export PATH
[root@jh-my001 log]# vi /etc/my.cnf
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
user=mysql
port=3306
basedir=/data/mysql
datadir=/data/data
tmpdir=/tmp
socket=/tmp/mysql.sock
skip-name-resolve
log-bin=mysql-bin
log-error=/data/log/mysqld.err
relay-log=/data/log/relay-log/relay-bin
server-id=1 => 각 server_id가 달라야합니다
binlog_checksum=none
enforce_gtid_consistency=on
gtid_mode=on
log_slave_updates
report_host = 192.168.100.40 => 각 서버별로 해당 ip만 바꿔준다
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 1cd05d2e-cf6a-11ec-af4f-f220cd10bb6e
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = 192.168.100.40:33061 => 각 서버별로 해당 ip만 바꿔준다
loose-group_replication_group_seeds = 192.168.100.40:33061,192.168.100.41:33061.192.168.100.42:33061
loose-group_replication_bootstrap_group = OFF
loose-group_replication_ip_whitelist = 192.168.100.40:33061,192.168.100.41:33061.192.168.100.42:33061
## loose-group_replication_group_name 값
mysql> show variables like 'gtid_executed';
+---------------+------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------------------------------------------------------------------------------------+
| gtid_executed | 1cd05d2e-cf6a-11ec-af4f-f220cd10bb6e:1,
[root@jh-my001 bin]# ./mysqld --initialize --user=mysql
[root@jh-my001 data]# mysqld_safe --user=mysql &
▶ Shell 설치(all)
download URL : https://dev.mysql.com/downloads/shell/
MySQL :: Download MySQL Shell
The MySQL Shell is an interactive Javascript, Python, or SQL interface supporting development and administration for the MySQL Server and is a component of the MySQL Server. You can use the MySQL Shell to perform data queries and updates as well as various
dev.mysql.com
OS종류 및 버전에 맞게 다운로드하면 됩니다.
[root@jh-my001 ~]# wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.20-1.el7.x86_64.rpm
[root@jh-my001 ~]# yum -y install mysql-shell-8.0.28-1.el7.x86_64.rpm
▶ Shell을 통해 mysql 접속 및 config 수정(all)
[root@jh-my001 ~]# mysqlsh
mysqlsh: /lib64/libcrypto.so.10: version `OPENSSL_1.0.2' not found (required by /usr/bin/../lib/mysqlsh/libssh.so.4)
[root@jh-my001 ~]# yum -y install openssl
[root@jh-my001 ~]# mysqlsh
MySQL Shell 8.0.28
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 >
## 내 db에 접속
MySQL JS > \c root@(/tmp/mysql.sock)
Creating a session to 'root@/tmp%2Fmysql.sock'
Please provide the password for 'root@/tmp%2Fmysql.sock': ****
Save password for 'root@/tmp%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): Y => password지정할건지 묻는다. 테스트라 y함. 운영에서 저장시 보안사고!
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 13
Server version: 8.0.29-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL localhost JS > \sql =>sql모드로 변경
Switching to SQL mode... Commands end with ;
MySQL localhost SQL > \py => python모드로 변경
Switching to Python mode...
MySQL localhost Py > \status
MySQL Shell version 8.0.28
Connection Id: 13
Current schema:
Current user: root@localhost
SSL: Not in use.
Using delimiter: ;
Server version: 8.0.29-commercial MySQL Enterprise Server - Commercial
Protocol version: Classic 10
Client library: 8.0.28
Connection: Localhost via UNIX socket
Unix socket: /tmp/mysql.sock
Server characterset: utf8mb4
Schema characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
Result characterset: utf8mb4
Compression: Disabled
Uptime: 1 hour 44 min 1.0000 sec
Threads: 2 Questions: 14 Slow queries: 0 Opens: 148 Flush tables: 3 Open tables: 64 Queries per second avg: 0.002
## Instance 설정
MySQL localhost JS > dba.configureInstance('root@(/tmp/mysql.sock)')
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as jh-my001:3306 => member들 통신 주소
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 => root권한 유저 만들건지
2) Create a new admin account for InnoDB cluster 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: icadmin
Password for new account: *
Confirm password: *
applierWorkerThreads will be set to the default value of 4.
NOTE: Some configuration options need to be fixed: => 현재 conf를 읽고 고쳐야하는 것드를 보여준다.
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| 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 |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
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 => parameter 수정해줌.(5.7은 my.cnf에 넣어주는데 8.0은 안넣어주고 다른곳에 넣어진다.-> datadir의 mysql-auto.cnf)
Do you want to restart the instance after configuring it? [y/n]: y => 서버 재시작 여부(mysql_safe로 기동했을 시 자동 restart 가능하다, 하지만 mysqld로만 띄우면 직접 다시 띄어야한다)
Cluster admin user 'icadmin'@'%' created.
Configuring instance...
The instance 'jh-my001:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at jh-my001:3306 was restarted.
MySQL localhost JS > Restarting mysqld...
2022-05-09T07:34:51.336934Z mysqld_safe Number of processes running now: 0
2022-05-09T07:34:51.340487Z mysqld_safe mysqld restarted
## 재기동
[root@jh-my001 ~]# mysqld --user=mysql &
## 생성한 icamin 유저의 권한 확인
mysql> show grants for 'icadmin'@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for icadmin@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT CLONE_ADMIN,CONNECTION_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,ROLE_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata`.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_bkp`.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_previous`.* TO `icadmin`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
## auto로 적용된 config 조회
[root@jh-my001 data]# cat /data/data/mysqld-auto.cnf
{"Version": 2, "mysql_static_variables": {"gtid_mode": {"Value": "ON", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652060770050232}}, "slave_parallel_workers": {"Value": "4", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652062713406968}}, "enforce_gtid_consistency": {"Value": "ON", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652060770048914}}, "replica_parallel_workers": {"Value": "4", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652062713406968}}}, "mysql_dynamic_variables": {"binlog_transaction_dependency_tracking": {"Value": "WRITESET", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652060770048658}}}}
참고
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster.html
MySQL :: MySQL Shell 8.0 :: 7 MySQL InnoDB Cluster
Chapter 7 MySQL InnoDB Cluster MySQL InnoDB Cluster provides a complete high availability solution for MySQL. By using AdminAPI, which is included with MySQL Shell, you can easily configure and administer a group of at least three MySQL server instances
dev.mysql.com
'DataBase > MySQL & MariaDB' 카테고리의 다른 글
[MySQL - InnoDB cluster] part 3 복구 테스트 (0) | 2022.05.14 |
---|---|
[MySQL - InnoDB cluster] part 2 cluster생성/인스턴스 추가 (0) | 2022.05.14 |
[Mysql - Performance parameter] (0) | 2022.03.24 |
[MariaDB - Sharding] use. Spider Engine (0) | 2022.03.12 |
[MariaDB - MHA Chain replication 구성을 통한 데이터 이관] (0) | 2022.03.10 |