MHA(Master HA)是一款开源的MySQL的高可用程序，它为MySQL主从复制架构提供了automating master failover 功能。MHA在监控到master节点故障时，会提升其中拥有最新数据的slave节点成为新的master节点，在此期间，MHA会通过与其它从节点获取额外信息来避免一致性方面的问题。MHA还提供了master节点的在线切换功能，即按需切换master/slave节点。
相较于其它HA软件，MHA的目的在于维持MySQL Replication中Master库的高可用性，其最大特点是可以修复多个Slave之间的差异日志，最终使所有Slave保持数据一致，然后从中选择一个充当新的Master，并将其它Slave指向它。
(1)从宕机崩溃的master保存二进制日志事件(binlog events);
(2)识别含有最新更新的slave；
(3)应用差异的中继日志(relay log)到其他的slave；
(4)应用从master保存的二进制日志事件(binlog events)；
(5)提升一个slave为新的master；
(6)使其他的slave连接新的master进行复制；
环境 ：A主机/B主机1/B主机2/B主机3/mha_manager
A：192.168.13.150
B：192.168.13.151
B：192.168.13.152
B：192.168.13.153
mha_manager: 192.168.13.160
✨步骤1：5机器安装mysql 并主从复制 ABBB，删除匿名用户，设置root密码
yum install -y mysql-server mysql (manager 不用装)
13.150机器 开启二进制binlog
vim /etc/my.cnf #加入一下三条
server_id=1
log_bin=binlog
log_bin_index=binlog.index
重启数据库 你的每次操作都回留下log 以便利从服务器操作
删除匿名用户
mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | 13-150 | |
| root | 127.0.0.1 | |
| | localhost | |
| | 13-150 | |
+------+-----------+----------+
5 rows in set (0.00 sec)
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | 13-150 | |
| root | 127.0.0.1 | |
+------+-----------+----------+
3 rows in set (0.00 sec)
mysql> delete from mysql.user where host='13-150';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where host='127.0.0.1';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host,password from mysql.user ;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
+------+-----------+----------+
1 row in set (0.00 sec)
加入权限
mysql> grant all on *.* to `root`@`192.168.13.%`;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user ;
+------+--------------+----------+
| user | host | password |
+------+--------------+----------+
| root | localhost | |
| root | 192.168.13.% | |
+------+--------------+----------+
2 rows in set (0.00 sec)
设置密码
mysql> update mysql.user set password=password('123');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select user,host,password from mysql.user ;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 192.168.13.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+--------------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> flush privileges; #使其生效
做主从复制 还需要一个账号
mysql> grant replication slave on *.* to 'sko'@'%' identified by 'skoo';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user ;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 192.168.13.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| sko | % | *FA2494A90A1C7995503F974ACDE08139BD31AC51 |
+------+--------------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
主的机器 部署到此结束
第一台从服务器 直接修改配置文件
vim /etc/my.cnf(开启二进制) 然后重启mysql
查看主binlog记录位置
mysql> show master status; 查询二进制文件写到哪里了
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 | 916 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
在第一台从服务器上设置
mysql> slave stop; #停止
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.13.150', master_user='sko', master_password='skoo', master_log_file='binlog.000003';
Query OK, 0 rows affected (0.01 sec)
mysql> slave start; #开始
查看主从复制是否成功
mysql>show slave status \G #查看是否主从成功
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.13.150
Master_User: sko
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 916
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1058
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.....
.....
....
1 row in set (0.00 sec)
第二台从服务器
mysql> slave stop；
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.13.150', master_user='sko', master_password='skoo', master_log_file='binlog.000003';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| | 13-152 |
| root | 13-152 |
| | localhost |
| root | localhost |
+------+-----------+
5 rows in set (0.00 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.13.150
Master_User: sko
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 916
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1058
Relay_Master_Log_File: binlog.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: 916
Relay_Log_Space: 1214
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:
1 row in set (0.00 sec)
mysql> select user,host from mysql.user;
+------+--------------+
| user | host |
+------+--------------+
| sko | % |
| root | 13-152 |
| root | 192.168.13.% |
| root | localhost |
+------+--------------+
4 rows in set (0.00 sec)
剩余几台也这么操作 此处不在记录
清除主从复制
1、slave stop;
2、reset slave;
3、重启 (上线注意安全)
五台机器互相密钥登陆
for i in 151 152 153 160;do ssh-copy-id -i .ssh/id_rsa.pub root@192.168.13.$i ;done
for i in 150 152 153 160;do ssh-copy-id -i .ssh/id_rsa.pub root@192.168.13.$i ;done
for i in 150 151 153 160;do ssh-copy-id -i .ssh/id_rsa.pub root@192.168.13.$i ;done
for i in 151 152 150 160;do ssh-copy-id -i .ssh/id_rsa.pub root@192.168.13.$i ;done
for i in 151 152 153 150;do ssh-copy-id -i .ssh/id_rsa.pub root@192.168.13.$i ;done
✨步骤2：安装node，所有节点都需要安装
MHA是由perl编写的，所以要安装perl语言环境
yum install -y perl-* (五台都需要安装)
上传软件包 mha4mysql-node-0.54-0.el6.noarch.rpm (五台都安装)
[root@13-160 mha_soft]# rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm (安装成功)
Preparing... ########################################### [100%]
1:mha4mysql-node ########################################### [100%]
✨步骤3:安装manager，需要做配置 所有的主机相互建立信任 使用manager监控管理mysql主从复制
安装server端 在13.160上
上传软件包依赖:
ompat-db43-4.3.29-15.el6.x86_64.rpm
perl-Compress-Raw-Bzip2-2.052-1.el6.rf.x86_64.rpm
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-1.el6.rfx.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MailTools-2.09-1.el6.rfx.noarch.rpm
perl-MIME-Lite-3.027-1.el6.rfx.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
perl-Params-Validate-0.95-1.el6.rfx.x86_64.rpm
perl-Time-HiRes-1.9724-1.el6.rfx.x86_64.rpm
yum -y localinstall ./*
即可全部安装上
上传软件包 : mha4mysql-manager-0.55-0.el6.noarch.rpm
[root@13-160 mha_soft]# rpm -ivh mha4mysql-manager-0.55-0.el6.noarch.rpm
Preparing... ########################################### [100%]
1:mha4mysql-manager ########################################### [100
接下来需要配置MHA高可用
创建目录 mkdir /etc/mha/
创建文件 vim mha.cnf
[server default]
#mysql_admin and password
user=root #mysql 用户
password=123 #mysql 密码
#work_dir
manager_workdir=/etc/mha #工作目录
#mha_log
manager_log=/etc/mha/manager.log #log日志
#ssh connetion account
ssh_user=root #链接账号 建立好信任的用户(免除秘钥)
#AB copy account and password #(主从复制账号密码)
repl_user=sko
repl——password=skoo
[server1] #管理机器1
hosername=192.168.13.150
ssh_port=22
master_binlog_dir=/var/lib/mysql
candidate_master=1
[server2] #机器2
hosername=192.168.13.151
ssh_port=22
master_binlog_dir=/var/lib/mysql #master二进制日志路径
candidate_master=1 #参与选举 1为参与
[server3] #机器3
hosername=192.168.13.152
ssh_port=22
master_binlog_dir=/var/lib/mysql
candidate_master=1
[server4] #机器4
hosername=192.168.13.152
ssh_port=22
master_binlog_dir=/var/lib/mysql
candidate_master=1
检查配置文件
命令：master_check_ssh --conf=/etc/mha/mha.cnf
此处如果报错找不到配置文件，(/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm)
请仔细检查配置文件 是否有特殊字符等 或者手动重新写入
[root@13-160 mha]# masterha_check_ssh --conf=/etc/mha/mha.cnf
Thu Aug 29 01:56:44 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Aug 29 01:56:44 2019 - [info] Reading application default configurations from /etc/mha/mha.cnf..
Thu Aug 29 01:56:44 2019 - [info] Reading server configurations from /etc/mha/mha.cnf..
Thu Aug 29 01:56:44 2019 - [info] Starting SSH connection tests..
Thu Aug 29 01:56:51 2019 - [debug]
Thu Aug 29 01:56:46 2019 - [debug] Connecting via SSH from root@192.168.13.153(192.168.13.153:22) to root@192.168.13.150(192.168.13.150:22)..
Thu Aug 29 01:56:50 2019 - [debug] ok.
Thu Aug 29 01:56:50 2019 - [debug] Connecting via SSH from root@192.168.13.153(192.168.13.153:22) to root@192.168.13.151(192.168.13.151:22)..
Thu Aug 29 01:56:50 2019 - [debug] ok.
Thu Aug 29 01:56:50 2019 - [debug] Connecting via SSH from root@192.168.13.153(192.168.13.153:22) to root@192.168.13.152(192.168.13.152:22)..
Thu Aug 29 01:56:51 2019 - [debug] ok.
Thu Aug 29 01:56:59 2019 - [debug]
Thu Aug 29 01:56:44 2019 - [debug] Connecting via SSH from root@192.168.13.150(192.168.13.150:22) to root@192.168.13.151(192.168.13.151:22)..
Thu Aug 29 01:56:48 2019 - [debug] ok.
Thu Aug 29 01:56:48 2019 - [debug] Connecting via SSH from root@192.168.13.150(192.168.13.150:22) to root@192.168.13.152(192.168.13.152:22)..
Thu Aug 29 01:56:54 2019 - [debug] ok.
Thu Aug 29 01:56:54 2019 - [debug] Connecting via SSH from root@192.168.13.150(192.168.13.150:22) to root@192.168.13.153(192.168.13.153:22)..
Thu Aug 29 01:56:59 2019 - [debug] ok.
Thu Aug 29 01:56:59 2019 - [debug]
Thu Aug 29 01:56:45 2019 - [debug] Connecting via SSH from root@192.168.13.151(192.168.13.151:22) to root@192.168.13.150(192.168.13.150:22)..
Thu Aug 29 01:56:48 2019 - [debug] ok.
Thu Aug 29 01:56:48 2019 - [debug] Connecting via SSH from root@192.168.13.151(192.168.13.151:22) to root@192.168.13.152(192.168.13.152:22)..
Thu Aug 29 01:56:54 2019 - [debug] ok.
Thu Aug 29 01:56:54 2019 - [debug] Connecting via SSH from root@192.168.13.151(192.168.13.151:22) to root@192.168.13.153(192.168.13.153:22)..
Thu Aug 29 01:56:59 2019 - [debug] ok.
Thu Aug 29 01:57:05 2019 - [debug]
Thu Aug 29 01:56:45 2019 - [debug] Connecting via SSH from root@192.168.13.152(192.168.13.152:22) to root@192.168.13.150(192.168.13.150:22)..
Thu Aug 29 01:56:54 2019 - [debug] ok.
Thu Aug 29 01:56:54 2019 - [debug] Connecting via SSH from root@192.168.13.152(192.168.13.152:22) to root@192.168.13.151(192.168.13.151:22)..
Thu Aug 29 01:57:00 2019 - [debug] ok.
Thu Aug 29 01:57:00 2019 - [debug] Connecting via SSH from root@192.168.13.152(192.168.13.152:22) to root@192.168.13.153(192.168.13.153:22)..
Thu Aug 29 01:57:05 2019 - [debug] ok.
Thu Aug 29 01:57:05 2019 - [info] All SSH connection tests passed successfully.
测试主从是否有问题
命令：[root@13-160 mha]# masterha_check_repl --conf=mha.cnf
[root@13-160 mha]# masterha_check_repl --conf=mha.cnf
Thu Aug 29 02:03:27 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Aug 29 02:03:27 2019 - [info] Reading application default configurations from mha.cnf..
Thu Aug 29 02:03:27 2019 - [info] Reading server configurations from mha.cnf..
Thu Aug 29 02:03:27 2019 - [info] MHA::MasterMonitor version 0.55.
Thu Aug 29 02:03:27 2019 - [info] Dead Servers:
Thu Aug 29 02:03:27 2019 - [info] Alive Servers:
Thu Aug 29 02:03:27 2019 - [info] 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:03:27 2019 - [info] 192.168.13.151(192.168.13.151:3306)
Thu Aug 29 02:03:27 2019 - [info] 192.168.13.152(192.168.13.152:3306)
Thu Aug 29 02:03:27 2019 - [info] 192.168.13.153(192.168.13.153:3306)
Thu Aug 29 02:03:27 2019 - [info] Alive Slaves:
Thu Aug 29 02:03:27 2019 - [info] 192.168.13.151(192.168.13.151:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:03:27 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:03:27 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:03:27 2019 - [info] 192.168.13.152(192.168.13.152:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:03:27 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:03:27 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:03:27 2019 - [info] 192.168.13.153(192.168.13.153:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:03:27 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:03:27 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:03:27 2019 - [info] Current Alive Master: 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:03:27 2019 - [info] Checking slave configurations..
Thu Aug 29 02:03:27 2019 - [info] read_only=1 is not set on slave 192.168.13.151(192.168.13.151:3306).
Thu Aug 29 02:03:27 2019 - [warning] relay_log_purge=0 is not set on slave 192.168.13.151(192.168.13.151:3306).
Thu Aug 29 02:03:27 2019 - [info] read_only=1 is not set on slave 192.168.13.152(192.168.13.152:3306).
Thu Aug 29 02:03:27 2019 - [warning] relay_log_purge=0 is not set on slave 192.168.13.152(192.168.13.152:3306).
Thu Aug 29 02:03:27 2019 - [info] read_only=1 is not set on slave 192.168.13.153(192.168.13.153:3306).
Thu Aug 29 02:03:27 2019 - [warning] relay_log_purge=0 is not set on slave 192.168.13.153(192.168.13.153:3306).
Thu Aug 29 02:03:27 2019 - [info] Checking replication filtering settings..
Thu Aug 29 02:03:27 2019 - [info] binlog_do_db= , binlog_ignore_db=
Thu Aug 29 02:03:27 2019 - [info] Replication filtering check ok.
Thu Aug 29 02:03:27 2019 - [info] Starting SSH connection tests..
Thu Aug 29 02:03:50 2019 - [info] All SSH connection tests passed successfully.
Thu Aug 29 02:03:50 2019 - [info] Checking MHA Node version..
Thu Aug 29 02:03:51 2019 - [info] Version check ok.
Thu Aug 29 02:03:51 2019 - [info] Checking SSH publickey authentication settings on the current master..
Thu Aug 29 02:03:52 2019 - [info] HealthCheck: SSH to 192.168.13.150 is reachable.
Thu Aug 29 02:03:52 2019 - [info] Master MHA Node version is 0.54.
Thu Aug 29 02:03:52 2019 - [info] Checking recovery script configurations on the current master..
Thu Aug 29 02:03:52 2019 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/var/tmp/save_binary_logs_test --manager_version=0.55 --start_file=binlog.000003
Thu Aug 29 02:03:52 2019 - [info] Connecting to root@192.168.13.150(192.168.13.150)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to binlog.000003
Thu Aug 29 02:03:52 2019 - [info] Master setting check done.
Thu Aug 29 02:03:52 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Aug 29 02:03:52 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.13.151 --slave_ip=192.168.13.151 --slave_port=3306 --workdir=/var/tmp --target_version=5.1.66-log --manager_version=0.55 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Aug 29 02:03:52 2019 - [info] Connecting to root@192.168.13.151(192.168.13.151:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mysqld-relay-bin.000004
Temporary relay log file is /var/lib/mysql/mysqld-relay-bin.000004
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Aug 29 02:03:52 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.13.152 --slave_ip=192.168.13.152 --slave_port=3306 --workdir=/var/tmp --target_version=5.1.66-log --manager_version=0.55 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Aug 29 02:03:52 2019 - [info] Connecting to root@192.168.13.152(192.168.13.152:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mysqld-relay-bin.000004
Temporary relay log file is /var/lib/mysql/mysqld-relay-bin.000004
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Aug 29 02:03:53 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.13.153 --slave_ip=192.168.13.153 --slave_port=3306 --workdir=/var/tmp --target_version=5.1.66-log --manager_version=0.55 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Aug 29 02:03:53 2019 - [info] Connecting to root@192.168.13.153(192.168.13.153:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mysqld-relay-bin.000003
Temporary relay log file is /var/lib/mysql/mysqld-relay-bin.000003
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Aug 29 02:03:53 2019 - [info] Slaves settings check done.
Thu Aug 29 02:03:53 2019 - [info]
192.168.13.150 (current master)
+--192.168.13.151
+--192.168.13.152
+--192.168.13.153
Thu Aug 29 02:03:53 2019 - [info] Checking replication health on 192.168.13.151..
Thu Aug 29 02:03:53 2019 - [info] ok.
Thu Aug 29 02:03:53 2019 - [info] Checking replication health on 192.168.13.152..
Thu Aug 29 02:03:53 2019 - [info] ok.
Thu Aug 29 02:03:53 2019 - [info] Checking replication health on 192.168.13.153..
Thu Aug 29 02:03:53 2019 - [info] ok.
Thu Aug 29 02:03:53 2019 - [warning] master_ip_failover_script is not defined.
Thu Aug 29 02:03:53 2019 - [warning] shutdown_script is not defined.
Thu Aug 29 02:03:53 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
接下来进行监控啦
masterha_manager --conf=/etc/mha/mha.cnf #开启之后一直占用终端所以扔到后台执行
ctrl + z mac系统 control+z
fg 将后台中的命令调至前台继续运行
bg 将一个在后台暂停的命令，变成继续执行
[root@13-160 mha]# masterha_manager --conf=/etc/mha/mha.cnf
Thu Aug 29 02:19:41 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Aug 29 02:19:41 2019 - [info] Reading application default configurations from /etc/mha/mha.cnf..
Thu Aug 29 02:19:41 2019 - [info] Reading server configurations from /etc/mha/mha.cnf..
^Z #control +z 了
[1]+ Stopped masterha_manager --conf=/etc/mha/mha.cnf
[root@13-160 mha]# bg
[1]+ masterha_manager --conf=/etc/mha/mha.cnf &
[root@13-160 mha]#
[root@13-160 mha]# jobs
[1]+ Running masterha_manager --conf=/etc/mha/mha.cnf &
[root@13-160 mha]#
mha.master_status.health #顾名思义 文件记录master是那台
manager.log #程序一直在ping主mysql_master，当主master没有响音便记录日志
✨步骤4 ：测试 干掉原来的主，看从能否选举新的主
13.150 操作命令：/etc/init.d/mysqld stop
13.160 manager.log 记录以下内容
[root@13-160 mha]# tailf manager.log
+--192.168.13.151
+--192.168.13.152
+--192.168.13.153
Thu Aug 29 02:34:57 2019 - [warning] master_ip_failover_script is not defined.
Thu Aug 29 02:34:57 2019 - [warning] shutdown_script is not defined.
Thu Aug 29 02:34:57 2019 - [info] Set master ping interval 3 seconds.
Thu Aug 29 02:34:57 2019 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Thu Aug 29 02:34:57 2019 - [info] Starting ping health check on 192.168.13.150(192.168.13.150:3306)..
Thu Aug 29 02:34:57 2019 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Thu Aug 29 02:35:15 2019 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Thu Aug 29 02:35:15 2019 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/var/tmp/save_binary_logs_test --manager_version=0.55 --binlog_prefix=binlog
Thu Aug 29 02:35:15 2019 - [info] HealthCheck: SSH to 192.168.13.150 is reachable.
Thu Aug 29 02:35:18 2019 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Thu Aug 29 02:35:18 2019 - [warning] Connection failed 1 time(s)..
Thu Aug 29 02:35:21 2019 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Thu Aug 29 02:35:21 2019 - [warning] Connection failed 2 time(s)..
Thu Aug 29 02:35:24 2019 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Thu Aug 29 02:35:24 2019 - [warning] Connection failed 3 time(s)..
Thu Aug 29 02:35:24 2019 - [warning] Master is not reachable from health checker!
Thu Aug 29 02:35:24 2019 - [warning] Master 192.168.13.150(192.168.13.150:3306) is not reachable!
Thu Aug 29 02:35:24 2019 - [warning] SSH is reachable.
Thu Aug 29 02:35:24 2019 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/mha.cnf again, and trying to connect to all servers to check server status..
Thu Aug 29 02:35:24 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Aug 29 02:35:24 2019 - [info] Reading application default configurations from /etc/mha/mha.cnf..
Thu Aug 29 02:35:24 2019 - [info] Reading server configurations from /etc/mha/mha.cnf..
Thu Aug 29 02:35:24 2019 - [info] Dead Servers:
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Alive Servers:
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.151(192.168.13.151:3306)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.152(192.168.13.152:3306)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.153(192.168.13.153:3306)
Thu Aug 29 02:35:24 2019 - [info] Alive Slaves:
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.151(192.168.13.151:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.152(192.168.13.152:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.153(192.168.13.153:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] Checking slave configurations..
Thu Aug 29 02:35:24 2019 - [info] read_only=1 is not set on slave 192.168.13.151(192.168.13.151:3306).
Thu Aug 29 02:35:24 2019 - [warning] relay_log_purge=0 is not set on slave 192.168.13.151(192.168.13.151:3306).
Thu Aug 29 02:35:24 2019 - [info] read_only=1 is not set on slave 192.168.13.152(192.168.13.152:3306).
Thu Aug 29 02:35:24 2019 - [warning] relay_log_purge=0 is not set on slave 192.168.13.152(192.168.13.152:3306).
Thu Aug 29 02:35:24 2019 - [info] read_only=1 is not set on slave 192.168.13.153(192.168.13.153:3306).
Thu Aug 29 02:35:24 2019 - [warning] relay_log_purge=0 is not set on slave 192.168.13.153(192.168.13.153:3306).
Thu Aug 29 02:35:24 2019 - [info] Checking replication filtering settings..
Thu Aug 29 02:35:24 2019 - [info] Replication filtering check ok.
Thu Aug 29 02:35:24 2019 - [info] Master is down!
Thu Aug 29 02:35:24 2019 - [info] Terminating monitoring script.
Thu Aug 29 02:35:24 2019 - [info] Got exit code 20 (Master dead).
Thu Aug 29 02:35:24 2019 - [info] MHA::MasterFailover version 0.55.
Thu Aug 29 02:35:24 2019 - [info] Starting master failover.
Thu Aug 29 02:35:24 2019 - [info]
Thu Aug 29 02:35:24 2019 - [info] * Phase 1: Configuration Check Phase..
Thu Aug 29 02:35:24 2019 - [info]
Thu Aug 29 02:35:24 2019 - [info] Dead Servers:
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Checking master reachability via mysql(double check)..
Thu Aug 29 02:35:24 2019 - [info] ok.
Thu Aug 29 02:35:24 2019 - [info] Alive Servers:
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.151(192.168.13.151:3306)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.152(192.168.13.152:3306)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.153(192.168.13.153:3306)
Thu Aug 29 02:35:24 2019 - [info] Alive Slaves:
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.151(192.168.13.151:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.152(192.168.13.152:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.153(192.168.13.153:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] ** Phase 1: Configuration Check Phase completed.
Thu Aug 29 02:35:24 2019 - [info]
Thu Aug 29 02:35:24 2019 - [info] * Phase 2: Dead Master Shutdown Phase..
Thu Aug 29 02:35:24 2019 - [info]
Thu Aug 29 02:35:24 2019 - [info] Forcing shutdown so that applications never connect to the current master..
Thu Aug 29 02:35:24 2019 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master ip address.
Thu Aug 29 02:35:24 2019 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Thu Aug 29 02:35:24 2019 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Thu Aug 29 02:35:24 2019 - [info]
Thu Aug 29 02:35:24 2019 - [info] * Phase 3: Master Recovery Phase..
Thu Aug 29 02:35:24 2019 - [info]
Thu Aug 29 02:35:24 2019 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Thu Aug 29 02:35:24 2019 - [info]
Thu Aug 29 02:35:24 2019 - [info] The latest binary log file/position on all slaves is binlog.000004:106
Thu Aug 29 02:35:24 2019 - [info] Latest slaves (Slaves that received relay log files to the latest):
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.151(192.168.13.151:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.152(192.168.13.152:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.153(192.168.13.153:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] The oldest binary log file/position on all slaves is binlog.000004:106
Thu Aug 29 02:35:24 2019 - [info] Oldest slaves:
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.151(192.168.13.151:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.152(192.168.13.152:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info] 192.168.13.153(192.168.13.153:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:24 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:24 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:24 2019 - [info]
Thu Aug 29 02:35:24 2019 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Thu Aug 29 02:35:24 2019 - [info]
Thu Aug 29 02:35:25 2019 - [info] Fetching dead master's binary logs..
Thu Aug 29 02:35:25 2019 - [info] Executing command on the dead master 192.168.13.150(192.168.13.150:3306): save_binary_logs --command=save --start_file=binlog.000004 --start_pos=106 --binlog_dir=/var/lib/mysql --output_file=/var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.55
Creating /var/tmp if not exists.. ok.
Concat binary/relay logs from binlog.000004 pos 106 to binlog.000004 EOF into /var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog ..
Dumping binlog format description event, from position 0 to 106.. ok.
Dumping effective binlog data from /var/lib/mysql/binlog.000004 position 106 to tail(125).. ok.
Concat succeeded.
Thu Aug 29 02:35:25 2019 - [info] scp from root@192.168.13.150:/var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog to local:/etc/mha/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog succeeded.
Thu Aug 29 02:35:29 2019 - [info] HealthCheck: SSH to 192.168.13.151 is reachable.
Thu Aug 29 02:35:29 2019 - [info] HealthCheck: SSH to 192.168.13.152 is reachable.
Thu Aug 29 02:35:30 2019 - [info] HealthCheck: SSH to 192.168.13.153 is reachable.
Thu Aug 29 02:35:30 2019 - [info]
Thu Aug 29 02:35:30 2019 - [info] * Phase 3.3: Determining New Master Phase..
Thu Aug 29 02:35:30 2019 - [info]
Thu Aug 29 02:35:30 2019 - [info] Finding the latest slave that has all relay logs for recovering other slaves..
Thu Aug 29 02:35:30 2019 - [info] All slaves received relay logs to the same position. No need to resync each other.
Thu Aug 29 02:35:30 2019 - [info] Searching new master from slaves..
Thu Aug 29 02:35:30 2019 - [info] Candidate masters from the configuration file:
Thu Aug 29 02:35:30 2019 - [info] 192.168.13.151(192.168.13.151:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:30 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:30 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:30 2019 - [info] 192.168.13.152(192.168.13.152:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:30 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:30 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:30 2019 - [info] 192.168.13.153(192.168.13.153:3306) Version=5.1.66-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 02:35:30 2019 - [info] Replicating from 192.168.13.150(192.168.13.150:3306)
Thu Aug 29 02:35:30 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 02:35:30 2019 - [info] Non-candidate masters:
Thu Aug 29 02:35:30 2019 - [info] Searching from candidate_master slaves which have received the latest relay log events..
Thu Aug 29 02:35:30 2019 - [info] New master is 192.168.13.151(192.168.13.151:3306)
Thu Aug 29 02:35:30 2019 - [info] Starting master failover..
Thu Aug 29 02:35:30 2019 - [info]
From:
192.168.13.150 (current master)
+--192.168.13.151
+--192.168.13.152
+--192.168.13.153
To:
192.168.13.151 (new master)
+--192.168.13.152
+--192.168.13.153
Thu Aug 29 02:35:30 2019 - [info]
Thu Aug 29 02:35:30 2019 - [info] * Phase 3.3: New Master Diff Log Generation Phase..
Thu Aug 29 02:35:30 2019 - [info]
Thu Aug 29 02:35:30 2019 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Thu Aug 29 02:35:30 2019 - [info] Sending binlog..
Thu Aug 29 02:35:31 2019 - [info] scp from local:/etc/mha/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog to root@192.168.13.151:/var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog succeeded.
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] * Phase 3.4: Master Log Apply Phase..
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Thu Aug 29 02:35:31 2019 - [info] Starting recovery on 192.168.13.151(192.168.13.151:3306)..
Thu Aug 29 02:35:31 2019 - [info] Generating diffs succeeded.
Thu Aug 29 02:35:31 2019 - [info] Waiting until all relay logs are applied.
Thu Aug 29 02:35:31 2019 - [info] done.
Thu Aug 29 02:35:31 2019 - [info] Getting slave status..
Thu Aug 29 02:35:31 2019 - [info] This slave(192.168.13.151)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(binlog.000004:106). No need to recover from Exec_Master_Log_Pos.
Thu Aug 29 02:35:31 2019 - [info] Connecting to the target slave host 192.168.13.151, running recover script..
Thu Aug 29 02:35:31 2019 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user='root' --slave_host=192.168.13.151 --slave_ip=192.168.13.151 --slave_port=3306 --apply_files=/var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog --workdir=/var/tmp --target_version=5.1.66-log --timestamp=20190829023524 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.55 --slave_pass=xxx
Thu Aug 29 02:35:31 2019 - [info]
Applying differential binary/relay log files /var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog on 192.168.13.151:3306. This may take long time...
Applying log files succeeded.
Thu Aug 29 02:35:31 2019 - [info] All relay logs were successfully applied.
Thu Aug 29 02:35:31 2019 - [info] Getting new master's binlog name and position..
Thu Aug 29 02:35:31 2019 - [info] binlog.000003:106
Thu Aug 29 02:35:31 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.13.151', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=106, MASTER_USER='sko', MASTER_PASSWORD='xxx';
Thu Aug 29 02:35:31 2019 - [warning] master_ip_failover_script is not set. Skipping taking over new master ip address.
Thu Aug 29 02:35:31 2019 - [info] ** Finished master recovery successfully.
Thu Aug 29 02:35:31 2019 - [info] * Phase 3: Master Recovery Phase completed.
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] * Phase 4: Slaves Recovery Phase..
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] -- Slave diff file generation on host 192.168.13.152(192.168.13.152:3306) started, pid: 2461. Check tmp log /etc/mha/192.168.13.152_3306_20190829023524.log if it takes time..
Thu Aug 29 02:35:31 2019 - [info] -- Slave diff file generation on host 192.168.13.153(192.168.13.153:3306) started, pid: 2462. Check tmp log /etc/mha/192.168.13.153_3306_20190829023524.log if it takes time..
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] Log messages from 192.168.13.153 ...
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Thu Aug 29 02:35:31 2019 - [info] End of log messages from 192.168.13.153.
Thu Aug 29 02:35:31 2019 - [info] -- 192.168.13.153(192.168.13.153:3306) has the latest relay log events.
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] Log messages from 192.168.13.152 ...
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Thu Aug 29 02:35:31 2019 - [info] End of log messages from 192.168.13.152.
Thu Aug 29 02:35:31 2019 - [info] -- 192.168.13.152(192.168.13.152:3306) has the latest relay log events.
Thu Aug 29 02:35:31 2019 - [info] Generating relay diff files from the latest slave succeeded.
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Thu Aug 29 02:35:31 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] -- Slave recovery on host 192.168.13.152(192.168.13.152:3306) started, pid: 2465. Check tmp log /etc/mha/192.168.13.152_3306_20190829023524.log if it takes time..
Thu Aug 29 02:35:31 2019 - [info] -- Slave recovery on host 192.168.13.153(192.168.13.153:3306) started, pid: 2466. Check tmp log /etc/mha/192.168.13.153_3306_20190829023524.log if it takes time..
Thu Aug 29 02:35:37 2019 - [info]
Thu Aug 29 02:35:37 2019 - [info] Log messages from 192.168.13.153 ...
Thu Aug 29 02:35:37 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] Sending binlog..
Thu Aug 29 02:35:31 2019 - [info] scp from local:/etc/mha/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog to root@192.168.13.153:/var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog succeeded.
Thu Aug 29 02:35:31 2019 - [info] Starting recovery on 192.168.13.153(192.168.13.153:3306)..
Thu Aug 29 02:35:31 2019 - [info] Generating diffs succeeded.
Thu Aug 29 02:35:31 2019 - [info] Waiting until all relay logs are applied.
Thu Aug 29 02:35:31 2019 - [info] done.
Thu Aug 29 02:35:31 2019 - [info] Getting slave status..
Thu Aug 29 02:35:31 2019 - [info] This slave(192.168.13.153)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(binlog.000004:106). No need to recover from Exec_Master_Log_Pos.
Thu Aug 29 02:35:31 2019 - [info] Connecting to the target slave host 192.168.13.153, running recover script..
Thu Aug 29 02:35:31 2019 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user='root' --slave_host=192.168.13.153 --slave_ip=192.168.13.153 --slave_port=3306 --apply_files=/var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog --workdir=/var/tmp --target_version=5.1.66-log --timestamp=20190829023524 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.55 --slave_pass=xxx
Thu Aug 29 02:35:37 2019 - [info]
Applying differential binary/relay log files /var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog on 192.168.13.153:3306. This may take long time...
Applying log files succeeded.
Thu Aug 29 02:35:37 2019 - [info] All relay logs were successfully applied.
Thu Aug 29 02:35:37 2019 - [info] Resetting slave 192.168.13.153(192.168.13.153:3306) and starting replication from the new master 192.168.13.151(192.168.13.151:3306)..
Thu Aug 29 02:35:37 2019 - [info] Executed CHANGE MASTER.
Thu Aug 29 02:35:37 2019 - [info] Slave started.
Thu Aug 29 02:35:37 2019 - [info] End of log messages from 192.168.13.153.
Thu Aug 29 02:35:37 2019 - [info] -- Slave recovery on host 192.168.13.153(192.168.13.153:3306) succeeded.
Thu Aug 29 02:35:37 2019 - [info]
Thu Aug 29 02:35:37 2019 - [info] Log messages from 192.168.13.152 ...
Thu Aug 29 02:35:37 2019 - [info]
Thu Aug 29 02:35:31 2019 - [info] Sending binlog..
Thu Aug 29 02:35:36 2019 - [info] scp from local:/etc/mha/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog to root@192.168.13.152:/var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog succeeded.
Thu Aug 29 02:35:36 2019 - [info] Starting recovery on 192.168.13.152(192.168.13.152:3306)..
Thu Aug 29 02:35:36 2019 - [info] Generating diffs succeeded.
Thu Aug 29 02:35:36 2019 - [info] Waiting until all relay logs are applied.
Thu Aug 29 02:35:36 2019 - [info] done.
Thu Aug 29 02:35:36 2019 - [info] Getting slave status..
Thu Aug 29 02:35:36 2019 - [info] This slave(192.168.13.152)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(binlog.000004:106). No need to recover from Exec_Master_Log_Pos.
Thu Aug 29 02:35:36 2019 - [info] Connecting to the target slave host 192.168.13.152, running recover script..
Thu Aug 29 02:35:36 2019 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user='root' --slave_host=192.168.13.152 --slave_ip=192.168.13.152 --slave_port=3306 --apply_files=/var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog --workdir=/var/tmp --target_version=5.1.66-log --timestamp=20190829023524 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.55 --slave_pass=xxx
Thu Aug 29 02:35:37 2019 - [info]
Applying differential binary/relay log files /var/tmp/saved_master_binlog_from_192.168.13.150_3306_20190829023524.binlog on 192.168.13.152:3306. This may take long time...
Applying log files succeeded.
Thu Aug 29 02:35:37 2019 - [info] All relay logs were successfully applied.
Thu Aug 29 02:35:37 2019 - [info] Resetting slave 192.168.13.152(192.168.13.152:3306) and starting replication from the new master 192.168.13.151(192.168.13.151:3306)..
Thu Aug 29 02:35:37 2019 - [info] Executed CHANGE MASTER.
Thu Aug 29 02:35:37 2019 - [info] Slave started.
Thu Aug 29 02:35:37 2019 - [info] End of log messages from 192.168.13.152.
Thu Aug 29 02:35:37 2019 - [info] -- Slave recovery on host 192.168.13.152(192.168.13.152:3306) succeeded.
Thu Aug 29 02:35:37 2019 - [info] All new slave servers recovered successfully.
Thu Aug 29 02:35:37 2019 - [info]
Thu Aug 29 02:35:37 2019 - [info] * Phase 5: New master cleanup phase..
Thu Aug 29 02:35:37 2019 - [info]
Thu Aug 29 02:35:37 2019 - [info] Resetting slave info on the new master..
Thu Aug 29 02:35:37 2019 - [info] 192.168.13.151: Resetting slave info succeeded.
Thu Aug 29 02:35:37 2019 - [info] Master failover to 192.168.13.151(192.168.13.151:3306) completed successfully.
Thu Aug 29 02:35:37 2019 - [info]
----- Failover Report -----
mha: MySQL Master failover 192.168.13.150 to 192.168.13.151 succeeded
Master 192.168.13.150 is down!
Check MHA Manager logs at 13-160:/etc/mha/manager.log for details.
Started automated(non-interactive) failover.
The latest slave 192.168.13.151(192.168.13.151:3306) has all relay logs for recovery.
Selected 192.168.13.151 as a new master.
192.168.13.151: OK: Applying all logs succeeded.
192.168.13.153: This host has the latest relay log events.
192.168.13.152: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.13.153: OK: Applying all logs succeeded. Slave started, replicating from 192.168.13.151.
192.168.13.152: OK: Applying all logs succeeded. Slave started, replicating from 192.168.13.151.
192.168.13.151: Resetting slave info succeeded.
Master failover to 192.168.13.151(192.168.13.151:3306) completed successfully.
MHA只能切换一次 切换成功会出现一个文件(mha.failover.complete)成功的标注
写：13.150
不能写死13.150，如果150挂了，就写不了了，所有不能写死 定义一个虚ip(13.200)
此处有一个脚本可以自动检测 绑定ip
!/bin/bash
VIP=192.168.13.200
NETMASK=255.255.255.0
MUSER=root
MPW=123
MYSQL_IP="192.168.13.151 192.168.13.151 192.168.13.152 192.168.13.153"
NIC=eth0 #需要修改
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~main program~~~~~~~~~~~~~~~~~~~~~~~~~~~~
check_mysql_client () {
which mysql &>/dev/null
if [ $? -ne 0 ];then
echo -e "\033[31m NOT FOUND MYSQL COMMAND \033[0m"
exit 10
fi
}
#check_master_mysql
check_master() {
for IP in $MYSQL_IP
do
ssh $IP "mysql -u $MUSER -p$MPW -e 'show slave status \G'|grep -w 'Slave_IO_Running'" &>/dev/null
if [ $? -eq 1 ] ;then
mysql -h $IP -u $MUSER -p$MPW -e "show databases" &>/dev/null
if [ $? -eq 0 ];then
MY_master=$IP
fi
fi
echo "$MY_master"
done
}
#check_master
#echo $MY_master
#set_VIP() {
#ssh $MY_master "ip add show eth0"|grep inet|grep "$VIP"
# if [ $? -ne 0 ];then
# ifconfig $NIC:200 $VIP netmask $NETMASK up
# fi
#}
VIP () {
for IP in $MYSQL_IP
do
ssh $IP "ip add show eth0"|grep inet|grep "$VIP" &>/dev/null
if [ $? -eq 0 ] && [ $MY_master != "$IP" ];then
ssh $IP "ifconfig $NIC:200 down"
elif [ $? -eq 1 ] && [ $MY_master == "$IP" ];then
ssh $IP "ifconfig $NIC:200 $VIP netmask $NETMASK up"
fi
done
}
check_mysql_client
while true
do
check_master
VIP
sleep 1
done
所需安装包
https://pan.baidu.com/s/1Ik1h5rhOERbuVF2gY0KViA
如有错误 请指出谢谢🙏
129
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
