1、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。
[root@localhost data]# cat mysqlback.sh
#!/bin/bash
path=/data
file_name=all.`date +%F`.sql
read -p "Do you use mysqldump or xtraback full backup?(mysqldump/xtraback) " nin
if [[ $nin = "mysqldump" ]];then
mysqldump -A --master-data=2 > $path/$file_name -uroot -p123456
echo "Back-up succcess!"
elif [[ $nin = "xtraback" ]];then
xtrabackup --backup --target-dir=$path/xtra.`date +%F` >/dev/null 2>&1 -uroot -p123456 不输出复制内容 如需要输出请将>/dev/null 2>&1删除
echo "Back-up succcess!"
else
echo "Please enter mysqldump or xtraback!"
exit 0
fi
测试
root@localhost data]# bash mysqlback.sh
Do you use mysqldump or xtraback full backup?(mysqldump/xtraback) xtraback
Back-up succcess!
[root@localhost data]# bash mysqlback.sh
Do you use mysqldump or xtraback full backup?(mysqldump/xtraback) mysqldump
Back-up succcess!
[root@localhost data]# bash mysqlback.sh
Do you use mysqldump or xtraback full backup?(mysqldump/xtraback) 111
Please enter mysqldump or xtraback!
2、配置Mysql主从同步
主机配置
启用二进制日志 为当前节点设置一个全局惟一的ID号
[mysqld]
server_id=1
log-bin=/data/logbin/mysql-bin
log-basename=master 可选项，设置datadir中日志名称，确保不依赖主机名
[root@localhost backup]# mysql -uroot -p 创建有复制权限的用户账号并查看二进制日志位置
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.100.%' IDENTIFIED BY '123456';
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 496 |
| mysql-bin.000002 | 1973 |
| mysql-bin.000003 | 404 |
+------------------+-----------+
3 rows in set (0.00 sec)
[root@localhost backup]# systemctl restart mariadb 重启服务使配置生效
从机配置
启动中继日志
[mysqld]
server_id=2 为当前节点设置一个全局惟的ID号
read_only=ON 设置数据库只读
relay_log=relay-log relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index 默认值hostname-relay-bin.index
使用有复制权限的用户账号连接至主服务器，并启动复制线程
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.50.127', 主机ip
MASTER_USER='repluser', 具有复制权限的账户
MASTER_PASSWORD='123456', 账户密码
MASTER_PORT=3306, 端口
MASTER_LOG_FILE='mysql-bin.000003', 复制时的二进制日志位置
MASTER_LOG_POS=404;
MariaDB [(none)]> start slave; 启动服务
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G 启动从服务查看配置
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.50.127
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 404
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.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: 404
Relay_Log_Space: 825
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
3、使用MHA实现Mysql高可用。
本次实验需要4台机器 1台管理主机 1主两从数据库服务器
1 数据主机配置：
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
log-bin
server_id=1
skip_name_resolve=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
MariaDB [(none)]> grant all on *.* to mhauser@'172.16.100.%'identified by 'magedu'; 创建管理用户
ariaDB [(none)]> grant replication slave on *.* to repluser@'172.16.100.%' identified by 'magedu'; 创建复制用户
MariaDB [(none)]> select user,host,password from mysql.user;
+----------+-----------------------+-------------------------------------------+
| user | host | password |
+----------+-----------------------+-------------------------------------------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| repluser | 172.16.100.% | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| mhauser | 172.16.100.% | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+----------+-----------------------+-------------------------------------------+
8 rows in set (0.00 sec)
从节点配置（两台除了server_id其它一样）
[root@localhost .ssh]# cat /etc/my.cnf
[mysqld]
server_id=x
log-bin
read_only
relay_log_purge=0
skip_name_resolve=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
启动三台服务器的mariadb
[root@localhost ~]# systemctl start mariadb
搭建主从复制（两台从一样）
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='172.16.100.46',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;
MariaDB [(none)]> start slave; 启动
MariaDB [(none)]> show slave status\G 查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.47
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000002
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: 245
Relay_Log_Space: 827
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
1 row in set (0.00 sec)
管理主机配置
先配好相应的yum源并开启epel
yum -y install mha*.rpm
mkdir /etc/mastermha/
配置管理节点
vim /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=magedu
ping_interval=1
[server1]
hostname=172.16.100.46
candidate_master=1
[server2]
hostname=172.16.100.47
candidate_master=1
[server3]
hostname=172.16.100.48
搭建免密登录
ssh-keygen
ssh-copy-id 172.16.100.43
将生成的公钥分发到各管理节点
scp -r .ssh 172.16.100.46:/root/
scp -r .ssh 172.16.100.47:/root/
scp -r .ssh 172.16.100.48:/root/
各节点安装node包
yum install mha4mysql-node-0.56-0.el6.noarch.rpm
Mha验证和启动
masterha_check_ssh --conf=/etc/mastermha/app1.cnf
masterha_check_repl --conf=/etc/mastermha/app1.cnf
masterha_manager --conf=/etc/mastermha/app1.cnf
设置故障检测
关闭主数据库
查看报错日志
cat /data/mastermha/app1/manager.log
Tue Sep 1 23:06:50 2020 - [info] MHA::MasterMonitor version 0.56.
Tue Sep 1 23:06:51 2020 - [info] GTID failover mode = 0
Tue Sep 1 23:06:51 2020 - [info] Dead Servers:
Tue Sep 1 23:06:51 2020 - [info] Alive Servers:
Tue Sep 1 23:06:51 2020 - [info] 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:06:51 2020 - [info] 172.16.100.47(172.16.100.47:3306)
Tue Sep 1 23:06:51 2020 - [info] 172.16.100.48(172.16.100.48:3306)
Tue Sep 1 23:06:51 2020 - [info] Alive Slaves:
Tue Sep 1 23:06:51 2020 - [info] 172.16.100.47(172.16.100.47:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:06:51 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:06:51 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Sep 1 23:06:51 2020 - [info] 172.16.100.48(172.16.100.48:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:06:51 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:06:51 2020 - [info] Current Alive Master: 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:06:51 2020 - [info] Checking slave configurations..
Tue Sep 1 23:06:51 2020 - [info] Checking replication filtering settings..
Tue Sep 1 23:06:51 2020 - [info] binlog_do_db= , binlog_ignore_db=
Tue Sep 1 23:06:51 2020 - [info] Replication filtering check ok.
Tue Sep 1 23:06:51 2020 - [info] GTID (with auto-pos) is not supported
Tue Sep 1 23:06:51 2020 - [info] Starting SSH connection tests..
Tue Sep 1 23:06:54 2020 - [info] All SSH connection tests passed successfully.
Tue Sep 1 23:06:54 2020 - [info] Checking MHA Node version..
Tue Sep 1 23:06:55 2020 - [info] Version check ok.
Tue Sep 1 23:06:55 2020 - [info] Checking SSH publickey authentication settings on the current master..
Tue Sep 1 23:06:55 2020 - [info] HealthCheck: SSH to 172.16.100.46 is reachable.
Tue Sep 1 23:06:55 2020 - [info] Master MHA Node version is 0.56.
Tue Sep 1 23:06:55 2020 - [info] Checking recovery script configurations on 172.16.100.46(172.16.100.46:3306)..
Tue Sep 1 23:06:55 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.56 --start_file=mariadb-bin.000003
Tue Sep 1 23:06:55 2020 - [info] Connecting to root@172.16.100.46(172.16.100.46:22)..
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mariadb-bin.000003
Tue Sep 1 23:06:55 2020 - [info] Binlog setting check done.
Tue Sep 1 23:06:55 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Sep 1 23:06:55 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=172.16.100.47 --slave_ip=172.16.100.47 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.65-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Sep 1 23:06:55 2020 - [info] Connecting to root@172.16.100.47(172.16.100.47:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000008
Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000008
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Sep 1 23:06:55 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=172.16.100.48 --slave_ip=172.16.100.48 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.65-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Sep 1 23:06:55 2020 - [info] Connecting to root@172.16.100.48(172.16.100.48:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000008
Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000008
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Sep 1 23:06:56 2020 - [info] Slaves settings check done.
Tue Sep 1 23:06:56 2020 - [info]
172.16.100.46(172.16.100.46:3306) (current master)
+--172.16.100.47(172.16.100.47:3306)
+--172.16.100.48(172.16.100.48:3306)
Tue Sep 1 23:06:56 2020 - [warning] master_ip_failover_script is not defined.
Tue Sep 1 23:06:56 2020 - [warning] shutdown_script is not defined.
Tue Sep 1 23:06:56 2020 - [info] Set master ping interval 1 seconds.
Tue Sep 1 23:06:56 2020 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Tue Sep 1 23:06:56 2020 - [info] Starting ping health check on 172.16.100.46(172.16.100.46:3306)..
Tue Sep 1 23:06:56 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Tue Sep 1 23:23:02 2020 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Tue Sep 1 23:23:02 2020 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.56 --binlog_prefix=mariadb-bin
Tue Sep 1 23:23:02 2020 - [warning] HealthCheck: SSH to 172.16.100.46 is NOT reachable.
Tue Sep 1 23:23:04 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.16.100.46' (4))
Tue Sep 1 23:23:04 2020 - [warning] Connection failed 2 time(s)..
Tue Sep 1 23:23:05 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.16.100.46' (4))
Tue Sep 1 23:23:05 2020 - [warning] Connection failed 3 time(s)..
Tue Sep 1 23:23:06 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.16.100.46' (4))
Tue Sep 1 23:23:06 2020 - [warning] Connection failed 4 time(s)..
Tue Sep 1 23:23:06 2020 - [warning] Master is not reachable from health checker!
Tue Sep 1 23:23:06 2020 - [warning] Master 172.16.100.46(172.16.100.46:3306) is not reachable!
Tue Sep 1 23:23:06 2020 - [warning] SSH is NOT reachable.
Tue Sep 1 23:23:06 2020 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mastermha/app1.cnf again, and trying to connect to all servers to check server status..
Tue Sep 1 23:23:06 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Sep 1 23:23:06 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Tue Sep 1 23:23:06 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Tue Sep 1 23:23:07 2020 - [info] GTID failover mode = 0
Tue Sep 1 23:23:07 2020 - [info] Dead Servers:
Tue Sep 1 23:23:07 2020 - [info] 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:07 2020 - [info] Alive Servers:
Tue Sep 1 23:23:07 2020 - [info] 172.16.100.47(172.16.100.47:3306)
Tue Sep 1 23:23:07 2020 - [info] 172.16.100.48(172.16.100.48:3306)
Tue Sep 1 23:23:07 2020 - [info] Alive Slaves:
Tue Sep 1 23:23:07 2020 - [info] 172.16.100.47(172.16.100.47:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:23:07 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:07 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Sep 1 23:23:07 2020 - [info] 172.16.100.48(172.16.100.48:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:23:07 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:07 2020 - [info] Checking slave configurations..
Tue Sep 1 23:23:07 2020 - [info] Checking replication filtering settings..
Tue Sep 1 23:23:07 2020 - [info] Replication filtering check ok.
Tue Sep 1 23:23:07 2020 - [info] Master is down!
Tue Sep 1 23:23:07 2020 - [info] Terminating monitoring script.
Tue Sep 1 23:23:07 2020 - [info] Got exit code 20 (Master dead).
Tue Sep 1 23:23:07 2020 - [info] MHA::MasterFailover version 0.56.
Tue Sep 1 23:23:07 2020 - [info] Starting master failover.
Tue Sep 1 23:23:07 2020 - [info]
Tue Sep 1 23:23:07 2020 - [info] * Phase 1: Configuration Check Phase..
Tue Sep 1 23:23:07 2020 - [info]
Tue Sep 1 23:23:08 2020 - [info] GTID failover mode = 0
Tue Sep 1 23:23:08 2020 - [info] Dead Servers:
Tue Sep 1 23:23:08 2020 - [info] 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:08 2020 - [info] Checking master reachability via MySQL(double check)...
Tue Sep 1 23:23:09 2020 - [info] ok.
Tue Sep 1 23:23:09 2020 - [info] Alive Servers:
Tue Sep 1 23:23:09 2020 - [info] 172.16.100.47(172.16.100.47:3306)
Tue Sep 1 23:23:09 2020 - [info] 172.16.100.48(172.16.100.48:3306)
Tue Sep 1 23:23:09 2020 - [info] Alive Slaves:
Tue Sep 1 23:23:09 2020 - [info] 172.16.100.47(172.16.100.47:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:23:09 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:09 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Sep 1 23:23:09 2020 - [info] 172.16.100.48(172.16.100.48:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:23:09 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:09 2020 - [info] Starting Non-GTID based failover.
Tue Sep 1 23:23:09 2020 - [info]
Tue Sep 1 23:23:09 2020 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Sep 1 23:23:09 2020 - [info]
Tue Sep 1 23:23:09 2020 - [info] * Phase 2: Dead Master Shutdown Phase..
Tue Sep 1 23:23:09 2020 - [info]
Tue Sep 1 23:23:09 2020 - [info] Forcing shutdown so that applications never connect to the current master..
Tue Sep 1 23:23:09 2020 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Tue Sep 1 23:23:09 2020 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Tue Sep 1 23:23:10 2020 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] * Phase 3: Master Recovery Phase..
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] The latest binary log file/position on all slaves is mariadb-bin.000003:143246
Tue Sep 1 23:23:10 2020 - [info] Latest slaves (Slaves that received relay log files to the latest):
Tue Sep 1 23:23:10 2020 - [info] 172.16.100.47(172.16.100.47:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:23:10 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:10 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Sep 1 23:23:10 2020 - [info] 172.16.100.48(172.16.100.48:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:23:10 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:10 2020 - [info] The oldest binary log file/position on all slaves is mariadb-bin.000003:143246
Tue Sep 1 23:23:10 2020 - [info] Oldest slaves:
Tue Sep 1 23:23:10 2020 - [info] 172.16.100.47(172.16.100.47:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:23:10 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:10 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Sep 1 23:23:10 2020 - [info] 172.16.100.48(172.16.100.48:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:23:10 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [warning] Dead Master is not SSH reachable. Could not save it's binlogs. Transactions that were not sent to the latest slave (Read_Master_Log_Pos to the tail of the dead master's binlog) were lost.
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] * Phase 3.3: Determining New Master Phase..
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] Finding the latest slave that has all relay logs for recovering other slaves..
Tue Sep 1 23:23:10 2020 - [info] All slaves received relay logs to the same position. No need to resync each other.
Tue Sep 1 23:23:10 2020 - [info] Searching new master from slaves..
Tue Sep 1 23:23:10 2020 - [info] Candidate masters from the configuration file:
Tue Sep 1 23:23:10 2020 - [info] 172.16.100.47(172.16.100.47:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Sep 1 23:23:10 2020 - [info] Replicating from 172.16.100.46(172.16.100.46:3306)
Tue Sep 1 23:23:10 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Sep 1 23:23:10 2020 - [info] Non-candidate masters:
Tue Sep 1 23:23:10 2020 - [info] Searching from candidate_master slaves which have received the latest relay log events..
Tue Sep 1 23:23:10 2020 - [info] New master is 172.16.100.47(172.16.100.47:3306)
Tue Sep 1 23:23:10 2020 - [info] Starting master failover..
Tue Sep 1 23:23:10 2020 - [info]
From:
172.16.100.46(172.16.100.46:3306) (current master)
+--172.16.100.47(172.16.100.47:3306)
+--172.16.100.48(172.16.100.48:3306)
To:
172.16.100.47(172.16.100.47:3306) (new master)
+--172.16.100.48(172.16.100.48:3306)
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] * Phase 3.3: New Master Diff Log Generation Phase..
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] * Phase 3.4: Master Log Apply Phase..
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Tue Sep 1 23:23:10 2020 - [info] Starting recovery on 172.16.100.47(172.16.100.47:3306)..
Tue Sep 1 23:23:10 2020 - [info] This server has all relay logs. Waiting all logs to be applied..
Tue Sep 1 23:23:10 2020 - [info] done.
Tue Sep 1 23:23:10 2020 - [info] All relay logs were successfully applied.
Tue Sep 1 23:23:10 2020 - [info] Getting new master's binlog name and position..
Tue Sep 1 23:23:10 2020 - [info] mariadb-bin.000002:245
Tue Sep 1 23:23:10 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.16.100.47', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=245, MASTER_USER='repluser', MASTER_PASSWORD='xxx';
Tue Sep 1 23:23:10 2020 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Tue Sep 1 23:23:10 2020 - [info] Setting read_only=0 on 172.16.100.47(172.16.100.47:3306)..
Tue Sep 1 23:23:10 2020 - [info] ok.
Tue Sep 1 23:23:10 2020 - [info] ** Finished master recovery successfully.
Tue Sep 1 23:23:10 2020 - [info] * Phase 3: Master Recovery Phase completed.
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] * Phase 4: Slaves Recovery Phase..
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Tue Sep 1 23:23:10 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] -- Slave diff file generation on host 172.16.100.48(172.16.100.48:3306) started, pid: 15723. Check tmp log /data/mastermha/app1//172.16.100.48_3306_20200901232307.log if it takes time..
Tue Sep 1 23:23:11 2020 - [info]
Tue Sep 1 23:23:11 2020 - [info] Log messages from 172.16.100.48 ...
Tue Sep 1 23:23:11 2020 - [info]
Tue Sep 1 23:23:10 2020 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Tue Sep 1 23:23:11 2020 - [info] End of log messages from 172.16.100.48.
Tue Sep 1 23:23:11 2020 - [info] -- 172.16.100.48(172.16.100.48:3306) has the latest relay log events.
Tue Sep 1 23:23:11 2020 - [info] Generating relay diff files from the latest slave succeeded.
Tue Sep 1 23:23:11 2020 - [info]
Tue Sep 1 23:23:11 2020 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Tue Sep 1 23:23:11 2020 - [info]
Tue Sep 1 23:23:11 2020 - [info] -- Slave recovery on host 172.16.100.48(172.16.100.48:3306) started, pid: 15725. Check tmp log /data/mastermha/app1//172.16.100.48_3306_20200901232307.log if it takes time..
Tue Sep 1 23:23:12 2020 - [info]
Tue Sep 1 23:23:12 2020 - [info] Log messages from 172.16.100.48 ...
Tue Sep 1 23:23:12 2020 - [info]
Tue Sep 1 23:23:11 2020 - [info] Starting recovery on 172.16.100.48(172.16.100.48:3306)..
Tue Sep 1 23:23:11 2020 - [info] This server has all relay logs. Waiting all logs to be applied..
Tue Sep 1 23:23:11 2020 - [info] done.
Tue Sep 1 23:23:11 2020 - [info] All relay logs were successfully applied.
Tue Sep 1 23:23:11 2020 - [info] Resetting slave 172.16.100.48(172.16.100.48:3306) and starting replication from the new master 172.16.100.47(172.16.100.47:3306)..
Tue Sep 1 23:23:11 2020 - [info] Executed CHANGE MASTER.
Tue Sep 1 23:23:11 2020 - [info] Slave started.
Tue Sep 1 23:23:12 2020 - [info] End of log messages from 172.16.100.48.
Tue Sep 1 23:23:12 2020 - [info] -- Slave recovery on host 172.16.100.48(172.16.100.48:3306) succeeded.
Tue Sep 1 23:23:12 2020 - [info] All new slave servers recovered successfully.
Tue Sep 1 23:23:12 2020 - [info]
Tue Sep 1 23:23:12 2020 - [info] * Phase 5: New master cleanup phase..
Tue Sep 1 23:23:12 2020 - [info]
Tue Sep 1 23:23:12 2020 - [info] Resetting slave info on the new master..
Tue Sep 1 23:23:12 2020 - [info] 172.16.100.47: Resetting slave info succeeded.
Tue Sep 1 23:23:12 2020 - [info] Master failover to 172.16.100.47(172.16.100.47:3306) completed successfully.
Tue Sep 1 23:23:12 2020 - [info]
----- Failover Report -----
app1: MySQL Master failover 172.16.100.46(172.16.100.46:3306) to 172.16.100.47(172.16.100.47:3306) succeeded
Master 172.16.100.46(172.16.100.46:3306) is down!
Check MHA Manager logs at localhost.localdomain:/data/mastermha/app1/manager.log for details.
Started automated(non-interactive) failover.
The latest slave 172.16.100.47(172.16.100.47:3306) has all relay logs for recovery.
Selected 172.16.100.47(172.16.100.47:3306) as a new master.
172.16.100.47(172.16.100.47:3306): OK: Applying all logs succeeded.
172.16.100.48(172.16.100.48:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
172.16.100.48(172.16.100.48:3306): OK: Applying all logs succeeded. Slave started, replicating from 172.16.100.47(172.16.100.47:3306)
172.16.100.47(172.16.100.47:3306): Resetting slave info succeeded.
Master failover to 172.16.100.47(172.16.100.47:3306) completed successfully.
[root@localhost yum.repos.d]#
发现已将172.16.100.47从设置为主数据库
查看从数据库状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.47
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000002
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: 245
Relay_Log_Space: 827
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
1 row in set (0.00 sec)
从服务器已将172.16.100.47设置为主服务器
在从服务器提升为主服务器后，必须人为去修改配置文件，将"read-only"删掉或注释掉
为防止监控前台运行被人为关掉，可改为后台执行
