MySQL: InnoDB Cluster 고가용성 구성

MySQL

MySQL: InnoDB Cluster 고가용성 구성

dewstream 2025. 7. 25. 08:00

728x90

※ Installing MySQL InnoDB Cluster.

※ Version: Linux 9.6 (Rocky), MySQL 8.

안녕하세요. 듀스트림입니다.

오늘은 간단하게 dnf를 활용하여 패키지된 MySQL InnoDB Cluster를 설치해보겠습니다.

※ MySQL 설치 이후의 과정으로, MySQL 설치는 이전 포스팅 참고 부탁드립니다.

MySQL: RHEL 기반 Linux에 MySQL 설치 방법

※ Installing MySQL 8.0 on Linux from source.※ Version: Linux 8.10 (Rocky), MySQL 8.0.※ 싱글 환경은 CPU 2Core, Memory 4GB 이상을 권장합니다. 안녕하세요. 듀스트림입니다. 오늘 포스팅은 MySQL InnoDB 스토리지 엔진 설

dewstream.tistory.com

▸ 빌드는 조금 다릅니다.

cmake .. \
  -DWITH_BOOST=/root/boost_1_77_0 \
  -DMYSQL_TCP_PORT=3306 \
  -DMYSQL_UNIX_ADDR=/data/mysql_data/mysql.sock \
  -DCMAKE_INSTALL_PREFIX=/mysql \
  -DSYSCONFDIR=/mysql/etc \
  -DMYSQL_DATADIR=/data/mysql_data \
  -DWITH_SSL=system \
  -DWITH_ZLIB=bundled \
  -DDEFAULT_STORAGE_ENGINE=InnoDB \
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \
  -DWITH_PARTITION_STORAGE_ENGINE=1 \
  -DWITH_FEDERATED_STORAGE_ENGINE=1 \
  -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
  -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
  -DWITH_DEBUG=0 \
  -DWITH_EXTRA_CHARSETS=all \
  -DWITH_SYSTEMD=1 \
  -DWITH_LIBEVENT=system \
  -DWITH_ICU=bundled \
  -DENABLED_LOCAL_INFILE=1

※ 구성도는 아래 링크를 참고 부탁드리며, 서버는 2대가 필요합니다.

MySQL :: MySQL 8.4 Reference Manual :: 23 InnoDB Cluster

Chapter 23 InnoDB Cluster This chapter introduces MySQL InnoDB Cluster, which combines MySQL technologies to enable you to deploy and administer a complete integrated high availability solution for MySQL. This content is a high-level overview of InnoDB Clu

dev.mysql.com

1. OS 설정

▸ 방화벽 포트 오픈: 모든 노드

# MySQL Group-replication 기본 포트: 33061
sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
sudo firewall-cmd --reload

▸ hostname 등록: 모든 노드

# 노드1
hostnamectl set-hostname node01

# 노드2
hostnamectl set-hostname node02

echo "10.12.20.32 node01" >> /etc/hosts
echo "10.12.20.53 node02" >> /etc/hosts

▸ SSL 생성 후 카피: node01에서 생성 후 카피

# ca.pem의 subject와 server-cert.pem, client-cert.pem의 issuer가 동일해야 합니다.

# CA 인증서 및 키 생성
openssl genpkey -algorithm RSA -out /mysql/etc/ca-key.pem
openssl req -new -key /mysql/etc/ca-key.pem -out /mysql/etc/ca-req.pem
openssl x509 -req -in /mysql/etc/ca-req.pem -signkey /mysql/etc/ca-key.pem -out /mysql/etc/ca.pem

# 서버 인증서 및 키 생성
openssl genpkey -algorithm RSA -out /mysql/etc/server-key.pem
openssl req -new -key /mysql/etc/server-key.pem -out /mysql/etc/server-req.pem
openssl x509 -req -in /mysql/etc/server-req.pem -CA /mysql/etc/ca.pem -CAkey /mysql/etc/ca-key.pem -CAcreateserial -out /mysql/etc/server-cert.pem

# 클라이언트 인증서 및 키 생성
openssl genpkey -algorithm RSA -out /mysql/etc/client-key.pem
openssl req -new -key /mysql/etc/client-key.pem -out /mysql/etc/client-req.pem
openssl x509 -req -in /mysql/etc/client-req.pem -CA /mysql/etc/ca.pem -CAkey /mysql/etc/ca-key.pem -CAcreateserial -out /mysql/etc/client-cert.pem

# 파일 권한 설정
chown mysql:mysql /mysql/etc/*.pem
chmod 600 /mysql/etc/*.pem

# 인증서 확인
openssl x509 -in /mysql/etc/ca.pem -noout -subject
openssl x509 -in /mysql/etc/server-cert.pem -noout -issuer
openssl x509 -in /mysql/etc/client-cert.pem -noout -issuer

# node02로 scp (이후 권한 확인 필수)
scp /mysql/etc/ca.pem root@node02:/mysql/etc/
scp /mysql/etc/server-cert.pem root@node02:/mysql/etc/
scp /mysql/etc/server-key.pem root@node02:/mysql/etc/
scp /mysql/etc/client-cert.pem root@node02:/mysql/etc/
scp /mysql/etc/client-key.pem root@node02:/mysql/etc/


# 확인
ls -l /mysql/etc/ca.pem
ls -l /mysql/etc/server-key.pem
ls -l /mysql/etc/server-cert.pem
ls -l /mysql/etc/client-key.pem
ls -l /mysql/etc/client-cert.pem

# 키 쌍 확인
openssl x509 -noout -modulus -in /mysql/etc/server-cert.pem | openssl md5
openssl rsa -noout -modulus -in /mysql/etc/server-key.pem | openssl md5

# + 삭제(필요시)
rm -f /mysql/etc/ca.pem /mysql/etc/server-cert.pem /mysql/etc/server-key.pem /mysql/etc/client-cert.pem /mysql/etc/client-key.pem

2. MySQL Shell 설치

# MySQL 공식 Yum 저장소 등록
dnf install -y https://repo.mysql.com/mysql80-community-release-el9-1.noarch.rpm

# Rocky 8 버전은 아래 명령으로
dnf install -y https://repo.mysql.com/mysql80-community-release-el8-1.noarch.rpm

# GPG-KEY 등록
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023

dnf install -y mysql-shell

3. 클러스터 관리 계정 생성: 모든 노드

mysql -u root -p

-- 클러스터 관리 계정 생성
CREATE USER 'cluster_admin'@'%' IDENTIFIED BY 'Votmdnjem1@34' REQUIRE SSL;
GRANT CLONE_ADMIN, CONNECTION_ADMIN, BACKUP_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 'cluster_admin'@'%' WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'cluster_admin'@'%' 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 'cluster_admin'@'%' 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 'cluster_admin'@'%' 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 'cluster_admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 필요시 --
GRANT ALL PRIVILEGES ON *.* TO 'cluster_admin'@'%' WITH GRANT OPTION;

-- 복제 전용(Recovery Channel) 계정 생성 (필요시)
CREATE USER 'replication_user'@'10.12.20.%' IDENTIFIED BY 'Votmdnjem1@34' REQUIRE SSL;
GRANT REPLICATION SLAVE, GROUP_REPLICATION_ADMIN ON *.* TO 'replication_user'@'10.12.20.%';
FLUSH PRIVILEGES;

4. my.cnf 설정 추가: 모든 노드

▸ Group Replication uuid 생성

uuidgen

vi /mysql/etc/my.cnf

▸ Master 서버 파라미터

[client]
port=3306
socket=/data/mysql_data/mysql.sock

[mysqld]
#------ 고유서버 ID ------#
server-id=1

#------ Plug in -------#
plugin-load-add=group_replication.so
plugin-load-add=mysql_clone.so

#------ 서비스 기본 ------#
bind_address=0.0.0.0
max_connections=300
port=3306
user=mysql
basedir=/mysql
datadir=/data/mysql_data
socket=/data/mysql_data/mysql.sock
pid-file=/mysql/etc/mysql.pid
symbolic-links=0 #보안
skip_name_resolve
event-scheduler=OFF
sysdate-is-now

binlog_expire_logs_seconds=604800
max_allowed_packet=1G
net_read_timeout=600
net_write_timeout=600
connect_timeout=30
slave_net_timeout=3600
slave_preserve_commit_order=ON
secure-file-priv=""

#------ Timestamp ------#
explicit_defaults_for_timestamp = TRUE

#------ GTID ------#
gtid_mode=ON
enforce_gtid_consistency=ON

#------복제안전성------#
relay_log_recovery=ON

#------그룹복제(GR)------#
transaction_write_set_extraction=XXHASH64
#binlog_transaction_dependency_tracking=WRITESET

report_host=node01
loose-group_replication_group_name="a7610bf0-b639-4930-9a4c-cd7eeac7b4a9"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="node01:33061"
loose-group_replication_group_seeds="node01:33061,node02:33061"
loose-group_replication_bootstrap_group=OFF # !!
loose-group_replication_ip_whitelist="node01:33061,node02:33061"
loose-group_replication_ip_allowlist="10.12.20.0/24,127.0.0.1,::1"
#group_replication_ip_allowlist="10.12.20.0/24":contentReference[oaicite:10]{index=10}

#------ Clone ------#
loose-clone_valid_donor_list="node02:3306"

#------ SSL ------#
require_secure_transport=ON
ssl-ca=/mysql/etc/ca.pem
ssl-cert=/mysql/etc/server-cert.pem
ssl-key=/mysql/etc/server-key.pem

loose-group_replication_ssl_mode=REQUIRED
loose-group_replication_recovery_use_ssl=ON
loose-group_replication_recovery_ssl_ca=/mysql/etc/ca.pem
loose-group_replication_recovery_ssl_cert=/mysql/etc/server-cert.pem
loose-group_replication_recovery_ssl_key=/mysql/etc/server-key.pem

#------ MySQL Shell(X Protocol) ------#
mysqlx=ON
mysqlx_port=33060
mysqlx-bind-address=0.0.0.0

#------ etc ------#
#innodb_buffer_pool_size=4G
#innodb_log_file_size=512M
#tmpdir=/data/mysql_data/tmp

#------ DDL 보호 ------#
super_read_only=OFF #clone addInstance시OFF

#------ Character ------#
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
init-connect=SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci'
lower_case_table_names=0

#------ Log ------#
log-error=/data/mysql_log/error.log
log_output=TABLE,FILE
general_log_file=/data/mysql_log/general_query.log
general_log=0
slow-query-log=1
long_query_time=1
slow_query_log_file=/data/mysql_log/slow_query.log
log_queries_not_using_indexes=OFF
log_timestamps=SYSTEM

#------ Binary Log ------#
binlog_format=ROW
log-bin=/data/mysql_data/binlog
sync_binlog=1
log_replica_updates=1
binlog_expire_logs_seconds=604800
log_bin_trust_function_creators=ON

[mysqld_safe]
pid-file=/mysql/etc/mysql.pid
open-files-limit = 16384

▸ Replica 서버 파라미터

[client]
port=3306
socket=/data/mysql_data/mysql.sock

[mysqld]
#------ 고유서버 ID ------#
server-id=2

#------ Plug in -------#
plugin-load-add=group_replication.so
plugin-load-add=mysql_clone.so

#------ 서비스 기본 ------#
bind_address=0.0.0.0
max_connections=300
port=3306
user=mysql
basedir=/mysql
datadir=/data/mysql_data
socket=/data/mysql_data/mysql.sock
pid-file=/mysql/etc/mysql.pid
symbolic-links=0 #보안
skip_name_resolve
event-scheduler=OFF
sysdate-is-now

binlog_expire_logs_seconds=604800
max_allowed_packet=1G
net_read_timeout=600
net_write_timeout=600
connect_timeout=30
slave_net_timeout=3600
slave_preserve_commit_order=ON
secure-file-priv=""

#------ Timestamp ------#
explicit_defaults_for_timestamp = TRUE

#------ GTID ------#
gtid_mode=ON
enforce_gtid_consistency=ON

#------복제안전성------#
relay_log_recovery=ON

#------그룹복제(GR)------#
transaction_write_set_extraction=XXHASH64
binlog_transaction_dependency_tracking=WRITESET

report_host=node02
loose-group_replication_group_name="a7610bf0-b639-4930-9a4c-cd7eeac7b4a9"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="node02:33061"
loose-group_replication_group_seeds="node01:33061,node02:33061"
loose-group_replication_bootstrap_group=OFF # Replica=OFF
loose-group_replication_ip_whitelist="node01:33061,node02:33061"
loose-group_replication_ip_allowlist="10.12.20.0/24,127.0.0.1,::1"
#group_replication_ip_allowlist="10.12.20.0/24":contentReference[oaicite:10]{index=10}

#------ Clone ------#
loose-clone_valid_donor_list="node01:3306"

#------ SSL ------#
require_secure_transport=ON
ssl-ca=/mysql/etc/ca.pem
ssl-cert=/mysql/etc/server-cert.pem
ssl-key=/mysql/etc/server-key.pem

loose-group_replication_ssl_mode=REQUIRED
loose-group_replication_recovery_use_ssl=ON
loose-group_replication_recovery_ssl_ca=/mysql/etc/ca.pem
loose-group_replication_recovery_ssl_cert=/mysql/etc/server-cert.pem
loose-group_replication_recovery_ssl_key=/mysql/etc/server-key.pem

#------ MySQL Shell(X Protocol) ------#
mysqlx=ON
mysqlx_port=33060
mysqlx-bind-address=0.0.0.0

#------ etc ------#
#innodb_buffer_pool_size=4G
#innodb_log_file_size=512M
#tmpdir=/data/mysql_data/tmp

#------ DDL 보호 ------#
super_read_only=OFF #clone addInstance시OFF

#------ Character ------#
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
init-connect=SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci'
lower_case_table_names=0

#------ Log ------#
log-error=/data/mysql_log/error.log
log_output=TABLE,FILE
general_log_file=/data/mysql_log/general_query.log
general_log=0
slow-query-log=1
long_query_time=1
slow_query_log_file=/data/mysql_log/slow_query.log
log_queries_not_using_indexes=OFF
log_timestamps=SYSTEM

#------ Binary Log ------#
binlog_format=ROW
log-bin=/data/mysql_data/binlog
sync_binlog=1
log_replica_updates=1
binlog_expire_logs_seconds=604800
log_bin_trust_function_creators=ON

[mysqld_safe]
pid-file=/mysql/etc/mysql.pid
open-files-limit = 16384

▸ 재기동

# 정지
mysqladmin -u root -p  shutdown --socket=/data/mysql_data/mysql.sock

# 시작
mysqld_safe --defaults-file=/mysql/etc/my.cnf --user=mysql &

+ --socket 귀찮으니까 심볼릭 링크

ln -sf /data/mysql_data/mysql.sock /tmp/mysql.sock

▸ 플러그인 설치

mysql -u root -p

-- 플러그인 확인
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.plugins WHERE PLUGIN_NAME IN ('clone', 'group_replication');

-- 필요시 --
SET GLOBAL super_read_only=OFF;

-- 없으면 설치: 클론 --
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

-- 없으면 설치: 그룹레플리케이션 --
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

▸ 그룹 레플리케이션 등록

mysql -u cluster_admin -p -h 10.12.20.32 --ssl-mode=REQUIRED

-- 관련 설정 확인
SHOW VARIABLES LIKE 'group_replication%';

CHANGE MASTER TO MASTER_USER='cluster_admin', MASTER_PASSWORD='Votmdnjem1@34' FOR CHANNEL 'group_replication_recovery';

-- 복제 전용 유저 사용시 --
CHANGE MASTER TO MASTER_USER='replication_user', MASTER_PASSWORD='Votmdnjem1@34' FOR CHANNEL 'group_replication_recovery';

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 확인
SELECT * FROM performance_schema.replication_group_members;
SHOW VARIABLES LIKE 'group_replication_bootstrap_group';

5. 클러스터 구성

▸ MySQL-Shell 접속

mysqlsh --uri cluster_admin@node01 --password

▸ MySQL Group-Replication 클러스터 생성

dba.configureLocalInstance('cluster_admin@node01:3306')

var cluster = dba.createCluster('MySQL-Cluster')

# 클러스터 상태 확인
cluster.status();

# 클러스터 메타데이터 동기화
cluster.rescan()

+ 이미 생성된 클러스터 불러오기

mysql -u root -p

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

var cluster = dba.getCluster('MySQL-Cluster')

▸ 클러스터에 노드 추가

dba.checkInstanceConfiguration('cluster_admin@node02:3306')

cluster.addInstance('cluster_admin@node02:3306')

▸ 확인

mysql -u root -p

SELECT * FROM performance_schema.replication_group_members;

오늘은 여기까지~

728x90

저작자표시 비영리 변경금지 (새창열림)