4.MySqlLogFormat/1.Statement-basedLog/README.md
실습 시작..
더보기
user@DESKTOP-FSLB27A MINGW64 ~
$ docker rm -f mysql
mysql
user@DESKTOP-FSLB27A MINGW64 ~
$ docker run -d --rm --name mysql --hostname=mysql -e MYSQL_ROOT_PASSWORD=mypass mysql:5.7 --server-id=1 --log-bin='mysql-bin-1.log' --binlog_format='STATEMENT'
41ffc01375fd8e3d36419cd1274b784ff2682649e417d75fad323e8b3b58e89d
user@DESKTOP-FSLB27A MINGW64 ~
$ docker exec -it mysql /usr/bin/mysql -uroot -pmypass -e "show variables like '%binlog_format%';"
## 이 명령어는 mysql 올라올때까지 좀 기다려야됨.
PS C:\Users\user> docker exec -it mysql /usr/bin/mysql -uroot -pmypass -e "show variables like '%binlog_format%';" mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug mysql
Learn more at https://docs.docker.com/go/debug-cli/
4.MySqlLogFormat/2.Row-basedLog
Row-based binlog 실습
은.. 귀찮다 그냥 두가지 차이점 정리하겠다. ㅋ_ㅋ
Statement는 로그에 SQL 명령어로 저장된다. 로그가 굉장히 가볍겠지. 다만, slave와 master 간의 이미 데이터 차이가 발생한 상태였다면, 같은 명령어라도 수행한 결과가 달라질 수 있겠지. 시작이 이미 다르니까.
Row-based는 각 로우의 실제 변경한 값이 로그에 박힌다. 로그가 오지게 쌓이겠지. 굉장히 무겁다. 다만, 복제(Replication)를 위해 사용하는 거였다면? Row-based를 써야만 정확한 값이 복제가 될 거다!
..지만? 둘의 장점 합친 Mixed 써라~
비대칭형 암호화 알고리즘
puttygen으로 서버간 통신에 사용할 키 생성하기
그럼 접속할 서버를 만들자
## 도커 기반의 ssh 연동하는 명령어 스크립트
# Container 생성
```
docker network create sshnet
x=$(docker ps -a -q |grep s1);[ ! ${#x} ]&& docker rm -f s1
x=$(docker ps -a -q |grep s2);[ ! ${#x} ]&& docker rm -f s2
docker rm -f s1 s2
docker run -d --rm --name s1 --net=sshnet rastasheep/ubuntu-sshd
docker run -d --rm --name s2 --net=sshnet rastasheep/ubuntu-sshd
#docker network inspect sshnet
docker exec -it s2 bash
export PS1=root@s2\
echo "PS1=root@s2\ " >> /root/.bashrc
mkdir ~/.ssh;chown root ~/.ssh;chmod 700 ~/.ssh
exit
docker exec -it s1 bash
export PS1=root@s1\
echo "PS1=root@s2\ " >> /root/.bashrc
ssh-keygen -f ~/.ssh/id_rsa -N ''
exit
docker cp s1:/root/.ssh/id_rsa.pub .
docker cp ./id_rsa.pub s2:/root/.ssh/authorized_keys
docker exec -it s2 bash
chown root ~/.ssh/authorized_keys
exit
docker exec -it s1 bash
ssh s2 ls
```
저걸 보고 실습 해보자(결과적으로 putty로 접속되면 성공임)
## network 생성
PS C:\Users\user> docker network create sshnet
cf3e7f041795a3c1da37c5e00db6cdf0ef450406ce877ba39116755326633328
## 서버1 생성
PS C:\Users\user> docker run -d --rm --name s1 --net=sshnet rastasheep/ubuntu-sshd
Unable to find image 'rastasheep/ubuntu-sshd:latest' locally
latest: Pulling from rastasheep/ubuntu-sshd
a48c500ed24e: Pull complete
1e1de00ff7e1: Pull complete
0330ca45a200: Pull complete
471db38bcfbf: Pull complete
0b4aba487617: Pull complete
b42109ad2a3d: Pull complete
dde737735b18: Pull complete
d836c14266f7: Pull complete
5ed86b5d4a15: Pull complete
5273c120f396: Pull complete
b0299e0551df: Pull complete
0ae38e059780: Pull complete
ca79c723275f: Pull complete
Digest: sha256:1a4010f95f6b3292f95fb26e442f85885d523f9a0bb82027b718df62fdd0d9e9
Status: Downloaded newer image for rastasheep/ubuntu-sshd:latest
df6bdeb81c4adf91695f7bacd56859c88510f4635baa5f58977962e22699d2e2
## 서버2 생성
PS C:\Users\user> docker run -d --rm --name s2 --net=sshnet rastasheep/ubuntu-sshd
b512327a329ebfb94d3cf65772b70243d994eab67f3d3b09b944a7d72af47c3f
PS C:\Users\user>PS C:\Users\user> docker exec -it s2 bash
>> export PS1=root@s2\
>> echo "PS1=root@s2\ " >> /root/.bashrc
>> mkdir ~/.ssh;chown root ~/.ssh;chmod 700 ~/.ssh
>> exit
## 환경변수 체크
root@b512327a329e:/# echo $PS1
\[\e]0;\u@\h: \w\a\]${debian_chroot:+($debian_chroot)}\u@\h:\w\$
## 굳
root@b512327a329e:/#
## s1에 접속. 아깐 s2
PS C:\Users\user> docker exec -it s1 bash
>> export PS1=root@s1\
>> echo "PS1=root@s2\ " >> /root/.bashrc
>> ssh-keygen -f ~/.ssh/id_rsa -N ''
>> exit
root@df6bdeb81c4a:/# echo $PS1
\[\e]0;\u@\h: \w\a\]${debian_chroot:+($debian_chroot)}\u@\h:\w\$
## 굳
root@df6bdeb81c4a:/#
root@df6bdeb81c4a:~# ssh-keygen -f ~/.ssh/id_rsa -N ''
Generating public/private rsa key pair.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:Bqw7KUGFLj+vNXXkryOsFfYDDvlv58OC7/l/zRH6duU root@df6bdeb81c4a
The key's randomart image is:
+---[RSA 2048]----+
| .. |
| .. . |
| .. o . |
|... o + . |
| o. + = S . .|
| o. O * . . ..|
| .o*.+.o.. .+o|
| o.++.+++ .oE|
| .... =**oo... .|
+----[SHA256]-----+
root@df6bdeb81c4a:~# cd ~/.ssh/
root@df6bdeb81c4a:~/.ssh# ls
id_rsa id_rsa.pub
## 키생성 완료ㅕ
5.MySQLHighAvailabilityTech/1.HA_Proxy_Maxscale/1.HaProxy/HaProxy.md
## 네트워크 생성
PS C:\Users\user> docker network create replicanet
## nginx 2개 생성
PS C:\Users\user> docker run -d --rm --name n1 --net=replicanet -p 8081:80 nginx
Unable to find image 'nginx:latest' locally
latest: Pulling from library/nginx
09f376ebb190: Pull complete
5529e0792248: Pull complete
9b3addd3eb3d: Pull complete
57910a8c4316: Pull complete
7b5f78f21449: Pull complete
b7923aa4e8a6: Pull complete
785625911f12: Pull complete
Digest: sha256:0f04e4f646a3f14bf31d8bc8d885b6c951fdcf42589d06845f64d18aec6a3c4d
Status: Downloaded newer image for nginx:latest
140d868922c6d2c069ebff79a99ab4f56eb994596505a074b22e169fd63273fa
PS C:\Users\user> docker run -d --rm --name n2 --net=replicanet -p 8082:80 nginx
0d7143e8c66ba8a2f29b893e9bbf1bbbc7e101cb261e6bb2bfdb25ba116f317e
PS C:\Users\user>생성 확인
Ha Proxy를 설치하자
## 보통 centos는 빅데이터나 네트웤단에서 많이 쓴단다.
## 보통 redhat linux 호환이기 때문에, 쓰다가 중요한 서버는 rha로 변경한대
## 그럼 redhat에 뭔가 작업 요청이 가능하니까
PS C:\Users\user> docker run -it --name haproxy2 -p 20080:20080 --net=replicanet centos:centos7 /bin/bash
Unable to find image 'centos:centos7' locally
centos7: Pulling from library/centos
2d473b07cdd5: Pull complete
Digest: sha256:be65f488b7764ad3638f236b7b515b3678369a5124c47b8d32916d6487418ea4
Status: Downloaded newer image for centos:centos7
[root@750d538ec16f /]#
## 설치하자
[root@750d538ec16f /]# yum -y install haproxy
Loaded plugins: fastestmirror, ovl
Determining fastest mirrors
* base: mirror.anigil.com
* extras: mirror.anigil.com
* updates: mirror.anigil.com
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/4): base/7/x86_64/group_gz | 153 kB 00:00:00
(2/4): extras/7/x86_64/primary_db | 253 kB 00:00:00
(3/4): base/7/x86_64/primary_db | 6.1 MB 00:00:00
(4/4): updates/7/x86_64/primary_db | 27 MB 00:00:01
Resolving Dependencies
--> Running transaction check
---> Package haproxy.x86_64 0:1.5.18-9.el7_9.1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
========================================================================================================================
Package Arch Version Repository Size
========================================================================================================================
Installing:
haproxy x86_64 1.5.18-9.el7_9.1 updates 835 k
Transaction Summary
========================================================================================================================
Install 1 Package
Total download size: 835 k
Installed size: 2.6 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/updates/packages/haproxy-1.5.18-9.el7_9.1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for haproxy-1.5.18-9.el7_9.1.x86_64.rpm is not installed
haproxy-1.5.18-9.el7_9.1.x86_64.rpm | 835 kB 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Importing GPG key 0xF4A80EB5:
Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
Package : centos-release-7-9.2009.0.el7.centos.x86_64 (@CentOS)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : haproxy-1.5.18-9.el7_9.1.x86_64 1/1
Verifying : haproxy-1.5.18-9.el7_9.1.x86_64 1/1
Installed:
haproxy.x86_64 0:1.5.18-9.el7_9.1
Complete!
[root@750d538ec16f /]#
## 아래 열어서 수정했다.
vi /etc/haproxy/haproxy.cfg
# 수정할
# mode tcp
# option tcplog
# 추가할
listen mysql_cluster 0.0.0.0:20080
mode tcp
balance roundrobin
#option mysql-check user haproxy
server node1 n1:80 weight 2 check
server node2 n2:80 weight 1 check
## 적용된 파일 기준 haproxy 실행
haproxy -f /etc/haproxy/haproxy.cfg -db -V
PS C:\Users\user> docker exec -it n1 bash
root@140d868922c6:/# echo 1 > /usr/share/nginx/html/index.html
root@140d868922c6:/# exit
exit
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug n1
Learn more at https://docs.docker.com/go/debug-cli/
PS C:\Users\user> docker exec -it n2 bash
root@0d7143e8c66b:/# echo 2 > /usr/share/nginx/html/index.html
root@0d7143e8c66b:/#각각 nginx의 index.html을 1, 2로 잘보이게 바꾸고 확인해보자
5.MySQLHighAvailabilityTech/1.HA_Proxy_Maxscale/1.HaProxy/HaProxy.md
MySQL DB준비
user@DESKTOP-FSLB27A MINGW64 ~
$ docker run -d --rm --name=master --net=replicanet --hostname=master \
-e MYSQL_ROOT_PASSWORD=mypass \
mysql:5.7 \
--server-id=1 \
--log-bin='mysql-bin-1.log'
6b18f8299856c125f8aeb5a34999c508e43ab1e4c57437f8ddd94980028a9485
user@DESKTOP-FSLB27A MINGW64 ~
$ docker run -d --rm --name=slave --net=replicanet --hostname=slave \
-e MYSQL_ROOT_PASSWORD=mypass \
mysql:5.7 \
--server-id=2
b2b7f600f7eb5a5a1ef03aa1cdf76af4bbc61778fd45e1e666a8aeaecd598fd4
## 체크
# Configure Master
PS C:\Users\user> docker exec -it master mysql -uroot -pmypass -e "CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';" -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';" -e "SHOW MASTER STATUS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| mysql-bin-1.000003 | 595 | | | |
+--------------------+----------+--------------+------------------+-------------------+
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug master
Learn more at https://docs.docker.com/go/debug-cli/
PS C:\Users\user>
# Configure Slave
PS C:\Users\user> docker exec -it slave mysql -uroot -pmypass -e "CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin-1.000003';"
mysql: [Warning] Using a password on the command line interface can be insecure.
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug slave
Learn more at https://docs.docker.com/go/debug-cli/
PS C:\Users\user>
# Start Slave
PS C:\Users\user> docker exec -it slave mysql -uroot -pmypass -e "START SLAVE;"
mysql: [Warning] Using a password on the command line interface can be insecure.
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug slave
Learn more at https://docs.docker.com/go/debug-cli/
PS C:\Users\user>상태 체크
PS C:\Users\user> docker exec -it slave mysql -uroot -pmypass -e "SHOW SLAVE STATUS\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-1.000003
Read_Master_Log_Pos: 595
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 812
Relay_Master_Log_File: mysql-bin-1.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_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: 595
Relay_Log_Space: 1019
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: 0
Master_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: c4e79e46-22f4-11ef-95dd-0242ac140002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug slave
Learn more at https://docs.docker.com/go/debug-cli/
PS C:\Users\user> docker exec -it master mysql -uroot -pmypass -e "CREATE DATABASE TEST; SHOW DATABASES;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST |
| mysql |
| performance_schema |
| sys |
+--------------------+
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug master
Learn more at https://docs.docker.com/go/debug-cli/
PS C:\Users\user> docker exec -it slave mysql -uroot -pmypass -e "SHOW DATABASES;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST |
| mysql |
| performance_schema |
| sys |
+--------------------+
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug slave
Learn more at https://docs.docker.com/go/debug-cli/
PS C:\Users\user>ip 확인
docker exec -it master yum install -y net-tools
PS C:\Users\user> docker exec -it master bash
bash-4.2# ifconfig | grep inet
inet 172.20.0.2 netmask 255.255.0.0 broadcast 172.20.255.255
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
bash-4.2# exit
## 172.20.0.2 이군
##접속 테스트
PS C:\Users\user> docker exec -it slave mysql -h 172.20.0.2 -u root -pmypass --port 3306 -e "SHOW DATABASES;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST |
| mysql |
| performance_schema |
| sys |
+--------------------+
PS C:\Users\user>
## 완료HaProxy Container 생성, 설정 및 구동
PS C:\Users\user> docker run -it --name haproxy -p 20000:20000 --net=replicanet centos:centos7
[root@edbedf6bce86 /]# yum -y install haproxy
Loaded plugins: fastestmirror, ovl
Determining fastest mirrors
* base: mirror.anigil.com
* extras: mirror.anigil.com
* updates: mirror.anigil.com
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/4): base/7/x86_64/group_gz | 153 kB 00:00:00
(2/4): extras/7/x86_64/primary_db | 253 kB 00:00:00
(3/4): base/7/x86_64/primary_db | 6.1 MB 00:00:01
(4/4): updates/7/x86_64/primary_db | 27 MB 00:00:01
Resolving Dependencies
--> Running transaction check
---> Package haproxy.x86_64 0:1.5.18-9.el7_9.1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=========================================================================================================================
Package Arch Version Repository Size
=========================================================================================================================
Installing:
haproxy x86_64 1.5.18-9.el7_9.1 updates 835 k
Transaction Summary
=========================================================================================================================
Install 1 Package
Total download size: 835 k
Installed size: 2.6 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/updates/packages/haproxy-1.5.18-9.el7_9.1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for haproxy-1.5.18-9.el7_9.1.x86_64.rpm is not installed
haproxy-1.5.18-9.el7_9.1.x86_64.rpm | 835 kB 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Importing GPG key 0xF4A80EB5:
Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
Package : centos-release-7-9.2009.0.el7.centos.x86_64 (@CentOS)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : haproxy-1.5.18-9.el7_9.1.x86_64 1/1
Verifying : haproxy-1.5.18-9.el7_9.1.x86_64 1/1
Installed:
haproxy.x86_64 0:1.5.18-9.el7_9.1
Complete!
[root@edbedf6bce86 /]#HaProxy.cfg 파일 수정
## 수정 스크립트!
vi /etc/haproxy/haproxy.cfg
# 수정할
mode tcp
option tcplog
# 추가할
listen mysql_cluster 0.0.0.0:20000
mode tcp
balance roundrobin
#option mysql-check user haproxy
server node1 master:3306 weight 2 check
server node2 slave:3306 weight 1 check
## haproxy 실행(상태 유지)
[root@edbedf6bce86 /]# haproxy -f /etc/haproxy/haproxy.cfg -db -V
[WARNING] 156/044910 (68) : config : 'option forwardfor' ignored for proxy 'mysql_cluster' as it requires HTTP mode.
[WARNING] 156/044910 (68) : config : 'option forwardfor' ignored for frontend 'main' as it requires HTTP mode.
[WARNING] 156/044910 (68) : config : 'option forwardfor' ignored for backend 'static' as it requires HTTP mode.
[WARNING] 156/044910 (68) : config : 'option forwardfor' ignored for backend 'app' as it requires HTTP mode.
Available polling systems :
epoll : pref=300, test result OK
poll : pref=200, test result OK
select : pref=150, test result FAILED
Total: 3 (2 usable), will use epoll.
Using epoll() as the polling mechanism.
[WARNING] 156/044910 (68) : Server static/static is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
[ALERT] 156/044910 (68) : backend 'static' has no server available!
[WARNING] 156/044910 (68) : Server app/app1 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 3 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
[WARNING] 156/044911 (68) : Server app/app2 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 2 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
[WARNING] 156/044911 (68) : Server app/app3 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
[WARNING] 156/044911 (68) : Server app/app4 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
[ALERT] 156/044911 (68) : backend 'app' has no server available!작동 확인 ( 1번에 붙을때 있고 2번에 붙을 때 있고 잘 작동 하고 있군)
PS C:\Users\user> docker exec -it slave mysql -h haproxy -u root -pmypass --port 20000 -e "show variables like 'server_id';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
PS C:\Users\user> docker exec -it slave mysql -h haproxy -u root -pmypass --port 20000 -e "show variables like 'server_id';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
PS C:\Users\user>
MaxScale 실습, MySQL DB 준비
docker network create replicanet
docker rm -f master
docker rm -f slave
# Create containers
docker run -d --rm --name=master --net=replicanet --hostname=master \
-e MYSQL_ROOT_PASSWORD=mypass \
mysql:5.7 \
--server-id=1 \
--log-bin='mysql-bin-1.log'
docker run -d --rm --name=slave --net=replicanet --hostname=slave \
-e MYSQL_ROOT_PASSWORD=mypass \
mysql:5.7 \
--server-id=2
sleep 10
# Configure Master
docker exec -it master mysql -uroot -pmypass \
-e "CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';" \
-e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';" \
-e "SHOW MASTER STATUS;"
# Configure Slave
docker exec -it slave mysql -uroot -pmypass \
-e "CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', \
MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin-1.000003';"
# Start Slave
docker exec -it slave mysql -uroot -pmypass -e "START SLAVE;"
# Test
docker exec -it slave mysql -uroot -pmypass -e "SHOW SLAVE STATUS\G"
docker exec -it master mysql -uroot -pmypass -e "CREATE DATABASE TEST; SHOW DATABASES;"
docker exec -it slave mysql -uroot -pmypass -e "SHOW DATABASES;"Maxscale User 생성
docker exec -it master mysql -uroot -pmypass -e "
CREATE USER 'maxscale'@'%' IDENTIFIED BY '1';
GRANT SELECT ON mysql.user TO 'maxscale'@'%';
GRANT SELECT ON mysql.db TO 'maxscale'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
GRANT REPLICATION CLIENT,REPLICATION SLAVE,SUPER,RELOAD on *.* to 'maxscale'@'%';
flush privileges;
"Maxscale Container 생성, 설정 및 구동
하 뭔가 되게 많았는데 내 컴이 너무 똥컴이라 오래걸려서 필기를 못했다.
결과는 일단 아래와 같다
5.MySQLHighAvailabilityTech/1.HA_Proxy_Maxscale/2.Maxscale/Maxscale.md 참고!
이렇게 한눈에도 볼 수 있네
그럼 저 옆에 slave를 하나 더 달아보자.
user@DESKTOP-FSLB27A MINGW64 ~
$ docker run -d --rm --name=slave2 --net=replicanet --hostname=slave2 \
-e MYSQL_ROOT_PASSWORD=mypass \
mysql:5.7 \
--server-id=3
fa33a34e94b59938b1c46ae656f39d356318e7bc90234de903121f8895c9f2e3
user@DESKTOP-FSLB27A MINGW64 ~
$ docker exec -it slave2 mysql -uroot -pmypass -e "CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', \
MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin-1.000003';"
mysql: [Warning] Using a password on the command line interface can be insecure.
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug slave2
Learn more at https://docs.docker.com/go/debug-cli/
user@DESKTOP-FSLB27A MINGW64 ~
$ docker run -d --rm --name=slave2 --net=replicanet --hostname=slave2 \
-e MYSQL_ROOT_PASSWORD=mypass \
mysql:5.7 \
--server-id=3
fa33a34e94b59938b1c46ae656f39d356318e7bc90234de903121f8895c9f2e3
user@DESKTOP-FSLB27A MINGW64 ~
$ docker exec -it slave2 mysql -uroot -pmypass -e "CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', \
MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin-1.000003';"
mysql: [Warning] Using a password on the command line interface can be insecure.
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug slave2
Learn more at https://docs.docker.com/go/debug-cli/
user@DESKTOP-FSLB27A MINGW64 ~
$ docker exec -it slave2 mysql -uroot -pmypass -e "START SLAVE;"
mysql: [Warning] Using a password on the command line interface can be insecure.
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug slave2
Learn more at https://docs.docker.com/go/debug-cli/
user@DESKTOP-FSLB27A MINGW64 ~
$ docker exec -it slave2 mysql -uroot -pmypass -e "SHOW DATABASES;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST |
| mysql |
| performance_schema |
| sys |
+--------------------+
What's next?
Try Docker Debug for seamless, persistent debugging tools in any container or image → docker debug slave2
Learn more at https://docs.docker.com/go/debug-cli/
## 체크!
user@DESKTOP-FSLB27A MINGW64 ~
$ docker exec -it mxs bash
[root@a52489eb45e8 /]# cat /etc/maxscale.cnf
[maxscale]
admin_secure_gui=false
threads=1
admin_host=0.0.0.0
[server1]
type=server
address=master
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=slave
port=3306
protocol=MariaDBBackend
[MariaDB-Monitor]
replication_password=1
replication_user=maxscale
module=mariadbmon
password=1
servers=server1,server2,server3
type=monitor
user=maxscale
[Splitter-Service]
password=1
router=readwritesplit
type=service
user=maxscale
targets=server1,server2,server3
[Splitter-Listener]
port=3306
service=Splitter-Service
type=listener
[server3]
type=server
address=slave2
port=3306
protocol=MariaDBBackend
[root@a52489eb45e8 /]#
## 하고 maxscale 재부팅 확인 하고 보면 끝
항상 다음 것 하기 전에 하는 청소~
user@DESKTOP-FSLB27A MINGW64 ~
$ docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS
NAMES
fa33a34e94b5 mysql:5.7 "docker-entrypoint.s…" 29 minutes ago Up 29 minutes 3306/tcp, 33060/tcp
slave2
a52489eb45e8 mariadb/maxscale:latest "/usr/bin/tini -- do…" 58 minutes ago Up 58 minutes 0.0.0.0:3307->3307/tcp,
3306/tcp, 0.0.0.0:8989->8989/tcp mxs
4dd9a2506d8e mysql:5.7 "docker-entrypoint.s…" About an hour ago Up About an hour 3306/tcp, 33060/tcp
slave
bb871e8f4a9a mysql:5.7 "docker-entrypoint.s…" About an hour ago Up About an hour 3306/tcp, 33060/tcp
master
user@DESKTOP-FSLB27A MINGW64 ~
$ docker rm -f master slave slave2 mxs
master
slave
slave2
mxs
user@DESKTOP-FSLB27A MINGW64 ~
$ docker network ls
NETWORK ID NAME DRIVER SCOPE
db7f0a0e36ec bridge bridge local
42fdc7890ac2 host host local
73f1b70b6934 net1 bridge local
830f1a6b86e0 none null local
a91dc988846c replicanet bridge local
5adfe894b1cc replnet bridge local
cf3e7f041795 sshnet bridge local
user@DESKTOP-FSLB27A MINGW64 ~
$ docker network rm sshnet net1 replnet
sshnet
net1
replnet
user@DESKTOP-FSLB27A MINGW64 ~
$ docker volume prune
WARNING! This will remove anonymous local volumes not used by at least one container.
Are you sure you want to continue? [y/N] y
Deleted Volumes:
6537fd57277f4f9da38cc371dd710140d42a501aa828301ec66957c8f57d56de
3133718dab90d147b2f55df2e1e8f89383addd657b0305e0c0b382280a5cd4ff
42f31e1ad496821c5670677c5ef693b603233837658b5d28622573111753434d
7231269a2fc24b88de9b44c32160ff0f1b3483d2b2cd78ee2010d0dfcf892d4e
c3c9cdee8ba093fca9733376d3f66cc11a47a76d096a087f1cf0de44b7f13d2d
22cb90e68c2b77fd366f210b6ff9bd128cd0b7141ec9b57c2c35e60ffb91bf74
ad6e6358394143fc331e2b17a68b16cdfaf8794168d0fef7f3ba1123e40c5db0
9ed30410d53d6715ec6cb931fd7d51379ffdab4e29801dece491952d99d40d10
8ae63c29a8c90d33b329c30703b31ce6ed30aec772f902089cbd05b8c398e8dc
4a0683bbfd0851202eb99cef8825a8320986b16df43aec403e58fa6fb991b2cc
5b40092ac26b8f542290512ffca75b3d276d75e57d347617a9871f67496a170b
85759d921accd2dc1502dcbe288e0fec68d61fb2a0788ec2205b6b14e5e756c1
75445084a05257bc95c8e80be0346c5901bf2f80717bb6d230438452e46ed656
4e7cacd394044ecd55d68e036a627c72efdfd516ec6a490c8b11d62ad2a76a74
03745d0207a665f0cc4f843d9ec8c9a0d1e62a4b39d44aea9b0a9c4235273ee4
1983337324b7fe969c684e32122d8bd87b650b5f3761881a4c0e117cc7a893e2
9a4f59fa481bcd41ed8a6fe83e1cf06c57f1298e7d85a8583295a0eb5fa3eb91
9b360da027fce4ec8f88ee23f9c01bd48b5069905c270c2276d87906b4546641
3de2b5acebd22b070a3982262be2af48471c8ca0e8cc235c10f919890a84b67e
ecd27c9e06834564da7fe2a0eaf6c5a6ce2be52db991f8be53e7d34bcf65ab6d
937ee900f0381ab4bc0c29603f7e938611e1d9e9a18e99c41dba05be53587b4b
Total reclaimed space: 3.425GB
user@DESKTOP-FSLB27A MINGW64 ~
$
MySQL HA(MHA)
MHA에 의해서 장애 발생시 자동 FailOver(Master 승격)를 지원하고 Slave 중 가장 최신의 Slave DB를 Master DB로 승격시켜 고가 용성을 유지 시킴
MMM(Multi-master Replication Manager)
하나의 마스터가 죽어도 다른 마스터 쓰는 형태겠군.
뭐가 문제일려나..
흠... 참고..
이걸 보완하기 위해 나온 것
MySQL Group Replication
~~~ 길었지만 이제 핵심을 왔다. 최종 버전!
Galera Cluster!!!!
Master Slave 헬스 체크 명령어!
• show slave status; <-- execute on slave
• show master status; <-- execute on master
SRE : Site Reliable Engineering (Cloud 기술인데.. 안죽게 만들어서 비싸게 서비스한다래)
수업은 도커로 했지만 현업에선 Ansible로 프로비저닝 한다.......
mysql을 도커로 올리지 않고 물리적인 서버 하나에 컨테이너만 올리는 형태로 한다고 한다.
'DBA공부 > MySQL' 카테고리의 다른 글
| [MySQL/MariaDB HA (2)] MySQL/MariaDB를 사용한 고가용성 구성 HA 2일차 (2) | 2024.06.04 |
|---|---|
| [MySQL/MariaDB HA (1)] 이중화를 이용한 고가용성 시스템 구축 시작 및 docker 간단 사용법 (0) | 2024.06.03 |