MySQL MHA+keepalived部署文档

DBA小记 2020-10-27

404

MySQL MHA+keepalived部署手册

1.1.环境介绍

系统：Redhat6.0-x64

数据库：Mysql-5.6.27

主库：172.168.86.79(MHA-Node)

从库1：172.168.86.80(MHA-Node)

从库2：172.168.86.81(MHA-Node MHA-Manager)

MHA版本：mha4mysql-manager-0.56.tar.gz

mha4mysql-node-0.56.tar.gz

Keepalived版本:

Keepalived v1.2.19

1.2.MySQL安装步骤

1.2.1.关闭selinux

vi /etc/sysconfig/selinux

将SELINUX=enforcing修改为disabled然后重启系统生效

1.2.2.关闭iptables

# service iptables stop

1.2.3.安装开发包和库文件

yum -y install gcc gcc-c++ autoconf automake bison flex freetype freetype-devel fontconfig-devel gettext-devel libjpeg libjpeg-devel libpng libpng-devel libxml2 libxml2-devel libtool libtool-ltdl libtool-ltdl-devel libtiff-devel libXpm-devel libicu libicu-devel libidn libidn-devel libxslt libxslt-devel gd gd-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel openssl openssl-devel openldap* openldap-devel openldap-clients openldap-servers pcre pcre-devel pam-devel vim-enhanced mysql-devel php-devel libevent libevent-devel

1.2.4.安装cmake

[mysql@DFJK-TEST-25 ~]$ cd app/tools-conf/

[mysql@DFJK-TEST-25 tools-conf]$ tar zxvf cmake-3.3.0.tar.gz

[mysql@DFJK-TEST-25 tools-conf]$ cd cmake-3.3.0

[mysql@DFJK-TEST-25 tools-conf]$ ./bootstrap --prefix=/app/tools-conf/cmake

[mysql@DFJK-TEST-25 tools-conf]$ make

[mysql@DFJK-TEST-25 tools-conf]$ make install

[mysql@DFJK-TEST-25 ~]$ vi ~/.bash_profile

export PATH=$PATH:/app/tools-conf/cmake/bin

1.2.5.安装Mysql

[root@DFJK-TEST-25 tools-conf]# mkdir -p app/mysql-5627

[root@DFJK-TEST-25 tools-conf]# chown -R mysql:mysql app/mysql-5627/

[mysql@DFJK-TEST-25 tools-conf]$ tar zxvf mysql-5.6.27.tar.gz

[mysql@DFJK-TEST-25 tools-conf]$ cd mysql-5.6.27

[mysql@DFJK-TEST-25 tools-conf]$ export bpath=/app/mysql-5627

[mysql@DFJK-TEST-25 tools-conf]$ cmake \
-DCMAKE_INSTALL_PREFIX=${bpath}  \
-DINSTALL_MYSQLDATADIR=“${bpath}/var”  \
-DMYSQL_DATADIR=${bpath}/var \
-DSYSCONFDIR=${bpath}/etc    \
-DWITH_INNOBASE_STORAGE_ENGINE=1  \
-DDEFAULT_CHARSET=utf8   \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_TCP_PORT=5627  \
-DMYSQL_UNIX_ADDR=${bpath}/tmp/mysql.sock
复制

[mysql@DFJK-TEST-25 tools-conf]$ make

[mysql@DFJK-TEST-25 tools-conf]$ make install

[mysql@DFJK-TEST-25 ~]$ cd $bpath

[mysql@DFJK-TEST-25 mysql-5627]$ pwd

/app/mysql-5627

[mysql@DFJK-TEST-25 mysql-5627]$ cp scripts/mysql_install_db app/mysql-5627/bin/

[mysql@DFJK-TEST-25 ~]$ cd app/mysql-5627/

[mysql@DFJK-TEST-25 mysql-5627]$ mkdir log tmp etc var binlog

[mysql@DFJK-TEST-25 mysql-5627]$ mkdir -p app/data/5627/data

[mysql@DFJK-TEST-25 mysql-5627]$ cp app/mysql-5627/support-files/my-default.cnf app/mysql-5627/etc/my.cnf

[mysql@DFJK-TEST-25 etc]$ vi my.cnf

# The MySQL server
[mysqld]
port = 5627
user = mysql
socket = /app/mysql-5627/tmp/mysql.sock
pid-file = /app/mysql-5627/tmp/mysql.pid
basedir = /app/mysql-5627
datadir = /app/data/5627/data
tmpdir = /app/mysql-5627/tmp
open_files_limit = 10240
explicit_defaults_for_timestamp
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


# Buffer
max_allowed_packet = 256M
max_heap_table_size = 256M
net_buffer_length = 8K
sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 16M


# Log
log-bin = /app/mysql-5627/binlog/mysql-bin
binlog_cache_size = 32M
max_binlog_cache_size = 512M
max_binlog_size = 512M
binlog_format = mixed
log_output = FILE
log-error = /app/mysql-5627/log/mysql-error.log
slow_query_log = 1
slow_query_log_file = /app/mysql-5627/log/slow_query
general_log = 0
general_log_file = /app/mysql-5627/log/general_query,log
expire-logs-days = 14




# InnoDB
#innodb_data_file_path = ibdata1:2048M:autoextend
#innodb_log_file_size = 256M
#innodb_log_files_in_group = 3
#innodb_buffer_pool_size = 1024M


[mysql]
no-auto-rehash
prompt   = (\u@\h) [\d]>\_
default-character-set = gbk
复制

[mysql@DFJK-TEST-25 ~]$ ./mysql_install_db --datadir=/app/data/5627/data --basedir=/app/mysql-5627[mysql@DFJK-TEST-25 ~]$ cd app/mysql-5627/bin

[mysql@DFJK-TEST-27 support-files]$ vi ~/.bash_profile

#export LANG=zh_CN.HB18030
export PATH=/app/mysql-5627/bin:$PATH
export MYSQL_PS1="(\u@\h) [\d]> "


export PATH=$PATH:/app/tools-conf/cmake/bin
复制

[mysql@DFJK-TEST-27 support-files]$ . ~/.bash_profile

[mysql@DFJK-TEST-25 support-files]$ cd app/mysql-5627/support-files/

[mysql@DFJK-TEST-25 support-files]$ ./mysql.server start

[mysql@DFJK-TEST-25 support-files]$ ./mysql.server stop

切换到root

[root@DFJK-TEST-25 ~]# cp app/mysql-5627/support-files/mysql.server etc/init.d/mysqld
[root@DFJK-TEST-25 ~]# chmod +x  etc/init.d/mysqld
[mysql@DFJK-TEST-25 ~]$ etc/init.d/mysqld start
Starting MySQL.                                            [  OK  ]
[mysql@DFJK-TEST-25 ~]$ ps -ef |grep -v grep |grep mysqld
mysql    22972     1  0 11:22 pts/1    00:00:00 bin/sh app/mysql-5627/bin/mysqld_safe --datadir=/app/data/5627/data --pid-file=/app/data/5627/mysql.pid
mysql    23452 22972  1 11:22 pts/1    00:00:00 app/mysql-5627/bin/mysqld --basedir=/app/mysql-5627 --datadir=/app/data/5627/data --plugin-dir=/app/mysql-5627/lib/plugin --log-error=/app/mysql-5627/log/mysql-error.log --open-files-limit=10240 --pid-file=/app/data/5627/mysql.pid --socket=/app/data/5627/mysql.sock --port=5627
复制

登陆测试

[mysql@DFJK-TEST-25 ~]$  app/mysql-5627/bin/mysqladmin -u root password 'mysql123'
[mysql@DFJK-TEST-25 bin]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.27-log Source distribution


Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


(root@localhost) [(none)]>
复制

以上单机安装3台服务器，步骤相同。

1.3.Mysql主从搭建步骤

1.3.1.打开主库的log_bin，并设置server_id

[mysql@DFJK-TEST-25 ~]$ vi app/mysql-5627/etc/my.cnf

server-id=101
log-bin = /app/mysql-5627/binlog/mysql-bin
复制

重启生效

[mysql@DFJK-TEST-25 ~]$ service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]
复制

1.3.2.在主库对从库的同步账户授权

[mysql@DFJK-TEST-25 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27-log Source distribution


Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


(root@localhost) [(none)]> grant replication slave on *.* to 'repl'@'172.16.86.80' identified by 'repl123';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]> grant replication slave on *.* to 'repl'@'172.16.86.81' identified by 'repl123';
(root@localhost) [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
复制

1.3.3.获取主库binlog点，记录下来

(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |      408 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
复制

1.3.4.获取数据快照，拷贝到从库

a.关闭mysqld，全库冷备份

b.用mysqldump或innodbbackupex

1.3.5.拷贝完毕主库可以继续提供服务

1.3.6.从库修改配置，更改server_id和主库不同按需要设置

172.16.86.80

server-id=102
read_only
skip-slave-start
复制

增加skip-slave-start(slave复制进程不随mysql启动而启动)

[mysql@DFJK-TEST-26 ~]$ vi app/mysql-5627/etc/my.cnf

[mysql@DFJK-TEST-26 ~]$service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]
复制

重启生效

172.16.86.81

[mysql@DFJK-TEST-27 ~]$ vi app/mysql-5627/etc/my.cnf
[mysql@DFJK-TEST-27 ~]$ service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]
复制

1.3.7.从库替换数据文件，若是mysqldump，则启动后source进去

1.3.8.从库mysqld启动

1.3.9. 2个从库指定主库binlog点

CHANGE MASTER TO

MASTER_HOST='172.16.86.79',

MASTER_USER='repl',

MASTER_PASSWORD='repl123',

MASTER_PORT=5627,

MASTER_LOG_FILE='mysql-bin.000012',

MASTER_LOG_POS=909;

1.3.10.从库start slave;

mysql(root@yyc@(none) 02:23:42)->start slave;
mysql(root@yyc@(none) 02:25:13)->show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.86.79
                  Master_User: repl
                  Master_Port: 5627
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 909
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000012
             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: 909
              Relay_Log_Space: 456
              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: 101
                  Master_UUID: a0ea307c-57ed-11e8-9ae3-005056b22769
             Master_Info_File: /app/data/5627/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
复制

1.3.11.从库show slave status\G;看到如下说明同步正常启动

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

主从上各show porcesslist;

从库1

从库2

主库：

(root@localhost) [yyc]> show processlist;
+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host               | db   | Command     | Time | State                                                                 | Info             |
+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
|  1 | root | localhost          | yyc  | Query       |    0 | init                                                                  | show processlist |
|  2 | repl | 172.16.86.80:54093 | NULL | Binlog Dump |  473 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|  3 | repl | 172.16.86.81:23664 | NULL | Binlog Dump |   24 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
复制

从库1：

从库2：

1.4.MHA搭建步骤

1.4.1.主库-从库1-从库2分别对MHA统一管理用户和密码进行授权

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mha'@'172.16.86.%' IDENTIFIED BY 'mysql123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.86.%' IDENTIFIED BY 'mysql123';
mysql> flush privileges;
复制

1.4.2.各节点修改/etc/hosts增加如下

cat etc/hosts

172.16.86.79       DFJK-TEST-25
172.16.86.80       DFJK-TEST-26
172.16.86.81       DFJK-TEST-27
复制

1.4.3.配置免密登陆

所有主从和MANAGER节点及本身做SSH公钥认证

从库81（MANAGER）到所有节点:

[root@DFJK-TEST-27 ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in root/.ssh/id_rsa.
Your public key has been saved in root/.ssh/id_rsa.pub.
The key fingerprint is:
c4:ac:9e:62:0a:8a:d3:33:96:2f:b4:0c:96:b8:e4:af root@DFJK-TEST-27
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
|       o         |
|        +        |
|       o         |
|. .   . S        |
|o+.  . .         |
|=* oo o          |
|=oXo .           |
|oE+*.            |
+-----------------+
[root@DFJK-TEST-27 ~]# ssh-copy-id -i root/.ssh/id_rsa 172.16.86.79
The authenticity of host '172.16.86.79 (172.16.86.79)' can't be established.
RSA key fingerprint is 3c:2e:20:d2:d4:b8:4c:ff:23:8d:a8:fe:28:ad:91:2d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.86.79' (RSA) to the list of known hosts.
root@172.16.86.79's password:
Now try logging into the machine, with "ssh '172.16.86.79'", and check in:


  .ssh/authorized_keys


to make sure we haven't added extra keys that you weren't expecting.


[root@DFJK-TEST-27 ~]# ssh-copy-id -i root/.ssh/id_rsa 172.16.86.80
root@172.16.86.80's password:
Now try logging into the machine, with "ssh '172.16.86.80'", and check in:


  .ssh/authorized_keys


to make sure we haven't added extra keys that you weren't expecting.


[root@DFJK-TEST-27 ~]# ssh-copy-id -i root/.ssh/id_rsa 172.16.86.81
The authenticity of host '172.16.86.81 (172.16.86.81)' can't be established.
RSA key fingerprint is 3c:2e:20:d2:d4:b8:4c:ff:23:8d:a8:fe:28:ad:91:2d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.86.81' (RSA) to the list of known hosts.
root@172.16.86.81's password:
Now try logging into the machine, with "ssh '172.16.86.81'", and check in:


  .ssh/authorized_keys




to make sure we haven't added extra keys that you weren't expecting.
复制

配置master到所有节点：

[root@DFJK-TEST-25 ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in root/.ssh/id_rsa.
Your public key has been saved in root/.ssh/id_rsa.pub.
The key fingerprint is:
3b:84:cd:d4:43:3d:43:cd:ed:22:16:e7:74:c5:03:12 root@DFJK-TEST-25
The key's randomart image is:
+--[ RSA 2048]----+
|          .Eo+.oo|
|         o  * =.+|
|        . o  B o.|
|       =   .o o .|
|      . S  . . . |
|       . .       |
|        o        |
|         .       |
|                 |
+-----------------+
[root@DFJK-TEST-25 ~]# ssh-copy-id -i 172.16.86.79
The authenticity of host '172.16.86.79 (172.16.86.79)' can't be established.
RSA key fingerprint is 3c:2e:20:d2:d4:b8:4c:ff:23:8d:a8:fe:28:ad:91:2d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.86.79' (RSA) to the list of known hosts.
root@172.16.86.79's password:
Now try logging into the machine, with "ssh '172.16.86.79'", and check in:


  .ssh/authorized_keys


to make sure we haven't added extra keys that you weren't expecting.


[root@DFJK-TEST-25 ~]# ssh-copy-id -i 172.16.86.80
root@172.16.86.80's password:
Now try logging into the machine, with "ssh '172.16.86.80'", and check in:


  .ssh/authorized_keys


to make sure we haven't added extra keys that you weren't expecting.


[root@DFJK-TEST-25 ~]# ssh-copy-id -i 172.16.86.81
The authenticity of host '172.16.86.81 (172.16.86.81)' can't be established.
RSA key fingerprint is 3c:2e:20:d2:d4:b8:4c:ff:23:8d:a8:fe:28:ad:91:2d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.86.81' (RSA) to the list of known hosts.
root@172.16.86.81's password:
Now try logging into the machine, with "ssh '172.16.86.81'", and check in:


  .ssh/authorized_keys


to make sure we haven't added extra keys that you weren't expecting.
复制

配置从库80到所有节点：

[root@DFJK-TEST-26 ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in root/.ssh/id_rsa.
Your public key has been saved in root/.ssh/id_rsa.pub.
The key fingerprint is:
79:54:fd:32:f4:48:43:f6:e9:c5:6c:37:bb:fb:15:1c root@DFJK-TEST-26
The key's randomart image is:
+--[ RSA 2048]----+
|            .oo  |
|           . .=+.|
|          .  o EB|
|         o    *.O|
|        S .    B |
|         .      o|
|               ..|
|                o|
|               .o|
+-----------------+
[root@DFJK-TEST-26 ~]# ssh-copy-id -i 172.16.86.79
root@172.16.86.79's password:
Now try logging into the machine, with "ssh '172.16.86.79'", and check in:


  .ssh/authorized_keys


to make sure we haven't added extra keys that you weren't expecting.


[root@DFJK-TEST-26 ~]# ssh-copy-id -i 172.16.86.80
The authenticity of host '172.16.86.80 (172.16.86.80)' can't be established.
RSA key fingerprint is 3c:2e:20:d2:d4:b8:4c:ff:23:8d:a8:fe:28:ad:91:2d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.86.80' (RSA) to the list of known hosts.
root@172.16.86.80's password:
Now try logging into the machine, with "ssh '172.16.86.80'", and check in:


  .ssh/authorized_keys


to make sure we haven't added extra keys that you weren't expecting.


[root@DFJK-TEST-26 ~]# ssh-copy-id -i 172.16.86.81
root@172.16.86.81's password:
Now try logging into the machine, with "ssh '172.16.86.81'", and check in:


  .ssh/authorized_keys


to make sure we haven't added extra keys that you weren't expecting.
复制

1.4.4.测试验证SSH各节点及自身的有效性

从库81（MANAGER）：

[root@DFJK-TEST-27 ~]# ssh 172.16.86.79
Last login: Wed May 16 10:07:53 2018 from 172.16.49.42
===This login time Wed May 16 10:37:08 CST 2018  ===
[root@DFJK-TEST-25 ~]# exit
logout
Connection to 172.16.86.79 closed.
[root@DFJK-TEST-27 ~]# ssh 172.16.86.80
Last login: Wed May 16 10:25:45 2018 from 10.1.58.23
[root@DFJK-TEST-26 ~]# exit
logout
Connection to 172.16.86.80 closed.
[root@DFJK-TEST-27 ~]# ssh 172.16.86.81
Last login: Wed May 16 09:18:17 2018 from 172.16.49.42


[root@DFJK-TEST-27 ~]# exit
logout
Connection to 172.16.86.81 closed
复制

从库80（backup）：

[root@DFJK-TEST-26 ~]# ssh 172.16.86.79
Last login: Wed May 16 10:37:08 2018 from dfjk-test-27.dfjk.coamc.com.cn
===This login time Wed May 16 10:37:54 CST 2018  ===
[root@DFJK-TEST-25 ~]# exit
logout
Connection to 172.16.86.79 closed.
[root@DFJK-TEST-26 ~]# ssh 172.16.86.80
Last login: Wed May 16 10:37:12 2018 from dfjk-test-27.dfjk.coamc.com.cn
[root@DFJK-TEST-26 ~]# exit
logout
Connection to 172.16.86.80 closed.
[root@DFJK-TEST-26 ~]# ssh 172.16.86.81
Last login: Wed May 16 10:37:17 2018 from dfjk-test-27.dfjk.coamc.com.cn
[root@DFJK-TEST-27 ~]# exit
logout
Connection to 172.16.86.81 closed.
复制

主库79：

[root@DFJK-TEST-25 ~]# ssh 172.16.86.79
Last login: Wed May 16 10:37:54 2018 from dfjk-test-26.dfjk.coamc.com.cn
===This login time Wed May 16 10:38:22 CST 2018  ===
[root@DFJK-TEST-25 ~]# exit
logout
Connection to 172.16.86.79 closed.
[root@DFJK-TEST-25 ~]# ssh 172.16.86.80
Last login: Wed May 16 10:37:57 2018 from dfjk-test-26.dfjk.coamc.com.cn
[root@DFJK-TEST-26 ~]# exit
logout
Connection to 172.16.86.80 closed.
[root@DFJK-TEST-25 ~]# ssh 172.16.86.81
Last login: Wed May 16 10:38:00 2018 from dfjk-test-26.dfjk.coamc.com.cn
[root@DFJK-TEST-27 ~]# exit
logout
Connection to 172.16.86.81 closed.


复制

1.4.5.在manager端安装MHA-Manager MHA-Node及相关软件包

# yum -y install MySQL-shared-compat perl-DBI perl-DBD-MySQL perl-Params-Validate

首先安装perl的mysql包：

yum install -y perl-DBD-MySQL

yum install -y perl-Config-Tiny

yum install -y perl-Log-Dispatch

yum install -y perl-Parallel-ForkManager

yum install -y perl-Config-IniFiles

perl -MCPAN -e "install Log::Dispatch"

perl -MCPAN -e 'install "YAML"'

perl -MCPAN -e "install Parallel::ForkManager"

yum install perl-Log-Dispatch-2.27-1.el6.noarch.rpm

yum install perl-Mail-Sender-0.8.16-3.el6.noarch.rpm

yum install perl-Mail-Sendmail-0.79-12.el6.noarch.rpm

yum install perl-Log-Dispatch-2.27-1.el6.noarch.rpm

如果最后还是安装不好，可以尝试一下perl CPAN的方式：

perl -MCPAN -e shell

cpan[1]> install Log::Dispatch

然后通过perlMakefile.PL检查mha的perl安装环境，如下所示

Manager端也作为node，所以在81服务器也安装node

[root@DFJK-TEST-27 ~]# cd app/tools-conf/
[root@DFJK-TEST-27 tools-conf]# tar zxvf mha4mysql-node-0.56.tar.gz
[root@DFJK-TEST-27 tools-conf]# cd mha4mysql-node-0.56
[root@DFJK-TEST-27 mha4mysql-node-0.56]# perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.609)
- DBD::mysql ...loaded. (4.013)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node
[root@DFJK-TEST-27 mha4mysql-node-0.56]# make
cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm
cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm
cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm
cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm
cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm
cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm
cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm
cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm
cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm
cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/filter_mysqlbinlog
cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/apply_diff_relay_logs
cp bin/purge_relay_logs blib/script/purge_relay_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/purge_relay_logs
cp bin/save_binary_logs blib/script/save_binary_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/save_binary_logs
Manifying blib/man1/filter_mysqlbinlog.1
Manifying blib/man1/apply_diff_relay_logs.1
Manifying blib/man1/purge_relay_logs.1
Manifying blib/man1/save_binary_logs.1
[root@DFJK-TEST-27 mha4mysql-node-0.56]# make install
Installing usr/local/share/perl5/MHA/BinlogHeaderParser.pm
Installing usr/local/share/perl5/MHA/BinlogPosFindManager.pm
Installing usr/local/share/perl5/MHA/BinlogPosFinderXid.pm
Installing usr/local/share/perl5/MHA/BinlogPosFinderElp.pm
Installing usr/local/share/perl5/MHA/NodeConst.pm
Installing usr/local/share/perl5/MHA/BinlogManager.pm
Installing usr/local/share/perl5/MHA/BinlogPosFinder.pm
Installing usr/local/share/perl5/MHA/SlaveUtil.pm
Installing usr/local/share/perl5/MHA/NodeUtil.pm
Installing usr/local/share/man/man1/purge_relay_logs.1
Installing usr/local/share/man/man1/apply_diff_relay_logs.1
Installing usr/local/share/man/man1/save_binary_logs.1
Installing usr/local/share/man/man1/filter_mysqlbinlog.1
Installing usr/local/bin/save_binary_logs
Installing usr/local/bin/filter_mysqlbinlog
Installing usr/local/bin/apply_diff_relay_logs
Installing usr/local/bin/purge_relay_logs
Appending installation info to usr/lib64/perl5/perllocal.pod
复制

1.4.6.主库-从库1-分别安装MHA-Node及相关软件包

主库79:

yum -y install perl-DBI perl-DBD-MySQL

从库80：

yum -y install perl-DBI perl-DBD-MySQL

主库:

[root@DFJK-TEST-25 ~]# cd app/tools-conf/
[root@DFJK-TEST-25 tools-conf]# tar zxvf mha4mysql-node-0.56.tar.gz
[root@DFJK-TEST-25 tools-conf]# cd mha4mysql-node-0.56
[root@DFJK-TEST-25 mha4mysql-node-0.56]# perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.609)
- DBD::mysql ...loaded. (4.013)
*** Module::AutoInstall configuration finished.
Writing Makefile for mha4mysql::node
[root@DFJK-TEST-25 mha4mysql-node-0.56]# make
cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm
cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm
cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm
cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm
cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm
cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm
cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm
cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm
cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm
cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/filter_mysqlbinlog
cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/apply_diff_relay_logs
cp bin/purge_relay_logs blib/script/purge_relay_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/purge_relay_logs
cp bin/save_binary_logs blib/script/save_binary_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/save_binary_logs
Manifying blib/man1/filter_mysqlbinlog.1
Manifying blib/man1/apply_diff_relay_logs.1
Manifying blib/man1/purge_relay_logs.1
Manifying blib/man1/save_binary_logs.1
[root@DFJK-TEST-25 mha4mysql-node-0.56]# make install
Installing usr/local/share/perl5/MHA/BinlogHeaderParser.pm
Installing usr/local/share/perl5/MHA/BinlogPosFindManager.pm
Installing usr/local/share/perl5/MHA/BinlogPosFinderXid.pm
Installing usr/local/share/perl5/MHA/BinlogPosFinderElp.pm
Installing usr/local/share/perl5/MHA/NodeConst.pm
Installing usr/local/share/perl5/MHA/BinlogManager.pm
Installing usr/local/share/perl5/MHA/BinlogPosFinder.pm
Installing usr/local/share/perl5/MHA/SlaveUtil.pm
Installing usr/local/share/perl5/MHA/NodeUtil.pm
Installing usr/local/share/man/man1/purge_relay_logs.1
Installing usr/local/share/man/man1/apply_diff_relay_logs.1
Installing usr/local/share/man/man1/save_binary_logs.1
Installing usr/local/share/man/man1/filter_mysqlbinlog.1
Installing usr/local/bin/save_binary_logs
Installing usr/local/bin/filter_mysqlbinlog
Installing usr/local/bin/apply_diff_relay_logs
Installing usr/local/bin/purge_relay_logs
Appending installation info to usr/lib64/perl5/perllocal.pod
复制

从库80

[root@DFJK-TEST-26 ~]# cd app/tools-conf/
[root@DFJK-TEST-26 tools-conf]# tar zxvf mha4mysql-node-0.56.tar.gz
[root@DFJK-TEST-26 tools-conf]# cd mha4mysql-node-0.56
[root@DFJK-TEST-26 mha4mysql-node-0.56]# perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.609)
- DBD::mysql ...loaded. (4.013)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node
[root@DFJK-TEST-26 mha4mysql-node-0.56]# make
cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm
cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm
cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm
cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm
cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm
cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm
cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm
cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm
cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm
cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/filter_mysqlbinlog
cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/apply_diff_relay_logs
cp bin/purge_relay_logs blib/script/purge_relay_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/purge_relay_logs
cp bin/save_binary_logs blib/script/save_binary_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/save_binary_logs
Manifying blib/man1/filter_mysqlbinlog.1
Manifying blib/man1/apply_diff_relay_logs.1
Manifying blib/man1/purge_relay_logs.1
Manifying blib/man1/save_binary_logs.1
[root@DFJK-TEST-26 mha4mysql-node-0.56]# echo $?
0
[root@DFJK-TEST-26 mha4mysql-node-0.56]# make install
Installing usr/local/share/perl5/MHA/BinlogHeaderParser.pm
Installing usr/local/share/perl5/MHA/BinlogPosFindManager.pm
Installing usr/local/share/perl5/MHA/BinlogPosFinderXid.pm
Installing usr/local/share/perl5/MHA/BinlogPosFinderElp.pm
Installing usr/local/share/perl5/MHA/NodeConst.pm
Installing usr/local/share/perl5/MHA/BinlogManager.pm
Installing usr/local/share/perl5/MHA/BinlogPosFinder.pm
Installing usr/local/share/perl5/MHA/SlaveUtil.pm
Installing usr/local/share/perl5/MHA/NodeUtil.pm
Installing usr/local/share/man/man1/purge_relay_logs.1
Installing /usr/local/share/man/man1/apply_diff_relay_logs.1
Installing /usr/local/share/man/man1/save_binary_logs.1
Installing /usr/local/share/man/man1/filter_mysqlbinlog.1
Installing /usr/local/bin/save_binary_logs
Installing /usr/local/bin/filter_mysqlbinlog
Installing /usr/local/bin/apply_diff_relay_logs
Installing /usr/local/bin/purge_relay_logs
Appending installation info to /usr/lib64/perl5/perllocal.pod
复制

1.4.7.manager端进行MHA配置

[root@DFJK-TEST-27 ~]# mkdir /etc/masterha
[root@DFJK-TEST-27 ~]# mkdir -p /masterha/app1
[root@DFJK-TEST-27 ~]# cp /app/tools-conf/mha4mysql-manager-0.56/samples/conf/* /etc/masterha/
[root@DFJK-TEST-27 ~]# ll !$
ll /etc/masterha/
total 8
-rw-r--r-- 1 root root 257 May 16 10:52 app1.cnf
-rw-r--r-- 1 root root 475 May 16 10:52 masterha_default.cnf
[root@DFJK-TEST-27 ~]# cd /etc/masterha/
[root@DFJK-TEST-27 masterha]# vi app1.cnf
[server default]
manager_workdir=/masterha/app1
manager_log=/masterha/app1/manager.log
user=mah
password=mysql123
ssh_user=root
repl_user=repl
repl_password=mysql123
ping_interval=1
#shutdown_script=""
#master_ip_failover_script="/usr/local/bin/master_ip_failover"
#master_ip_online_change_script=""
#report_script=""
[server1]
hostname=172.16.86.79
master_binlog_dir="/app/mysql-5627/binlog"
port=5627
candidate_master=1


[server2]
hostname=172.16.86.80
master_binlog_dir="/app/mysql-5627/binlog"
port=5627
candidate_master=1


[server3]
hostname=172.16.86.81
master_binlog_dir="/app/mysql-5627/binlog"
port=5627
no_master=1
复制

1.4.8.测试MHA的ssh连接和主从复制性

MANAGE端：

MHA的ssh检测 masterha_check_ssh --conf=/etc/masterha/app1.cnf

[root@DFJK-TEST-27 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Wed May 16 15:29:14 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 16 15:29:14 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed May 16 15:29:14 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Wed May 16 15:29:14 2018 - [info] Starting SSH connection tests..
Wed May 16 15:29:15 2018 - [debug]
Wed May 16 15:29:15 2018 - [debug]  Connecting via SSH from root@172.16.86.81(172.16.86.81:22) to root@172.16.86.79(172.16.86.79:22)..
Wed May 16 15:29:15 2018 - [debug]   ok.
Wed May 16 15:29:15 2018 - [debug]  Connecting via SSH from root@172.16.86.81(172.16.86.81:22) to root@172.16.86.80(172.16.86.80:22)..
Wed May 16 15:29:15 2018 - [debug]   ok.
Wed May 16 15:29:15 2018 - [debug]
Wed May 16 15:29:14 2018 - [debug]  Connecting via SSH from root@172.16.86.79(172.16.86.79:22) to root@172.16.86.81(172.16.86.81:22)..
Wed May 16 15:29:14 2018 - [debug]   ok.
Wed May 16 15:29:14 2018 - [debug]  Connecting via SSH from root@172.16.86.79(172.16.86.79:22) to root@172.16.86.80(172.16.86.80:22)..
Wed May 16 15:29:15 2018 - [debug]   ok.
Wed May 16 15:29:16 2018 - [debug]
Wed May 16 15:29:15 2018 - [debug]  Connecting via SSH from root@172.16.86.80(172.16.86.80:22) to root@172.16.86.79(172.16.86.79:22)..
Wed May 16 15:29:15 2018 - [debug]   ok.
Wed May 16 15:29:15 2018 - [debug]  Connecting via SSH from root@172.16.86.80(172.16.86.80:22) to root@172.16.86.81(172.16.86.81:22)..
Wed May 16 15:29:16 2018 - [debug]   ok.
Wed May 16 15:29:16 2018 - [info] All SSH connection tests passed successfully.
复制

MHA的复制性检测 masterha_check_repl --conf=/etc/masterha/app1.cnf

[root@DFJK-TEST-27 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Wed May 16 16:04:50 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 16 16:04:50 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed May 16 16:04:50 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Wed May 16 16:04:50 2018 - [info] MHA::MasterMonitor version 0.56.
Wed May 16 16:04:51 2018 - [info] GTID failover mode = 0
Wed May 16 16:04:51 2018 - [info] Dead Servers:
Wed May 16 16:04:51 2018 - [info] Alive Servers:
Wed May 16 16:04:51 2018 - [info]   172.16.86.79(172.16.86.79:5627)
Wed May 16 16:04:51 2018 - [info]   172.16.86.80(172.16.86.80:5627)
Wed May 16 16:04:51 2018 - [info]   172.16.86.81(172.16.86.81:5627)
Wed May 16 16:04:51 2018 - [info] Alive Slaves:
Wed May 16 16:04:51 2018 - [info]   172.16.86.80(172.16.86.80:5627)  Version=5.6.27-log (oldest major version between slaves) log-bin:enabled
Wed May 16 16:04:51 2018 - [info]     Replicating from 172.16.86.79(172.16.86.79:5627)
Wed May 16 16:04:51 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed May 16 16:04:51 2018 - [info]   172.16.86.81(172.16.86.81:5627)  Version=5.6.27-log (oldest major version between slaves) log-bin:enabled
Wed May 16 16:04:51 2018 - [info]     Replicating from 172.16.86.79(172.16.86.79:5627)
Wed May 16 16:04:51 2018 - [info]     Not candidate for the new Master (no_master is set)
Wed May 16 16:04:51 2018 - [info] Current Alive Master: 172.16.86.79(172.16.86.79:5627)
Wed May 16 16:04:51 2018 - [info] Checking slave configurations..
Wed May 16 16:04:51 2018 - [info]  read_only=1 is not set on slave 172.16.86.80(172.16.86.80:5627).
Wed May 16 16:04:51 2018 - [warning]  relay_log_purge=0 is not set on slave 172.16.86.80(172.16.86.80:5627).
Wed May 16 16:04:52 2018 - [info]  read_only=1 is not set on slave 172.16.86.81(172.16.86.81:5627).
Wed May 16 16:04:52 2018 - [warning]  relay_log_purge=0 is not set on slave 172.16.86.81(172.16.86.81:5627).
Wed May 16 16:04:52 2018 - [info] Checking replication filtering settings..
Wed May 16 16:04:52 2018 - [info]  binlog_do_db= , binlog_ignore_db=
Wed May 16 16:04:52 2018 - [info]  Replication filtering check ok.
Wed May 16 16:04:52 2018 - [info] GTID (with auto-pos) is not supported
Wed May 16 16:04:52 2018 - [info] Starting SSH connection tests..
Wed May 16 16:04:53 2018 - [info] All SSH connection tests passed successfully.
Wed May 16 16:04:53 2018 - [info] Checking MHA Node version..
Wed May 16 16:04:53 2018 - [info]  Version check ok.
Wed May 16 16:04:53 2018 - [info] Checking SSH publickey authentication settings on the current master..
Wed May 16 16:04:54 2018 - [info] HealthCheck: SSH to 172.16.86.79 is reachable.
Wed May 16 16:04:54 2018 - [info] Master MHA Node version is 0.56.
Wed May 16 16:04:54 2018 - [info] Checking recovery script configurations on 172.16.86.79(172.16.86.79:5627)..
Wed May 16 16:04:54 2018 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/app/mysql-5627/binlog --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000018
Wed May 16 16:04:54 2018 - [info]   Connecting to root@172.16.86.79(172.16.86.79:22)..
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /app/mysql-5627/binlog, up to mysql-bin.000018
Wed May 16 16:04:54 2018 - [info] Binlog setting check done.
Wed May 16 16:04:54 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed May 16 16:04:54 2018 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=172.16.86.80 --slave_ip=172.16.86.80 --slave_port=5627 --workdir=/var/tmp --target_version=5.6.27-log --manager_version=0.56 --relay_log_info=/app/data/5627/data/relay-log.info  --relay_dir=/app/data/5627/data/  --slave_pass=xxx
Wed May 16 16:04:54 2018 - [info]   Connecting to root@172.16.86.80(172.16.86.80:22)..
  Checking slave recovery environment settings..
    Opening /app/data/5627/data/relay-log.info ... ok.
    Relay log found at /app/data/5627/data, up to mysql-relay-bin.000015
    Temporary relay log file is /app/data/5627/data/mysql-relay-bin.000015
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed May 16 16:04:54 2018 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=172.16.86.81 --slave_ip=172.16.86.81 --slave_port=5627 --workdir=/var/tmp --target_version=5.6.27-log --manager_version=0.56 --relay_log_info=/app/data/5627/data/relay-log.info  --relay_dir=/app/data/5627/data/  --slave_pass=xxx
Wed May 16 16:04:54 2018 - [info]   Connecting to root@172.16.86.81(172.16.86.81:22)..
  Checking slave recovery environment settings..
    Opening /app/data/5627/data/relay-log.info ... ok.
    Relay log found at /app/data/5627/data, up to mysql-relay-bin.000012
    Temporary relay log file is /app/data/5627/data/mysql-relay-bin.000012
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed May 16 16:04:55 2018 - [info] Slaves settings check done.
Wed May 16 16:04:55 2018 - [info]
172.16.86.79(172.16.86.79:5627) (current master)
 +--172.16.86.80(172.16.86.80:5627)
 +--172.16.86.81(172.16.86.81:5627)


Wed May 16 16:04:55 2018 - [info] Checking replication health on 172.16.86.80..
Wed May 16 16:04:55 2018 - [info]  ok.
Wed May 16 16:04:55 2018 - [info] Checking replication health on 172.16.86.81..
Wed May 16 16:04:55 2018 - [info]  ok.
Wed May 16 16:04:55 2018 - [warning] master_ip_failover_script is not defined.
Wed May 16 16:04:55 2018 - [warning] shutdown_script is not defined.
Wed May 16 16:04:55 2018 - [info] Got exit code 0 (Not master dead).


MySQL Replication Health is OK.
复制

1.4.9.MHA-manager启动

[root@DFJK-TEST-27 ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf < /dev/null >/app/mysql-5627/log/manager.log 2>&1 &
[1] 6164
[root@DFJK-TEST-27 ~]# ps -ef |grep master
root      2646     1  0 15:16 ?        00:00:00 /usr/libexec/postfix/master
root      6164  3741  4 16:10 pts/1    00:00:00 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf
root      6265  3741  0 16:10 pts/1    00:00:00 grep master
[root@DFJK-TEST-27 ~]# tail -100f /app/mysql-5627/log/manager.log
Wed May 16 16:13:53 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 16 16:13:53 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed May 16 16:13:53 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
复制

1.4.10.MHA-manager关闭

[root@DFJK-TEST-27 ~]# masterha_stop --conf=/etc/masterha/app1.cnf  &
[2] 6335
[root@DFJK-TEST-27 ~]# Stopped app1 successfully.
[1]-  Exit 1                  nohup masterha_manager --conf=/etc/masterha/app1.cnf < /dev/null > /app/mysql-5627/log/manager.log 2>&1
[2]+  Done                    masterha_stop --conf=/etc/masterha/app1.cnf
[root@DFJK-TEST-27 ~]# ps -ef |grep master
root      2646     1  0 15:16 ?        00:00:00 /usr/libexec/postfix/master
root      6337  3741  0 16:11 pts/1    00:00:00 grep master
复制

1.4.11.使用masterha_check_status检测下

[root@DFJK-TEST-27 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:6342) is running(0:PING_OK), master:172.16.86.79
复制

1.4.12.mha工具包

(1)、 Manager工具:

- masterha_check_ssh : 检查MHA的SSH配置。

- masterha_check_repl : 检查MySQL复制。

- masterha_manager : 启动MHA。

- masterha_check_status : 检测当前MHA运行状态。

- masterha_master_monitor : 监测master是否宕机。

- masterha_master_switch : 控制故障转移(自动或手动)。

- masterha_conf_host : 添加或删除配置的server信息。

(2)、 Node工具(这些工具通常由MHAManager的脚本触发,无需人手操作)。

- save_binary_logs : 保存和复制master的二进制日志。

- apply_diff_relay_logs : 识别差异的中继日志事件并应用于其它slave。

- filter_mysqlbinlog : 去除不必要的ROLLBACK事件(MHA已不再使用这个工具)。

- purge_relay_logs : 清除中继日志(不会阻塞SQL线程)。

1.5.配合Keepalived进行VIP漂移

1.5.1.安装 keepalived（主-从库1-从库2全部安装）

[root@DFJK-TEST-25 ~]# cd /app/tools-conf/

[root@DFJK-TEST-25 tools-conf]# tar zxvf keepalived-1.2.19.tar.gz

[root@DFJK-TEST-25 tools-conf]# yum -y install gcc gcc-c++ gcc-g77 ncurses-devel bison libaio-devel cmake libnl* libpopt* popt-static openssl-devel

[root@DFJK-TEST-25 tools-conf]# cd keepalived-1.2.19

[root@DFJK-TEST-25 keepalived-1.2.19]# ./configure

[root@DFJK-TEST-25 keepalived-1.2.19]# make && make install

[root@DFJK-TEST-25 ~]# mkdir /etc/keepalived

[root@DFJK-TEST-25 ~]# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/

[root@DFJK-TEST-25 ~]# cp /usr/local/etc/rc.d/init.d/keepalived /etc/init.d/

[root@DFJK-TEST-25 ~]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/

[root@DFJK-TEST-25 ~]# cp /usr/local/sbin/keepalived /usr/sbin/

从1从2相同步骤。

1.5.2.主库配置keepalived

主库配置keepalived-VIP-172.16.86.201负责写

[root@DFJK-TEST-25 ~]# cat /etc/keepalived/keepalived.conf
vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.16.86.201
    }
}


virtual_server 172.16.86.201:5627 {
   delay_loop 2
   lb_algo wrr
   lb_kind DR
   persistence_timeout 60
   protocol TCP


    real_server 172.16.86.79:5627 {
        weight 3
        notify_down /root/check_mysql.sh
        TCP_CHECK {   
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 5627
        }
    }
复制

1.5.3.从库1配置keepalived

当主库挂了keepalived-VIP-172.16.86.201漂移到从库1上

[root@DFJK-TEST-26 ~]# cat /etc/keepalived/keepalived.conf
vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.16.86.201
    }
}


virtual_server 172.16.86.201:5627 {
   delay_loop 2
   lb_algo wrr
   lb_kind DR
   persistence_timeout 60
   protocol TCP


    real_server 172.16.86.79:5627 {
        weight 3
        notify_down /root/check_mysql.sh
        TCP_CHECK {   
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 5627
        }
    }


vrrp_instance VI_2 {
    state BACKUP
    interface eth1
    virtual_router_id 52
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.16.86.202
    }
}


virtual_server 172.16.86.202:5627 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60
    protocol TCP


    real_server 172.16.86.80:5627 {
        weight 3
        notify_down /root/check_mysql.sh
        TCP_CHECK {   
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 5627
        }


    }
复制

1.5.4.从库2配置keepalived

从库2配置keepalived-VIP-172.16.86.202负责读

当从库2宕机keepalived-VIP-172.16.86.202漂移到从库1上

[root@DFJK-TEST-27 ~]# cat /etc/keepalived/keepalived.conf
vrrp_instance VI_2 {
    state BACKUP
    interface eth1
    virtual_router_id 52
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.16.86.202
    }
}


virtual_server 172.16.86.202:5627 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60
    protocol TCP


    real_server 172.16.86.81:5627 {
        weight 3
        notify_down /root/check_mysql.sh
        TCP_CHECK {   
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 5627
        }
    }
复制

1.5.5.Mysql状态检查脚本

[root@DFJK-TEST-25 ~]# cat /root/check_mysql.sh
#!/bin/bash
MYSQL=/app/mysql-5627/bin/mysql
MYSQL_HOST=172.16.86.79
MYSQL_USER=root
MYSQL_PASSWORD=mysql123
CHECK_TIME=3
#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "select 1 from dual;" >/dev/null 2>&1
if [ $? = 0 ] ;then
  MYSQL_OK=1
else
  MYSQL_OK=0
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
  let "CHECK_TIME -= 1"
  check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
  CHECK_TIME=0
  exit 0
fi
if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
then
  pkill keepalived
exit 1
fi
sleep 1
done
复制

1.5.6.keepalived启动测试

1.5.6.1.主库正常状态

[root@DFJK-TEST-25 ~]# ip a
1: lo:mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth1:mtu 1500 qdisc mq state UP qlen 1000
    link/ether 00:50:56:b2:27:69 brd ff:ff:ff:ff:ff:ff
    inet 172.16.86.79/24 brd 172.16.86.255 scope global eth1
    inet 172.16.86.201/32 scope global eth1
    inet6 fe80::250:56ff:feb2:2769/64 scope link
       valid_lft forever preferred_lft forever
复制

1.5.6.2.从库1正常状态

[root@DFJK-TEST-26 ~]# ip a
1: lo:mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth1:mtu 1500 qdisc mq state UNKNOWN qlen 1000
    link/ether 00:50:56:b2:3e:3a brd ff:ff:ff:ff:ff:ff
    inet 172.16.86.80/24 brd 172.16.86.255 scope global eth1
    inet6 fe80::250:56ff:feb2:3e3a/64 scope link
       valid_lft forever preferred_lft forever
复制

1.5.6.3.从库2正常状态

[root@DFJK-TEST-27 ~]# ip a
1: lo:mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth1:mtu 1500 qdisc mq state UNKNOWN qlen 1000
    link/ether 00:50:56:b2:0d:0f brd ff:ff:ff:ff:ff:ff
    inet 172.16.86.81/24 brd 172.16.86.255 scope global eth1
    inet 172.16.86.202/32 scope global eth1
    inet6 fe80::250:56ff:feb2:d0f/64 scope link
       valid_lft forever preferred_lft forever
复制

1.5.6.4.虚拟VIP漂移测试

主库停止mysql，查看写vip情况

[root@DFJK-TEST-25 ~]# service mysqld stop
Shutting down MySQL.....                                   [  OK  ]
[root@DFJK-TEST-25 ~]# ip a
1: lo:mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth1:mtu 1500 qdisc mq state UP qlen 1000
    link/ether 00:50:56:b2:27:69 brd ff:ff:ff:ff:ff:ff
    inet 172.16.86.79/24 brd 172.16.86.255 scope global eth1
    inet6 fe80::250:56ff:feb2:2769/64 scope link
       valid_lft forever preferred_lft forever


从库1查看写VIP漂移过来,此时从库1已为主库。
[root@DFJK-TEST-26 ~]# ip a
1: lo:mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth1:mtu 1500 qdisc mq state UNKNOWN qlen 1000
    link/ether 00:50:56:b2:3e:3a brd ff:ff:ff:ff:ff:ff
    inet 172.16.86.80/24 brd 172.16.86.255 scope global eth1
    inet 172.16.86.201/32 scope global eth1
    inet6 fe80::250:56ff:feb2:3e3a/64 scope link
       valid_lft forever preferred_lft forever
复制

原主库修复mysql重启,写VIP不漂移回来

[root@DFJK-TEST-25 ~]# service mysqld start
Starting MySQL.                                            [  OK  ]
[root@DFJK-TEST-25 ~]# ip a
1: lo:mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
   inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth1:mtu 1500 qdisc mq state UP qlen 1000
    link/ether 00:50:56:b2:27:69 brd ff:ff:ff:ff:ff:ff
    inet 172.16.86.79/24 brd 172.16.86.255 scope global eth1
    inet6 fe80::250:56ff:feb2:2769/64 scope link
       valid_lft forever preferred_lft forever


重启从库1的keepalived可以恢复vip到主库：
[root@DFJK-TEST-26 ~]# ip a
1: lo:mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth1:mtu 1500 qdisc mq state UNKNOWN qlen 1000
    link/ether 00:50:56:b2:3e:3a brd ff:ff:ff:ff:ff:ff
    inet 172.16.86.80/24 brd 172.16.86.255 scope global eth1
    inet 172.16.86.201/32 scope global eth1
    inet6 fe80::250:56ff:feb2:3e3a/64 scope link
       valid_lft forever preferred_lft forever
[root@DFJK-TEST-26 ~]# service keepalived restart
Stopping keepalived:                                       [  OK  ]
Starting keepalived:                                       [  OK  ]
[root@DFJK-TEST-26 ~]# ip a
1: lo:mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth1:mtu 1500 qdisc mq state UNKNOWN qlen 1000
    link/ether 00:50:56:b2:3e:3a brd ff:ff:ff:ff:ff:ff
    inet 172.16.86.80/24 brd 172.16.86.255 scope global eth1
    inet6 fe80::250:56ff:feb2:3e3a/64 scope link
       valid_lft forever preferred_lft forever








复制

因为停止了mysql master，MHA已经将从库1变成了master。此时主库的IP地址并未出现vip：

从2被迫连接到新的主库（原来的从1）



新主（80，原从1）记录：
[root@DFJK-TEST-26 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8951
Server version: 5.6.27-log Source distribution


Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


(root@localhost) [(none)]> show processlist\G
*************************** 1. row ***************************
     Id: 8135
   User: repl
   Host: DFJK-TEST-27:51005
     db: NULL
Command: Binlog Dump
   Time: 882
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 8951
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
2 rows in set (0.00 sec)
(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000014 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


(root@localhost) [(none)]> show slave status\G
Empty set (0.00 sec)


从2记录：
[root@DFJK-TEST-27 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4321
Server version: 5.6.27-log Source distribution


Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql(root@yyc@(none) 02:47:59)->show processlist \G
*************************** 1. row ***************************
     Id: 3837
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 968
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 3838
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 968
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 4321
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
3 rows in set (0.00 sec)


mysql(root@yyc@(none) 02:48:06)->show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.86.80
                  Master_User: repl
                  Master_Port: 5627
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000014
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000014
             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: 120
              Relay_Log_Space: 456
              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: 103
                  Master_UUID: 4e7da0ee-5804-11e8-9b77-005056b23e3a
             Master_Info_File: /app/data/5627/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
复制

1.5.6.5.MHA自动切换后处理

1.5.6.5.1.Manager自动完成了切换操作。

可以查看日志

此时MHA已经停止：

[root@DFJK-TEST-27 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf

app1 is stopped(2:NOT_RUNNING).

启动manager进程，发现启不来。

[root@DFJK-TEST-27 log]# nohup masterha_manager --conf=/etc/masterha/app1.cnf < /dev/null >/app/mysql-5627/log/manager.log 2>&1 &
[1] 5106
 [1]+  Exit 1                  nohup masterha_manager --conf=/etc/masterha/app1.cnf < /dev/null > /app/mysql-5627/log/manager.log 2>&1
复制

1.5.6.5.2.回切测试1测试结果：(1)无写负载压力下，切换正常，耗时10s。（2）manager进程停止，无法控制接下来的主从问题。(3)被提升为写的实例，原主从状态被清空。

预测：因为主从信息被清空，回切肯定失败。

测试内容：(1)保留79宕，80主，71从的状态，未配置master_ip_failover_script。(2)启动79的mysql服务，观察主从。(3)启动manager服务（需要更改app1.cnf的ip地址，将原有79改成80,80改成79），观察主从。(4)观察日志。

[root@DFJK-TEST-25 ~]# service mysqld start
80服务器：
(root@localhost) [(none)]> show slave status\G
Empty set (0.00 sec)


(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000014 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
81服务器：
mysql(root@yyc@(none) 02:48:22)->show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.86.80
                  Master_User: repl
                  Master_Port: 5627
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000014
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000014
             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: 120
              Relay_Log_Space: 456
              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: 103
                  Master_UUID: 4e7da0ee-5804-11e8-9b77-005056b23e3a
             Master_Info_File: /app/data/5627/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
复制

启动manager。观察主从，观察manager日志。

80,81主从照旧，manager还是起不来。

测试结论：未配置master_ip_failover_script等参数状态下，回切失败。

1.5.6.5.3.将主库重新加入MHA，设置为slave

注意原主库（79）到新主库（80）的权限：

80上执行：

grant replication slave on *.* to 'repl'@'172.16.86.79' identified by 'repl123';
复制

79上执行：

reset slave;


CHANGE MASTER TO
  MASTER_HOST='172.16.86.80',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl123',
  MASTER_LOG_FILE='mysql-bin.000014',
  MASTER_LOG_POS=120;
复制

(root@localhost) [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)


(root@localhost) [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='172.16.86.80',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='repl123',
    ->   MASTER_LOG_FILE='mysql-bin.000014',
    ->   MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)


(root@localhost) [(none)]>
(root@localhost) [(none)]>
(root@localhost) [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)


(root@localhost) [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.86.80
                  Master_User: repl
                  Master_Port: 5627
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000014
          Read_Master_Log_Pos: 408
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 571
        Relay_Master_Log_File: mysql-bin.000014
             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: 408
              Relay_Log_Space: 744
              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: 103
                  Master_UUID: 4e7da0ee-5804-11e8-9b77-005056b23e3a
             Master_Info_File: /app/data/5627/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec);


复制

1.5.6.5.4.MHA 复制检查

[root@DFJK-TEST-27 log]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Thu May 17 16:07:16 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 17 16:07:16 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Thu May 17 16:07:16 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Thu May 17 16:07:16 2018 - [info] MHA::MasterMonitor version 0.56.
Thu May 17 16:07:17 2018 - [info] GTID failover mode = 0
Thu May 17 16:07:17 2018 - [info] Dead Servers:
Thu May 17 16:07:17 2018 - [info] Alive Servers:
Thu May 17 16:07:17 2018 - [info]   172.16.86.80(172.16.86.80:5627)
Thu May 17 16:07:17 2018 - [info]   172.16.86.79(172.16.86.79:5627)
Thu May 17 16:07:17 2018 - [info]   172.16.86.81(172.16.86.81:5627)
Thu May 17 16:07:17 2018 - [info] Alive Slaves:
Thu May 17 16:07:17 2018 - [info]   172.16.86.79(172.16.86.79:5627)  Version=5.6.27-log (oldest major version between slaves) log-bin:enabled
Thu May 17 16:07:17 2018 - [info]     Replicating from 172.16.86.80(172.16.86.80:5627)
Thu May 17 16:07:17 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 17 16:07:17 2018 - [info]   172.16.86.81(172.16.86.81:5627)  Version=5.6.27-log (oldest major version between slaves) log-bin:enabled
Thu May 17 16:07:17 2018 - [info]     Replicating from 172.16.86.80(172.16.86.80:5627)
Thu May 17 16:07:17 2018 - [info]     Not candidate for the new Master (no_master is set)
Thu May 17 16:07:17 2018 - [info] Current Alive Master: 172.16.86.80(172.16.86.80:5627)
Thu May 17 16:07:17 2018 - [info] Checking slave configurations..
Thu May 17 16:07:17 2018 - [info]  read_only=1 is not set on slave 172.16.86.79(172.16.86.79:5627).
Thu May 17 16:07:17 2018 - [warning]  relay_log_purge=0 is not set on slave 172.16.86.79(172.16.86.79:5627).
Thu May 17 16:07:17 2018 - [info]  read_only=1 is not set on slave 172.16.86.81(172.16.86.81:5627).
Thu May 17 16:07:17 2018 - [warning]  relay_log_purge=0 is not set on slave 172.16.86.81(172.16.86.81:5627).
Thu May 17 16:07:17 2018 - [info] Checking replication filtering settings..
Thu May 17 16:07:17 2018 - [info]  binlog_do_db= , binlog_ignore_db=
Thu May 17 16:07:17 2018 - [info]  Replication filtering check ok.
Thu May 17 16:07:17 2018 - [info] GTID (with auto-pos) is not supported
Thu May 17 16:07:17 2018 - [info] Starting SSH connection tests..
Thu May 17 16:07:18 2018 - [info] All SSH connection tests passed successfully.
Thu May 17 16:07:18 2018 - [info] Checking MHA Node version..
Thu May 17 16:07:19 2018 - [info]  Version check ok.
Thu May 17 16:07:19 2018 - [info] Checking SSH publickey authentication settings on the current master..
Thu May 17 16:07:19 2018 - [info] HealthCheck: SSH to 172.16.86.80 is reachable.
Thu May 17 16:07:19 2018 - [info] Master MHA Node version is 0.56.
Thu May 17 16:07:19 2018 - [info] Checking recovery script configurations on 172.16.86.80(172.16.86.80:5627)..
Thu May 17 16:07:19 2018 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/app/mysql-5627/binlog --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000014
Thu May 17 16:07:19 2018 - [info]   Connecting to root@172.16.86.80(172.16.86.80:22)..
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /app/mysql-5627/binlog, up to mysql-bin.000014
Thu May 17 16:07:19 2018 - [info] Binlog setting check done.
Thu May 17 16:07:19 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu May 17 16:07:19 2018 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=172.16.86.79 --slave_ip=172.16.86.79 --slave_port=5627 --workdir=/var/tmp --target_version=5.6.27-log --manager_version=0.56 --relay_log_info=/app/data/5627/data/relay-log.info  --relay_dir=/app/data/5627/data/  --slave_pass=xxx
Thu May 17 16:07:19 2018 - [info]   Connecting to root@172.16.86.79(172.16.86.79:22)..
  Checking slave recovery environment settings..
    Opening /app/data/5627/data/relay-log.info ... ok.
    Relay log found at /app/data/5627/data, up to mysql-relay-bin.000004
    Temporary relay log file is /app/data/5627/data/mysql-relay-bin.000004
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu May 17 16:07:20 2018 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=172.16.86.81 --slave_ip=172.16.86.81 --slave_port=5627 --workdir=/var/tmp --target_version=5.6.27-log --manager_version=0.56 --relay_log_info=/app/data/5627/data/relay-log.info  --relay_dir=/app/data/5627/data/  --slave_pass=xxx
Thu May 17 16:07:20 2018 - [info]   Connecting to root@172.16.86.81(172.16.86.81:22)..
  Checking slave recovery environment settings..
    Opening /app/data/5627/data/relay-log.info ... ok.
    Relay log found at /app/data/5627/data, up to mysql-relay-bin.000002
    Temporary relay log file is /app/data/5627/data/mysql-relay-bin.000002
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu May 17 16:07:20 2018 - [info] Slaves settings check done.
Thu May 17 16:07:20 2018 - [info]
172.16.86.80(172.16.86.80:5627) (current master)
 +--172.16.86.79(172.16.86.79:5627)
 +--172.16.86.81(172.16.86.81:5627)


Thu May 17 16:07:20 2018 - [info] Checking replication health on 172.16.86.79..
Thu May 17 16:07:20 2018 - [info]  ok.
Thu May 17 16:07:20 2018 - [info] Checking replication health on 172.16.86.81..
Thu May 17 16:07:20 2018 - [info]  ok.
Thu May 17 16:07:20 2018 - [warning] master_ip_failover_script is not defined.
Thu May 17 16:07:20 2018 - [warning] shutdown_script is not defined.
Thu May 17 16:07:20 2018 - [info] Got exit code 0 (Not master dead).


MySQL Replication Health is OK.
复制

80主库查看进程：

(root@localhost) [(none)]> show processlist;
+-------+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id    | User | Host               | db   | Command     | Time | State                                                                 | Info             |
+-------+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
|  8135 | repl | DFJK-TEST-27:51005 | NULL | Binlog Dump | 5590 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|  8951 | root | localhost          | NULL | Query       |    0 | init                                                                  | show processlist |
| 12226 | mha  | DFJK-TEST-27:53146 | NULL | Sleep       |    1 |                                                                       | NULL             |
| 13764 | repl | DFJK-TEST-25:42824 | NULL | Binlog Dump |   25 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+-------+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
复制

手动切换master，命令如下

masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=dead --dead_master_host=172.16.86.79

masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.16.86.80

现主库80停止后步骤同原79库。

mysql

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

柚子

2年前

能否稍稍对每一步操作做下说明，以及有些操作日常会遇到的问题

2年前