MySQL 8.4.3 InnoDB Cluster 구성 및 성능 테스트

2024. 12. 10. 13:58ㆍ데이터베이스

반응형

# Intro

 AWS RDS 에서도 MySQL 8.4.3 버전이 릴리즈 되었습니다.

내년에는 Aurora MySQL 4 버전이 나오지 않을까 예상됩니다.

지난 몇년간 MySQL 5.7 에서 MySQL 8.0으로의 업그레이드 & 마이그레이션이 이루어졌던 것 처럼, 이제는 8.4 버전에 대해 미리 학습하고 대비해두는 것이 좋을 것으로 보입니다.

하여튼, 이 글에서는 MySQL 8.4 버전을 활용해서 InnoDB Cluster를 구성하는 내용을 기록하겠습니다.

 

 

 

# 환경

Docker에 편하게 구성하려 했으나... 성능 간섭이 생길 것 같아, 비용은 들지만 AWS EC2 에 인스턴스를 구성합니다.

  • Ubuntu Server 24.04 LTS
  • t2.micro
  • gp2 8GB 스토리지
  • 인스턴스 수량 총 4대 필요

콘솔 화면

 

인스턴스를 4개 생성하면 됩니다. 

 

InnoDB Cluster를 만족하기 위한 MySQL 서버의 최소 숫자는 3입니다.

또한, MySQL 공식문서에서 가이드하는 베스트 프랙티스는 MySQL Router를 Application과 같은 서버에 설치하여 레이턴시를 감소시키고 자연스레 따라오는 HA의 이점을 권장하는데, 현실적이지 않은 것 같아 따로 인스턴스를 구성합니다.

  1. MySQL Router
  2. MySQL Server 1 (writer)
  3. MySQL Server 2 (reader)
  4. MySQL Server 3 (reader)

 

 

 

# MySQL 설치

 

MySQL 서버 설치 방식은 바이너리 압축 해제 방식으로 진행합니다. 자세한 내용은 참조 인덱스의 hoing.io 블로그를 참조해주시면 됩니다.

 

 

아래 내용은 각 MySQL 서버 인스턴스에서 진행합니다. 총 3번 수행해야 합니다.

sudo groupadd mysql

sudo useradd -M -s /sbin/nologin -g mysql mysql

wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.3-linux-glibc2.28-x86_64.tar.xz 

sudo tar -xvf mysql-8.4.3-linux-glibc2.28-x86_64.tar.xz \
-C /usr/local/

sudo ln -s /usr/local/mysql-8.4.3-linux-glibc2.28-x86_64 \
/usr/local/mysql

sudo chown -R mysql:mysql /usr/local/mysql-8.4.3-linux-glibc2.28-x86_64

sudo echo "/usr/local/mysql/lib" | \
sudo tee -a /etc/ld.so.conf

sudo ldconfig

 

 

 

설정 파일을 작성합니다.

sudo nano /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
server-id=1
port=3306
bind-address=0.0.0.0
mysqlx=ON
mysqlx-port=33060
mysqlx-bind-address=0.0.0.0

basedir = /usr/local/mysql
datadir= /usr/local/mysql/data
tmpdir = /usr/local/mysql/data

socket=/tmp/mysql.sock
user=mysql
skip_name_resolve
event-scheduler=OFF
sysdate-is-now

binlog_expire_logs_seconds=604800
max_allowed_packet=1073741824
secure-file-priv=""

### timestamp
explicit_defaults_for_timestamp = TRUE

### log
# Error Log
log_error=/usr/local/mysql/log/mysqld.err
log_output=TABLE,FILE
general_log_file = /usr/local/mysql/log/general_query.log
general_log=0
slow-query-log=1
long_query_time = 1
slow_query_log_file = /usr/local/mysql/log/slow_query.log
log_queries_not_using_indexes = OFF
pid-file=/usr/local/mysql/tmp/mysqld.pid
log_timestamps=SYSTEM

### replication
log-bin=/usr/local/mysql/data/binlog
sync_binlog=1
log_slave_updates
binlog_expire_logs_seconds=604800
log_bin_trust_function_creators=ON

### group replication
report_host = EC2프라이빗IP1
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = "EC2프라이빗IP1:33061"
loose-group_replication_group_seeds = "EC2프라이빗IP1:33061,EC2프라이빗IP2:33061,EC2프라이빗IP3:33061"
loose-group_replication_bootstrap_group = OFF
loose-group_replication_ip_whitelist = "EC2프라이빗IP1:33061,EC2프라이빗IP2:33061,EC2프라이빗IP3:33061"

gtid-mode=ON
enforce_gtid_consistency=ON

###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=1

[mysqld_safe]
pid-file=/usr/local/mysql/tmp/mysqld.pid
open-files-limit = 16384

 

이 단계에서 필요한 설정 값을 모두 작성하지 않아도 나중에 클러스터를 구성할 때 부족한 설정이 뭐가 있는지 알려줍니다.

server-id, report_host, loose-group_replication_local_address 부분은 각 서버에 맞춰 다른 값으로 변경해야 합니다.

EC2프라이빗IP 는 각 인스턴스의 private ip를 보고 옮겨적으면 됩니다.

 

프라이빗 IPv4 주소

 

 

 

 

curl -O http://launchpadlibrarian.net/646633572/libaio1_0.3.113-4_amd64.deb

sudo dpkg -i libaio1_0.3.113-4_amd64.deb



cd /usr/local/mysql

sudo mkdir -p log tmp data

sudo chown mysql:mysql log tmp data




sudo apt-get install libaio-dev

sudo apt-get install libaio1 libaio-dev

sudo apt install libnss3-tools

curl -O http://launchpadlibrarian.net/648013231/libtinfo5_6.4-2_amd64.deb

sudo dpkg -i libtinfo5_6.4-2_amd64.deb


sudo rm -rf /usr/local/mysql/data/*

cd /usr/local/mysql/bin
sudo ./mysqld \
--defaults-file=/etc/my.cnf \
--initialize \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

sudo rm -rf ./*

/usr/local/mysql/bin/mysqld --version


echo "export MYSQL_HOME=/usr/local/mysql" >> ~/.bash_profile
echo "export PATH=\$PATH:\$MYSQL_HOME/bin" >> ~/.bash_profile
source ~/.bash_profile

sudo /usr/local/mysql/bin/mysqld_safe &
sudo /usr/local/mysql/support-files/mysql.server start


sudo cat $MYSQL_HOME/log/mysqld.err | grep password
sudo cat $MYSQL_HOME/log/mysqld.err

sudo truncate -s 0 $MYSQL_HOME/log/mysqld.err


mysql -uroot -p


ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY 'root';


CREATE USER 'shell'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'password';

GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SELECT, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, REPLICATION_APPLIER, CREATE USER, SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN, BACKUP_ADMIN, CLONE_ADMIN, EXECUTE ON *.* TO 'shell'@'%' with grant option;

GRANT CONNECTION_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, ROLE_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, 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, 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, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'shell'@'%' WITH GRANT OPTION;


------------------------------------------------------------------


user$ cd /usr/lib/systemd/system/
user$ sudo nano /usr/lib/systemd/system/mysqld.service

[Unit]
Description=MySQL Community Server
After=network.target
After=syslog.target
[Service]
Type=forking
LimitNOFILE=infinity
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
[Install]
WantedBy=multi-user.target

user$ sudo systemctl daemon-reload
user$ sudo systemctl enable mysqld.service
user$ sudo systemctl start mysqld
user$ sudo systemctl status mysql


cd /usr/lib/systemd/system/
sudo systemctl restart mysqld

-- mysqladmin -u root -p shutdown



https://kwt.co.kr/mysql-operator-%EB%A1%9C-%EC%9A%B4%EC%98%81%ED%95%98%EB%8A%94-innodb-cluster/
https://saramin.github.io/2021-09-28-mysql-group-replication/




==============================================================
wget https://dev.mysql.com/get/Downloads/shell/mysql-shell-8.4.3-linux-glibc2.28-x86-64bit.tar.gz 

sudo tar -xvf mysql-shell-8.4.3-linux-glibc2.28-x86-64bit.tar.gz \
-C /usr/local/

ln -s /usr/local/mysql-shell-8.4.3-linux-glibc2.28-x86-64bit mysql-shell

sudo nano /etc/hosts

##Mysql Setting
172.31.0.148 m1
172.31.1.178 m2
172.31.9.197 m3


172.31.8.142 mrouter



cd ~/mysql-shell/bin
sudo ./mysqlsh --log-level=DEBUG3

\js

dba.checkInstanceConfiguration('shell@127.0.0.1:3306')
dba.checkInstanceConfiguration('root@localhost:3306')
\exit


dba.configureInstance('shell@172.31.0.148:3306')
dba.configureInstance('root@127.0.0.1:3306')

dba.dropMetadataSchema()
\connect shell@172.31.0.148:3306
var cluster = dba.createCluster('zzangmin')

cluster.addInstance('shell@m2:3306')
cluster.addInstance('shell@m3:3306')

var cluster = dba.getCluster()
cluster.status()
cluster.status({'extended':1})
cluster.status({'extended':2})
cluster.options()
cluster.listRouters()

c.rejoinInstance('shell@172.31.0.148:3306', {password: 'password'});
c.rejoinInstance('shell@172.31.1.178:3306', {password: 'your_password'});
c.rejoinInstance('shell@172.31.9.197:3306', {password: 'your_password'});


SELECT * FROM performance_schema.replication_group_members;
select * from mysql_innodb_cluster_metadata.routers\G


cluster.removeRouterMetadata('ic-router-1::ic-router1')

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

SHOW STATUS LIKE '%group%';
SELECT * FROM performance_schema.replication_group_member_stats;

SELECT MEMBER_ROLE FROM performance_schema.replication_group_members WHERE MEMBER_ID = @@global.server_uuid;

===============================================================================
https://jhdatabase.tistory.com/entry/Mysql-InnoDB-cluster-part-4-Router%EC%84%A4%EC%B9%98


wget https://dev.mysql.com/get/Downloads/router/mysql-router-8.4.3-linux-glibc2.28-x86_64.tar.xz


sudo tar xvf mysql-router-8.4.3-linux-glibc2.28-x86_64.tar.xz -C /usr/local

cd /usr/local
sudo chown -R mysql:mysql mysql-router-8.4.3-linux-glibc2.28-x86_64
sudo mv mysql-router-8.4.3-linux-glibc2.28-x86_64 mysql-router 

echo "export PATH=\$PATH:/usr/local/mysql-router/bin" >> ~/.bash_profile
source ~/.bash_profile

sudo nano /etc/hosts
##Mysql Setting
172.31.0.148 m1
172.31.1.178 m2
172.31.9.197 m3


sudo /usr/local/mysql-router/bin/mysqlrouter --bootstrap shell@m1:3306 --directory /usr/local/mysql-router/myrouter --user=mysql --account mrouter --name zzangminrouter

sudo lsof -i :8443


# Reconfiguring MySQL Router 8.4.3 (MySQL Community - GPL) instance at '/mysql-router'...

- Fetching password for current account (mysql_router1_chfdimv) from keyring
- Creating account(s) (only those that are needed, if any)
- Using existing certificates from the '/mysql-router/data' directory
- 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 'zzangmin'

After this MySQL Router has been started with the generated configuration

    $ /usr/local/mysql-router/mysql-router-8.4.3-linux-glibc2.28-x86_64/bin/mysqlrouter -c /mysql-router/mysqlrouter.conf

InnoDB Cluster 'zzangmin' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447
- Read/Write Split Connections: localhost:6450

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449



sudo chmod 755 test
cd /usr/local/mysql-router/test
sudo chmod 755 start.sh
./start.sh


nano /etc/mysqlrouter.conf

[DEFAULT]
name=mrouter
user=mysql
logging_folder=/usr/local/mysql-router-8.0.23-linux-glibc2.12-x86_64/.../log
runtime_folder=/usr/local/mysql-router-8.0.23-linux-glibc2.12-x86_64/.../run
data_folder=/usr/local/mysql-router-8.0.23-linux-glibc2.12-x86_64/.../data
keyring_path=/usr/local/mysql-router-8.0.23-linux-glibc2.12-x86_64/.../data/keyring
master_key_path=/usr/local/mysql-router-8.0.23-linux-glibc2.12-x86_64/.../mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/usr/local/mysql-router-8.0.23-linux-glibc2.12-x86_64/.../data/state.json
client_ssl_cert=/usr/local/mysql-router-8.0.23-linux-glibc2.12-x86_64/.../data/router-cert.pem
client_ssl_key=/usr/local/mysql-router-8.0.23-linux-glibc2.12-x86_64/.../data/router-key.pem

 

 

 

 

 

 

<작성중>

 

 

 

 

# 참조

https://dev.mysql.com/doc/mysql-shell/8.4/en/mysql-innodb-cluster.html

https://dev.mysql.com/doc/refman/8.4/en/mysql-innodb-cluster-introduction.html

https://jhdatabase.tistory.com/entry/Mysql-InnoDB-cluster-part-4-Router%EC%84%A4%EC%B9%98

https://hoing.io/archives/12987

반응형

'데이터베이스' 카테고리의 다른 글

AWS RDS MySQL의 Handler 메트릭 모니터링 [PMM]  (2) 2024.11.12
ProxySQL과 route53으로 MySQL 무중단 마이그레이션 및 업그레이드  (0) 2024.11.11
데이터 정규화 모델링  (0) 2024.07.11
MySQL InnoDB 테이블 charset 변경시 주의사항  (0) 2024.07.03
mysql internal 및 도구 기록용  (0) 2024.06.29

관련글

댓글 0

티스토리툴바