MHA 구성
MHA는 최소 3대의 서버를 필요로 한다.
0. 테스트 환경
Cent7 minimal + Maria 10.2 + MHA 0.57 조합으로 테스트 진행
Oracle VM으로 테스트를 진행 하며 서버는 다음과 같은 서버를 구성하여 사용.
각각의 서버는 2개의 네트워크 카드를 가지게 설정 했으며, enp0s3 : NAS(인터넷연결) enp0s8:Local network로 구성 함.
enp0s3는 필요에 따라서 vip로 변경 할 수 있으나, 여기서는 테스트 하지 않고, vip로 변경 시 mhauser로 수행 할 것을 대비해서 sudo 권한은 mhauser에게 주는 설정은 추가 시킴
- MHA Manager Server 192.168.56.150 (mhamgr)
- Master DB 1ea 192.168.56.151 (master)
- Slave DB 01 192.168.56.152 (slave01)
- Slave DB 02 192.168.56.153 (slave02)
기본 설정
selinux=disabled (/etc/selinux/config)
firewalld stop (systemctl disable firewalld / systemctl stop firewalld)
모든 서버의 hosts 파일
192.168.56.1 notebook
192.168.56.150 mhamgr
192.168.56.151 master
192.168.56.152 slave01
192.168.56.153 slave02
모든 서버의 hostname은 hosts파일과 맞게 수정해 줌 (/etc/hostname 수정 후 변경 안되게 chmod 400 /etc/hostname)
MHA 0.57 Download : https://mega.nz/#F!G4oRjARB!SWzFS59bUv9VrKwdAeIGVw
다운 받은 파일은 각각의 서버에 /source 디렉터리에 업로드 (mha4mysql-manager-0.57.tar.gz , mha4mysql-node-0.57.tar.gz)
1. 기본 패키지 설치
- 필요 패키지 설치 (모든 서버)
yum install epel-release -y
yum install net-tools sysstat wget lrzsz lsof htop iftop rsync bzip2 unzip patch syslog -y
- MHA 관련 (모든 서버)
yum -y install epel perl-devel perl-CPAN perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Module-Install
- MariaDB Repo 설치 (모든 서버)
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.2"
2. Maria 10.2 설치
2.1 MariaDB Server 설치 (master, slave01, slave02)
yum install MariaDB-server MariaDB-client -y
2.2 MariaDB Client 설치 (management)
yum install MariaDB-client -y
3. Maria Master/Slave 구성(master, slave01 ,slave02)
3.1 DB 기동
service mysql start
3.2 root 패스워드 변경
mysql_secure_installation
Enter current password for root (enter for none): enter
Set root password? [Y/n] y
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] n
Remove test database and access to it? [Y/n] n
Reload privilege tables now? [Y/n] y
3.3 root 접속 테스트
mysql -uroot -p
show global variables like '%dir%' ;
basedir : /usr
datadir : /var/lib/mysql/
3.4 Replication 유저 생성
create user 'rep'@'%' identified by 'rep1122';
grant replication slave on *.* to 'rep';
3.5 mha 유저 생성
grant all privileges on *.* to 'mha'@'%' identified by 'mha1122' ;
flush privileges;
select user , host from mysql.user ;
+------+-----------+
| user | host |
+------+-----------+
| mha | % |
| rep | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | master |
+------+-----------+
exit
3.6 DB 종료
service mysql stop
3.7 환경변수 설정
my.cnf 설정
vi /etc/my.cnf.d/server.cnf
3.7.1 master
[mariadb]
server_id=1
log-bin=mysql-bin
3.7.2 slave01
[mariadb]
server_id=2
log-bin=mysql-bin
read_only=1
relay_log_purge=0
3.7.3 slave02
[mariadb]
server_id=3
log-bin=mysql-bin
read_only=1
relay_log_purge=0
3.7.4 기동 후 확인
service mysql start
mysql -uroot -p
show global variables like 'server_id' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
3.8 Slave 구성
3.8.1 master 서버에서 현재 로그 파일과 위치 확인
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
show variables like 'port' ;
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| port | 3306 |
+----------------------+-------+
3.8.2 slave01, slave02 서버에서 리플리케이션 세팅
change master to master_host='192.168.56.151' , master_user='rep' , master_password='rep1122' , master_port=3306 ,
master_log_file='mysql-bin.000001' , master_log_pos=328 , master_connect_retry=10 ;
start slave ;
show slave status\G;
3.8.2 master에서 slave 세션이 접속 되었는지 확인
show processlist ;
+----+-------------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 10 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
| 11 | rep | slave02:50688 | NULL | Binlog Dump | 26 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
| 12 | rep | slave01:48970 | NULL | Binlog Dump | 21 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
+----+-------------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
3.8.3 리플리케이션 정상 동작 확인 테스트
3.8.3.1 Master DB에서 수행
create database reptest ;
show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| reptest |
| test |
+--------------------+
use reptest
create table rep1 ( id int) ;
insert into rep1 values (1) ;
insert into rep1 values (2) ;
insert into rep1 values (3) ;
exit
3.8.3.2 Slave DB(slave01, slave02)에서 확인
show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| reptest |
| test |
+--------------------+
use reptest
select * from rep1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
exit
4. MHA 설치
4.1 MHA 프로그램이 사용할 디렉터리 생성 (모든 서버)
mkdir /mha
4.2 MHA 유저 생성 (모든 서버)
master, slave01, slave02는 MariaDB가 설치 되어 있어서 mysql 그룹이 있지만 management server는 mysql 그룹이 없으므로 그룹 생성.
groupadd mysql (management 서버만)
유저는 모든 서버에서 생성해 줍니다.
useradd -g mysql -d /home/mhauser -m -s /bin/bash mhauser
passwd mhauser
mhauser1122
chown -R mhauser:mysql /mha
4.3 MHA node 설치 (모든 서버)
root 로 수행
cd /source
tar xvzf mha4mysql-node-0.57.tar.gz
cd /source/mha4mysql-node-0.57
perl Makefile.PL
make
make install
4.4 MHA manager 설치 (management 서버)
root 로 수행
cpan YAML
perl -MCPAN -e "install File::Remove"
perl -MCPAN -e "install Build"
perl -MCPAN -e "install Module::Install"
perl -MCPAN -e "install Net::Telnet"
perl -MCPAN -e "install Log::Dispatch"
cd /source
tar xvzf mha4mysql-manager-0.57.tar.gz
cd /source/mha4mysql-manager-0.57
perl Makefile.PL
make
make install
4.5 SSH 연결 설정
mha는 ssh를 통해 노드를 연결하고, scp를 통해 로그를 전송합니다.
4개의 서버가 각각의 서버에 ssh 접속 시 비밀 번호 입력 없이 접근이 가능하도록 키 생성 및 복사 작업 진행
MHA는 mhauser 계정으로 ssh 접속을 할 것이기 때문에 다음의 작업은 mhauser에서 수행합니다.
4.5.1 mhauser sudo 권한 부여
페일 오버 후 vip를 네트워크 인터페이스에 할당하고 이를 up(활성화)/down(비활성화) 하는 명령을 실행하기 위해 sudo 파일 설정
나중에 필요 할 권한이므로 설정은 해 주고 갑니다.
root로 수행
visudo 명령어 친 후 다음 내용을 제일 밑에 추가 한다.
mhauser ALL=(ALL) NOPASSWD:/sbin/ifconfig
4.5.2 각 서버에서 다음의 작업으로 키 생성, 복사, 접속 테스트 수행
management 서버 : 키 생성 후 master,slave01,slave02로 전송
|
키 생성 : su - mhauser cd ssh-keygen -t rsa [엔터 3번]
키 복사 : ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.151 ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.152 ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.153
연결 테스트 : ssh 192.168.56.151 hostname ssh 192.168.56.152 hostname ssh 192.168.56.153 hostname |
master 서버 : 키 생성 후 management,slave01,slave02로 전송
|
키 생성 : su - mhauser cd ssh-keygen -t rsa [엔터 3번]
키 복사 : ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.150 ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.152 ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.153
연결 테스트: ssh 192.168.56.150 hostname ssh 192.168.56.152 hostname ssh 192.168.56.153 hostname |
slave01 서버 : 키 생성 후 management, master,slave02로 전송
|
키 생성 : su - mhauser cd ssh-keygen -t rsa [엔터 3번]
키 복사 : ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.150 ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.151 ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.153
연결 테스트 : ssh 192.168.56.150 hostname ssh 192.168.56.151 hostname ssh 192.168.56.153 hostname |
slave02 서버 : 키 생성 후 management, master,slave01로 전송
|
키 생성 : su - mhauser cd ssh-keygen -t rsa [엔터 3번]
키 복사 : ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.150 ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.151 ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.152
연결 테스트 : ssh 192.168.56.150 hostname ssh 192.168.56.151 hostname ssh 192.168.56.152 hostname |
모든 서버에서 파일의 내용이 변경되지 않도록 권한 수정
su - mhauser
cd
cd .ssh
chmod 400 authorized_keys
4.6 mhauser 환경 변수 설정 (management server)
.bash_profile 에 다음 내용 추가
|
set -o vi
alias sshcheck='/usr/local/bin/masterha_check_ssh --conf=/etc/mha.cnf' alias replcheck='/usr/local/bin/masterha_check_repl --conf=/etc/mha.cnf'
alias start='/usr/local/bin/masterha_manager --conf=/etc/mha.cnf &' alias stop='/usr/local/bin/masterha_stop --conf=/etc/mha.cnf' alias status='/usr/local/bin/masterha_check_status --conf=/etc/mha.cnf'
alias log='tail -f /mha/manager.log' |
4.7 MHA에서 사용할 config 파일 생성 (management server)
기본적으로 manager 설치 파일 압축 풀어 놓은 디렉터리 안의 sample 파일 이용
cp /source/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/mha.cnf
vi /etc/mha.cnf
|
[server default]
#mysql user user=mha password=mha1122
#ssh user ssh_user=mhauser
#replication user repl_user=rep repl_password=rep1122
#mha log manager_workdir=/mha manager_log=/mha/manager.log
#remote mha log remote_workdir=/mha
#binary bin log path master_binlog_dir=/var/lib/mysql
[server1] hostname=192.168.56.151 candidate_master=1
[server2] hostname=192.168.56.152 candidate_master=1
[server3] hostname=192.168.56.153 candidate_master=1 |
4.8 설정 확인 (management server)
4.8.1 ssh 설정 체크 (mhauser)
sshcheck
|
Thu Jan 9 13:05:52 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jan 9 13:05:52 2020 - [info] Reading application default configuration from /etc/mha.cnf.. Thu Jan 9 13:05:52 2020 - [info] Reading server configuration from /etc/mha.cnf.. Thu Jan 9 13:05:52 2020 - [info] Starting SSH connection tests.. Thu Jan 9 13:05:53 2020 - [debug] Thu Jan 9 13:05:52 2020 - [debug] Connecting via SSH from mhauser@192.168.56.151(192.168.56.151:22) to mhauser@192.168.56.152(192.168.56.152:22).. Thu Jan 9 13:05:52 2020 - [debug] ok. Thu Jan 9 13:05:52 2020 - [debug] Connecting via SSH from mhauser@192.168.56.151(192.168.56.151:22) to mhauser@192.168.56.153(192.168.56.153:22).. Thu Jan 9 13:05:52 2020 - [debug] ok. Thu Jan 9 13:05:53 2020 - [debug] Thu Jan 9 13:05:53 2020 - [debug] Connecting via SSH from mhauser@192.168.56.152(192.168.56.152:22) to mhauser@192.168.56.151(192.168.56.151:22).. Thu Jan 9 13:05:53 2020 - [debug] ok. Thu Jan 9 13:05:53 2020 - [debug] Connecting via SSH from mhauser@192.168.56.152(192.168.56.152:22) to mhauser@192.168.56.153(192.168.56.153:22).. Thu Jan 9 13:05:53 2020 - [debug] ok. Thu Jan 9 13:05:54 2020 - [debug] Thu Jan 9 13:05:53 2020 - [debug] Connecting via SSH from mhauser@192.168.56.153(192.168.56.153:22) to mhauser@192.168.56.151(192.168.56.151:22).. Thu Jan 9 13:05:53 2020 - [debug] ok. Thu Jan 9 13:05:53 2020 - [debug] Connecting via SSH from mhauser@192.168.56.153(192.168.56.153:22) to mhauser@192.168.56.152(192.168.56.152:22).. Thu Jan 9 13:05:53 2020 - [debug] ok. Thu Jan 9 13:05:54 2020 - [info] All SSH connection tests passed successfully. |
4.8.2 replication 설정 체크 (mhauser)
replcheck
|
Thu Jan 9 13:07:27 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jan 9 13:07:27 2020 - [info] Reading application default configuration from /etc/mha.cnf.. Thu Jan 9 13:07:27 2020 - [info] Reading server configuration from /etc/mha.cnf.. Thu Jan 9 13:07:27 2020 - [info] MHA::MasterMonitor version 0.57. Thu Jan 9 13:07:28 2020 - [info] GTID failover mode = 0 Thu Jan 9 13:07:28 2020 - [info] Dead Servers: Thu Jan 9 13:07:28 2020 - [info] Alive Servers: Thu Jan 9 13:07:28 2020 - [info] 192.168.56.151(192.168.56.151:3306) Thu Jan 9 13:07:28 2020 - [info] 192.168.56.152(192.168.56.152:3306) Thu Jan 9 13:07:28 2020 - [info] 192.168.56.153(192.168.56.153:3306) Thu Jan 9 13:07:28 2020 - [info] Alive Slaves: Thu Jan 9 13:07:28 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 13:07:28 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 13:07:28 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 13:07:28 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 13:07:28 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 13:07:28 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 13:07:28 2020 - [info] Current Alive Master: 192.168.56.151(192.168.56.151:3306) Thu Jan 9 13:07:28 2020 - [info] Checking slave configurations.. Thu Jan 9 13:07:28 2020 - [info] Checking replication filtering settings.. Thu Jan 9 13:07:28 2020 - [info] binlog_do_db= , binlog_ignore_db= Thu Jan 9 13:07:28 2020 - [info] Replication filtering check ok. Thu Jan 9 13:07:28 2020 - [info] GTID (with auto-pos) is not supported Thu Jan 9 13:07:28 2020 - [info] Starting SSH connection tests.. Thu Jan 9 13:07:30 2020 - [info] All SSH connection tests passed successfully. Thu Jan 9 13:07:30 2020 - [info] Checking MHA Node version.. Thu Jan 9 13:07:30 2020 - [info] Version check ok. Thu Jan 9 13:07:30 2020 - [info] Checking SSH publickey authentication settings on the current master.. Thu Jan 9 13:07:30 2020 - [info] HealthCheck: SSH to 192.168.56.151 is reachable. Thu Jan 9 13:07:30 2020 - [info] Master MHA Node version is 0.57. Thu Jan 9 13:07:30 2020 - [info] Checking recovery script configurations on 192.168.56.151(192.168.56.151:3306).. Thu Jan 9 13:07:30 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/mha/save_binary_logs_test --manager_version=0.57 --start_file=mysql-bin.000004 Thu Jan 9 13:07:30 2020 - [info] Connecting to mhauser@192.168.56.151(192.168.56.151:22).. Creating /mha if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to mysql-bin.000004 Thu Jan 9 13:07:30 2020 - [info] Binlog setting check done. Thu Jan 9 13:07:30 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Thu Jan 9 13:07:30 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.56.152 --slave_ip=192.168.56.152 --slave_port=3306 --workdir=/mha --target_version=10.2.30-MariaDB-log --manager_version=0.57 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Thu Jan 9 13:07:30 2020 - [info] Connecting to mhauser@192.168.56.152(192.168.56.152:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to slave01-relay-bin.000002 Temporary relay log file is /var/lib/mysql/slave01-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Thu Jan 9 13:07:31 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.56.153 --slave_ip=192.168.56.153 --slave_port=3306 --workdir=/mha --target_version=10.2.30-MariaDB-log --manager_version=0.57 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Thu Jan 9 13:07:31 2020 - [info] Connecting to mhauser@192.168.56.153(192.168.56.153:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to slave02-relay-bin.000002 Temporary relay log file is /var/lib/mysql/slave02-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Thu Jan 9 13:07:31 2020 - [info] Slaves settings check done. Thu Jan 9 13:07:31 2020 - [info] 192.168.56.151(192.168.56.151:3306) (current master) +--192.168.56.152(192.168.56.152:3306) +--192.168.56.153(192.168.56.153:3306)
Thu Jan 9 13:07:31 2020 - [info] Checking replication health on 192.168.56.152.. Thu Jan 9 13:07:31 2020 - [info] ok. Thu Jan 9 13:07:31 2020 - [info] Checking replication health on 192.168.56.153.. Thu Jan 9 13:07:31 2020 - [info] ok. Thu Jan 9 13:07:31 2020 - [warning] master_ip_failover_script is not defined. Thu Jan 9 13:07:31 2020 - [warning] shutdown_script is not defined. Thu Jan 9 13:07:31 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK. |
4.9 MHA 실행, 종료, 상태 확인 (management server)
start
|
[1] 6150 [mhauser@mhamgr ~]$ Thu Jan 9 13:12:43 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jan 9 13:12:43 2020 - [info] Reading application default configuration from /etc/mha.cnf.. Thu Jan 9 13:12:43 2020 - [info] Reading server configuration from /etc/mha.cnf.. |
status
|
mha (pid:6150) is running(0:PING_OK), master:192.168.56.151 |
stop
|
Stopped mha successfully. [1]+ Exit 1 /usr/local/bin/masterha_manager --conf=/etc/mha.cnf |
log
|
+--192.168.56.152(192.168.56.152:3306) +--192.168.56.153(192.168.56.153:3306)
Thu Jan 9 13:12:47 2020 - [warning] master_ip_failover_script is not defined. Thu Jan 9 13:12:47 2020 - [warning] shutdown_script is not defined. Thu Jan 9 13:12:47 2020 - [info] Set master ping interval 3 seconds. Thu Jan 9 13:12:47 2020 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Thu Jan 9 13:12:47 2020 - [info] Starting ping health check on 192.168.56.151(192.168.56.151:3306).. Thu Jan 9 13:12:47 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. Thu Jan 9 13:14:07 2020 - [info] Got terminate signal. Exit. |
여기까지 구성을 하고 VM을 모두 종료 한 후 백업을 해 놓는 것을 권장
아래 시나리오 중에서 master 장애 시점 테스트 하면 master를 다시 slave로 구성 해야 하니 그냥 백업 본으로 복구 하는 것을 권장.
5. MHA 운영 중 master, slave 변경 테스트
masterha_master_switch 명령어 중에서 --orig_master_is_new_slave 옵션 테스트
masterha_master_switch 명령어는 mha가 실행 중이면 안 된다.
mha가 실행 중인지 확인 후 종료 한 후에 masterha_master_switch 명령어 수행
5.1 master --> slave01로 마스터 변경
master, slave01, slave02 모두 정상 동작 중인 상태에서 명령어를 이용하여 마스터 변경
management (mhauser) :
|
상태 확인 : [mhauser@mhamgr ~]$ status mha (pid:2169) is running(0:PING_OK), master:192.168.56.151
종료 : [mhauser@mhamgr ~]$ stop Stopped mha successfully. [1]+ Exit 1 /usr/local/bin/masterha_manager --conf=/etc/mha.cnf
master --> slave01로 마스터 변경 : [mhauser@mhamgr ~]$ masterha_master_switch --master_state=alive --conf=/etc/mha.cnf --orig_master_is_new_slave ... Thu Jan 9 10:02:34 2020 - [info] Current Alive Master: 192.168.56.151(192.168.56.151:3306) Thu Jan 9 10:02:34 2020 - [info] Alive Slaves: Thu Jan 9 10:02:34 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:02:34 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 10:02:34 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 10:02:34 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:02:34 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 10:02:34 2020 - [info] Primary candidate for the new Master (candidate_master is set)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.56.151(192.168.56.151:3306)? (YES/no): yes Thu Jan 9 10:02:50 2020 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Thu Jan 9 10:02:50 2020 - [info] ok. Thu Jan 9 10:02:50 2020 - [info] Checking MHA is not monitoring or doing failover.. Thu Jan 9 10:02:50 2020 - [info] Checking replication health on 192.168.56.152.. Thu Jan 9 10:02:50 2020 - [info] ok. Thu Jan 9 10:02:50 2020 - [info] Checking replication health on 192.168.56.153.. Thu Jan 9 10:02:50 2020 - [info] ok. Thu Jan 9 10:02:50 2020 - [info] Searching new master from slaves.. Thu Jan 9 10:02:50 2020 - [info] Candidate masters from the configuration file: Thu Jan 9 10:02:50 2020 - [info] 192.168.56.151(192.168.56.151:3306) Version=10.2.30-MariaDB-log log-bin:enabled Thu Jan 9 10:02:50 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:02:50 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 10:02:50 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 10:02:50 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:02:50 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 10:02:50 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 10:02:50 2020 - [info] Non-candidate masters: Thu Jan 9 10:02:50 2020 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Thu Jan 9 10:02:50 2020 - [info] From: 192.168.56.151(192.168.56.151:3306) (current master) +--192.168.56.152(192.168.56.152:3306) +--192.168.56.153(192.168.56.153:3306)
To: 192.168.56.152(192.168.56.152:3306) (new master) +--192.168.56.153(192.168.56.153:3306) +--192.168.56.151(192.168.56.151:3306)
Starting master switch from 192.168.56.151(192.168.56.151:3306) to 192.168.56.152(192.168.56.152:3306)? (yes/NO): yes Thu Jan 9 10:03:05 2020 - [info] Checking whether 192.168.56.152(192.168.56.152:3306) is ok for the new master.. Thu Jan 9 10:03:05 2020 - [info] ok. Thu Jan 9 10:03:05 2020 - [info] 192.168.56.151(192.168.56.151:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host. Thu Jan 9 10:03:05 2020 - [info] 192.168.56.151(192.168.56.151:3306): Resetting slave pointing to the dummy host. Thu Jan 9 10:03:05 2020 - [info] ** Phase 1: Configuration Check Phase completed. Thu Jan 9 10:03:05 2020 - [info] Thu Jan 9 10:03:05 2020 - [info] * Phase 2: Rejecting updates Phase.. Thu Jan 9 10:03:05 2020 - [info] master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes Thu Jan 9 10:04:06 2020 - [info] Locking all tables on the orig master to reject updates from everybody (including root): Thu Jan 9 10:04:06 2020 - [info] Executing FLUSH TABLES WITH READ LOCK.. Thu Jan 9 10:04:06 2020 - [info] ok. Thu Jan 9 10:04:06 2020 - [info] Orig master binlog:pos is mysql-bin.000004:342. Thu Jan 9 10:04:06 2020 - [info] Waiting to execute all relay logs on 192.168.56.152(192.168.56.152:3306).. Thu Jan 9 10:04:06 2020 - [info] master_pos_wait(mysql-bin.000004:342) completed on 192.168.56.152(192.168.56.152:3306). Executed 0 events. Thu Jan 9 10:04:06 2020 - [info] done. Thu Jan 9 10:04:06 2020 - [info] Getting new master's binlog name and position.. Thu Jan 9 10:04:06 2020 - [info] mysql-bin.000005:328 Thu Jan 9 10:04:06 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.56.152', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=328, MASTER_USER='rep', MASTER_PASSWORD='xxx'; Thu Jan 9 10:04:06 2020 - [info] Setting read_only=0 on 192.168.56.152(192.168.56.152:3306).. Thu Jan 9 10:04:06 2020 - [info] ok. Thu Jan 9 10:04:06 2020 - [info] Thu Jan 9 10:04:06 2020 - [info] * Switching slaves in parallel.. Thu Jan 9 10:04:06 2020 - [info] Thu Jan 9 10:04:06 2020 - [info] -- Slave switch on host 192.168.56.153(192.168.56.153:3306) started, pid: 2362 Thu Jan 9 10:04:06 2020 - [info] Thu Jan 9 10:04:07 2020 - [info] Log messages from 192.168.56.153 ... Thu Jan 9 10:04:07 2020 - [info] Thu Jan 9 10:04:06 2020 - [info] Waiting to execute all relay logs on 192.168.56.153(192.168.56.153:3306).. Thu Jan 9 10:04:06 2020 - [info] master_pos_wait(mysql-bin.000004:342) completed on 192.168.56.153(192.168.56.153:3306). Executed 0 events. Thu Jan 9 10:04:06 2020 - [info] done. Thu Jan 9 10:04:06 2020 - [info] Resetting slave 192.168.56.153(192.168.56.153:3306) and starting replication from the new master 192.168.56.152(192.168.56.152:3306).. Thu Jan 9 10:04:06 2020 - [info] Executed CHANGE MASTER. Thu Jan 9 10:04:06 2020 - [info] Slave started. Thu Jan 9 10:04:07 2020 - [info] End of log messages from 192.168.56.153 ... Thu Jan 9 10:04:07 2020 - [info] Thu Jan 9 10:04:07 2020 - [info] -- Slave switch on host 192.168.56.153(192.168.56.153:3306) succeeded. Thu Jan 9 10:04:07 2020 - [info] Unlocking all tables on the orig master: Thu Jan 9 10:04:07 2020 - [info] Executing UNLOCK TABLES.. Thu Jan 9 10:04:07 2020 - [info] ok. Thu Jan 9 10:04:07 2020 - [info] Starting orig master as a new slave.. Thu Jan 9 10:04:07 2020 - [info] Resetting slave 192.168.56.151(192.168.56.151:3306) and starting replication from the new master 192.168.56.152(192.168.56.152:3306).. Thu Jan 9 10:04:07 2020 - [info] Executed CHANGE MASTER. Thu Jan 9 10:04:07 2020 - [info] Slave started. Thu Jan 9 10:04:07 2020 - [info] All new slave servers switched successfully. Thu Jan 9 10:04:07 2020 - [info] Thu Jan 9 10:04:07 2020 - [info] * Phase 5: New master cleanup phase.. Thu Jan 9 10:04:07 2020 - [info] Thu Jan 9 10:04:07 2020 - [info] 192.168.56.152: Resetting slave info succeeded. Thu Jan 9 10:04:07 2020 - [info] Switching master to 192.168.56.152(192.168.56.152:3306) completed successfully. |
master :
|
[root@master ~]# mysql -uroot -proot1122 -e "show variables like 'read_only';" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | <-- read only가 ON 되어야 하는데 안 됨. +---------------+-------+
[root@master ~]# mysql -uroot -proot1122 -e "show slave status\G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.152 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 328 Relay_Log_File: master-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...... Seconds_Behind_Master: 0 ...... SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
기존 master가 slave로 세팅 되어 진 것을 확인 할 수 있다. |
slave01 :
|
[root@slave01 ~]# mysql -uroot -proot1122 -e "show variables like 'read_only'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | <-- master로 승격 되면서 기존에 read only에서 read write로 변경된 것을 확인 할 수 있다. +---------------+-------+
[root@slave01 ~]# mysql -uroot -proot1122 -e "show processlist;" +----+-------------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 18 | rep | slave02:59068 | NULL | Binlog Dump | 295 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 | | 19 | rep | master:53104 | NULL | Binlog Dump | 294 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 | | 24 | mha | mhamgr:59566 | NULL | Sleep | 0 | | NULL | 0.000 | | 27 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 | +----+-------------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ |
slave02 :
|
[root@slave02 ~]# mysql -uroot -proot1122 -e "show slave status\G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.152 Master_User: rep Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 328 Relay_Log_File: slave02-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes .... Seconds_Behind_Master: 0 .... SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
[root@slave02 ~]# mysql -uroot -proot1122 -e "show variables like 'read_only'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ |
5.2 slave01 --> master로 마스터 다시 변경
management (mhauser) :
|
[mhauser@mhamgr ~]$ status mha is stopped(2:NOT_RUNNING).
[mhauser@mhamgr ~]$ masterha_master_switch --master_state=alive --conf=/etc/mha.cnf --orig_master_is_new_slave Thu Jan 9 10:09:54 2020 - [info] MHA::MasterRotate version 0.57. Thu Jan 9 10:09:54 2020 - [info] Starting online master switch.. Thu Jan 9 10:09:54 2020 - [info] Thu Jan 9 10:09:54 2020 - [info] * Phase 1: Configuration Check Phase.. Thu Jan 9 10:09:54 2020 - [info] Thu Jan 9 10:09:54 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jan 9 10:09:54 2020 - [info] Reading application default configuration from /etc/mha.cnf.. Thu Jan 9 10:09:54 2020 - [info] Reading server configuration from /etc/mha.cnf.. Thu Jan 9 10:09:55 2020 - [info] GTID failover mode = 0 Thu Jan 9 10:09:55 2020 - [info] Current Alive Master: 192.168.56.152(192.168.56.152:3306) Thu Jan 9 10:09:55 2020 - [info] Alive Slaves: Thu Jan 9 10:09:55 2020 - [info] 192.168.56.151(192.168.56.151:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:09:55 2020 - [info] Replicating from 192.168.56.152(192.168.56.152:3306) Thu Jan 9 10:09:55 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 10:09:55 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:09:55 2020 - [info] Replicating from 192.168.56.152(192.168.56.152:3306) Thu Jan 9 10:09:55 2020 - [info] Primary candidate for the new Master (candidate_master is set)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.56.152(192.168.56.152:3306)? (YES/no): yes Thu Jan 9 10:09:58 2020 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Thu Jan 9 10:09:58 2020 - [info] ok. Thu Jan 9 10:09:58 2020 - [info] Checking MHA is not monitoring or doing failover.. Thu Jan 9 10:09:58 2020 - [info] Checking replication health on 192.168.56.151.. Thu Jan 9 10:09:58 2020 - [info] ok. Thu Jan 9 10:09:58 2020 - [info] Checking replication health on 192.168.56.153.. Thu Jan 9 10:09:58 2020 - [info] ok. Thu Jan 9 10:09:58 2020 - [info] Searching new master from slaves.. Thu Jan 9 10:09:58 2020 - [info] Candidate masters from the configuration file: Thu Jan 9 10:09:58 2020 - [info] 192.168.56.151(192.168.56.151:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:09:58 2020 - [info] Replicating from 192.168.56.152(192.168.56.152:3306) Thu Jan 9 10:09:58 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 10:09:58 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log log-bin:enabled Thu Jan 9 10:09:58 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:09:58 2020 - [info] Replicating from 192.168.56.152(192.168.56.152:3306) Thu Jan 9 10:09:58 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 10:09:58 2020 - [info] Non-candidate masters: Thu Jan 9 10:09:58 2020 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Thu Jan 9 10:09:58 2020 - [info] From: 192.168.56.152(192.168.56.152:3306) (current master) +--192.168.56.151(192.168.56.151:3306) +--192.168.56.153(192.168.56.153:3306)
To: 192.168.56.151(192.168.56.151:3306) (new master) +--192.168.56.153(192.168.56.153:3306) +--192.168.56.152(192.168.56.152:3306)
Starting master switch from 192.168.56.152(192.168.56.152:3306) to 192.168.56.151(192.168.56.151:3306)? (yes/NO): yes Thu Jan 9 10:10:24 2020 - [info] Checking whether 192.168.56.151(192.168.56.151:3306) is ok for the new master.. Thu Jan 9 10:10:24 2020 - [info] ok. Thu Jan 9 10:10:24 2020 - [info] 192.168.56.152(192.168.56.152:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host. Thu Jan 9 10:10:24 2020 - [info] 192.168.56.152(192.168.56.152:3306): Resetting slave pointing to the dummy host. Thu Jan 9 10:10:24 2020 - [info] ** Phase 1: Configuration Check Phase completed. Thu Jan 9 10:10:24 2020 - [info] Thu Jan 9 10:10:24 2020 - [info] * Phase 2: Rejecting updates Phase.. Thu Jan 9 10:10:24 2020 - [info] master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes Thu Jan 9 10:10:35 2020 - [info] Locking all tables on the orig master to reject updates from everybody (including root): Thu Jan 9 10:10:35 2020 - [info] Executing FLUSH TABLES WITH READ LOCK.. Thu Jan 9 10:10:35 2020 - [info] ok. Thu Jan 9 10:10:35 2020 - [info] Orig master binlog:pos is mysql-bin.000005:328. Thu Jan 9 10:10:35 2020 - [info] Waiting to execute all relay logs on 192.168.56.151(192.168.56.151:3306).. Thu Jan 9 10:10:35 2020 - [info] master_pos_wait(mysql-bin.000005:328) completed on 192.168.56.151(192.168.56.151:3306). Executed 0 events. Thu Jan 9 10:10:35 2020 - [info] done. Thu Jan 9 10:10:35 2020 - [info] Getting new master's binlog name and position.. Thu Jan 9 10:10:35 2020 - [info] mysql-bin.000004:342 Thu Jan 9 10:10:35 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.56.151', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=342, MASTER_USER='rep', MASTER_PASSWORD='xxx'; Thu Jan 9 10:10:35 2020 - [info] Thu Jan 9 10:10:35 2020 - [info] * Switching slaves in parallel.. Thu Jan 9 10:10:35 2020 - [info] Thu Jan 9 10:10:35 2020 - [info] -- Slave switch on host 192.168.56.153(192.168.56.153:3306) started, pid: 2528 Thu Jan 9 10:10:35 2020 - [info] Thu Jan 9 10:10:36 2020 - [info] Log messages from 192.168.56.153 ... Thu Jan 9 10:10:36 2020 - [info] Thu Jan 9 10:10:35 2020 - [info] Waiting to execute all relay logs on 192.168.56.153(192.168.56.153:3306).. Thu Jan 9 10:10:35 2020 - [info] master_pos_wait(mysql-bin.000005:328) completed on 192.168.56.153(192.168.56.153:3306). Executed 0 events. Thu Jan 9 10:10:35 2020 - [info] done. Thu Jan 9 10:10:35 2020 - [info] Resetting slave 192.168.56.153(192.168.56.153:3306) and starting replication from the new master 192.168.56.151(192.168.56.151:3306).. Thu Jan 9 10:10:35 2020 - [info] Executed CHANGE MASTER. Thu Jan 9 10:10:35 2020 - [info] Slave started. Thu Jan 9 10:10:36 2020 - [info] End of log messages from 192.168.56.153 ... Thu Jan 9 10:10:36 2020 - [info] Thu Jan 9 10:10:36 2020 - [info] -- Slave switch on host 192.168.56.153(192.168.56.153:3306) succeeded. Thu Jan 9 10:10:36 2020 - [info] Unlocking all tables on the orig master: Thu Jan 9 10:10:36 2020 - [info] Executing UNLOCK TABLES.. Thu Jan 9 10:10:36 2020 - [info] ok. Thu Jan 9 10:10:36 2020 - [info] Starting orig master as a new slave.. Thu Jan 9 10:10:36 2020 - [info] Resetting slave 192.168.56.152(192.168.56.152:3306) and starting replication from the new master 192.168.56.151(192.168.56.151:3306).. Thu Jan 9 10:10:36 2020 - [info] Executed CHANGE MASTER. Thu Jan 9 10:10:36 2020 - [info] Slave started. Thu Jan 9 10:10:36 2020 - [info] All new slave servers switched successfully. Thu Jan 9 10:10:36 2020 - [info] Thu Jan 9 10:10:36 2020 - [info] * Phase 5: New master cleanup phase.. Thu Jan 9 10:10:36 2020 - [info] Thu Jan 9 10:10:36 2020 - [info] 192.168.56.151: Resetting slave info succeeded. Thu Jan 9 10:10:36 2020 - [info] Switching master to 192.168.56.151(192.168.56.151:3306) completed successfully.
|
master :
|
[root@master ~]# mysql -uroot -proot1122 -e "show slave status\G" [root@master ~]# mysql -uroot -proot1122 -e "show variables like 'read_only';" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ |
slave01 :
|
[root@slave01 ~]# mysql -uroot -proot1122 -e "show variables like 'read_only'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | <-- 역시나 안 바뀜 +---------------+-------+
[root@slave01 ~]# mysql -uroot -proot1122 -e "show slave status\G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.151 Master_User: rep Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 342 Relay_Log_File: slave01-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes ..... Seconds_Behind_Master: 0 ..... SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it |
slave02 :
|
[root@slave02 ~]# mysql -uroot -proot1122 -e "show slave status\G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.151 Master_User: rep Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 342 Relay_Log_File: slave02-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes .... Seconds_Behind_Master: 0 .... SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
[root@slave02 ~]# mysql -uroot -proot1122 -e "show variables like 'read_only'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ |
5.3 master_ip_online_change_script 작성 (management)
스위치 중간에 나오는 master_ip_online_change_script is not defined라는 문구 때문인 것 같아서 해당 스크립트 수정 및 추가 작업 진행
mkdir /mha/scripts
cp /source/mha4mysql-manager-0.57/samples/scripts/master_ip_online_change /mha/scripts/master_ip_online_change
/mha/scripts/master_ip_online_change 파일 편집
vi로 연 후에 다음 명령어를 이용하여 원하는 위치로 쉽게 이동한다.
|
150, 151, 152, 245, 246, 247, 248 라인에 주석 추가
:set number :150
149 ## Drop application user so that nobody can connect. Disabling per-session binlog beforehand 150 # $orig_master_handler->disable_log_bin_local(); 151 # print current_time_us() . " Drpping app user on the orig master..\n"; 152 # FIXME_xxx_drop_app_user($orig_master_handler);
:245
244 ## Creating an app user on the new master 245 # print current_time_us() . " Creating app user on the new master..\n"; 246 # FIXME_xxx_create_app_user($new_master_handler); 247 # $new_master_handler->enable_log_bin_local(); 248 # $new_master_handler->disconnect(); |
/etc/mha.cnf 파일에 내용 추가
|
[server default]
#mysql user user=mha password=mha1122
#ssh user ssh_user=mhauser
#replication user repl_user=rep repl_password=rep1122
#mha log manager_workdir=/mha manager_log=/mha/manager.log
#remote mha log remote_workdir=/mha
#binary bin log path master_binlog_dir=/var/lib/mysql
#add online_change_script at 2020.01.09 master_ip_online_change_script=/mha/scripts/master_ip_online_change
[server1] hostname=192.168.56.151 candidate_master=1
[server2] hostname=192.168.56.152 candidate_master=1
[server3] hostname=192.168.56.153 candidate_master=1 |
5.4 다시 master --> slave01 마스터 변경 작업 진행
management (mhauser) :
|
[mhauser@mhamgr ~]$ masterha_master_switch --master_state=alive --conf=/etc/mha.cnf --orig_master_is_new_slave ........... From: 192.168.56.151(192.168.56.151:3306) (current master) +--192.168.56.152(192.168.56.152:3306) +--192.168.56.153(192.168.56.153:3306)
To: 192.168.56.152(192.168.56.152:3306) (new master) +--192.168.56.153(192.168.56.153:3306) +--192.168.56.151(192.168.56.151:3306)
Starting master switch from 192.168.56.151(192.168.56.151:3306) to 192.168.56.152(192.168.56.152:3306)? (yes/NO): yes Thu Jan 9 10:26:51 2020 - [info] Checking whether 192.168.56.152(192.168.56.152:3306) is ok for the new master.. Thu Jan 9 10:26:51 2020 - [info] ok. Thu Jan 9 10:26:51 2020 - [info] 192.168.56.151(192.168.56.151:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host. ......... Thu Jan 9 10:26:52 2020 - [info] Switching master to 192.168.56.152(192.168.56.152:3306) completed successfully. |
master :
|
[root@master ~]# mysql -uroot -proot1122 -e "show variables like 'read_only';" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+
[root@master ~]# mysql -uroot -proot1122 -e "show slave status\G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.152 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 328 Relay_Log_File: master-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes .... Seconds_Behind_Master: 0 .... SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it |
slave01 :
|
[root@slave01 ~]# mysql -uroot -proot1122 -e "show slave status\G"
[root@slave01 ~]# mysql -uroot -proot1122 -e "show variables like 'read_only'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ |
slave02 :
|
[root@slave02 ~]# mysql -uroot -proot1122 -e "show slave status\G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.152 Master_User: rep Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 328 Relay_Log_File: slave02-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes .... Seconds_Behind_Master: 0 .... SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
[root@slave02 ~]# mysql -uroot -proot1122 -e "show variables like 'read_only'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ |
5.5 다시 slave01 --> master로 마스터 변경 작업 진행
management (mhauser) :
|
[mhauser@mhamgr ~]$ masterha_master_switch --master_state=alive --conf=/etc/mha.cnf --orig_master_is_new_slave ..... It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.56.152(192.168.56.152:3306)? (YES/no): yes Thu Jan 9 10:27:58 2020 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Thu Jan 9 10:27:58 2020 - [info] ok. Thu Jan 9 10:27:58 2020 - [info] Checking MHA is not monitoring or doing failover.. Thu Jan 9 10:27:58 2020 - [info] Checking replication health on 192.168.56.151.. Thu Jan 9 10:27:58 2020 - [info] ok. Thu Jan 9 10:27:58 2020 - [info] Checking replication health on 192.168.56.153.. Thu Jan 9 10:27:58 2020 - [info] ok. Thu Jan 9 10:27:58 2020 - [info] Searching new master from slaves.. Thu Jan 9 10:27:58 2020 - [info] Candidate masters from the configuration file: Thu Jan 9 10:27:58 2020 - [info] 192.168.56.151(192.168.56.151:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:27:58 2020 - [info] Replicating from 192.168.56.152(192.168.56.152:3306) Thu Jan 9 10:27:58 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 10:27:58 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log log-bin:enabled Thu Jan 9 10:27:58 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 10:27:58 2020 - [info] Replicating from 192.168.56.152(192.168.56.152:3306) Thu Jan 9 10:27:58 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 10:27:58 2020 - [info] Non-candidate masters: Thu Jan 9 10:27:58 2020 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Thu Jan 9 10:27:58 2020 - [info] From: 192.168.56.152(192.168.56.152:3306) (current master) +--192.168.56.151(192.168.56.151:3306) +--192.168.56.153(192.168.56.153:3306)
To: 192.168.56.151(192.168.56.151:3306) (new master) +--192.168.56.153(192.168.56.153:3306) +--192.168.56.152(192.168.56.152:3306)
....... Thu Jan 9 10:28:02 2020 - [info] Switching master to 192.168.56.151(192.168.56.151:3306) completed successfully. |
master :
|
[root@master ~]# mysql -uroot -proot1122 -e "show slave status\G"
[root@master ~]# mysql -uroot -proot1122 -e "show variables like 'read_only';" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ |
slave01 :
|
[root@slave01 ~]# mysql -uroot -proot1122 -e "show variables like 'read_only'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+
[root@slave01 ~]# mysql -uroot -proot1122 -e "show slave status\G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.151 Master_User: rep Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 342 Relay_Log_File: slave01-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes .... Seconds_Behind_Master: 0 .... SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it |
slave02 :
|
[root@slave02 ~]# mysql -uroot -proot1122 -e "show slave status\G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.151 Master_User: rep Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 342 Relay_Log_File: slave02-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes .... Seconds_Behind_Master: 0 .... SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
[root@slave02 ~]# mysql -uroot -proot1122 -e "show variables like 'read_only'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ |
6. MHA master 장애 상황 테스트 #1
위의 테스트에서는 유저가 필요에 의해서 운영 중에 failover를 수행 한 경우라고 한다면 , 이번 테스트는 운영 중이던 master에 대한 헬스 체크가 실패 할 경우
자동으로 failover를 수행하는지에 대한 테스트 시나리오 임
자동으로 failover를 하기 위해서는 /etc/mha.cnf에 master_ip_failover_script 설정이 되어 있어야 한다.
6.1 MHA 설정 및 기동
management (mhauser) :
|
sample 스크립트를 수정해서 사용한다.
스크립트 복사 : cp /source/mha4mysql-manager-0.57/samples/scripts/master_ip_failover /mha/scripts/
스크립트 수정 : 87, 88, 89, 90, 93 라인 주석 처리
vi /mha/scripts/master_ip_failover
:set number :87
86 ## Creating an app user on the new master 87 # print "Creating app user on the new master..\n"; 88 # FIXME_xxx_create_user( $new_master_handler->{dbh} ); 89 # $new_master_handler->enable_log_bin_local(); 90 # $new_master_handler->disconnect(); 91 92 ## Update master ip on the catalog database, etc 93 # FIXME_xxx;
스크립트 추가 :
vi /etc/mha.cnf
|
management(mhauser) :
|
MHA 기동 : [mhauser@mhamgr ~]$ start [1] 6675 [mhauser@mhamgr ~]$ Thu Jan 9 15:16:22 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jan 9 15:16:22 2020 - [info] Reading application default configuration from /etc/mha.cnf.. Thu Jan 9 15:16:22 2020 - [info] Reading server configuration from /etc/mha.cnf..
MHA 로그 확인 : [mhauser@mhamgr ~]$ log
Thu Jan 9 15:16:26 2020 - [info] 192.168.56.151(192.168.56.151:3306) (current master) +--192.168.56.152(192.168.56.152:3306) +--192.168.56.153(192.168.56.153:3306)
Thu Jan 9 15:16:26 2020 - [info] Checking master_ip_failover_script status: Thu Jan 9 15:16:26 2020 - [info] /mha/scripts/master_ip_failover --command=status --ssh_user=mhauser --orig_master_host=192.168.56.151 --orig_master_ip=192.168.56.151 --orig_master_port=3306 Thu Jan 9 15:16:26 2020 - [info] OK. Thu Jan 9 15:16:26 2020 - [warning] shutdown_script is not defined. Thu Jan 9 15:16:26 2020 - [info] Set master ping interval 3 seconds. Thu Jan 9 15:16:26 2020 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Thu Jan 9 15:16:26 2020 - [info] Starting ping health check on 192.168.56.151(192.168.56.151:3306).. Thu Jan 9 15:16:26 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
|
6.2 master장애 발생
master 서버에서 mysql 종료 시켜 봄.
master :
|
[root@master ~]# service mysql stop Stopping mysql (via systemctl): [ OK ] |
management(mhauser) :
6.1 에서 열어놓은 log를 보는 화면에 다음과 같이 master 장애를 감지(select가 안 되는 상태)하고, master를 변경하는 것을 확인 할 수 있다.
로그를 보면 다음과 같이 동작한다는 것을 확인 할 수 있다.
1. 서버 select 접속 실패
2. ssh로 서버가 살아 있는지 확인 (아마 mysql만 죽고 서버가 살아 있으면, 마지막 로그를 가져와서 슬래이브로 전송하기 위함 같다.)
3. failover하기 전에 다시 한번 master의 상태를 체크 한다.
4. master의 상태가 아직도 죽어 있는 것을 확인 후 failover 수행
5. 바이너리 로그를 mha의 디렉터리로 copy
6. 슬래이브들 중에서 마스터로 승격시킬 서버 선택 (마스터 서버와의 로그 갭이 가장 작은 서버 선택)
7. 승격 대상 서버로 바이너리 로그 전송
8. 전송 된 바이너리 로그 적용
9. 마스터 승격 및 다른 슬래이브 서버들 변경
|
Thu Jan 9 15:19:44 2020 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away) <-- DB에 select 시도가 실패 함 Thu Jan 9 15:19:44 2020 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/mha/save_binary_logs_test --manager_version=0.57 --binlog_prefix=mysql-bin Thu Jan 9 15:19:44 2020 - [info] HealthCheck: SSH to 192.168.56.151 is reachable. <-- ssh 접속은 가능 한 것으로 확인. Thu Jan 9 15:19:47 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.56.151' (111 "Connection refused")) Thu Jan 9 15:19:47 2020 - [warning] Connection failed 2 time(s).. Thu Jan 9 15:19:50 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.56.151' (111 "Connection refused")) Thu Jan 9 15:19:50 2020 - [warning] Connection failed 3 time(s).. Thu Jan 9 15:19:53 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.56.151' (111 "Connection refused")) Thu Jan 9 15:19:53 2020 - [warning] Connection failed 4 time(s).. Thu Jan 9 15:19:53 2020 - [warning] Master is not reachable from health checker! Thu Jan 9 15:19:53 2020 - [warning] Master 192.168.56.151(192.168.56.151:3306) is not reachable! Thu Jan 9 15:19:53 2020 - [warning] SSH is reachable. Thu Jan 9 15:19:53 2020 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha.cnf again, and trying to connect to all servers to check server status.. Thu Jan 9 15:19:53 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jan 9 15:19:53 2020 - [info] Reading application default configuration from /etc/mha.cnf.. Thu Jan 9 15:19:53 2020 - [info] Reading server configuration from /etc/mha.cnf.. Thu Jan 9 15:19:54 2020 - [info] GTID failover mode = 0 Thu Jan 9 15:19:54 2020 - [info] Dead Servers: Thu Jan 9 15:19:54 2020 - [info] 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:54 2020 - [info] Alive Servers: Thu Jan 9 15:19:54 2020 - [info] 192.168.56.152(192.168.56.152:3306) Thu Jan 9 15:19:54 2020 - [info] 192.168.56.153(192.168.56.153:3306) Thu Jan 9 15:19:54 2020 - [info] Alive Slaves: Thu Jan 9 15:19:54 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:54 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:54 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:54 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:54 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:54 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:54 2020 - [info] Checking slave configurations.. Thu Jan 9 15:19:54 2020 - [info] Checking replication filtering settings.. Thu Jan 9 15:19:54 2020 - [info] Replication filtering check ok. Thu Jan 9 15:19:54 2020 - [info] Master is down! Thu Jan 9 15:19:54 2020 - [info] Terminating monitoring script. Thu Jan 9 15:19:54 2020 - [info] Got exit code 20 (Master dead). Thu Jan 9 15:19:54 2020 - [info] MHA::MasterFailover version 0.57. Thu Jan 9 15:19:54 2020 - [info] Starting master failover. Thu Jan 9 15:19:54 2020 - [info] Thu Jan 9 15:19:54 2020 - [info] * Phase 1: Configuration Check Phase.. Thu Jan 9 15:19:54 2020 - [info] Thu Jan 9 15:19:55 2020 - [info] GTID failover mode = 0 Thu Jan 9 15:19:55 2020 - [info] Dead Servers: Thu Jan 9 15:19:55 2020 - [info] 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:55 2020 - [info] Checking master reachability via MySQL(double check)... Thu Jan 9 15:19:55 2020 - [info] ok. Thu Jan 9 15:19:55 2020 - [info] Alive Servers: Thu Jan 9 15:19:55 2020 - [info] 192.168.56.152(192.168.56.152:3306) Thu Jan 9 15:19:55 2020 - [info] 192.168.56.153(192.168.56.153:3306) Thu Jan 9 15:19:55 2020 - [info] Alive Slaves: Thu Jan 9 15:19:55 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:55 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:55 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:55 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:55 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:55 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:55 2020 - [info] Starting Non-GTID based failover. Thu Jan 9 15:19:55 2020 - [info] Thu Jan 9 15:19:55 2020 - [info] ** Phase 1: Configuration Check Phase completed. Thu Jan 9 15:19:55 2020 - [info] Thu Jan 9 15:19:55 2020 - [info] * Phase 2: Dead Master Shutdown Phase.. Thu Jan 9 15:19:55 2020 - [info] Thu Jan 9 15:19:55 2020 - [info] Forcing shutdown so that applications never connect to the current master.. Thu Jan 9 15:19:55 2020 - [info] Executing master IP deactivation script: Thu Jan 9 15:19:55 2020 - [info] /mha/scripts/master_ip_failover --orig_master_host=192.168.56.151 --orig_master_ip=192.168.56.151 --orig_master_port=3306 --command=stopssh --ssh_user=mhauser Thu Jan 9 15:19:55 2020 - [info] done. Thu Jan 9 15:19:55 2020 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Thu Jan 9 15:19:55 2020 - [info] * Phase 2: Dead Master Shutdown Phase completed. Thu Jan 9 15:19:55 2020 - [info] Thu Jan 9 15:19:55 2020 - [info] * Phase 3: Master Recovery Phase.. Thu Jan 9 15:19:55 2020 - [info] Thu Jan 9 15:19:55 2020 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Thu Jan 9 15:19:55 2020 - [info] Thu Jan 9 15:19:55 2020 - [info] The latest binary log file/position on all slaves is mysql-bin.000006:342 Thu Jan 9 15:19:55 2020 - [info] Latest slaves (Slaves that received relay log files to the latest): Thu Jan 9 15:19:55 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:55 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:55 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:55 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:55 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:55 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:55 2020 - [info] The oldest binary log file/position on all slaves is mysql-bin.000006:342 Thu Jan 9 15:19:55 2020 - [info] Oldest slaves: Thu Jan 9 15:19:55 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:55 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:55 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:55 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:55 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:55 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:55 2020 - [info] Thu Jan 9 15:19:55 2020 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase.. Thu Jan 9 15:19:55 2020 - [info] Thu Jan 9 15:19:55 2020 - [info] Fetching dead master's binary logs.. Thu Jan 9 15:19:55 2020 - [info] Executing command on the dead master 192.168.56.151(192.168.56.151:3306): save_binary_logs --command=save --start_file=mysql-bin.000006 --start_pos=342 --binlog_dir=/var/lib/mysql --output_file=/mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.57 Creating /mha if not exists.. ok. Concat binary/relay logs from mysql-bin.000006 pos 342 to mysql-bin.000006 EOF into /mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog .. Binlog Checksum enabled Dumping binlog format description event, from position 0 to 256.. ok. Dumping effective binlog data from /var/lib/mysql/mysql-bin.000006 position 342 to tail(365).. ok. Binlog Checksum enabled Concat succeeded. Thu Jan 9 15:19:56 2020 - [info] scp from mhauser@192.168.56.151:/mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog to local:/mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog succeeded. Thu Jan 9 15:19:56 2020 - [info] HealthCheck: SSH to 192.168.56.152 is reachable. Thu Jan 9 15:19:56 2020 - [info] HealthCheck: SSH to 192.168.56.153 is reachable. Thu Jan 9 15:19:56 2020 - [info] Thu Jan 9 15:19:56 2020 - [info] * Phase 3.3: Determining New Master Phase.. Thu Jan 9 15:19:56 2020 - [info] Thu Jan 9 15:19:56 2020 - [info] Finding the latest slave that has all relay logs for recovering other slaves.. Thu Jan 9 15:19:56 2020 - [info] All slaves received relay logs to the same position. No need to resync each other. Thu Jan 9 15:19:56 2020 - [info] Searching new master from slaves.. Thu Jan 9 15:19:56 2020 - [info] Candidate masters from the configuration file: Thu Jan 9 15:19:56 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:56 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:56 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:56 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jan 9 15:19:56 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Thu Jan 9 15:19:56 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jan 9 15:19:56 2020 - [info] Non-candidate masters: Thu Jan 9 15:19:56 2020 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Thu Jan 9 15:19:56 2020 - [info] New master is 192.168.56.152(192.168.56.152:3306) Thu Jan 9 15:19:56 2020 - [info] Starting master failover.. Thu Jan 9 15:19:56 2020 - [info] From: 192.168.56.151(192.168.56.151:3306) (current master) +--192.168.56.152(192.168.56.152:3306) +--192.168.56.153(192.168.56.153:3306)
To: 192.168.56.152(192.168.56.152:3306) (new master) +--192.168.56.153(192.168.56.153:3306) Thu Jan 9 15:19:56 2020 - [info] Thu Jan 9 15:19:56 2020 - [info] * Phase 3.3: New Master Diff Log Generation Phase.. Thu Jan 9 15:19:56 2020 - [info] Thu Jan 9 15:19:56 2020 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Thu Jan 9 15:19:56 2020 - [info] Sending binlog.. Thu Jan 9 15:19:56 2020 - [info] scp from local:/mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog to mhauser@192.168.56.152:/mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog succeeded. Thu Jan 9 15:19:56 2020 - [info] Thu Jan 9 15:19:56 2020 - [info] * Phase 3.4: Master Log Apply Phase.. Thu Jan 9 15:19:56 2020 - [info] Thu Jan 9 15:19:56 2020 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed. Thu Jan 9 15:19:56 2020 - [info] Starting recovery on 192.168.56.152(192.168.56.152:3306).. Thu Jan 9 15:19:56 2020 - [info] Generating diffs succeeded. Thu Jan 9 15:19:56 2020 - [info] Waiting until all relay logs are applied. Thu Jan 9 15:19:56 2020 - [info] done. Thu Jan 9 15:19:56 2020 - [info] Getting slave status.. Thu Jan 9 15:19:56 2020 - [info] This slave(192.168.56.152)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000006:342). No need to recover from Exec_Master_Log_Pos. Thu Jan 9 15:19:56 2020 - [info] Connecting to the target slave host 192.168.56.152, running recover script.. Thu Jan 9 15:19:56 2020 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user='mha' --slave_host=192.168.56.152 --slave_ip=192.168.56.152 --slave_port=3306 --apply_files=/mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog --workdir=/mha --target_version=10.2.30-MariaDB-log --timestamp=20200109151954 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.57 --slave_pass=xxx Thu Jan 9 15:19:56 2020 - [info] MySQL client version is 10.2.30. Using --binary-mode. Applying differential binary/relay log files /mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog on 192.168.56.152:3306. This may take long time... Applying log files succeeded. Thu Jan 9 15:19:56 2020 - [info] All relay logs were successfully applied. Thu Jan 9 15:19:56 2020 - [info] Getting new master's binlog name and position.. Thu Jan 9 15:19:56 2020 - [info] mysql-bin.000005:328 Thu Jan 9 15:19:56 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.56.152', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=328, MASTER_USER='rep', MASTER_PASSWORD='xxx'; Thu Jan 9 15:19:56 2020 - [info] Executing master IP activate script: Thu Jan 9 15:19:56 2020 - [info] /mha/scripts/master_ip_failover --command=start --ssh_user=mhauser --orig_master_host=192.168.56.151 --orig_master_ip=192.168.56.151 --orig_master_port=3306 --new_master_host=192.168.56.152 --new_master_ip=192.168.56.152 --new_master_port=3306 --new_master_user='mha' --new_master_password=xxx Set read_only=0 on the new master. Thu Jan 9 15:19:57 2020 - [info] OK. Thu Jan 9 15:19:57 2020 - [info] ** Finished master recovery successfully. Thu Jan 9 15:19:57 2020 - [info] * Phase 3: Master Recovery Phase completed. Thu Jan 9 15:19:57 2020 - [info] Thu Jan 9 15:19:57 2020 - [info] * Phase 4: Slaves Recovery Phase.. Thu Jan 9 15:19:57 2020 - [info] Thu Jan 9 15:19:57 2020 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase.. Thu Jan 9 15:19:57 2020 - [info] Thu Jan 9 15:19:57 2020 - [info] -- Slave diff file generation on host 192.168.56.153(192.168.56.153:3306) started, pid: 6820. Check tmp log /mha/192.168.56.153_3306_20200109151954.log if it takes time.. Thu Jan 9 15:19:58 2020 - [info] Thu Jan 9 15:19:58 2020 - [info] Log messages from 192.168.56.153 ... Thu Jan 9 15:19:58 2020 - [info] Thu Jan 9 15:19:57 2020 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Thu Jan 9 15:19:58 2020 - [info] End of log messages from 192.168.56.153. Thu Jan 9 15:19:58 2020 - [info] -- 192.168.56.153(192.168.56.153:3306) has the latest relay log events. Thu Jan 9 15:19:58 2020 - [info] Generating relay diff files from the latest slave succeeded. Thu Jan 9 15:19:58 2020 - [info] Thu Jan 9 15:19:58 2020 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase.. Thu Jan 9 15:19:58 2020 - [info] Thu Jan 9 15:19:58 2020 - [info] -- Slave recovery on host 192.168.56.153(192.168.56.153:3306) started, pid: 6822. Check tmp log /mha/192.168.56.153_3306_20200109151954.log if it takes time.. Thu Jan 9 15:19:59 2020 - [info] Thu Jan 9 15:19:59 2020 - [info] Log messages from 192.168.56.153 ... Thu Jan 9 15:19:59 2020 - [info] Thu Jan 9 15:19:58 2020 - [info] Sending binlog.. Thu Jan 9 15:19:58 2020 - [info] scp from local:/mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog to mhauser@192.168.56.153:/mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog succeeded. Thu Jan 9 15:19:58 2020 - [info] Starting recovery on 192.168.56.153(192.168.56.153:3306).. Thu Jan 9 15:19:58 2020 - [info] Generating diffs succeeded. Thu Jan 9 15:19:58 2020 - [info] Waiting until all relay logs are applied. Thu Jan 9 15:19:58 2020 - [info] done. Thu Jan 9 15:19:58 2020 - [info] Getting slave status.. Thu Jan 9 15:19:58 2020 - [info] This slave(192.168.56.153)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000006:342). No need to recover from Exec_Master_Log_Pos. Thu Jan 9 15:19:58 2020 - [info] Connecting to the target slave host 192.168.56.153, running recover script.. Thu Jan 9 15:19:58 2020 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user='mha' --slave_host=192.168.56.153 --slave_ip=192.168.56.153 --slave_port=3306 --apply_files=/mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog --workdir=/mha --target_version=10.2.30-MariaDB-log --timestamp=20200109151954 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.57 --slave_pass=xxx Thu Jan 9 15:19:58 2020 - [info] MySQL client version is 10.2.30. Using --binary-mode. Applying differential binary/relay log files /mha/saved_master_binlog_from_192.168.56.151_3306_20200109151954.binlog on 192.168.56.153:3306. This may take long time... Applying log files succeeded. Thu Jan 9 15:19:58 2020 - [info] All relay logs were successfully applied. Thu Jan 9 15:19:58 2020 - [info] Resetting slave 192.168.56.153(192.168.56.153:3306) and starting replication from the new master 192.168.56.152(192.168.56.152:3306).. Thu Jan 9 15:19:58 2020 - [info] Executed CHANGE MASTER. Thu Jan 9 15:19:58 2020 - [info] Slave started. Thu Jan 9 15:19:59 2020 - [info] End of log messages from 192.168.56.153. Thu Jan 9 15:19:59 2020 - [info] -- Slave recovery on host 192.168.56.153(192.168.56.153:3306) succeeded. Thu Jan 9 15:19:59 2020 - [info] All new slave servers recovered successfully. Thu Jan 9 15:19:59 2020 - [info] Thu Jan 9 15:19:59 2020 - [info] * Phase 5: New master cleanup phase.. Thu Jan 9 15:19:59 2020 - [info] Thu Jan 9 15:19:59 2020 - [info] Resetting slave info on the new master.. Thu Jan 9 15:19:59 2020 - [info] 192.168.56.152: Resetting slave info succeeded. Thu Jan 9 15:19:59 2020 - [info] Master failover to 192.168.56.152(192.168.56.152:3306) completed successfully. Thu Jan 9 15:19:59 2020 - [info]
----- Failover Report -----
mha: MySQL Master failover 192.168.56.151(192.168.56.151:3306) to 192.168.56.152(192.168.56.152:3306) succeeded
Master 192.168.56.151(192.168.56.151:3306) is down!
Check MHA Manager logs at mhamgr:/mha/manager.log for details.
Started automated(non-interactive) failover. Invalidated master IP address on 192.168.56.151(192.168.56.151:3306) The latest slave 192.168.56.152(192.168.56.152:3306) has all relay logs for recovery. Selected 192.168.56.152(192.168.56.152:3306) as a new master. 192.168.56.152(192.168.56.152:3306): OK: Applying all logs succeeded. 192.168.56.152(192.168.56.152:3306): OK: Activated master IP address. 192.168.56.153(192.168.56.153:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.56.153(192.168.56.153:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.56.152(192.168.56.152:3306) 192.168.56.152(192.168.56.152:3306): Resetting slave info succeeded. Master failover to 192.168.56.152(192.168.56.152:3306) completed successfully. |
slave01 :
|
cat check.sh
mysql -uroot -proot1122 -e "show variables like 'read_only'" mysql -uroot -proot1122 -e "show master status ;" mysql -uroot -proot1122 -e "show slave status\G ;" mysql -uroot -proot1122 -e "show processlist" |
|
[root@slave01 ~]# ./check.sh +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 328 | | | +------------------+----------+--------------+------------------+ +----+-------------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 87 | rep | slave02:59222 | NULL | Binlog Dump | 2111 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 | | 95 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 | +----+-------------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ |
slave02 :
|
[root@slave02 ~]# ./check.sh +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 328 | | | +------------------+----------+--------------+------------------+ *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.152 Master_User: rep Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 328 Relay_Log_File: slave02-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000005 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: 328 Relay_Log_Space: 866 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: 2 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it +----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 77 | system user | | NULL | Slave_IO | 20 | Waiting for master to send event | NULL | 0.000 | | 78 | system user | | NULL | Slave_SQL | 20 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 | | 82 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 | +----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+ |
6.3 정상 동작 확인
새로 마스터로 승격 된 slave01과 slave02가 정상적으로 동기화 하는지 확인
slave01 :
|
[root@slave01 ~]# mysql -uroot -proot1122 -e "create database aftermaster;" [root@slave01 ~]# mysql -uroot -proot1122 -e "create table aftermaster.test(id int);" [root@slave01 ~]# mysql -uroot -proot1122 -e "insert into aftermaster.test values(1) ;" [root@slave01 ~]# mysql -uroot -proot1122 -e "select * from aftermaster.test ;" +------+ | id | +------+ | 1 | +------+ |
slave02 :
|
[root@slave02 ~]# mysql -uroot -proot1122 -e "show databases;" +--------------------+ | Database | +--------------------+ | aftermaster | | information_schema | | mysql | | performance_schema | | reptest | | test | +--------------------+ [root@slave02 ~]# mysql -uroot -proot1122 -e "select * from aftermaster.test ;" +------+ | id | +------+ | 1 | +------+ |
7. MHA slave 장애 상황 테스트
6번 까지 테스트를 했으면 서버 두 대만이 동기화 중이다.
master : slave01
slave : slave02
운영 중인 상태에서 slave에 장애가 발생 한다면 MHA는 어떤 동작을 하는지 확인해 본다.
management(mhauser) :
log를 계속해서 보고 있는 상태
slave02 :
|
[root@slave02 ~]# service mysql stop Stopping mysql (via systemctl): [ OK ] |
management(mhauser) :
위와 같이 slave02가 종료 되어서 management 서버의 manage.log에는 변화가 없다.
여기까지 테스트를 했으면 , management 서버의 /etc/mha.cnf 파일과 /mha/scripts 밑의 두 개의 파일을 backup 받은 후에
4번에서 save해 놓았던 VM으로 교체 후, 백업 받은 /etc/mha.cnf 파일과 /mha/scripts 밑의 파일을 copy해서 넣자.
8. 수동 fail-over 테스트
5번 시나리오가 운영 중에 master, slave를 교체 하는 작업이었다면,
8번 시나리오는 운영 중에 master에 문제가 생겼는데 자동으로 failover가 되지 않을 경우, 또는 필요에 의해서 수동으로 failover를 시키는 방법이다.
master는 살아 있지만 crash 났다고 가정하고, 수동으로 failover를 시킨다.
5번과 마찬가지로 MHA가 기동되고 있지 않아야 한다.
management (mhauser) :
|
status mha (pid:2169) is running(0:PING_OK), master:192.168.56.151
stop Stopped mha successfully. [1]+ Exit 1 /usr/local/bin/masterha_manager --conf=/etc/mha.cnf
[mhauser@mhamgr ~]$ masterha_master_switch --master_state=alive --conf=/etc/mha.cnf Wed Jan 8 16:15:18 2020 - [info] MHA::MasterRotate version 0.57. Wed Jan 8 16:15:18 2020 - [info] Starting online master switch.. Wed Jan 8 16:15:18 2020 - [info] Wed Jan 8 16:15:18 2020 - [info] * Phase 1: Configuration Check Phase.. Wed Jan 8 16:15:18 2020 - [info] Wed Jan 8 16:15:18 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Wed Jan 8 16:15:18 2020 - [info] Reading application default configuration from /etc/mha.cnf.. Wed Jan 8 16:15:18 2020 - [info] Reading server configuration from /etc/mha.cnf.. Wed Jan 8 16:15:19 2020 - [info] GTID failover mode = 0 Wed Jan 8 16:15:19 2020 - [info] Current Alive Master: 192.168.56.151(192.168.56.151:3306) Wed Jan 8 16:15:19 2020 - [info] Alive Slaves: Wed Jan 8 16:15:19 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Wed Jan 8 16:15:19 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Wed Jan 8 16:15:19 2020 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jan 8 16:15:19 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Wed Jan 8 16:15:19 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Wed Jan 8 16:15:19 2020 - [info] Primary candidate for the new Master (candidate_master is set)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.56.151(192.168.56.151:3306)? (YES/no): yes Wed Jan 8 16:15:36 2020 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Wed Jan 8 16:15:36 2020 - [info] ok. Wed Jan 8 16:15:36 2020 - [info] Checking MHA is not monitoring or doing failover.. Wed Jan 8 16:15:36 2020 - [info] Checking replication health on 192.168.56.152.. Wed Jan 8 16:15:36 2020 - [info] ok. Wed Jan 8 16:15:36 2020 - [info] Checking replication health on 192.168.56.153.. Wed Jan 8 16:15:36 2020 - [info] ok. Wed Jan 8 16:15:36 2020 - [info] Searching new master from slaves.. Wed Jan 8 16:15:36 2020 - [info] Candidate masters from the configuration file: Wed Jan 8 16:15:36 2020 - [info] 192.168.56.152(192.168.56.152:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Wed Jan 8 16:15:36 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Wed Jan 8 16:15:36 2020 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jan 8 16:15:36 2020 - [info] 192.168.56.153(192.168.56.153:3306) Version=10.2.30-MariaDB-log (oldest major version between slaves) log-bin:enabled Wed Jan 8 16:15:36 2020 - [info] Replicating from 192.168.56.151(192.168.56.151:3306) Wed Jan 8 16:15:36 2020 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jan 8 16:15:36 2020 - [info] Non-candidate masters: Wed Jan 8 16:15:36 2020 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Wed Jan 8 16:15:36 2020 - [info] From: 192.168.56.151(192.168.56.151:3306) (current master) +--192.168.56.152(192.168.56.152:3306) +--192.168.56.153(192.168.56.153:3306)
To: 192.168.56.152(192.168.56.152:3306) (new master) +--192.168.56.153(192.168.56.153:3306)
Starting master switch from 192.168.56.151(192.168.56.151:3306) to 192.168.56.152(192.168.56.152:3306)? (yes/NO): yes Wed Jan 8 16:15:46 2020 - [info] Checking whether 192.168.56.152(192.168.56.152:3306) is ok for the new master.. Wed Jan 8 16:15:46 2020 - [info] ok. Wed Jan 8 16:15:46 2020 - [info] ** Phase 1: Configuration Check Phase completed. Wed Jan 8 16:15:46 2020 - [info] Wed Jan 8 16:15:46 2020 - [info] * Phase 2: Rejecting updates Phase.. Wed Jan 8 16:15:46 2020 - [info] master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes Wed Jan 8 16:16:22 2020 - [info] Locking all tables on the orig master to reject updates from everybody (including root): Wed Jan 8 16:16:22 2020 - [info] Executing FLUSH TABLES WITH READ LOCK.. Wed Jan 8 16:16:22 2020 - [info] ok. Wed Jan 8 16:16:22 2020 - [info] Orig master binlog:pos is mysql-bin.000003:342. Wed Jan 8 16:16:22 2020 - [info] Waiting to execute all relay logs on 192.168.56.152(192.168.56.152:3306).. Wed Jan 8 16:16:22 2020 - [info] master_pos_wait(mysql-bin.000003:342) completed on 192.168.56.152(192.168.56.152:3306). Executed 0 events. Wed Jan 8 16:16:22 2020 - [info] done. Wed Jan 8 16:16:22 2020 - [info] Getting new master's binlog name and position.. Wed Jan 8 16:16:22 2020 - [info] mysql-bin.000004:328 Wed Jan 8 16:16:22 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.56.152', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=328, MASTER_USER='rep', MASTER_PASSWORD='xxx'; Wed Jan 8 16:16:22 2020 - [info] Setting read_only=0 on 192.168.56.152(192.168.56.152:3306).. Wed Jan 8 16:16:22 2020 - [info] ok. Wed Jan 8 16:16:22 2020 - [info] Wed Jan 8 16:16:22 2020 - [info] * Switching slaves in parallel.. Wed Jan 8 16:16:22 2020 - [info] Wed Jan 8 16:16:22 2020 - [info] -- Slave switch on host 192.168.56.153(192.168.56.153:3306) started, pid: 2285 Wed Jan 8 16:16:22 2020 - [info] Wed Jan 8 16:16:23 2020 - [info] Log messages from 192.168.56.153 ... Wed Jan 8 16:16:23 2020 - [info] Wed Jan 8 16:16:22 2020 - [info] Waiting to execute all relay logs on 192.168.56.153(192.168.56.153:3306).. Wed Jan 8 16:16:22 2020 - [info] master_pos_wait(mysql-bin.000003:342) completed on 192.168.56.153(192.168.56.153:3306). Executed 0 events. Wed Jan 8 16:16:22 2020 - [info] done. Wed Jan 8 16:16:22 2020 - [info] Resetting slave 192.168.56.153(192.168.56.153:3306) and starting replication from the new master 192.168.56.152(192.168.56.152:3306).. Wed Jan 8 16:16:23 2020 - [info] Executed CHANGE MASTER. Wed Jan 8 16:16:23 2020 - [info] Slave started. Wed Jan 8 16:16:23 2020 - [info] End of log messages from 192.168.56.153 ... Wed Jan 8 16:16:23 2020 - [info] Wed Jan 8 16:16:23 2020 - [info] -- Slave switch on host 192.168.56.153(192.168.56.153:3306) succeeded. Wed Jan 8 16:16:23 2020 - [info] Unlocking all tables on the orig master: Wed Jan 8 16:16:23 2020 - [info] Executing UNLOCK TABLES.. Wed Jan 8 16:16:23 2020 - [info] ok. Wed Jan 8 16:16:23 2020 - [info] All new slave servers switched successfully. Wed Jan 8 16:16:23 2020 - [info] Wed Jan 8 16:16:23 2020 - [info] * Phase 5: New master cleanup phase.. Wed Jan 8 16:16:23 2020 - [info] Wed Jan 8 16:16:24 2020 - [info] 192.168.56.152: Resetting slave info succeeded. Wed Jan 8 16:16:24 2020 - [info] Switching master to 192.168.56.152(192.168.56.152:3306) completed successfully.
|
|
slave01 :
|
mysql -uroot -proot1122 -e "show variables like 'read_only' ;" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ |
slave02 :
|
mysql -uroot -proot1122 -e "show slave status\G;" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.152 Master_User: rep Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 328 Relay_Log_File: slave02-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...... Seconds_Behind_Master: 0 ...... SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it |