2020-08-08第十五周

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  不輸出復(fù)制內(nèi)容 如需要輸出請將>/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主從同步

主機配置
啟用二進制日志 為當(dāng)前節(jié)點設(shè)置一個全局惟一的ID號
[mysqld]
server_id=1
log-bin=/data/logbin/mysql-bin
log-basename=master 可選項，設(shè)置datadir中日志名稱贤牛，確保不依賴主機名
[root@localhost backup]# mysql -uroot -p   創(chuàng)建有復(fù)制權(quán)限的用戶賬號并查看二進制日志位置
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 重啟服務(wù)使配置生效
從機配置
啟動中繼日志

[mysqld]
server_id=2 為當(dāng)前節(jié)點設(shè)置一個全局惟的ID號
read_only=ON 設(shè)置數(shù)據(jù)庫只讀
relay_log=relay-log relay log的文件路徑惋鹅，默認值hostname-relay-bin
relay_log_index=relay-log.index 默認值hostname-relay-bin.index
 使用有復(fù)制權(quán)限的用戶賬號連接至主服務(wù)器，并啟動復(fù)制線程
MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST='192.168.50.127',  主機ip
  MASTER_USER='repluser',              具有復(fù)制權(quán)限的賬戶
  MASTER_PASSWORD='123456',    賬戶密碼
  MASTER_PORT=3306,                     端口
  MASTER_LOG_FILE='mysql-bin.000003',    復(fù)制時的二進制日志位置
  MASTER_LOG_POS=404;

MariaDB [(none)]> start slave;   啟動服務(wù)
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G  啟動從服務(wù)查看配置
*************************** 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實現(xiàn)Mysql高可用闰集。

本次實驗需要4臺機器 1臺管理主機 1主兩從數(shù)據(jù)庫服務(wù)器
1 數(shù)據(jù)主機配置：

 [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';    創(chuàng)建管理用戶
ariaDB [(none)]> grant replication slave on *.* to repluser@'172.16.100.%' identified by 'magedu'; 創(chuàng)建復(fù)制用戶

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)

從節(jié)點配置（兩臺除了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

啟動三臺服務(wù)器的mariadb

[root@localhost ~]# systemctl start mariadb

搭建主從復(fù)制（兩臺從一樣）

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   查看狀態(tài)
*************************** 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)

管理主機配置
先配好相應(yīng)的yum源并開啟epel

yum -y install mha*.rpm
mkdir /etc/mastermha/

配置管理節(jié)點

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
將生成的公鑰分發(fā)到各管理節(jié)點
 scp -r .ssh 172.16.100.46:/root/
 scp -r .ssh 172.16.100.47:/root/
 scp -r .ssh 172.16.100.48:/root/

各節(jié)點安裝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

設(shè)置故障檢測
關(guān)閉主數(shù)據(jù)庫
查看報錯日志

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]#

發(fā)現(xiàn)已將172.16.100.47從設(shè)置為主數(shù)據(jù)庫
查看從數(shù)據(jù)庫狀態(tài)

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)

從服務(wù)器已將172.16.100.47設(shè)置為主服務(wù)器
在從服務(wù)器提升為主服務(wù)器后，必須人為去修改配置文件般卑，將"read-only"刪掉或注釋掉
為防止監(jiān)控前臺運行被人為關(guān)掉武鲁，可改為后臺執(zhí)行

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市蝠检，隨后出現(xiàn)的幾起案子沐鼠，更是在濱河造成了極大的恐慌，老刑警劉巖叹谁，帶你破解...
    沈念sama閱讀 222,590評論 6贊 517
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件饲梭，死亡現(xiàn)場離奇詭異，居然都是意外死亡焰檩，警方通過查閱死者的電腦和手機憔涉，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,157評論 3贊 399
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來锅尘，“玉大人监氢，你說我怎么就攤上這事√傥ィ” “怎么了浪腐？”我有些...
    開封第一講書人閱讀 169,301評論 0贊 362
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長顿乒。 經(jīng)常有香客問我议街，道長，這世上最難降的妖魔是什么璧榄？ 我笑而不...
    開封第一講書人閱讀 60,078評論 1贊 300
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任特漩，我火速辦了婚禮吧雹，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘涂身。我一直安慰自己雄卷，他們只是感情好，可當(dāng)我...
    茶點故事閱讀 69,082評論 6贊 398
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布蛤售。 她就那樣靜靜地躺著丁鹉，像睡著了一般。 火紅的嫁衣襯著肌膚如雪悴能。 梳的紋絲不亂的頭發(fā)上揣钦，一...
    開封第一講書人閱讀 52,682評論 1贊 312
  • 城市分裂傳說
    那天，我揣著相機與錄音漠酿，去河邊找鬼冯凹。 笑死，一個胖子當(dāng)著我的面吹牛炒嘲，可吹牛的內(nèi)容都是我干的宇姚。 我是一名探鬼主播，決...
    沈念sama閱讀 41,155評論 3贊 422
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼摸吠，長吁一口氣：“原來是場噩夢啊……” “哼空凸！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起寸痢，我...
    開封第一講書人閱讀 40,098評論 0贊 277
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤呀洲，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后啼止，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體道逗，經(jīng)...
    沈念sama閱讀 46,638評論 1贊 319
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,701評論 3贊 342
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年献烦，在試婚紗的時候發(fā)現(xiàn)自己被綠了滓窍。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 40,852評論 1贊 353
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡巩那，死狀恐怖吏夯，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情即横，我是刑警寧澤噪生，帶...
    沈念sama閱讀 36,520評論 5贊 351
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站东囚，受9級特大地震影響跺嗽，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點故事閱讀 42,181評論 3贊 335
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一桨嫁、第九天 我趴在偏房一處隱蔽的房頂上張望植兰。 院中可真熱鬧，春花似錦璃吧、人聲如沸楣导。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,674評論 0贊 25
  • 一樁弒父案畜挨，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽爷辙。三九已至，卻和暖如春朦促，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背栓始。 一陣腳步聲響...
    開封第一講書人閱讀 33,788評論 1贊 274
  • 情欲美人皮
    我被黑心中介騙來泰國打工务冕， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人幻赚。 一個月前我還...
    沈念sama閱讀 49,279評論 3贊 379
  • 代替公主和親
    正文 我出身青樓禀忆，卻偏偏與公主長得像，于是被迫代替她去往敵國和親落恼。 傳聞我的和親對象是個殘疾皇子箩退，可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 45,851評論 2贊 361