(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
如有错误 请指出谢谢?