반응형
Docker MySQL Replication(MHA) 구성(이중화)
OS 기본환경
| OS | Red Hat Enterprise Linux Server release 7.8 (Maipo) |
| CPU | 8 |
| Memory | 32GB |
| IP | eth0 :10.231.248.21 |
| Docker0 :172.17.0.1 |
Docker 환경
| Docker Name | Docker IP | Service Version | 비고 |
| db001 | 172.18.0.2 | percona:5.7.30 | Master DB |
| db002 | 172.18.0.3 | percona:5.7.30 | Slave DB |
| db003 | 172.18.0.4 | percona:5.7.30 | Slave DB |
| orchestrator | 172.18.0.5 | None | HA |
| proxysql | 172.18.0.6 | Server version: 5.5.30 (ProxySQL Admin Module) | Proxy SQL |
Docker 사전구성
1. Docker 컨테이너 설치
| # sudo yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo adding repo from: https://download.docker.com/linux/centos/docker-ce.repo grabbing file https://download.docker.com/linux/centos/docker-ce.repo to /etc/yum.repos.d/docker-ce.repo repo saved to /etc/yum.repos.d/docker-ce.repo # sudo yum install docker-ce Loaded plugins: search-disabled-repos Resolving Dependencies --> Running transaction check ---> Package docker-ce.x86_64 3:25.0.3-1.el7 will be installed --> Processing Dependency: container-selinux >= 2:2.74 for package: 3:docker-ce-25.0.3-1.el7.x86_64 --> Processing Dependency: containerd.io >= 1.6.24 for package: 3:docker-ce-25.0.3-1.el7.x86_64 --> Processing Dependency: docker-ce-cli for package: 3:docker-ce-25.0.3-1.el7.x86_64 --> Processing Dependency: docker-ce-rootless-extras for package: 3:docker-ce-25.0.3-1.el7.x86_64 --> Running transaction check ---> Package container-selinux.noarch 2:2.119.2-1.911c772.el7_8 will be installed ---> Package containerd.io.x86_64 0:1.6.28-3.1.el7 will be installed ---> Package docker-ce-cli.x86_64 1:25.0.3-1.el7 will be installed --> Processing Dependency: docker-buildx-plugin for package: 1:docker-ce-cli-25.0.3-1.el7.x86_64 --> Processing Dependency: docker-compose-plugin for package: 1:docker-ce-cli-25.0.3-1.el7.x86_64 ---> Package docker-ce-rootless-extras.x86_64 0:25.0.3-1.el7 will be installed --> Processing Dependency: fuse-overlayfs >= 0.7 for package: docker-ce-rootless-extras-25.0.3-1.el7.x86_64 --> Processing Dependency: slirp4netns >= 0.4 for package: docker-ce-rootless-extras-25.0.3-1.el7.x86_64 --> Running transaction check ---> Package docker-buildx-plugin.x86_64 0:0.12.1-1.el7 will be installed ---> Package docker-compose-plugin.x86_64 0:2.24.5-1.el7 will be installed ---> Package fuse-overlayfs.x86_64 0:0.7.2-6.el7_8 will be installed --> Processing Dependency: libfuse3.so.3(FUSE_3.0)(64bit) for package: fuse-overlayfs-0.7.2-6.el7_8.x86_64 --> Processing Dependency: libfuse3.so.3(FUSE_3.2)(64bit) for package: fuse-overlayfs-0.7.2-6.el7_8.x86_64 --> Processing Dependency: libfuse3.so.3()(64bit) for package: fuse-overlayfs-0.7.2-6.el7_8.x86_64 ---> Package slirp4netns.x86_64 0:0.4.3-4.el7_8 will be installed --> Running transaction check ---> Package fuse3-libs.x86_64 0:3.6.1-4.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ====================================================================================================================================================================================================================== Package Arch Version Repository Size ====================================================================================================================================================================================================================== Installing: docker-ce x86_64 3:25.0.3-1.el7 docker-ce-stable 26 M Installing for dependencies: container-selinux noarch 2:2.119.2-1.911c772.el7_8 rhel-7-server-extras-rpms 40 k containerd.io x86_64 1.6.28-3.1.el7 docker-ce-stable 35 M docker-buildx-plugin x86_64 0.12.1-1.el7 docker-ce-stable 13 M docker-ce-cli x86_64 1:25.0.3-1.el7 docker-ce-stable 14 M docker-ce-rootless-extras x86_64 25.0.3-1.el7 docker-ce-stable 9.4 M docker-compose-plugin x86_64 2.24.5-1.el7 docker-ce-stable 13 M fuse-overlayfs x86_64 0.7.2-6.el7_8 rhel-7-server-extras-rpms 55 k fuse3-libs x86_64 3.6.1-4.el7 rhel-7-server-extras-rpms 82 k slirp4netns x86_64 0.4.3-4.el7_8 rhel-7-server-extras-rpms 82 k Transaction Summary ====================================================================================================================================================================================================================== Install 1 Package (+9 Dependent packages) Total download size: 110 M Installed size: 388 M Is this ok [y/d/N]: Downloading packages: (1/10): container-selinux-2.119.2-1.911c772.el7_8.noarch.rpm | 40 kB 00:00:00 warning: /var/cache/yum/x86_64/7Server/docker-ce-stable/packages/docker-buildx-plugin-0.12.1-1.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID 621e9f35: NOKEY ] 6.2 MB/s | 23 MB 00:00:14 ETA Public key for docker-buildx-plugin-0.12.1-1.el7.x86_64.rpm is not installed (2/10): docker-buildx-plugin-0.12.1-1.el7.x86_64.rpm | 13 MB 00:00:02 (3/10): docker-ce-25.0.3-1.el7.x86_64.rpm | 26 MB 00:00:03 (4/10): containerd.io-1.6.28-3.1.el7.x86_64.rpm | 35 MB 00:00:07 (5/10): docker-ce-cli-25.0.3-1.el7.x86_64.rpm | 14 MB 00:00:02 (6/10): fuse-overlayfs-0.7.2-6.el7_8.x86_64.rpm | 55 kB 00:00:00 (7/10): docker-compose-plugin-2.24.5-1.el7.x86_64.rpm | 13 MB 00:00:01 (8/10): docker-ce-rootless-extras-25.0.3-1.el7.x86_64.rpm | 9.4 MB 00:00:02 (9/10): fuse3-libs-3.6.1-4.el7.x86_64.rpm | 82 kB 00:00:03 (10/10): slirp4netns-0.4.3-4.el7_8.x86_64.rpm | 82 kB 00:00:03 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 8.9 MB/s | 110 MB 00:00:12 Retrieving key from https://download.docker.com/linux/centos/gpg Importing GPG key 0x621E9F35: Userid : "Docker Release (CE rpm) <docker@docker.com>" Fingerprint: 060a 61c5 1b55 8a7f 742b 77aa c52f eb6b 621e 9f35 From : https://download.docker.com/linux/centos/gpg Is this ok [y/N]: y Running transaction check Running transaction test Transaction test succeeded Running transaction Warning: RPMDB altered outside of yum. Installing : 2:container-selinux-2.119.2-1.911c772.el7_8.noarch 1/10 setsebool: SELinux is disabled. Installing : containerd.io-1.6.28-3.1.el7.x86_64 2/10 Installing : slirp4netns-0.4.3-4.el7_8.x86_64 3/10 Installing : docker-compose-plugin-2.24.5-1.el7.x86_64 4/10 Installing : fuse3-libs-3.6.1-4.el7.x86_64 5/10 Installing : fuse-overlayfs-0.7.2-6.el7_8.x86_64 6/10 Installing : docker-buildx-plugin-0.12.1-1.el7.x86_64 7/10 Installing : 1:docker-ce-cli-25.0.3-1.el7.x86_64 8/10 Installing : docker-ce-rootless-extras-25.0.3-1.el7.x86_64 9/10 Installing : 3:docker-ce-25.0.3-1.el7.x86_64 10/10 Verifying : 3:docker-ce-25.0.3-1.el7.x86_64 1/10 Verifying : docker-buildx-plugin-0.12.1-1.el7.x86_64 2/10 Verifying : fuse3-libs-3.6.1-4.el7.x86_64 3/10 Verifying : docker-compose-plugin-2.24.5-1.el7.x86_64 4/10 Verifying : containerd.io-1.6.28-3.1.el7.x86_64 5/10 Verifying : fuse-overlayfs-0.7.2-6.el7_8.x86_64 6/10 Verifying : slirp4netns-0.4.3-4.el7_8.x86_64 7/10 Verifying : 2:container-selinux-2.119.2-1.911c772.el7_8.noarch 8/10 Verifying : 1:docker-ce-cli-25.0.3-1.el7.x86_64 9/10 Verifying : docker-ce-rootless-extras-25.0.3-1.el7.x86_64 10/10 Installed: docker-ce.x86_64 3:25.0.3-1.el7 Dependency Installed: container-selinux.noarch 2:2.119.2-1.911c772.el7_8 containerd.io.x86_64 0:1.6.28-3.1.el7 docker-buildx-plugin.x86_64 0:0.12.1-1.el7 docker-ce-cli.x86_64 1:25.0.3-1.el7 docker-ce-rootless-extras.x86_64 0:25.0.3-1.el7 docker-compose-plugin.x86_64 0:2.24.5-1.el7 fuse-overlayfs.x86_64 0:0.7.2-6.el7_8 fuse3-libs.x86_64 0:3.6.1-4.el7 slirp4netns.x86_64 0:0.4.3-4.el7_8 Complete! # docker --version Docker version 25.0.3, build 4debf41 # systemctl start docker # ps -ef | grep docker root 325 18874 0 15:56 pts/0 00:00:00 grep --color=auto docker root 32344 1 2 15:56 ? 00:00:00 /usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock |
1. Bridge 네트워크 구성
| Docker 컨테이너는 언제든 재시작시 해당 컨테이너의 IP정보가 변경될 수 있다. MySQL Replication 설정이나 HA설정 IP를 사용하게 되면 컨테이너 IP때문에 Replication 설정이 깨질 수 있다. 이러한 문제를 방지하기 위해 Bridge Network를 구성하고 net ALIAS를 사용하여 IP변경에도 문제가 발생하지 않도록 할 수 있게 구성한다. |
| # docker network ls NETWORK ID NAME DRIVER SCOPE 69f284c2ce30 bridge bridge local 22cda16b8dbe host host local 39017583680e none null loca |
| # docker network create --driver bridge mybridge # docker network ls NETWORK ID NAME DRIVER SCOPE 69f284c2ce30 bridge bridge local 22cda16b8dbe host host local b878e1fa6c9b mybridge bridge local 39017583680e none null local |
Docker MySQL 생성
Container Docker Data Stateful
CONTAINER가 삭제된 후 재 생성되면 DOCKER IMAGE 초기의 상태로 시작되게 된다.
이렇게 되면 데이터를 유지할 수 없게 되는데 이러한 문제를
Docker MySQL 데이터파일들을 Docker 외부로 설정하여 Docker 컨테이너가 삭제되더라도 데이터파일은 삭제되지 않게 하여 데이터를 보존할 수 있도록 설정한다.
Directory 생성
| # mkdir -p /db/db001/log /db/db001/conf /db/db001/data/ # mkdir -p /db/db002/log /db/db002/conf /db/db002/data/ # mkdir -p /db/db003/log /db/db003/conf /db/db003/data/ |
| # chmod 777 /db/db001/log /db/db001/conf /db/db001/data/ # chmod 777 /db/db002/log /db/db002/conf /db/db001/data/ # chmod 777 /db/db003/log /db/db003/conf /db/db001/data/ |
Docker MySQL Parameter Configuration
| # cd /db/db001/conf # vi my.cnf [mysqld] log_bin = mysql-bin binlog_format = ROW gtid_mode = ON enforce-gtid-consistency = true server-id = 100 log_slave_updates datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock #symbolic-link = 0 log-error = /var/log/mysql/mysqld.log pid-file = /var/run/mysqld/mysqld.pid report_host = db001 [mysqld_safe] pid-file = /var/run/mysqld/mysqld.pid socket = /var/lib/mysql/mysql.sock nice = 0 |
| # cd /db/db002/conf # vi my.cnf [mysqld] log_bin = mysql-bin binlog_format = ROW gtid_mode = ON enforce-gtid-consistency = true server-id = 200 log_slave_updates datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-link = 0 log-error = /var/log/mysql/mysqld.log pid-file = /var/run/mysqld/mysqld.pid report_host = db002 [mysqld_safe] pid-file = /var/run/mysqld/mysqld.pid socket = /var/lib/mysql/mysql.sock nice = 0 |
| # cd /db/db003/conf # vi my.cnf [mysqld] log_bin = mysql-bin binlog_format = ROW gtid_mode = ON enforce-gtid-consistency = true server-id = 300 log_slave_updates datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-link = 0 log-error = /var/log/mysql/mysqld.log pid-file = /var/run/mysqld/mysqld.pid report_host = db003 [mysqld_safe] pid-file = /var/run/mysqld/mysqld.pid socket = /var/lib/mysql/mysql.sock nice = 0 |
Docker MySQL 생성
| #cd /db/db001/conf # docker run -i -t --name db001 -h db001 -p 3306:3306 --net mybridge --net-alias=db001 -v /db/db001/data:/var/lib/mysql -v /db/db001/log:/var/log/mysql -v /db/db001/conf:/etc/percona-server.conf.d -e MYSQL_ROOT_PASSWORD="root" -d percona:5.7.30 |
| #cd /db/db002/conf # docker run -i -t --name db002 -h db002 -p 3307:3306 --net mybridge --net-alias=db002 -v /db/db002/data:/var/lib/mysql -v /db/db002/log:/var/log/mysql -v /db/db002/conf:/etc/percona-server.conf.d -e MYSQL_ROOT_PASSWORD="root" -d percona:5.7.30 |
| #cd /db/db003/conf # docker run -i -t --name db003 -h db003 -p 3308:3306 --net mybridge --net-alias=db003 -v /db/db003/data:/var/lib/mysql -v /db/db003/log:/var/log/mysql -v /db/db003/conf:/etc/percona-server.conf.d -e MYSQL_ROOT_PASSWORD="root" -d percona:5.7.30 |
| # docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES d7d07da14990 percona:5.7.30 "/docker-entrypoint.?? 26 seconds ago Up 11 seconds 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp db003 792068ff667f percona:5.7.30 "/docker-entrypoint.?? 33 seconds ago Up 32 seconds 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp db002 118ff8cca60d percona:5.7.30 "/docker-entrypoint.?? 40 seconds ago Up 39 seconds 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp db001 |
Docker MySQL Replication 설정
| DB001<Master> #docker exec -it -uroot db001 /bin/bash #mysql -uroot -p Password: root mysql> create user 'repl'@'%' IDENTIFIED BY 'repl'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; |
| Ping Test # ifconfig eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 172.17.0.3 netmask 255.255.0.0 broadcast 172.17.255.255 ether 02:42:ac:11:00:03 txqueuelen 0 (Ethernet) RX packets 8 bytes 656 (656.0 B) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 0 bytes 0 (0.0 B) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 # ping db001 PING db001 (172.18.0.2) 56(84) bytes of data. 64 bytes from db001 (172.18.0.2): icmp_seq=1 ttl=64 time=0.029 ms 64 bytes from db001 (172.18.0.2): icmp_seq=2 ttl=64 time=0.032 ms 64 bytes from db001 (172.18.0.2): icmp_seq=3 ttl=64 time=0.033 ms 64 bytes from db001 (172.18.0.2): icmp_seq=4 ttl=64 time=0.035 ms --- db001 ping statistics --- 7 packets transmitted, 7 received, 0% packet loss, time 6001ms rtt min/avg/max/mdev = 0.029/0.031/0.035/0.006 ms [root@db001 /]# ping db002 PING db002 (172.18.0.3) 56(84) bytes of data. 64 bytes from db002.mybridge (172.18.0.3): icmp_seq=1 ttl=64 time=0.078 ms 64 bytes from db002.mybridge (172.18.0.3): icmp_seq=2 ttl=64 time=0.056 ms --- db002 ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 1000ms rtt min/avg/max/mdev = 0.056/0.067/0.078/0.011 ms [root@db001 /]# ping db003 PING db003 (172.18.0.4) 56(84) bytes of data. 64 bytes from db003.mybridge (172.18.0.4): icmp_seq=1 ttl=64 time=0.074 ms 64 bytes from db003.mybridge (172.18.0.4): icmp_seq=2 ttl=64 time=0.057 ms 64 bytes from db003.mybridge (172.18.0.4): icmp_seq=3 ttl=64 time=0.060 ms |
| -- db002(Slave) #docker exec -it db002 /bin/bash #mysql -uroot -p mysql> reset master; mysql> CHANGE MASTER TO MASTER_HOST='db001', MASTER_USER='repl',MASTER_PASSWORD='repl', MASTER_AUTO_POSITION=1; mysql> START SLAVE; mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes |
| -- db003(Slave) #docker exec -it -uroot db003 /bin/bash #mysql -uroot -p mysql> reset master; mysql> CHANGE MASTER TO MASTER_HOST='db001', MASTER_USER='repl',MASTER_PASSWORD='repl', MASTER_AUTO_POSITION=1; mysql> START SLAVE; mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes |
Docker MySQL Replication 데이터 검증
Master(db001)테이블 생성 후 데이터 삽입 한 뒤 Slave(db002),(db003) 에서 조회 했을 때 테이블과 데이터가 일치 하는지 확인
| Master DB |
| #docker exec -it -uroot db001 /bin/bash mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.01 sec) mysql> USE testdb; -- 테이블 생성 CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, department VARCHAR(50), salary INT, hire_date DATE ); |
-- 데이터 삽입 INSERT INTO employees (name, department, salary, hire_date) VALUES ('Alice', 'HR', 50000, '2022-01-15'), ('Bob', 'IT', 60000, '2022-03-10'), ('Charlie', 'Finance', 55000, '2022-06-25'); |
| Slave DB(DB002) |
| #docker exec -it -uroot db002 /bin/bash mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.01 sec) mysql> USE testdb; |
| -- 데이터확인 mysql> select * from employees; +----+---------+------------+--------+------------+ | id | name | department | salary | hire_date | +----+---------+------------+--------+------------+ | 1 | Alice | HR | 50000 | 2022-01-15 | | 2 | Bob | IT | 60000 | 2022-03-10 | | 3 | Charlie | Finance | 55000 | 2022-06-25 | +----+---------+------------+--------+------------+ 3 rows in set (0.00 sec |
| Slave DB(DB003) |
| #docker exec -it -uroot db003 /bin/bash mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.01 sec) mysql> USE testdb; |
| -- 데이터확인 mysql> select * from employees; +----+---------+------------+--------+------------+ | id | name | department | salary | hire_date | +----+---------+------------+--------+------------+ | 1 | Alice | HR | 50000 | 2022-01-15 | | 2 | Bob | IT | 60000 | 2022-03-10 | | 3 | Charlie | Finance | 55000 | 2022-06-25 | +----+---------+------------+--------+------------+ 3 rows in set (0.00 sec |
Docker Orchestrator 구성
| Orchestrator Container 생성 |
| 복제 구성이 완료되었지만 Master(DB001) 서버가 문제가 발생하여 shutdown 될 경우 자동으로 Failover 해주는(Slave -> Master 승격) orchestrator(HA)구성합니다. |
| # docker run -i -t --name orchestrator -h orchestrator |
| Orchestrator 사용을 위한 유저 생성 |
| docker exec -it uroot db001 /bin/bash #mysql -uroot -p root:root mysql> create user orc_client_user@'172.%' identified by 'orc_client_password'; mysql> GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO orc_client_user@'172.%'; mysql> GRANT SELECT ON mysql.slave_master_info TO orc_client_user@'172.%'; |
| Docker 컨테이너 IP조회 |
| [root@ts-oracle1 conf]# docker inspect --format '{{.NetworkSettings.Networks.mybridge.IPAddress}}' orchestrator 172.18.0.5 [root@ts-oracle1 ~]# docker inspect --format '{{.NetworkSettings.Networks.mybridge.IPAddress}}' orchestrator db001 db002 db003 172.18.0.5 172.18.0.2 172.18.0.3 172.18.0.4 |
| MySQL 사용자(USER) 조회 |
| mysql> select user, host from user; +-----------------+-----------+ | user | host | +-----------------+-----------+ | repl | % | | root | % | | orc_client_user | 172.% | | mysql.sys | localhost | | root | localhost | +-----------------+-----------+ |
http://{Docker_Host_IP}:3000/web/clusters
http://172.18.0.5:3000/web/discover
Discovery 메뉴에 Docker Hostname(db001 과 3306 Port 등록)
| Node | 수행 | Docker |
| Master | DML(Update, Insert , Select) | db001 |
| Slave#1 | Select | db002 |
| Slave#2 | Select | db003 |
반응형
'MySQL > MySQL Installation' 카테고리의 다른 글
| Docker MySQL8.0 설치 (0) | 2024.07.04 |
|---|---|
| MySQL 5.7.27 Source Installation (MySQL 5.7.27 소스설치) (0) | 2022.01.18 |
댓글