21.11.15 TIL

Mysql Replication with Docker

필수 패키지 설치

sudo apt-get install apt-transport-https ca-certificates curl gnupg-agent software-properties-common

GPG Key 인증

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

도커 설치

sudo apt-get update && sudo apt-get install docker-ce docker-ce-cli containerd.io

ubuntu 유저에 도커 권한 부여

sudo usermod -aG docker ubuntu

Mysql 도커 이미지 다운로드

sudo docker pull mysql

이미지 확인

sudo docker images

디렉토리 링크 생성

mkdir /home/ubuntu/data

도커 컨테이너 생성

docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password --name mysql-container -v /home/ubuntu/data:/var/lib/mysql mysql --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

MySQL 컨테이너 접근하기

docker exec -it mysql-container bash
root@1f0e488d19f5:/# 
root@1f0e488d19f5:/# 
root@1f0e488d19f5:/# 
root@1f0e488d19f5:/# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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 '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

DB 생성

create database repl_db default character set utf8;

계정 생성

create user user1@'%' identified by 'test123';

권한 부여

grant all privileges on repl_db.* to user1@'%';

Replication 계정 생성

create user repl_user@'%' identified by 'test456';ALTER USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'test456';

Replication 권한 부여

grant replication slave on *.* to 'repl_user'@'%';

docker - vim 설치

apt-get updateapt-get install vim

my.cnf 수정

vim /etc/mysql/my.cnf

[mysqld]log-bin=mysql-binserver-id=1

컨테이너 재시작

docker restart mysql-container

마스터 정보 확인

mysql> SHOW MASTER STATUS\G  *************************** 1. row ***************************             File: mysql-bin.000002         Position: 448     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

슬레이브 Mysql 컨테이너 생성

docker run -d -p 3307:3307 -e MYSQL_ROOT_PASSWORD=password --name mysql-container-slave --link mysql-container -v /home/ubuntu/slave:/var/lib/mysql mysql --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

DB 생성

create database repl_db default character set utf8;

계정 생성

create user user1@'%' identified by 'test123';

권한 부여

grant all privileges on repl_db.* to user1@'%';

슬레이브 Mysql my.cnf 수정

[mysqld]server-id=2replicate-do-db='repl_db'

컨테이너 재시작

docker restart mysql-container-slave

마스터 데이터 덤프

mysqldump -u user1 -p repl_db > dump_repl_db.sql 

슬레이브에서 복원

mysql -u user1 -p repl_db < /var/lib/mysql/dump_repl_db.sql 

연결

CHANGE MASTER TO MASTER_HOST='mysql-container', MASTER_USER='repl_user', MASTER_PASSWORD='test456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=448;START SLAVE;  

Master 서버 상태 확인 (repl_user)

show processlist\G;*************************** 3. row ***************************     Id: 13   User: repl_user   Host: 172.17.0.3:39996     db: NULLCommand: Binlog Dump   Time: 122  State: Source has sent all binlog to replica; waiting for more updates   Info: NULL3 rows in set (0.00 sec)

Slave 서버 상태 확인

*************************** 3. row ***************************     Id: 7   User: system user   Host:      db: NULLCommand: Query   Time: 149  State: Replica has read all relay log; waiting for more updates   Info: NULL

Slave 상태 확인

show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for source to send event                  Master_Host: mysql-container                  Master_User: repl_user                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 448               Relay_Log_File: 136e116927de-relay-bin.000003                Relay_Log_Pos: 324        Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: repl_db          Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 448              Relay_Log_Space: 719              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1                  Master_UUID: 2e898f27-46bd-11ec-bd6e-0242ac110002             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 0         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version:        Master_public_key_path:         Get_master_public_key: 0            Network_Namespace: 1 row in set, 1 warning (0.00 sec)