MySQL 5.7.15：Master HA（0.56） on CentOS7
Adamhuan发表于 2017-02-08 16:38:56
所有服务器的基本配置：
关闭防火墙与SELINUX
[root@mha1 script_me]# sestatus SELinux status: disabled [root@mha1 script_me]# [root@mha1 script_me]# systemctl status firewalld firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled) Active: inactive (dead) [root@mha1 script_me]#
文件：/etc/hosts
[root@mha1 script_me]# systemctl status firewalld firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled) Active: inactive (dead) [root@mha1 script_me]# [root@mha1 script_me]# [root@mha1 script_me]# cat /etc/hosts #127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 #::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 # Local 127.0.0.1 localhost # Pub 192.168.19.151 mha1 # manager 192.168.19.152 mha2 # node 192.168.19.153 mha3 # node 192.168.19.154 mha4 # node [root@mha1 script_me]#
SSH等价：
脚本下载地址：https://github.com/adamhuan/linux-script/tree/master/Linux_%E5%BC%82%E7%AB%AF/SSH%E7%AD%89%E4%BB%B7%E5%85%B3%E7%B3%BB
运行该脚本即可：
sh ssh_disable_password.sh mha1 mha2 mha3 mha4
注意，执行该脚本配置SSH等价关系之前，需要确保所有的【/etc/hosts】文件都已经如上配置。
打开YUM缓存：
文件：/etc/yum.conf
sed -i ‘/keepcache/s/0/1/g’ /etc/yum.conf
[root@mha1 script_me]# cat /etc/yum.conf [main] cachedir=/var/cache/yum/$basearch/$releasever keepcache=0 debuglevel=2 logfile=/var/log/yum.log exactarch=1 obsoletes=1 gpgcheck=1 plugins=1 installonly_limit=5 bugtracker_url=http://bugs.centos.org/set_project.php?project_id=23&ref=http://bugs.centos.org/bug_report_page.php?category=yum distroverpkg=centos-release # This is the default, if you make this bigger yum won't see if the metadata # is newer on the remote and so you'll "gain" the bandwidth of not having to # download the new metadata and "pay" for it by yum not having correct # information. # It is esp. important, to have correct metadata, for distributions like # Fedora which don't keep old packages around. If you don't like this checking # interupting your command line usage, it's much better to have something # manually check the metadata once an hour (yum-updatesd will do this). # metadata_expire=90m # PUT YOUR REPOS HERE OR IN separate files named file.repo # in /etc/yum.repos.d [root@mha1 script_me]# [root@mha1 script_me]# sed -i '/keepcache/s/0/1/g' /etc/yum.conf [root@mha1 script_me]# [root@mha1 script_me]# cat /etc/yum.conf [main] cachedir=/var/cache/yum/$basearch/$releasever keepcache=1 debuglevel=2 logfile=/var/log/yum.log exactarch=1 obsoletes=1 gpgcheck=1 plugins=1 installonly_limit=5 bugtracker_url=http://bugs.centos.org/set_project.php?project_id=23&ref=http://bugs.centos.org/bug_report_page.php?category=yum distroverpkg=centos-release # This is the default, if you make this bigger yum won't see if the metadata # is newer on the remote and so you'll "gain" the bandwidth of not having to # download the new metadata and "pay" for it by yum not having correct # information. # It is esp. important, to have correct metadata, for distributions like # Fedora which don't keep old packages around. If you don't like this checking # interupting your command line usage, it's much better to have something # manually check the metadata once an hour (yum-updatesd will do this). # metadata_expire=90m # PUT YOUR REPOS HERE OR IN separate files named file.repo # in /etc/yum.repos.d [root@mha1 script_me]#
查看下当前的系统的缓存RPM包的信息：
（各个节点的主机名会不同，以此区分）
[root@mha1 script_me]# find /var/cache/yum/ -name "*.rpm" | wc -l 749 [root@mha1 script_me]# [root@mha2 script_me]# find /var/cache/yum/ -name "*.rpm" | wc -l 835 [root@mha2 script_me]# [root@mha3 script_me]# find /var/cache/yum/ -name "*.rpm" | wc -l 835 [root@mha3 script_me]# [root@mha4 script_me]# find /var/cache/yum/ -name "*.rpm" | wc -l 835 [root@mha4 script_me]#
创建需要的目录结构（1）：
mkdir /{temp_me,backup_me,software_me,iso_me,script_me}
这里是开始配置的时候，按照个人习惯，做出的配置，后面再具体配置MHA的时候，还会涉及到目录结构的创建。
————————
至此，以上就是通用配置了。
下面开始针对具体的服务器做出配置。
一、配置MySQL
（所有服务器上安装，但是启动，只需要在MHA的NODE节点）
我这里采用的MySQL发行版是：Percona
官方网站为：
https://www.percona.com/
下载页面：
https://www.percona.com/downloads/
MySQL 5.7.17-11
https://www.percona.com/downloads/Percona-Server-5.7/LATEST/
MySQL 5.6.34-79.1
https://www.percona.com/downloads/Percona-Server-5.6/LATEST/
这里，我选择的MySQL版本为：5.7.15-9
上传服务器，并解压：
[root@mha1 script_me]# ls -ltr /software_me/ total 225380 -rw-r--r-- 1 root root 230789120 Nov 7 12:58 Percona-Server-5.7.15-9-r9f0fd0a-el6-x86_64-bundle.tar [root@mha1 script_me]# [root@mha1 script_me]# [root@mha1 script_me]# scp /software_me/Percona-Server-5.7.15-9-r9f0fd0a-el6-x86_64-bundle.tar mha2:/software_me/ Percona-Server-5.7.15-9-r9f0fd0a-el6-x86_64-bundle.tar 100% 220MB 44.0MB/s 00:05 [root@mha1 script_me]# [root@mha1 script_me]# scp /software_me/Percona-Server-5.7.15-9-r9f0fd0a-el6-x86_64-bundle.tar mha3:/software_me/ Percona-Server-5.7.15-9-r9f0fd0a-el6-x86_64-bundle.tar 100% 220MB 31.4MB/s 00:07 [root@mha1 script_me]# [root@mha1 script_me]# scp /software_me/Percona-Server-5.7.15-9-r9f0fd0a-el6-x86_64-bundle.tar mha4:/software_me/ Percona-Server-5.7.15-9-r9f0fd0a-el6-x86_64-bundle.tar 100% 220MB 36.7MB/s 00:06 [root@mha1 script_me]# 解压： [root@mha1 script_me]# cd /software_me [root@mha1 software_me]# ls -ltr * -rw-r--r-- 1 root root 230789120 Nov 7 12:58 Percona-Server-5.7.15-9-r9f0fd0a-el6-x86_64-bundle.tar [root@mha1 software_me]# [root@mha1 software_me]# tar -xf *.tar [root@mha1 software_me]# ls -ltr * -rw-rw-r-- 1 root root 1281236 Oct 18 16:16 Percona-Server-devel-57-5.7.15-9.1.el6.x86_64.rpm -rw-rw-r-- 1 root root 8404220 Oct 18 16:16 Percona-Server-client-57-5.7.15-9.1.el6.x86_64.rpm -rw-rw-r-- 1 root root 145811460 Oct 18 16:16 Percona-Server-57-debuginfo-5.7.15-9.1.el6.x86_64.rpm -rw-rw-r-- 1 root root 42409500 Oct 18 16:16 Percona-Server-server-57-5.7.15-9.1.el6.x86_64.rpm -rw-rw-r-- 1 root root 30371004 Oct 18 16:16 Percona-Server-test-57-5.7.15-9.1.el6.x86_64.rpm -rw-rw-r-- 1 root root 925228 Oct 18 16:16 Percona-Server-shared-57-5.7.15-9.1.el6.x86_64.rpm -rw-rw-r-- 1 root root 1573044 Oct 18 16:16 Percona-Server-tokudb-57-5.7.15-9.1.el6.x86_64.rpm -rw-r--r-- 1 root root 230789120 Nov 7 12:58 Percona-Server-5.7.15-9-r9f0fd0a-el6-x86_64-bundle.tar [root@mha1 software_me]#
配置本地YUM：
[root@mha1 software_me]# yum repolist Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.btte.net * extras: mirrors.btte.net * updates: mirrors.zju.edu.cn repo id repo name status ISO ISO 8,652 base/7/x86_64 CentOS-7 - Base 9,363 extras/7/x86_64 CentOS-7 - Extras 263 updates/7/x86_64 CentOS-7 - Updates 807 repolist: 19,085 [root@mha1 software_me]# [root@mha1 software_me]# yum install -y createrepo Loaded plugins: fastestmirror, langpacks ISO | 3.6 kB 00:00:00 Loading mirror speeds from cached hostfile * base: mirrors.btte.net * extras: mirrors.btte.net * updates: mirrors.zju.edu.cn Resolving Dependencies --> Running transaction check ---> Package createrepo.noarch 0:0.9.9-23.el7 will be updated ---> Package createrepo.noarch 0:0.9.9-26.el7 will be an update --> Finished Dependency Resolution Dependencies Resolved =========================================================================================================== Package Arch Version Repository Size =========================================================================================================== Updating: createrepo noarch 0.9.9-26.el7 base 92 k Transaction Summary =========================================================================================================== Upgrade 1 Package Total size: 92 k Downloading packages: warning: /var/cache/yum/x86_64/7/base/packages/createrepo-0.9.9-26.el7.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 Importing GPG key 0xF4A80EB5: Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) " Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5 Package : centos-release-7-1.1503.el7.centos.2.8.x86_64 (@anaconda) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 Running transaction check Running transaction test Transaction test succeeded Running transaction Updating : createrepo-0.9.9-26.el7.noarch 1/2 Cleanup : createrepo-0.9.9-23.el7.noarch 2/2 Verifying : createrepo-0.9.9-26.el7.noarch 1/2 Verifying : createrepo-0.9.9-23.el7.noarch 2/2 Updated: createrepo.noarch 0:0.9.9-26.el7 Complete! [root@mha1 software_me]# [root@mha1 software_me]# [root@mha1 software_me]# createrepo /software_me Spawning worker 0 with 7 pkgs Workers Finished Saving Primary metadata Saving file lists metadata Saving other metadata Generating sqlite DBs Sqlite DBs complete [root@mha1 software_me]# [root@mha1 software_me]# cat /etc/yum.repos.d/local_percona.repo [local_percona] name=local_percona baseurl=file:///software_me gpgcheck=0 [root@mha1 software_me]# [root@mha1 software_me]# scp /etc/yum.repos.d/local_percona.repo mha2:/etc/yum.repos.d/ local_percona.repo 100% 74 0.1KB/s 00:00 [root@mha1 software_me]# scp /etc/yum.repos.d/local_percona.repo mha3:/etc/yum.repos.d/ local_percona.repo 100% 74 0.1KB/s 00:00 [root@mha1 software_me]# scp /etc/yum.repos.d/local_percona.repo mha4:/etc/yum.repos.d/ local_percona.repo 100% 74 0.1KB/s 00:00 [root@mha1 software_me]# [root@mha1 software_me]# yum repolist Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.btte.net * extras: mirrors.btte.net * updates: mirrors.zju.edu.cn repo id repo name status ISO ISO 8,652 base/7/x86_64 CentOS-7 - Base 9,363 extras/7/x86_64 CentOS-7 - Extras 263 local_percona local_percona 7 updates/7/x86_64 CentOS-7 - Updates 807 repolist: 19,092 [root@mha1 software_me]#
利用刚刚配好的YUM安装Percona发行版的MySQL：
[root@mha1 software_me]# yum list | grep --color percona Percona-Server-57-debuginfo.x86_64 5.7.15-9.1.el6 local_percona Percona-Server-client-57.x86_64 5.7.15-9.1.el6 local_percona Percona-Server-devel-57.x86_64 5.7.15-9.1.el6 local_percona Percona-Server-server-57.x86_64 5.7.15-9.1.el6 local_percona Percona-Server-shared-57.x86_64 5.7.15-9.1.el6 local_percona Percona-Server-test-57.x86_64 5.7.15-9.1.el6 local_percona Percona-Server-tokudb-57.x86_64 5.7.15-9.1.el6 local_percona [root@mha1 software_me]# [root@mha1 software_me]# yum install -y Percona-Server-server-57.x86_64 Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.btte.net * extras: mirrors.btte.net * updates: mirrors.zju.edu.cn Resolving Dependencies --> Running transaction check ---> Package Percona-Server-server-57.x86_64 0:5.7.15-9.1.el6 will be installed --> Processing Dependency: Percona-Server-shared-57 for package: Percona-Server-server-57-5.7.15-9.1.el6.x86_64 --> Processing Dependency: Percona-Server-client-57 for package: Percona-Server-server-57-5.7.15-9.1.el6.x86_64 --> Running transaction check ---> Package Percona-Server-client-57.x86_64 0:5.7.15-9.1.el6 will be installed ---> Package Percona-Server-shared-57.x86_64 0:5.7.15-9.1.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved =========================================================================================================== Package Arch Version Repository Size =========================================================================================================== Installing: Percona-Server-server-57 x86_64 5.7.15-9.1.el6 local_percona 40 M Installing for dependencies: Percona-Server-client-57 x86_64 5.7.15-9.1.el6 local_percona 8.0 M Percona-Server-shared-57 x86_64 5.7.15-9.1.el6 local_percona 904 k Transaction Summary =========================================================================================================== Install 1 Package (+2 Dependent packages) Total download size: 49 M Installed size: 219 M Downloading packages: ----------------------------------------------------------------------------------------------------------- Total 200 MB/s | 49 MB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : Percona-Server-shared-57-5.7.15-9.1.el6.x86_64 1/3 Installing : Percona-Server-client-57-5.7.15-9.1.el6.x86_64 2/3 Installing : Percona-Server-server-57-5.7.15-9.1.el6.x86_64 3/3 Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit. Run the following commands to create these functions: mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'" See http://www.percona.com/doc/percona-server/5.7/management/udf_percona_toolkit.html for more details Verifying : Percona-Server-client-57-5.7.15-9.1.el6.x86_64 1/3 Verifying : Percona-Server-shared-57-5.7.15-9.1.el6.x86_64 2/3 Verifying : Percona-Server-server-57-5.7.15-9.1.el6.x86_64 3/3 Installed: Percona-Server-server-57.x86_64 0:5.7.15-9.1.el6 Dependency Installed: Percona-Server-client-57.x86_64 0:5.7.15-9.1.el6 Percona-Server-shared-57.x86_64 0:5.7.15-9.1.el6 Complete! [root@mha1 software_me]# [root@mha1 software_me]#
配置MySQL：
文件：/etc/percona-server.conf.d/mysqld.cnf
MySQL Master:
[root@mha2 ~]# cat /etc/percona-server.conf.d/mysqld.cnf # Percona Server template configuration [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # mysql replication server-id=1 log-bin=mysql-bin relay-log=mysql-relay relay_log_purge=off #read_only=on [root@mha2 ~]#
MySQL Behind Master
[root@mha3 software_me]# cat /etc/percona-server.conf.d/mysqld.cnf # Percona Server template configuration [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # mysql replication server-id=2 log-bin=mysql-bin relay-log=mysql-relay relay_log_purge=off read_only=on [root@mha3 software_me]#
MySQL Slave
[root@mha4 software_me]# cat /etc/percona-server.conf.d/mysqld.cnf # Percona Server template configuration [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # mysql replication server-id=3 log-bin=mysql-bin relay-log=mysql-relay relay_log_purge=off read_only=on [root@mha4 software_me]#
然后，启动MySQL数据库：
[root@mha2 ~]# ps -ef | grep --color mysql root 44094 43966 0 17:46 pts/1 00:00:00 grep --color=auto --color mysql [root@mha2 ~]# [root@mha2 ~]# netstat -tupln | grep mysql [root@mha2 ~]# [root@mha2 ~]# service mysql start Starting mysql (via systemctl): [ OK ] [root@mha2 ~]# [root@mha2 ~]# ps -ef | grep --color mysql root 44219 1 0 17:47 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql mysql 44503 44219 10 17:47 ? 00:00:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock root 44544 43966 0 17:47 pts/1 00:00:00 grep --color=auto --color mysql [root@mha2 ~]# [root@mha2 ~]# netstat -tupln | grep mysql tcp6 0 0 :::3306 :::* LISTEN 44503/mysqld [root@mha2 ~]# [root@mha2 ~]#
修改Percona的默认临时口令：
[root@mha2 ~]# cat /var/log/mysqld.log | grep --color "A temporary" 2017-02-08T09:46:48.023753Z 1 [Note] A temporary password is generated for root@localhost: H,irl_=ke7Vv [root@mha2 ~]# [root@mha2 ~]# mysqladmin -u root -p'H,irl_=ke7Vv' password 'Abcd@123' mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. [root@mha2 ~]# [root@mha2 ~]# mysql -u root -p'Abcd@123' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> exit Bye [root@mha2 ~]#
配置MySQL的复制（主：MHA2，从：MHA3 / MHA4）
MySQL Master
[root@mha2 ~]# mysql -u root -p'Abcd@123' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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> mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 414 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show slave status\G Empty set (0.00 sec) mysql> 创建需要的用户： mysql> select user,host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | mysql.sys | localhost | | root | localhost | +-----------+-----------+ 2 rows in set (0.00 sec) mysql> # 在决定作为主以及备主的库中，需要存在该用户 mysql> grant replication slave on *.* to 'replme'@'192.168.19.%' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) # 该用户，可以在后面建立了主从结构后，再操作，MySQL的三台服务器都需要有该用户，主从建立好了以后，只需要在主上执行，就可以同步到两台从库 mysql> grant select,insert,update,delete,create,drop,super,process on *.* to 'mhame'@'192.168.19.%' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> select user,host from mysql.user; +-----------+--------------+ | user | host | +-----------+--------------+ | mhame | 192.168.19.% | | replme | 192.168.19.% | | mysql.sys | localhost | | root | localhost | +-----------+--------------+ 4 rows in set (0.00 sec) mysql> mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 1869 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
MySQL Slave：
MHA3 / MHA4
[root@mha3 software_me]# mysql -u root -p'Abcd@123';
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, 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> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 414 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> show slave status\G
Empty set (0.00 sec)
mysql>
mysql> change master to master_host='192.168.19.152',master_user='replme',master_password='Abcd@123',master_log_file='mysql-bin.000003',master_log_pos=1869;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.19.152
Master_User: replme
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1869
Relay_Log_File: mysql-relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
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: 1869
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.19.152
Master_User: replme
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1869
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 320
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: 1869
Relay_Log_Space: 523
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
Master_UUID: 81e39572-ede3-11e6-a180-000c2966cef7
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>操作都是类似的，上面只演示在MHA3上的操作。
MySQL的Replication配置好了以后，可以更新一下用户策略：
mysql> select user,host from mysql.user; +-----------+--------------+ | user | host | +-----------+--------------+ | mhame | 192.168.19.% | | replme | 192.168.19.% | | mysql.sys | localhost | | root | localhost | +-----------+--------------+ 4 rows in set (0.00 sec) mysql> mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'mha1' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'mha2' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'mha3' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'mha4' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, SUPER ON *.* TO 'mhame'@'mha1' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, SUPER ON *.* TO 'mhame'@'mha2' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, SUPER ON *.* TO 'mhame'@'mha3' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, SUPER ON *.* TO 'mhame'@'mha4' identified by 'Abcd@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> select user,host from mysql.user; +-----------+--------------+ | user | host | +-----------+--------------+ | mhame | 192.168.19.% | | replme | 192.168.19.% | | mysql.sys | localhost | | root | localhost | | mhame | mha1 | | root | mha1 | | mhame | mha2 | | root | mha2 | | mhame | mha3 | | root | mha3 | | mhame | mha4 | | root | mha4 | +-----------+--------------+ 12 rows in set (0.00 sec) mysql>
这样，MySQL部分的配置就算完成了。
二、Keepalive：安装与配置
只需要在主与备主的两个服务器上配置。
在当前环境里，即：MHA2 / MHA3
通过YUM就可以安装KEEPALIVED：
[root@mha2 ~]# rpm -qa | grep --color keepalive [root@mha2 ~]# [root@mha2 ~]# yum list | grep --color keepalive keepalived.x86_64 1.2.13-8.el7 base [root@mha2 ~]# [root@mha2 ~]# yum install -y keepalived Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.zju.edu.cn * extras: mirrors.zju.edu.cn * updates: mirrors.zju.edu.cn Resolving Dependencies --> Running transaction check ---> Package keepalived.x86_64 0:1.2.13-8.el7 will be installed --> Processing Dependency: libnetsnmpmibs.so.31()(64bit) for package: keepalived-1.2.13-8.el7.x86_64 --> Processing Dependency: libnetsnmpagent.so.31()(64bit) for package: keepalived-1.2.13-8.el7.x86_64 --> Running transaction check ---> Package net-snmp-agent-libs.x86_64 1:5.7.2-24.el7_2.1 will be installed --> Processing Dependency: net-snmp-libs = 1:5.7.2-24.el7_2.1 for package: 1:net-snmp-agent-libs-5.7.2-24.el7_2.1.x86_64 --> Running transaction check ---> Package net-snmp-libs.x86_64 1:5.7.2-20.el7 will be updated ---> Package net-snmp-libs.x86_64 1:5.7.2-24.el7_2.1 will be an update --> Finished Dependency Resolution Dependencies Resolved =========================================================================================================== Package Arch Version Repository Size =========================================================================================================== Installing: keepalived x86_64 1.2.13-8.el7 base 224 k Installing for dependencies: net-snmp-agent-libs x86_64 1:5.7.2-24.el7_2.1 base 702 k Updating for dependencies: net-snmp-libs x86_64 1:5.7.2-24.el7_2.1 base 747 k Transaction Summary =========================================================================================================== Install 1 Package (+1 Dependent package) Upgrade ( 1 Dependent package) Total size: 1.6 M Total download size: 926 k Downloading packages: warning: /var/cache/yum/x86_64/7/base/packages/keepalived-1.2.13-8.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY Public key for keepalived-1.2.13-8.el7.x86_64.rpm is not installed (1/2): keepalived-1.2.13-8.el7.x86_64.rpm | 224 kB 00:00:14 (2/2): net-snmp-agent-libs-5.7.2-24.el7_2.1.x86_64.rpm | 702 kB 00:00:23 ----------------------------------------------------------------------------------------------------------- Total 39 kB/s | 926 kB 00:00:23 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 Importing GPG key 0xF4A80EB5: Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) " Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5 Package : centos-release-7-1.1503.el7.centos.2.8.x86_64 (@anaconda) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 Running transaction check Running transaction test Transaction test succeeded Running transaction Updating : 1:net-snmp-libs-5.7.2-24.el7_2.1.x86_64 1/4 Installing : 1:net-snmp-agent-libs-5.7.2-24.el7_2.1.x86_64 2/4 Installing : keepalived-1.2.13-8.el7.x86_64 3/4 Cleanup : 1:net-snmp-libs-5.7.2-20.el7.x86_64 4/4 Verifying : 1:net-snmp-agent-libs-5.7.2-24.el7_2.1.x86_64 1/4 Verifying : keepalived-1.2.13-8.el7.x86_64 2/4 Verifying : 1:net-snmp-libs-5.7.2-24.el7_2.1.x86_64 3/4 Verifying : 1:net-snmp-libs-5.7.2-20.el7.x86_64 4/4 Installed: keepalived.x86_64 0:1.2.13-8.el7 Dependency Installed: net-snmp-agent-libs.x86_64 1:5.7.2-24.el7_2.1 Dependency Updated: net-snmp-libs.x86_64 1:5.7.2-24.el7_2.1 Complete! [root@mha2 ~]# [root@mha2 ~]# rpm -qa | grep --color keepalive keepalived-1.2.13-8.el7.x86_64 [root@mha2 ~]# [root@mha2 ~]# rpm -ql keepalived-1.2.13-8.el7 /etc/keepalived /etc/keepalived/keepalived.conf /etc/sysconfig/keepalived /usr/bin/genhash /usr/lib/systemd/system/keepalived.service /usr/libexec/keepalived /usr/sbin/keepalived /usr/share/doc/keepalived-1.2.13 /usr/share/doc/keepalived-1.2.13/AUTHOR /usr/share/doc/keepalived-1.2.13/CONTRIBUTORS /usr/share/doc/keepalived-1.2.13/COPYING /usr/share/doc/keepalived-1.2.13/ChangeLog /usr/share/doc/keepalived-1.2.13/NOTE_vrrp_vmac.txt /usr/share/doc/keepalived-1.2.13/README /usr/share/doc/keepalived-1.2.13/TODO /usr/share/doc/keepalived-1.2.13/VERSION /usr/share/doc/keepalived-1.2.13/keepalived.conf.SYNOPSIS /usr/share/doc/keepalived-1.2.13/samples /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.HTTP_GET.port /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.IPv6 /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.SMTP_CHECK /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.SSL_GET /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.fwmark /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.inhibit /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.misc_check /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.misc_check_arg /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.quorum /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.sample /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.status_code /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.track_interface /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.virtual_server_group /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.virtualhost /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.vrrp /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.vrrp.localcheck /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.vrrp.lvs_syncd /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.vrrp.routes /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.vrrp.scripts /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.vrrp.static_ipaddress /usr/share/doc/keepalived-1.2.13/samples/keepalived.conf.vrrp.sync /usr/share/doc/keepalived-1.2.13/samples/sample.misccheck.smbcheck.sh /usr/share/man/man1/genhash.1.gz /usr/share/man/man5/keepalived.conf.5.gz /usr/share/man/man8/keepalived.8.gz /usr/share/snmp/mibs/KEEPALIVED-MIB.txt [root@mha2 ~]#
配置KEEPALIVED：
文件：/etc/keepalived/keepalived.conf
[root@mha2 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.200.16
192.168.200.17
192.168.200.18
}
}
virtual_server 192.168.200.100 443 {
delay_loop 6
lb_algo rr
lb_kind NAT
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.201.100 443 {
weight 1
SSL_GET {
url {
path /
digest ff20ad2481f97b1754ef3e12ecd3a9cc
}
url {
path /mrtg/
digest 9b3a0c85a887a256d6939da88aabd8cd
}
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
virtual_server 10.10.10.2 1358 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
sorry_server 192.168.200.200 1358
real_server 192.168.200.2 1358 {
weight 1
HTTP_GET {
url {
path /testurl/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334d
}
url {
path /testurl2/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334d
}
url {
path /testurl3/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334d
}
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
real_server 192.168.200.3 1358 {
weight 1
HTTP_GET {
url {
path /testurl/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334c
}
url {
path /testurl2/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334c
}
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
virtual_server 10.10.10.3 1358 {
delay_loop 3
lb_algo rr
lb_kind NAT
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.200.4 1358 {
weight 1
HTTP_GET {
url {
path /testurl/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334d
}
url {
path /testurl2/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334d
}
url {
path /testurl3/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334d
}
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
real_server 192.168.200.5 1358 {
weight 1
HTTP_GET {
url {
path /testurl/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334d
}
url {
path /testurl2/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334d
}
url {
path /testurl3/test.jsp
digest 640205b7b0fc66c1ea91c463fac6334d
}
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
[root@mha2 ~]#
[root@mha2 ~]# cp /etc/keepalived/keepalived.conf /backup_me/
[root@mha2 ~]#
[root@mha2 ~]# echo "" > /etc/keepalived/keepalived.conf
[root@mha2 ~]#
[root@mha2 ~]# vi /etc/keepalived/keepalived.conf
[root@mha2 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
# notification_email {
# acassen@firewall.loc
# failover@firewall.loc
# sysadmin@firewall.loc
# }
# notification_email_from Alexandre.Cassen@firewall.loc
# smtp_server 192.168.200.1
# smtp_connect_timeout 30
router_id percona_1 # route id , 用来区分不同主机的
}
vrrp_instance VI_66 { #实例号，多个实例环境中，用来区分配置的
state MASTER #这个是初始化的状态，几乎不起作用，可以随便设置
interface eno16777728 #IP绑定的网卡口，非常重要，绑错，问题就大了
virtual_router_id 66 #这个相同组的实例必须有相同id
priority 93 #非常重要，决定了谁是master,范围是1-255
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.19.160/24 #VIP,可以设置多个
}
}
[root@mha2 ~]#
启动MHA2的KEEPALIVED：
[root@mha2 ~]# service keepalived start
Redirecting to /bin/systemctl start keepalived.service
[root@mha2 ~]#
[root@mha2 ~]# service keepalived status
Redirecting to /bin/systemctl status keepalived.service
keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled)
Active: active (running) since Wed 2017-02-08 20:54:12 CST; 11s ago
Process: 46024 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 46025 (keepalived)
CGroup: /system.slice/keepalived.service
├─46025 /usr/sbin/keepalived -D
├─46026 /usr/sbin/keepalived -D
└─46027 /usr/sbin/keepalived -D
Feb 08 20:54:12 mha2 Keepalived_healthcheckers[46026]: Registering Kernel netlink command channel
Feb 08 20:54:12 mha2 Keepalived_healthcheckers[46026]: Opening file '/etc/keepalived/keepalived.conf'.
Feb 08 20:54:12 mha2 Keepalived_healthcheckers[46026]: Configuration is using : 6130 Bytes
Feb 08 20:54:12 mha2 Keepalived_healthcheckers[46026]: Using LinkWatch kernel netlink reflector...
Feb 08 20:54:13 mha2 Keepalived_vrrp[46027]: VRRP_Instance(VI_66) Transition to MASTER STATE
Feb 08 20:54:14 mha2 Keepalived_vrrp[46027]: VRRP_Instance(VI_66) Entering MASTER STATE
Feb 08 20:54:14 mha2 Keepalived_vrrp[46027]: VRRP_Instance(VI_66) setting protocol VIPs.
Feb 08 20:54:14 mha2 Keepalived_vrrp[46027]: VRRP_Instance(VI_66) Sending gratuitous ARPs on eno1677....160
Feb 08 20:54:14 mha2 Keepalived_healthcheckers[46026]: Netlink reflector reports IP 192.168.19.160 added
Feb 08 20:54:19 mha2 Keepalived_vrrp[46027]: VRRP_Instance(VI_66) Sending gratuitous ARPs on eno1677....160
Hint: Some lines were ellipsized, use -l to show in full.
[root@mha2 ~]#
[root@mha2 ~]# ps -ef | grep --color keepalived
root 46025 1 0 20:54 ? 00:00:00 /usr/sbin/keepalived -D
root 46026 46025 0 20:54 ? 00:00:00 /usr/sbin/keepalived -D
root 46027 46025 0 20:54 ? 00:00:00 /usr/sbin/keepalived -D
root 46046 45289 0 20:54 pts/0 00:00:00 grep --color=auto --color keepalived
[root@mha2 ~]#
启动后，你可以看到MHA2上多了一个IP，就是我们设置的虚拟IP
[root@mha2 ~]# ip a
1: lo: mtu 65536 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
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eno16777728: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:66:ce:f7 brd ff:ff:ff:ff:ff:ff
inet 192.168.19.152/24 brd 192.168.19.255 scope global eno16777728
valid_lft forever preferred_lft forever
inet 192.168.19.160/24 scope global secondary eno16777728
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe66:cef7/64 scope link
valid_lft forever preferred_lft forever
[root@mha2 ~]#
将配置文件传送到MHA3，修改其中的优先级的参数：
传送：
[root@mha2 ~]# scp /etc/keepalived/keepalived.conf mha3:/etc/keepalived/
keepalived.conf 100% 940 0.9KB/s 00:00
[root@mha2 ~]#
编辑：
[root@mha3 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
# notification_email {
# acassen@firewall.loc
# failover@firewall.loc
# sysadmin@firewall.loc
# }
# notification_email_from Alexandre.Cassen@firewall.loc
# smtp_server 192.168.200.1
# smtp_connect_timeout 30
router_id percona_1 # route id , 用来区分不同主机的
}
vrrp_instance VI_66 { #实例号，多个实例环境中，用来区分配置的
state MASTER #这个是初始化的状态，几乎不起作用，可以随便设置
interface eno16777728 #IP绑定的网卡口，非常重要，绑错，问题就大了
virtual_router_id 66 #这个相同组的实例必须有相同id
priority 93 #非常重要，决定了谁是master,范围是1-255
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.19.160/24 #VIP,可以设置多个
}
}
[root@mha3 ~]#
[root@mha3 ~]# vi /etc/keepalived/keepalived.conf
[root@mha3 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
# notification_email {
# acassen@firewall.loc
# failover@firewall.loc
# sysadmin@firewall.loc
# }
# notification_email_from Alexandre.Cassen@firewall.loc
# smtp_server 192.168.200.1
# smtp_connect_timeout 30
router_id percona_1 # route id , 用来区分不同主机的
}
vrrp_instance VI_66 { #实例号，多个实例环境中，用来区分配置的
state MASTER #这个是初始化的状态，几乎不起作用，可以随便设置
interface eno16777728 #IP绑定的网卡口，非常重要，绑错，问题就大了
virtual_router_id 66 #这个相同组的实例必须有相同id
priority 92 #非常重要，决定了谁是master,范围是1-255
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.19.160/24 #VIP,可以设置多个
}
}
[root@mha3 ~]#优先级规则：KEEPALIVED的优先级规则是【priority】越大，越优先。
在我的环境中，我设定为：
MHA2为主
MHA3为备主
所以最优先的运行为MHA2。
上面的MHA3的配置文件写好了以后，启动MHA3上面的KEEPALIVED
[root@mha3 ~]# service keepalived start
Redirecting to /bin/systemctl start keepalived.service
[root@mha3 ~]#
[root@mha3 ~]# ip a
1: lo: mtu 65536 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
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eno16777728: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:b6:d6:8b brd ff:ff:ff:ff:ff:ff
inet 192.168.19.153/24 brd 192.168.19.255 scope global eno16777728
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:feb6:d68b/64 scope link
valid_lft forever preferred_lft forever
[root@mha3 ~]#可以看到，如预期：
两个KEEPALIVED同时运行的时候，VIP最优先执行在MHA2上，而不会被MHA3抢过来。
三、MasterHA：安装
MySQL的MasterHA的项目主页：
http://code.google.com/p/mysql-master-ha/
官方下载页面：
http://code.google.com/p/mysql-master-ha/wiki/Downloads?tm=2
简称MHA，是一个开源项目，包含一组用perl写的脚本。
这些脚本可以帮助你完成：
1. 自动的（非计划）
2. 手动的（计划的）
MySQL Master切换操作。以便于减少数据库的宕机时间，保证高可用。
与其他的高可用不一样的是：
MHA只保证一次非计划的宕机，即只能自动切换一次。
如果想再次恢复保障，需要手动恢复环境，手动完成同步，手动将修复好的节点加入MHA集群，在通过MHA的检查后，才能再次进行监控，以便于保证下一次的非计划宕机。
这里，我将安装的MHA的版本是：0.56。
将它们上传服务器：
[root@mha1 ~]# ls -ltr /software_me | grep mha -rw-r--r-- 1 root root 87119 Nov 11 09:45 mha4mysql-manager-0.56-0.el6.noarch.rpm -rw-r--r-- 1 root root 113914 Nov 11 09:45 mha4mysql-manager-0.56.tar.gz -rw-r--r-- 1 root root 36326 Nov 11 09:45 mha4mysql-node-0.56-0.el6.noarch.rpm -rw-r--r-- 1 root root 50172 Nov 21 19:45 mha4mysql-node-0.56.tar.gz [root@mha1 ~]# [root@mha1 ~]# scp /software_me/mha4mysql-* mha2:/software_me mha4mysql-manager-0.56-0.el6.noarch.rpm 100% 85KB 85.1KB/s 00:00 mha4mysql-manager-0.56.tar.gz 100% 111KB 111.2KB/s 00:00 mha4mysql-node-0.56-0.el6.noarch.rpm 100% 35KB 35.5KB/s 00:00 mha4mysql-node-0.56.tar.gz 100% 49KB 49.0KB/s 00:00 [root@mha1 ~]# [root@mha1 ~]# scp /software_me/mha4mysql-* mha3:/software_me mha4mysql-manager-0.56-0.el6.noarch.rpm 100% 85KB 85.1KB/s 00:00 mha4mysql-manager-0.56.tar.gz 100% 111KB 111.2KB/s 00:00 mha4mysql-node-0.56-0.el6.noarch.rpm 100% 35KB 35.5KB/s 00:00 mha4mysql-node-0.56.tar.gz 100% 49KB 49.0KB/s 00:00 [root@mha1 ~]# [root@mha1 ~]# scp /software_me/mha4mysql-* mha4:/software_me mha4mysql-manager-0.56-0.el6.noarch.rpm 100% 85KB 85.1KB/s 00:00 mha4mysql-manager-0.56.tar.gz 100% 111KB 111.2KB/s 00:00 mha4mysql-node-0.56-0.el6.noarch.rpm 100% 35KB 35.5KB/s 00:00 mha4mysql-node-0.56.tar.gz 100% 49KB 49.0KB/s 00:00 [root@mha1 ~]# [root@mha1 ~]#
关于软件包的安装（大概的分布）：
MHA Manager:
mha4mysql-manager
mha4mysql-node
MHA Node：
mha4mysql-node
为了没有依赖包的干扰顺利的安装上面的软件包（具体参考：https://code.google.com/p/mysql-master-ha/wiki/Installation）。
对于MHA的各个服务器，你还需要配置接下来的事项：
EPEL：
[root@mha1 ~]# cd /software_me/ [root@mha1 software_me]# mkdir epel [root@mha1 software_me]# cd epel/ [root@mha1 epel]# ls [root@mha1 epel]# [root@mha1 epel]# wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-9.noarch.rpm --2017-02-08 21:20:57-- http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-9.noarch.rpm Resolving dl.fedoraproject.org (dl.fedoraproject.org)... 209.132.181.27, 209.132.181.23, 209.132.181.25, ... Connecting to dl.fedoraproject.org (dl.fedoraproject.org)|209.132.181.27|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 14704 (14K) [application/x-rpm] Saving to: ‘epel-release-7-9.noarch.rpm’ 100%[=================================================================>] 14,704 30.8KB/s in 0.5s 2017-02-08 21:20:59 (30.8 KB/s) - ‘epel-release-7-9.noarch.rpm’ saved [14704/14704] [root@mha1 epel]# [root@mha1 epel]# ls -ltr total 16 -rw-r--r-- 1 root root 14704 Dec 28 01:40 epel-release-7-9.noarch.rpm [root@mha1 epel]# [root@mha1 epel]# ls -ltr /etc/yum.repos.d/ total 32 -rw-r--r--. 1 root root 1002 Apr 1 2015 CentOS-Vault.repo -rw-r--r--. 1 root root 1331 Apr 1 2015 CentOS-Sources.repo -rw-r--r--. 1 root root 290 Apr 1 2015 CentOS-fasttrack.repo -rw-r--r--. 1 root root 649 Apr 1 2015 CentOS-Debuginfo.repo -rw-r--r--. 1 root root 1309 Apr 1 2015 CentOS-CR.repo -rw-r--r--. 1 root root 1664 Apr 1 2015 CentOS-Base.repo -rw-r--r-- 1 root root 394 Feb 8 17:25 rhel7.repo -rw-r--r-- 1 root root 74 Feb 8 17:29 local_percona.repo [root@mha1 epel]# [root@mha1 epel]# rpm -ivh epel-release-7-9.noarch.rpm warning: epel-release-7-9.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:epel-release-7-9 ################################# [100%] [root@mha1 epel]# [root@mha1 epel]# ls -ltr /etc/yum.repos.d/ total 40 -rw-r--r--. 1 root root 1002 Apr 1 2015 CentOS-Vault.repo -rw-r--r--. 1 root root 1331 Apr 1 2015 CentOS-Sources.repo -rw-r--r--. 1 root root 290 Apr 1 2015 CentOS-fasttrack.repo -rw-r--r--. 1 root root 649 Apr 1 2015 CentOS-Debuginfo.repo -rw-r--r--. 1 root root 1309 Apr 1 2015 CentOS-CR.repo -rw-r--r--. 1 root root 1664 Apr 1 2015 CentOS-Base.repo -rw-r--r-- 1 root root 1056 Dec 28 01:37 epel-testing.repo -rw-r--r-- 1 root root 957 Dec 28 01:37 epel.repo -rw-r--r-- 1 root root 394 Feb 8 17:25 rhel7.repo -rw-r--r-- 1 root root 74 Feb 8 17:29 local_percona.repo [root@mha1 epel]# [root@mha1 epel]# yum repolist Loaded plugins: fastestmirror, langpacks epel/x86_64/metalink | 6.2 kB 00:00:00 epel | 4.3 kB 00:00:00 (1/3): epel/x86_64/group_gz | 170 kB 00:00:00 (2/3): epel/x86_64/updateinfo | 732 kB 00:00:03 (3/3): epel/x86_64/primary_db | 4.5 MB 00:00:14 Loading mirror speeds from cached hostfile * base: mirrors.btte.net * epel: mirror.premi.st * extras: mirrors.btte.net * updates: mirrors.zju.edu.cn repo id repo name status ISO ISO 8,652 base/7/x86_64 CentOS-7 - Base 9,363 epel/x86_64 Extra Packages for Enterprise Linux 7 - x86_64 11,137 extras/7/x86_64 CentOS-7 - Extras 263 local_percona local_percona 7 updates/7/x86_64 CentOS-7 - Updates 807 repolist: 30,229 [root@mha1 epel]# ## 这里，可能会出现问题，如果不能正常的使用EPEL源，可以参考下面的文档去修正： ## http://d-prototype.com/archives/6749 ## http://d-prototype.com/archives/4993 将正确的YUM源文件，分发到其他的服务器： [root@mha1 epel]# scp /etc/yum.repos.d/epel* mha2:/etc/yum.repos.d/ epel.repo 100% 957 0.9KB/s 00:00 epel-testing.repo 100% 1056 1.0KB/s 00:00 [root@mha1 epel]# scp /etc/yum.repos.d/epel* mha3:/etc/yum.repos.d/ epel.repo 100% 957 0.9KB/s 00:00 epel-testing.repo 100% 1056 1.0KB/s 00:00 [root@mha1 epel]# scp /etc/yum.repos.d/epel* mha4:/etc/yum.repos.d/ epel.repo 100% 957 0.9KB/s 00:00 epel-testing.repo 100% 1056 1.0KB/s 00:00 [root@mha1 epel]# [root@mha1 epel]#
然后，根据官方提供的依赖列表，使用EPEL，安装依赖包：
## Install dependent Perl modules
# yum install perl-DBD-MySQL
# yum install perl-Config-Tiny
# yum install perl-Log-Dispatch
# yum install perl-Parallel-ForkManager
## Install MHA Node, since MHA Manager uses some modules provided by MHA Node.
# rpm -ivh mha4mysql-node-X.Y-0.noarch.rpm
## Finally you can install MHA Manager
# rpm -ivh mha4mysql-manager-X.Y-0.noarch.rpm
各个节点执行YUM安装：
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
或：
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes –nogpgcheck
如下：
[root@mha1 epel]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.btte.net * epel: mirror.premi.st * extras: mirrors.btte.net * updates: mirrors.zju.edu.cn Package 4:perl-Time-HiRes-1.9725-3.el7.x86_64 already installed and latest version Resolving Dependencies --> Running transaction check ---> Package perl-Config-Tiny.noarch 0:2.14-7.el7 will be installed ---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed --> Processing Dependency: perl(DBI::Const::GetInfoType) for package: perl-DBD-MySQL-4.023-5.el7.x86_64 --> Processing Dependency: perl(DBI) for package: perl-DBD-MySQL-4.023-5.el7.x86_64 ---> Package perl-Log-Dispatch.noarch 0:2.41-1.el7.1 will be installed --> Processing Dependency: perl(Sys::Syslog) >= 0.25 for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Params::Validate) >= 0.15 for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Params::Validate) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Mail::Sendmail) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Mail::Sender) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Mail::Send) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(MIME::Lite) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Class::Load) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch ---> Package perl-Parallel-ForkManager.noarch 0:1.05-1.el7 will be installed --> Running transaction check ---> Package perl-Class-Load.noarch 0:0.20-3.el7 will be installed --> Processing Dependency: perl(Package::Stash) >= 0.14 for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Module::Runtime) >= 0.012 for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Module::Implementation) >= 0.04 for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Try::Tiny) for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Module::Runtime) for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Data::OptList) for package: perl-Class-Load-0.20-3.el7.noarch ---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed --> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64 --> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64 --> Processing Dependency: perl(Data::Dumper) for package: perl-DBI-1.627-4.el7.x86_64 ---> Package perl-MIME-Lite.noarch 0:3.030-1.el7 will be installed --> Processing Dependency: perl(MIME::Types) >= 1.28 for package: perl-MIME-Lite-3.030-1.el7.noarch --> Processing Dependency: perl(Email::Date::Format) for package: perl-MIME-Lite-3.030-1.el7.noarch ---> Package perl-Mail-Sender.noarch 0:0.8.23-1.el7 will be installed --> Processing Dependency: perl(Net::SSLeay) for package: perl-Mail-Sender-0.8.23-1.el7.noarch --> Processing Dependency: perl(IO::Socket::SSL) for package: perl-Mail-Sender-0.8.23-1.el7.noarch ---> Package perl-Mail-Sendmail.noarch 0:0.79-21.el7 will be installed ---> Package perl-MailTools.noarch 0:2.12-2.el7 will be installed --> Processing Dependency: perl(Net::SMTP::SSL) for package: perl-MailTools-2.12-2.el7.noarch --> Processing Dependency: perl(Date::Parse) for package: perl-MailTools-2.12-2.el7.noarch --> Processing Dependency: perl(Date::Format) for package: perl-MailTools-2.12-2.el7.noarch ---> Package perl-Params-Validate.x86_64 0:1.08-4.el7 will be installed ---> Package perl-Sys-Syslog.x86_64 0:0.33-3.el7 will be installed --> Running transaction check ---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed ---> Package perl-Data-OptList.noarch 0:0.107-9.el7 will be installed --> Processing Dependency: perl(Sub::Install) >= 0.921 for package: perl-Data-OptList-0.107-9.el7.noarch --> Processing Dependency: perl(Params::Util) for package: perl-Data-OptList-0.107-9.el7.noarch ---> Package perl-Email-Date-Format.noarch 0:1.002-15.el7 will be installed ---> Package perl-IO-Socket-SSL.noarch 0:1.94-5.el7 will be installed --> Processing Dependency: perl(IO::Socket::IP) >= 0.20 for package: perl-IO-Socket-SSL-1.94-5.el7.noarch --> Processing Dependency: perl(Net::LibIDN) for package: perl-IO-Socket-SSL-1.94-5.el7.noarch ---> Package perl-MIME-Types.noarch 0:1.38-2.el7 will be installed ---> Package perl-Module-Implementation.noarch 0:0.06-6.el7 will be installed ---> Package perl-Module-Runtime.noarch 0:0.013-4.el7 will be installed ---> Package perl-Net-SMTP-SSL.noarch 0:1.01-13.el7 will be installed ---> Package perl-Net-SSLeay.x86_64 0:1.55-4.el7 will be installed ---> Package perl-Package-Stash.noarch 0:0.34-2.el7 will be installed --> Processing Dependency: perl(Package::Stash::XS) >= 0.26 for package: perl-Package-Stash-0.34-2.el7.noarch --> Processing Dependency: perl(Package::DeprecationManager) for package: perl-Package-Stash-0.34-2.el7.noarch ---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed --> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch ---> Package perl-TimeDate.noarch 1:2.30-2.el7 will be installed ---> Package perl-Try-Tiny.noarch 0:0.12-2.el7 will be installed --> Running transaction check ---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed --> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch --> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch ---> Package perl-IO-Socket-IP.noarch 0:0.21-4.el7 will be installed ---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed ---> Package perl-Net-LibIDN.x86_64 0:0.12-15.el7 will be installed ---> Package perl-Package-DeprecationManager.noarch 0:0.13-7.el7 will be installed --> Processing Dependency: perl(List::MoreUtils) for package: perl-Package-DeprecationManager-0.13-7.el7.noarch ---> Package perl-Package-Stash-XS.x86_64 0:0.26-3.el7 will be installed ---> Package perl-Params-Util.x86_64 0:1.07-6.el7 will be installed ---> Package perl-Sub-Install.noarch 0:0.926-6.el7 will be installed --> Running transaction check ---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed ---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed ---> Package perl-List-MoreUtils.x86_64 0:0.33-9.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================== Package Arch Version Repository Size ============================================================================================================== Installing: perl-Config-Tiny noarch 2.14-7.el7 ISO 25 k perl-DBD-MySQL x86_64 4.023-5.el7 ISO 140 k perl-Log-Dispatch noarch 2.41-1.el7.1 epel 82 k perl-Parallel-ForkManager noarch 1.05-1.el7 epel 24 k Installing for dependencies: perl-Class-Load noarch 0.20-3.el7 ISO 27 k perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 ISO 32 k perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 ISO 57 k perl-DBI x86_64 1.627-4.el7 ISO 802 k perl-Data-Dumper x86_64 2.145-3.el7 ISO 47 k perl-Data-OptList noarch 0.107-9.el7 ISO 23 k perl-Email-Date-Format noarch 1.002-15.el7 epel 17 k perl-IO-Compress noarch 2.061-2.el7 ISO 260 k perl-IO-Socket-IP noarch 0.21-4.el7 ISO 35 k perl-IO-Socket-SSL noarch 1.94-5.el7 base 114 k perl-List-MoreUtils x86_64 0.33-9.el7 ISO 58 k perl-MIME-Lite noarch 3.030-1.el7 epel 96 k perl-MIME-Types noarch 1.38-2.el7 epel 38 k perl-Mail-Sender noarch 0.8.23-1.el7 epel 59 k perl-Mail-Sendmail noarch 0.79-21.el7 epel 29 k perl-MailTools noarch 2.12-2.el7 ISO 108 k perl-Module-Implementation noarch 0.06-6.el7 ISO 17 k perl-Module-Runtime noarch 0.013-4.el7 ISO 19 k perl-Net-Daemon noarch 0.48-5.el7 ISO 51 k perl-Net-LibIDN x86_64 0.12-15.el7 ISO 28 k perl-Net-SMTP-SSL noarch 1.01-13.el7 ISO 9.1 k perl-Net-SSLeay x86_64 1.55-4.el7 base 285 k perl-Package-DeprecationManager noarch 0.13-7.el7 ISO 18 k perl-Package-Stash noarch 0.34-2.el7 ISO 34 k perl-Package-Stash-XS x86_64 0.26-3.el7 ISO 31 k perl-Params-Util x86_64 1.07-6.el7 ISO 38 k perl-Params-Validate x86_64 1.08-4.el7 ISO 69 k perl-PlRPC noarch 0.2020-14.el7 ISO 36 k perl-Sub-Install noarch 0.926-6.el7 ISO 21 k perl-Sys-Syslog x86_64 0.33-3.el7 ISO 42 k perl-TimeDate noarch 1:2.30-2.el7 ISO 52 k perl-Try-Tiny noarch 0.12-2.el7 ISO 23 k Transaction Summary ============================================================================================================== Install 4 Packages (+32 Dependent packages) Total download size: 2.8 M Installed size: 6.6 M Downloading packages: (1/36): perl-Class-Load-0.20-3.el7.noarch.rpm | 27 kB 00:00:00 (2/36): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm | 57 kB 00:00:00 (3/36): perl-Config-Tiny-2.14-7.el7.noarch.rpm | 25 kB 00:00:00 (4/36): perl-DBD-MySQL-4.023-5.el7.x86_64.rpm | 140 kB 00:00:00 (5/36): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm | 32 kB 00:00:00 (6/36): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:00:00 (7/36): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00:00 (8/36): perl-Data-OptList-0.107-9.el7.noarch.rpm | 23 kB 00:00:00 (9/36): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00:00 (10/36): perl-List-MoreUtils-0.33-9.el7.x86_64.rpm | 58 kB 00:00:00 (11/36): perl-IO-Socket-IP-0.21-4.el7.noarch.rpm | 35 kB 00:00:00 warning: /var/cache/yum/x86_64/7/epel/packages/perl-Email-Date-Format-1.002-15.el7.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY Public key for perl-Email-Date-Format-1.002-15.el7.noarch.rpm is not installed (12/36): perl-Email-Date-Format-1.002-15.el7.noarch.rpm | 17 kB 00:00:00 (13/36): perl-Mail-Sender-0.8.23-1.el7.noarch.rpm | 59 kB 00:00:00 (14/36): perl-Mail-Sendmail-0.79-21.el7.noarch.rpm | 29 kB 00:00:00 (15/36): perl-MailTools-2.12-2.el7.noarch.rpm | 108 kB 00:00:00 (16/36): perl-Module-Implementation-0.06-6.el7.noarch.rpm | 17 kB 00:00:00 (17/36): perl-Module-Runtime-0.013-4.el7.noarch.rpm | 19 kB 00:00:00 (18/36): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00:00 (19/36): perl-Net-LibIDN-0.12-15.el7.x86_64.rpm | 28 kB 00:00:00 (20/36): perl-Net-SMTP-SSL-1.01-13.el7.noarch.rpm | 9.1 kB 00:00:00 (21/36): perl-MIME-Lite-3.030-1.el7.noarch.rpm | 96 kB 00:00:02 (22/36): perl-Package-DeprecationManager-0.13-7.el7.noarch.rpm | 18 kB 00:00:00 (23/36): perl-Package-Stash-0.34-2.el7.noarch.rpm | 34 kB 00:00:00 (24/36): perl-Package-Stash-XS-0.26-3.el7.x86_64.rpm | 31 kB 00:00:00 (25/36): perl-IO-Socket-SSL-1.94-5.el7.noarch.rpm | 114 kB 00:00:02 (26/36): perl-Params-Util-1.07-6.el7.x86_64.rpm | 38 kB 00:00:00 (27/36): perl-Params-Validate-1.08-4.el7.x86_64.rpm | 69 kB 00:00:00 (28/36): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00:00 (29/36): perl-Sub-Install-0.926-6.el7.noarch.rpm | 21 kB 00:00:00 (30/36): perl-Sys-Syslog-0.33-3.el7.x86_64.rpm | 42 kB 00:00:00 (31/36): perl-TimeDate-2.30-2.el7.noarch.rpm | 52 kB 00:00:00 (32/36): perl-Try-Tiny-0.12-2.el7.noarch.rpm | 23 kB 00:00:00 (33/36): perl-Net-SSLeay-1.55-4.el7.x86_64.rpm | 285 kB 00:00:01 (34/36): perl-Parallel-ForkManager-1.05-1.el7.noarch.rpm | 24 kB 00:00:00 (35/36): perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm | 82 kB 00:00:03 (36/36): perl-MIME-Types-1.38-2.el7.noarch.rpm | 38 kB 00:00:04 -------------------------------------------------------------------------------------------------------------- Total 510 kB/s | 2.8 MB 00:00:05 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 Importing GPG key 0x352C64E5: Userid : "Fedora EPEL (7) " Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5 Package : epel-release-7-9.noarch (installed) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 Running transaction check Running transaction test Transaction test succeeded Running transaction Warning: RPMDB altered outside of yum. Installing : perl-Sub-Install-0.926-6.el7.noarch 1/36 Installing : perl-Params-Util-1.07-6.el7.x86_64 2/36 Installing : perl-Net-SSLeay-1.55-4.el7.x86_64 3/36 Installing : perl-Try-Tiny-0.12-2.el7.noarch 4/36 Installing : perl-Module-Runtime-0.013-4.el7.noarch 5/36 Installing : perl-Module-Implementation-0.06-6.el7.noarch 6/36 Installing : perl-Params-Validate-1.08-4.el7.x86_64 7/36 Installing : perl-Data-OptList-0.107-9.el7.noarch 8/36 Installing : perl-Email-Date-Format-1.002-15.el7.noarch 9/36 Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 10/36 Installing : perl-Net-LibIDN-0.12-15.el7.x86_64 11/36 Installing : 1:perl-TimeDate-2.30-2.el7.noarch 12/36 Installing : perl-Package-Stash-XS-0.26-3.el7.x86_64 13/36 Installing : perl-Data-Dumper-2.145-3.el7.x86_64 14/36 Installing : perl-MIME-Types-1.38-2.el7.noarch 15/36 Installing : perl-MIME-Lite-3.030-1.el7.noarch 16/36 Installing : perl-List-MoreUtils-0.33-9.el7.x86_64 17/36 Installing : perl-Package-DeprecationManager-0.13-7.el7.noarch 18/36 Installing : perl-Package-Stash-0.34-2.el7.noarch 19/36 Installing : perl-Class-Load-0.20-3.el7.noarch 20/36 Installing : perl-Net-Daemon-0.48-5.el7.noarch 21/36 Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 22/36 Installing : perl-IO-Compress-2.061-2.el7.noarch 23/36 Installing : perl-PlRPC-0.2020-14.el7.noarch 24/36 Installing : perl-DBI-1.627-4.el7.x86_64 25/36 Installing : perl-IO-Socket-IP-0.21-4.el7.noarch 26/36 Installing : perl-IO-Socket-SSL-1.94-5.el7.noarch 27/36 Installing : perl-Mail-Sender-0.8.23-1.el7.noarch 28/36 Installing : perl-Net-SMTP-SSL-1.01-13.el7.noarch 29/36 Installing : perl-MailTools-2.12-2.el7.noarch 30/36 Installing : perl-Mail-Sendmail-0.79-21.el7.noarch 31/36 Installing : perl-Sys-Syslog-0.33-3.el7.x86_64 32/36 Installing : perl-Log-Dispatch-2.41-1.el7.1.noarch 33/36 Installing : perl-DBD-MySQL-4.023-5.el7.x86_64 34/36 Installing : perl-Parallel-ForkManager-1.05-1.el7.noarch 35/36 Installing : perl-Config-Tiny-2.14-7.el7.noarch 36/36 Verifying : perl-Sys-Syslog-0.33-3.el7.x86_64 1/36 Verifying : perl-IO-Compress-2.061-2.el7.noarch 2/36 Verifying : perl-Module-Runtime-0.013-4.el7.noarch 3/36 Verifying : perl-Mail-Sendmail-0.79-21.el7.noarch 4/36 Verifying : perl-MIME-Lite-3.030-1.el7.noarch 5/36 Verifying : perl-IO-Socket-IP-0.21-4.el7.noarch 6/36 Verifying : perl-Config-Tiny-2.14-7.el7.noarch 7/36 Verifying : perl-Try-Tiny-0.12-2.el7.noarch 8/36 Verifying : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 9/36 Verifying : perl-Mail-Sender-0.8.23-1.el7.noarch 10/36 Verifying : perl-Package-DeprecationManager-0.13-7.el7.noarch 11/36 Verifying : perl-Net-Daemon-0.48-5.el7.noarch 12/36 Verifying : perl-IO-Socket-SSL-1.94-5.el7.noarch 13/36 Verifying : perl-Net-SSLeay-1.55-4.el7.x86_64 14/36 Verifying : perl-Parallel-ForkManager-1.05-1.el7.noarch 15/36 Verifying : perl-List-MoreUtils-0.33-9.el7.x86_64 16/36 Verifying : perl-DBD-MySQL-4.023-5.el7.x86_64 17/36 Verifying : perl-MIME-Types-1.38-2.el7.noarch 18/36 Verifying : perl-Params-Util-1.07-6.el7.x86_64 19/36 Verifying : perl-Data-Dumper-2.145-3.el7.x86_64 20/36 Verifying : perl-Package-Stash-0.34-2.el7.noarch 21/36 Verifying : perl-Package-Stash-XS-0.26-3.el7.x86_64 22/36 Verifying : perl-MailTools-2.12-2.el7.noarch 23/36 Verifying : perl-Params-Validate-1.08-4.el7.x86_64 24/36 Verifying : perl-Class-Load-0.20-3.el7.noarch 25/36 Verifying : perl-Log-Dispatch-2.41-1.el7.1.noarch 26/36 Verifying : perl-PlRPC-0.2020-14.el7.noarch 27/36 Verifying : perl-Net-SMTP-SSL-1.01-13.el7.noarch 28/36 Verifying : perl-DBI-1.627-4.el7.x86_64 29/36 Verifying : perl-Sub-Install-0.926-6.el7.noarch 30/36 Verifying : perl-Module-Implementation-0.06-6.el7.noarch 31/36 Verifying : 1:perl-TimeDate-2.30-2.el7.noarch 32/36 Verifying : perl-Data-OptList-0.107-9.el7.noarch 33/36 Verifying : perl-Net-LibIDN-0.12-15.el7.x86_64 34/36 Verifying : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 35/36 Verifying : perl-Email-Date-Format-1.002-15.el7.noarch 36/36 Installed: perl-Config-Tiny.noarch 0:2.14-7.el7 perl-DBD-MySQL.x86_64 0:4.023-5.el7 perl-Log-Dispatch.noarch 0:2.41-1.el7.1 perl-Parallel-ForkManager.noarch 0:1.05-1.el7 Dependency Installed: perl-Class-Load.noarch 0:0.20-3.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBI.x86_64 0:1.627-4.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7 perl-Data-OptList.noarch 0:0.107-9.el7 perl-Email-Date-Format.noarch 0:1.002-15.el7 perl-IO-Compress.noarch 0:2.061-2.el7 perl-IO-Socket-IP.noarch 0:0.21-4.el7 perl-IO-Socket-SSL.noarch 0:1.94-5.el7 perl-List-MoreUtils.x86_64 0:0.33-9.el7 perl-MIME-Lite.noarch 0:3.030-1.el7 perl-MIME-Types.noarch 0:1.38-2.el7 perl-Mail-Sender.noarch 0:0.8.23-1.el7 perl-Mail-Sendmail.noarch 0:0.79-21.el7 perl-MailTools.noarch 0:2.12-2.el7 perl-Module-Implementation.noarch 0:0.06-6.el7 perl-Module-Runtime.noarch 0:0.013-4.el7 perl-Net-Daemon.noarch 0:0.48-5.el7 perl-Net-LibIDN.x86_64 0:0.12-15.el7 perl-Net-SMTP-SSL.noarch 0:1.01-13.el7 perl-Net-SSLeay.x86_64 0:1.55-4.el7 perl-Package-DeprecationManager.noarch 0:0.13-7.el7 perl-Package-Stash.noarch 0:0.34-2.el7 perl-Package-Stash-XS.x86_64 0:0.26-3.el7 perl-Params-Util.x86_64 0:1.07-6.el7 perl-Params-Validate.x86_64 0:1.08-4.el7 perl-PlRPC.noarch 0:0.2020-14.el7 perl-Sub-Install.noarch 0:0.926-6.el7 perl-Sys-Syslog.x86_64 0:0.33-3.el7 perl-TimeDate.noarch 1:2.30-2.el7 perl-Try-Tiny.noarch 0:0.12-2.el7 Complete! [root@mha1 epel]#
然后安装MHA的RPM包：
MHA Manager
[root@mha1 software_me]# [root@mha1 software_me]# ls -ltr | grep mha | grep rpm -rw-r--r-- 1 root root 87119 Nov 11 09:45 mha4mysql-manager-0.56-0.el6.noarch.rpm -rw-r--r-- 1 root root 36326 Nov 11 09:45 mha4mysql-node-0.56-0.el6.noarch.rpm [root@mha1 software_me]# [root@mha1 software_me]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm Preparing... ################################# [100%] Updating / installing... 1:mha4mysql-node-0.56-0.el6 ################################# [100%] [root@mha1 software_me]# [root@mha1 software_me]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm Preparing... ################################# [100%] Updating / installing... 1:mha4mysql-manager-0.56-0.el6 ################################# [100%] [root@mha1 software_me]#
MHA Node
[root@mha2 ~]# cd /software_me/ [root@mha2 software_me]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm Preparing... ################################# [100%] Updating / installing... 1:mha4mysql-node-0.56-0.el6 ################################# [100%] [root@mha2 software_me]# [root@mha3 ~]# cd /software_me/ [root@mha3 software_me]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm Preparing... ################################# [100%] Updating / installing... 1:mha4mysql-node-0.56-0.el6 ################################# [100%] [root@mha3 software_me]# [root@mha4 ~]# cd /software_me/ [root@mha4 software_me]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm Preparing... ################################# [100%] Updating / installing... 1:mha4mysql-node-0.56-0.el6 ################################# [100%] [root@mha4 software_me]#
这样，MHA的软件就安装完成了。
三、MasterHA：配置
文件：/etc/masterha_default.cnf
[root@mha1 software_me]# cat /etc/masterha_default.cnf [server default] user=root password=Abcd@123 ssh_user=root master_binlog_dir=/var/lib/mysql remote_workdir=/work_dir/mha_node repl_user=replme repl_password=Abcd@123 master_ip_failover_script=/script/mha/master_ip_failover master_ip_online_change_script=/script/mha/master_ip_online_change ping_interval=1 [root@mha1 software_me]#
文件：/etc/masterha_application_1.cnf
[root@mha1 software_me]# cat /etc/masterha_application_1.cnf [server default] manager_workdir=/work_dir/mha_manager manager_log=/work_dir/mha_manager/mha_manager.log [server1] hostname=mha2 candidate_master=1 [server2] hostname=mha3 candidate_master=1 [server3] hostname=mha4 #candidate_master=1 no_master=1 [root@mha1 software_me]#
创建需要的脚本目录：
[root@mha1 software_me]# mkdir -p /script/{mha,shell}
[root@mha1 software_me]#
文件：/script/mha/master_ip_failover
[root@mha1 software_me]# cat /script/mha/master_ip_failover
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
## Note: This is a sample script and is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
my (
$command, $ssh_user, $orig_master_host,
$orig_master_ip, $orig_master_port, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password
);
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
'new_master_user=s' => \$new_master_user,
'new_master_password=s' => \$new_master_password,
);
exit &main();
sub main {
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
# updating global catalog, etc
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
my $new_master_handler = new MHA::DBHelper();
# args: hostname, port, user, password, raise_error_or_not
$new_master_handler->connect( $new_master_ip, $new_master_port,
"mhame", "Or\@cle123", 1 );
## Set read_only=0 on the new master
$new_master_handler->disable_log_bin_local();
print "Set read_only=0 on the new master.\n";
$new_master_handler->disable_read_only();
## Creating an app user on the new master
print "Creating app user on the new master..\n";
#FIXME_xxx_create_user( $new_master_handler->{dbh} );
$new_master_handler->enable_log_bin_local();
$new_master_handler->disconnect();
## Update master ip on the catalog database, etc
#FIXME_xxx;
`echo "script begin running..." > /work_dir/mha_manager/test_me/master_ip_failover_run_shell`;
`/usr/bin/ssh -t root\@${orig_master_ip} "service keepalived stop" > /work_dir/mha_manager/test_me/orig_master.log`;
`/usr/bin/ssh -t root\@${new_master_ip} "service keepalived start" > /work_dir/mha_manager/test_me/new_master.log`;
`sh /script/shell/do_mha.sh > /work_dir/mha_manager/test_me/do_mha.log`;
$exit_code = 0;
};
if ($@) {
warn $@;
# If you want to continue failover, exit 10.
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
# do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
[root@mha1 software_me]#
文件：/script/mha/master_ip_online_change
[root@mha1 software_me]# cat /script/mha/master_ip_online_change
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
## Note: This is a sample script and is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
use MHA::NodeUtil;
use Time::HiRes qw( sleep gettimeofday tv_interval );
use Data::Dumper;
my $_tstart;
my $_running_interval = 0.1;
my (
$command, $orig_master_is_new_slave, $orig_master_host,
$orig_master_ip, $orig_master_port, $orig_master_user,
$orig_master_password, $orig_master_ssh_user, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password, $new_master_ssh_user,
);
GetOptions(
'command=s' => \$command,
'orig_master_is_new_slave' => \$orig_master_is_new_slave,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'orig_master_user=s' => \$orig_master_user,
'orig_master_password=s' => \$orig_master_password,
'orig_master_ssh_user=s' => \$orig_master_ssh_user,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
'new_master_user=s' => \$new_master_user,
'new_master_password=s' => \$new_master_password,
'new_master_ssh_user=s' => \$new_master_ssh_user,
);
exit &main();
sub current_time_us {
my ( $sec, $microsec ) = gettimeofday();
my $curdate = localtime($sec);
return $curdate . " " . sprintf( "%06d", $microsec );
}
sub sleep_until {
my $elapsed = tv_interval($_tstart);
if ( $_running_interval > $elapsed ) {
sleep( $_running_interval - $elapsed );
}
}
sub get_threads_util {
my $dbh = shift;
my $my_connection_id = shift;
my $running_time_threshold = shift;
my $type = shift;
$running_time_threshold = 0 unless ($running_time_threshold);
$type = 0 unless ($type);
my @threads;
my $sth = $dbh->prepare("SHOW PROCESSLIST");
$sth->execute();
while ( my $ref = $sth->fetchrow_hashref() ) {
my $id = $ref->{Id};
my $user = $ref->{User};
my $host = $ref->{Host};
my $command = $ref->{Command};
my $state = $ref->{State};
my $query_time = $ref->{Time};
my $info = $ref->{Info};
$info =~ s/^\s*(.*?)\s*$/$1/ if defined($info);
next if ( $my_connection_id == $id );
next if ( defined($query_time) && $query_time < $running_time_threshold );
next if ( defined($command) && $command eq "Binlog Dump" );
next if ( defined($user) && $user eq "system user" );
next
if ( defined($command)
&& $command eq "Sleep"
&& defined($query_time)
&& $query_time >= 1 );
if ( $type >= 1 ) {
next if ( defined($command) && $command eq "Sleep" );
next if ( defined($command) && $command eq "Connect" );
}
if ( $type >= 2 ) {
next if ( defined($info) && $info =~ m/^select/i );
next if ( defined($info) && $info =~ m/^show/i );
}
push @threads, $ref;
}
return @threads;
}
sub main {
if ( $command eq "stop" ) {
## Gracefully killing connections on the current master
# 1. Set read_only= 1 on the new master
# 2. DROP USER so that no app user can establish new connections
# 3. Set read_only= 1 on the current master
# 4. Kill current queries
# * Any database access failure will result in script die.
my $exit_code = 1;
eval {
## Setting read_only=1 on the new master (to avoid accident)
my $new_master_handler = new MHA::DBHelper();
# args: hostname, port, user, password, raise_error(die_on_error)_or_not
$new_master_handler->connect( $new_master_ip, $new_master_port,
"root", "Abcd1\@34", 1 );
print current_time_us() . " Set read_only on the new master.. ";
$new_master_handler->enable_read_only();
if ( $new_master_handler->is_read_only() ) {
print "ok.\n";
}
else {
die "Failed!\n";
}
$new_master_handler->disconnect();
# Connecting to the orig master, die if any database error happens
my $orig_master_handler = new MHA::DBHelper();
$orig_master_handler->connect( $orig_master_ip, $orig_master_port,
$orig_master_user, $orig_master_password, 1 );
## Drop application user so that nobody can connect. Disabling per-session binlog beforehand
$orig_master_handler->disable_log_bin_local();
print current_time_us() . " Drpping app user on the orig master..\n";
#FIXME_xxx_drop_app_user($orig_master_handler);
## Waiting for N * 100 milliseconds so that current connections can exit
my $time_until_read_only = 15;
$_tstart = [gettimeofday];
my @threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
while ( $time_until_read_only > 0 && $#threads >= 0 ) {
if ( $time_until_read_only % 5 == 0 ) {
printf
"%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n",
current_time_us(), $#threads + 1, $time_until_read_only * 100;
if ( $#threads < 5 ) {
print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
foreach (@threads);
}
}
sleep_until();
$_tstart = [gettimeofday];
$time_until_read_only--;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
}
## Setting read_only=1 on the current master so that nobody(except SUPER) can write
print current_time_us() . " Set read_only=1 on the orig master.. ";
$orig_master_handler->enable_read_only();
if ( $orig_master_handler->is_read_only() ) {
print "ok.\n";
}
else {
die "Failed!\n";
}
## Waiting for M * 100 milliseconds so that current update queries can complete
my $time_until_kill_threads = 5;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
while ( $time_until_kill_threads > 0 && $#threads >= 0 ) {
if ( $time_until_kill_threads % 5 == 0 ) {
printf
"%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n",
current_time_us(), $#threads + 1, $time_until_kill_threads * 100;
if ( $#threads < 5 ) {
print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
foreach (@threads);
}
}
sleep_until();
$_tstart = [gettimeofday];
$time_until_kill_threads--;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
}
## Terminating all threads
print current_time_us() . " Killing all application threads..\n";
$orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );
print current_time_us() . " done.\n";
$orig_master_handler->enable_log_bin_local();
$orig_master_handler->disconnect();
## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
## Activating master ip on the new master
# 1. Create app user with write privileges
# 2. Moving backup script if needed
# 3. Register new master's ip to the catalog database
# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.
# If exit code is 0 or 10, MHA does not abort
my $exit_code = 10;
eval {
my $new_master_handler = new MHA::DBHelper();
# args: hostname, port, user, password, raise_error_or_not
$new_master_handler->connect( $new_master_ip, $new_master_port,
"root", "Abcd1\@34", 1 );
## Set read_only=0 on the new master
$new_master_handler->disable_log_bin_local();
print current_time_us() . " Set read_only=0 on the new master.\n";
$new_master_handler->disable_read_only();
## Creating an app user on the new master
print current_time_us() . " Creating app user on the new master..\n";
#FIXME_xxx_create_app_user($new_master_handler);
$new_master_handler->enable_log_bin_local();
$new_master_handler->disconnect();
## Update master ip on the catalog database, etc
`/usr/bin/ssh -t root\@${orig_master_ip} "service keepalived stop"`;
`/usr/bin/ssh -t root\@${new_master_ip} "service keepalived start"`;
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
# do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}
sub usage {
print
"Usage: master_ip_online_change --command=start|stop|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
die;
}
[root@mha1 software_me]#
注意：配置好脚本后，最好检查一下脚本文件是否有执行权限，否则，后面执行的时候，可能会出现下面的错误：
[root@mha1 software_me]# masterha_check_repl --conf=/etc/masterha_application_1.cnf
Thu Feb 9 00:12:52 2017 - [info] Reading default configuration from /etc/masterha_default.cnf..
Thu Feb 9 00:12:52 2017 - [info] Reading application default configuration from /etc/masterha_application_1.cnf..
Thu Feb 9 00:12:52 2017 - [info] Reading server configuration from /etc/masterha_application_1.cnf..
Thu Feb 9 00:12:52 2017 - [info] MHA::MasterMonitor version 0.56.
Thu Feb 9 00:12:52 2017 - [info] GTID failover mode = 0
Thu Feb 9 00:12:52 2017 - [info] Dead Servers:
Thu Feb 9 00:12:52 2017 - [info] Alive Servers:
Thu Feb 9 00:12:52 2017 - [info] mha2(192.168.19.152:3306)
Thu Feb 9 00:12:52 2017 - [info] mha3(192.168.19.153:3306)
Thu Feb 9 00:12:52 2017 - [info] mha4(192.168.19.154:3306)
Thu Feb 9 00:12:52 2017 - [info] Alive Slaves:
Thu Feb 9 00:12:52 2017 - [info] mha3(192.168.19.153:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled
Thu Feb 9 00:12:52 2017 - [info] Replicating from 192.168.19.152(192.168.19.152:3306)
Thu Feb 9 00:12:52 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Feb 9 00:12:52 2017 - [info] mha4(192.168.19.154:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled
Thu Feb 9 00:12:52 2017 - [info] Replicating from 192.168.19.152(192.168.19.152:3306)
Thu Feb 9 00:12:52 2017 - [info] Not candidate for the new Master (no_master is set)
Thu Feb 9 00:12:52 2017 - [info] Current Alive Master: mha2(192.168.19.152:3306)
Thu Feb 9 00:12:52 2017 - [info] Checking slave configurations..
Thu Feb 9 00:12:52 2017 - [info] Checking replication filtering settings..
Thu Feb 9 00:12:52 2017 - [info] binlog_do_db= , binlog_ignore_db=
Thu Feb 9 00:12:52 2017 - [info] Replication filtering check ok.
Thu Feb 9 00:12:52 2017 - [info] GTID (with auto-pos) is not supported
Thu Feb 9 00:12:52 2017 - [info] Starting SSH connection tests..
Thu Feb 9 00:12:54 2017 - [info] All SSH connection tests passed successfully.
Thu Feb 9 00:12:54 2017 - [info] Checking MHA Node version..
Thu Feb 9 00:12:55 2017 - [info] Version check ok.
Thu Feb 9 00:12:55 2017 - [info] Checking SSH publickey authentication settings on the current master..
Thu Feb 9 00:12:55 2017 - [info] HealthCheck: SSH to mha2 is reachable.
Thu Feb 9 00:12:56 2017 - [info] Master MHA Node version is 0.56.
Thu Feb 9 00:12:56 2017 - [info] Checking recovery script configurations on mha2(192.168.19.152:3306)..
Thu Feb 9 00:12:56 2017 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/work_dir/mha_node/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000003
Thu Feb 9 00:12:56 2017 - [info] Connecting to root@192.168.19.152(mha2:22)..
Creating /work_dir/mha_node if not exists.. Creating directory /work_dir/mha_node.. done.
ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mysql-bin.000003
Thu Feb 9 00:12:56 2017 - [info] Binlog setting check done.
Thu Feb 9 00:12:56 2017 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Feb 9 00:12:56 2017 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mha3 --slave_ip=192.168.19.153 --slave_port=3306 --workdir=/work_dir/mha_node --target_version=5.7.15-9-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Feb 9 00:12:56 2017 - [info] Connecting to root@192.168.19.153(mha3:22)..
Creating directory /work_dir/mha_node.. done.
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mysql-relay.000002
Temporary relay log file is /var/lib/mysql/mysql-relay.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Feb 9 00:12:57 2017 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mha4 --slave_ip=192.168.19.154 --slave_port=3306 --workdir=/work_dir/mha_node --target_version=5.7.15-9-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Feb 9 00:12:57 2017 - [info] Connecting to root@192.168.19.154(mha4:22)..
Creating directory /work_dir/mha_node.. done.
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mysql-relay.000003
Temporary relay log file is /var/lib/mysql/mysql-relay.000003
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Feb 9 00:12:57 2017 - [info] Slaves settings check done.
Thu Feb 9 00:12:57 2017 - [info]
mha2(192.168.19.152:3306) (current master)
+--mha3(192.168.19.153:3306)
+--mha4(192.168.19.154:3306)
Thu Feb 9 00:12:57 2017 - [info] Checking replication health on mha3..
Thu Feb 9 00:12:57 2017 - [info] ok.
Thu Feb 9 00:12:57 2017 - [info] Checking replication health on mha4..
Thu Feb 9 00:12:57 2017 - [info] ok.
Thu Feb 9 00:12:57 2017 - [info] Checking master_ip_failover_script status:
Thu Feb 9 00:12:57 2017 - [info] /script/mha/master_ip_failover --command=status --ssh_user=root --orig_master_host=mha2 --orig_master_ip=192.168.19.152 --orig_master_port=3306
Thu Feb 9 00:12:57 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. Can't exec "/script/mha/master_ip_failover": Permission denied at /usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm line 68.
Thu Feb 9 00:12:57 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Thu Feb 9 00:12:57 2017 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
Thu Feb 9 00:12:57 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln226] Failed to get master_ip_failover_script status with return code 1:0.
Thu Feb 9 00:12:57 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48.
Thu Feb 9 00:12:57 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Thu Feb 9 00:12:57 2017 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
[root@mha1 software_me]#
[root@mha1 software_me]#
[root@mha1 software_me]# ls -ltr /script/mha/*
-rw-r--r-- 1 root root 4067 Feb 8 23:59 /script/mha/master_ip_failover
-rw-r--r-- 1 root root 9983 Feb 9 00:01 /script/mha/master_ip_online_change
[root@mha1 software_me]#
[root@mha1 software_me]# chmod -R 755 /script/
[root@mha1 software_me]#
[root@mha1 software_me]# ls -ltr /script/mha/*
-rwxr-xr-x 1 root root 4067 Feb 8 23:59 /script/mha/master_ip_failover
-rwxr-xr-x 1 root root 9983 Feb 9 00:01 /script/mha/master_ip_online_change
[root@mha1 software_me]#
[root@mha1 software_me]#
创建工作目录：
[root@mha1 software_me]# mkdir -p /work_dir/{mha_manager/test_me,mha_node}
[root@mha1 software_me]#
[root@mha1 software_me]# tree /work_dir/
/work_dir/
├── mha_manager
│ └── test_me
└── mha_node
3 directories, 0 files
[root@mha1 software_me]#
测试：SSH连通性
[root@mha1 software_me]# masterha_check_ssh --conf=/etc/masterha_application_1.cnf Thu Feb 9 00:06:12 2017 - [info] Reading default configuration from /etc/masterha_default.cnf.. Thu Feb 9 00:06:12 2017 - [info] Reading application default configuration from /etc/masterha_application_1.cnf.. Thu Feb 9 00:06:12 2017 - [info] Reading server configuration from /etc/masterha_application_1.cnf.. Thu Feb 9 00:06:12 2017 - [info] Starting SSH connection tests.. Thu Feb 9 00:06:13 2017 - [debug] Thu Feb 9 00:06:12 2017 - [debug] Connecting via SSH from root@mha2(192.168.19.152:22) to root@mha3(192.168.19.153:22).. Thu Feb 9 00:06:12 2017 - [debug] ok. Thu Feb 9 00:06:12 2017 - [debug] Connecting via SSH from root@mha2(192.168.19.152:22) to root@mha4(192.168.19.154:22).. Thu Feb 9 00:06:12 2017 - [debug] ok. Thu Feb 9 00:06:13 2017 - [debug] Thu Feb 9 00:06:12 2017 - [debug] Connecting via SSH from root@mha3(192.168.19.153:22) to root@mha2(192.168.19.152:22).. Thu Feb 9 00:06:13 2017 - [debug] ok. Thu Feb 9 00:06:13 2017 - [debug] Connecting via SSH from root@mha3(192.168.19.153:22) to root@mha4(192.168.19.154:22).. Thu Feb 9 00:06:13 2017 - [debug] ok. Thu Feb 9 00:06:13 2017 - [debug] Thu Feb 9 00:06:13 2017 - [debug] Connecting via SSH from root@mha4(192.168.19.154:22) to root@mha2(192.168.19.152:22).. Thu Feb 9 00:06:13 2017 - [debug] ok. Thu Feb 9 00:06:13 2017 - [debug] Connecting via SSH from root@mha4(192.168.19.154:22) to root@mha3(192.168.19.153:22).. Thu Feb 9 00:06:13 2017 - [debug] ok. Thu Feb 9 00:06:13 2017 - [info] All SSH connection tests passed successfully. [root@mha1 software_me]#
测试：MySQL Replication
[root@mha1 software_me]# masterha_check_repl --conf=/etc/masterha_application_1.cnf
Thu Feb 9 00:17:55 2017 - [info] Reading default configuration from /etc/masterha_default.cnf..
Thu Feb 9 00:17:55 2017 - [info] Reading application default configuration from /etc/masterha_application_1.cnf..
Thu Feb 9 00:17:55 2017 - [info] Reading server configuration from /etc/masterha_application_1.cnf..
Thu Feb 9 00:17:55 2017 - [info] MHA::MasterMonitor version 0.56.
Thu Feb 9 00:17:55 2017 - [info] GTID failover mode = 0
Thu Feb 9 00:17:55 2017 - [info] Dead Servers:
Thu Feb 9 00:17:55 2017 - [info] Alive Servers:
Thu Feb 9 00:17:55 2017 - [info] mha2(192.168.19.152:3306)
Thu Feb 9 00:17:55 2017 - [info] mha3(192.168.19.153:3306)
Thu Feb 9 00:17:55 2017 - [info] mha4(192.168.19.154:3306)
Thu Feb 9 00:17:55 2017 - [info] Alive Slaves:
Thu Feb 9 00:17:55 2017 - [info] mha3(192.168.19.153:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled
Thu Feb 9 00:17:55 2017 - [info] Replicating from 192.168.19.152(192.168.19.152:3306)
Thu Feb 9 00:17:55 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Feb 9 00:17:55 2017 - [info] mha4(192.168.19.154:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled
Thu Feb 9 00:17:55 2017 - [info] Replicating from 192.168.19.152(192.168.19.152:3306)
Thu Feb 9 00:17:55 2017 - [info] Not candidate for the new Master (no_master is set)
Thu Feb 9 00:17:55 2017 - [info] Current Alive Master: mha2(192.168.19.152:3306)
Thu Feb 9 00:17:55 2017 - [info] Checking slave configurations..
Thu Feb 9 00:17:55 2017 - [info] Checking replication filtering settings..
Thu Feb 9 00:17:55 2017 - [info] binlog_do_db= , binlog_ignore_db=
Thu Feb 9 00:17:55 2017 - [info] Replication filtering check ok.
Thu Feb 9 00:17:55 2017 - [info] GTID (with auto-pos) is not supported
Thu Feb 9 00:17:55 2017 - [info] Starting SSH connection tests..
Thu Feb 9 00:17:57 2017 - [info] All SSH connection tests passed successfully.
Thu Feb 9 00:17:57 2017 - [info] Checking MHA Node version..
Thu Feb 9 00:17:58 2017 - [info] Version check ok.
Thu Feb 9 00:17:58 2017 - [info] Checking SSH publickey authentication settings on the current master..
Thu Feb 9 00:17:58 2017 - [info] HealthCheck: SSH to mha2 is reachable.
Thu Feb 9 00:17:58 2017 - [info] Master MHA Node version is 0.56.
Thu Feb 9 00:17:58 2017 - [info] Checking recovery script configurations on mha2(192.168.19.152:3306)..
Thu Feb 9 00:17:58 2017 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/work_dir/mha_node/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000003
Thu Feb 9 00:17:58 2017 - [info] Connecting to root@192.168.19.152(mha2:22)..
Creating /work_dir/mha_node if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mysql-bin.000003
Thu Feb 9 00:17:58 2017 - [info] Binlog setting check done.
Thu Feb 9 00:17:58 2017 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Feb 9 00:17:58 2017 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mha3 --slave_ip=192.168.19.153 --slave_port=3306 --workdir=/work_dir/mha_node --target_version=5.7.15-9-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Feb 9 00:17:58 2017 - [info] Connecting to root@192.168.19.153(mha3:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mysql-relay.000002
Temporary relay log file is /var/lib/mysql/mysql-relay.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Feb 9 00:17:59 2017 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mha4 --slave_ip=192.168.19.154 --slave_port=3306 --workdir=/work_dir/mha_node --target_version=5.7.15-9-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Feb 9 00:17:59 2017 - [info] Connecting to root@192.168.19.154(mha4:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mysql-relay.000003
Temporary relay log file is /var/lib/mysql/mysql-relay.000003
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Feb 9 00:17:59 2017 - [info] Slaves settings check done.
Thu Feb 9 00:17:59 2017 - [info]
mha2(192.168.19.152:3306) (current master)
+--mha3(192.168.19.153:3306)
+--mha4(192.168.19.154:3306)
Thu Feb 9 00:17:59 2017 - [info] Checking replication health on mha3..
Thu Feb 9 00:17:59 2017 - [info] ok.
Thu Feb 9 00:17:59 2017 - [info] Checking replication health on mha4..
Thu Feb 9 00:17:59 2017 - [info] ok.
Thu Feb 9 00:17:59 2017 - [info] Checking master_ip_failover_script status:
Thu Feb 9 00:17:59 2017 - [info] /script/mha/master_ip_failover --command=status --ssh_user=root --orig_master_host=mha2 --orig_master_ip=192.168.19.152 --orig_master_port=3306
Thu Feb 9 00:17:59 2017 - [info] OK.
Thu Feb 9 00:17:59 2017 - [warning] shutdown_script is not defined.
Thu Feb 9 00:17:59 2017 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
[root@mha1 software_me]#这样，就说明MySQL的Replication架构和MHA的配合已经没有问题了。
四、增强脚本部署
如果你希望手动做主库与备主库的切换，你可以执行类似下面这样的命令：
masterha_master_switch –master_state=alive –conf=/masterha_me/conf/mha_total.cnf –new_master_host=mha3 –interactive=0
另一种方法是，让MHA自动捕获异常，并执行切换。
但默认的情况下，执行保证一次切换操作。
为了实现类似Oracle RAC的群集的特性，可以按照下面的方法部署SHELL脚本实现。
文件：/script/shell/do_mha.sh
[root@mha1 software_me]# cat /script/shell/do_mha.sh
#!/bin/bash
# Script Name: MySQL_MHA_Enhance_shell.sh
# About: auto register in MySQL Replication
# Script Type: Bash Shell
# OS: RHEL6 / CentOS6
# Architecture: MySQL MasterHA
# Auther: adamhuan
# Blog: d-prototype.com
# --------------------------
# variable and file path
# Part:: Linux
# Account info
str_linux_username="root"
# Part:: MySQL
# Account info
str_mysql_username="root"
str_mysql_password=Abcd1@34
str_repl_username="replme"
str_repl_password=Or@cle123
# Part:: MHA
# pid
str_pid_masterha_manager=`ps -ef | grep masterha_manager | grep perl | awk '{print $2}'`
# file
file_conf_mha_global="/etc/masterha_default.cnf"
file_conf_mha_application="/etc/masterha_application_1.cnf"
# file: relation / by computed
file_log_mha_manager=`cat $file_conf_mha_application | grep --color manager_log | cut -d'=' -f2`
# variable: ip info
# 如果MHA中MySQL主库的候选服务器数量超过了两台，也许下面这个list参数，就会排上用场
list_ip_candicate=`cat $file_conf_mha_application | grep -B 2 "^candidate" | grep "hostname" | cut -d'=' -f2`
str_ip_orig_master=`cat $file_log_mha_manager | grep --color "MySQL Master failover" | cut -d'(' -f2 | cut -d':' -f1 | tail -n 1`
str_ip_new_master=`cat $file_log_mha_manager | grep --color "MySQL Master failover" | cut -d'(' -f3 | cut -d':' -f1 | tail -n 1`
str_ip_mha_manager="10.158.1.94"
# 为[change master]准备的参数
str_log_file_new_master=""
str_log_pos_new_master=""
# Part:: String SQL
str_sql_mysql_change_master=""
# --------------------------
# function
function do_sql() {
# variable
func_str_ip="$1"
func_str_sql="$2"
# action
# 本场景中不涉及到对MySQL某个库的操作，所以没有选择[db]
# mysql -u $user -p"$password" $db -N -e "$f_sql_str"
mysql -u $str_mysql_username -h $func_str_ip -p"$str_mysql_password" -N -e "$func_str_sql"
}
# 获取主库状态信息
#function get_info_mysql_master_new_master() {
# version ONE
#str_log_file_new_master=`do_sql "$str_ip_new_master" "show master status" | awk '{print $1}'`
#str_log_pos_new_master=`do_sql "$str_ip_new_master" "show master status" | awk '{print $2}'`
#}
# 生成orig_master作为slave加入new_master的[change master]SQL命令
function gen_sql_mysql_change_master() {
#if [[ "$str_log_file_new_master" == "" || $str_log_pos_new_master == "" ]]
#then
# get_info_mysql_master_new_master
#fi
#str_sql_mysql_change_master="CHANGE MASTER TO MASTER_HOST='$str_ip_new_master',MASTER_USER='$str_repl_username',MASTER_PASSWORD='$str_repl_password',MASTER_LOG_FILE='$str_log_file_new_master',MASTER_LOG_POS=$str_log_pos_new_master;"
# version TWO
func_temp_master_host_sed=`cat $file_log_mha_manager | grep --color "All other slaves should start" | tail -n 1 | cut -d',' -f1 | cut -d'=' -f2 | cut -d\' -f2`
func_temp_repl_password_sed=`cat $file_log_mha_manager | grep --color "All other slaves should start" | tail -n 1 | rev | cut -d\' -f2`
echo "======================"
echo "@@ func variable: func_temp_repl_password_sed = $func_temp_repl_password_sed"
echo "======================"
str_sql_mysql_change_master=`cat $file_log_mha_manager | grep --color "All other slaves should start" | tail -n 1 | sed "s/'$func_temp_repl_password_sed'/'$str_repl_password'/g" | cut -d':' -f4`
str_sql_mysql_change_master=`echo $str_sql_mysql_change_master | sed "s/'$func_temp_master_host_sed'/'$str_ip_new_master'/g"`
}
# 对指定主机执行Linux命令
# 前提：
# 1. IP可达
# 2. SSH等价关系
function do_linux_by_ssh() {
# variable
func_str_ip="$1"
func_str_user="$2"
func_str_command="$3"
# action
ssh -t $func_str_user@$func_str_ip "$func_str_command"
}
# 处理VIP的事宜
function do_part_vip() {
do_linux_by_ssh "$str_ip_new_master" "root" "service keepalived start"
do_linux_by_ssh "$str_ip_orig_master" "root" "service keepalived stop"
}
function do_part_orig_master_is_new_slave() {
do_linux_by_ssh "$str_ip_orig_master" "root" "service mysql start"
do_sql "$str_ip_orig_master" "set global read_only=1;"
do_sql "$str_ip_orig_master" "$str_sql_mysql_change_master"
do_sql "$str_ip_orig_master" "start slave;"
}
function do_part_mha_master_manager_start() {
do_linux_by_ssh "$str_ip_mha_manager" "root" "nohup masterha_manager --conf=$file_conf_mha_application --ignore_last_failover &"
}
# 如果PID不存在，则执行该脚本，否则，退出
function runable_by_mha_manager_pid() {
echo "-----------------"
echo "Script for MySQL Master HA"
echo "-----------------"
echo "Begin:: "`date "+|%Y-%m-%d|%H:%M:%S|"`
if [[ "$str_pid_masterha_manager" == "" ]]
then
echo "## masterha_manager is [NOT ALIVED]."
else
echo "## masterha_manager is [ALIVED]."
echo "[masterha_manager] PID is:: $str_pid_masterha_manager"
# do something.
echo "## Exit Script"
exit 0
fi
}
# --------------------------
# action
# 如果PID不存在，则执行该脚本，否则，退出
echo "------------------"
echo "app: runable_by_mha_manager_pid"
runable_by_mha_manager_pid
echo ""
echo "------------------"
echo "app: gen_sql_mysql_change_master"
gen_sql_mysql_change_master
echo ""
#echo "------------------"
#echo "app: do_part_vip"
#do_part_vip
#echo ""
echo "------------------"
echo "app: do_part_orig_master_is_new_slave"
do_part_orig_master_is_new_slave
echo ""
echo "------------------"
echo "app: do_part_mha_master_manager_start"
#do_part_mha_master_manager_start
nohup masterha_manager --conf=$file_conf_mha_application --ignore_last_failover &
echo ""
# --------------------------
# Show time
# ---------
# version one
# ---------
#echo "new master is:: $str_ip_new_master"
#echo "Master log file is:: $str_log_file_new_master"
#echo "Master log POS is:: $str_log_pos_new_master"
#echo "orig master --> new master ## SQL: CHANGE MASTER ## is:: $str_sql_mysql_change_master"
# ---------
# version two
# ---------
echo "================="
echo "MySQL info:"
echo "## Account and Password"
echo "username @ $str_mysql_username"
echo "password @ $str_mysql_password"
echo "--- for REPLICATION ---"
echo "repl @ username ## $str_repl_username"
echo "repl @ password ## $str_repl_password"
echo "## Master Server info"
echo "log file @ Master ## $str_log_file_new_master"
echo "log pos @ Master ## $str_log_pos_new_master"
echo ""
echo "================="
echo "SQL statement:"
echo "[CHANGE MASTER] -->"
echo "$str_sql_mysql_change_master"
echo ""
echo "================="
echo "MasterHA info:"
echo "## File and Path"
echo "MHA Global config file @ $file_conf_mha_global"
echo "MHA Application config file @ $file_conf_mha_application"
echo "MHA Log file:: masterha_manager @ $file_log_mha_manager"
echo "## Architecture"
echo "Candicate Server list::"
echo "$list_ip_candicate"
echo "## IP"
echo "MHA Manager Server:: $str_ip_mha_manager"
echo "Last:: new master:: $str_ip_new_master"
echo "Last:: orig master:: $str_ip_orig_master"
echo ""
# --------------------------
echo "-----------------"
echo "Finished:: "`date "+|%Y-%m-%d|%H:%M:%S|"`
# Done
[root@mha1 software_me]#
文件：/script/shell/call_do_mha.sh
[root@mha1 software_me]# cat /script/shell/call_do_mha.sh # name: call_do_mha.sh while [ true ] do sh /script/shell/do_mha.sh done [root@mha1 software_me]#
如上，脚本就配置好了。
运行：
[root@mha1 ~]# ps -ef | grep manager root 47588 44708 0 00:31 pts/1 00:00:00 grep --color=auto manager [root@mha1 ~]# [root@mha1 ~]# nohup masterha_manager --conf=/etc/masterha_application_1.cnf --ignore_last_failover & [1] 47589 [root@mha1 ~]# nohup: ignoring input and appending output to ‘nohup.out’ [root@mha1 ~]# [root@mha1 ~]# ps -ef | grep manager root 47589 44708 2 00:31 pts/1 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/masterha_application_1.cnf --ignore_last_failover root 47662 44708 0 00:31 pts/1 00:00:00 grep --color=auto manager [root@mha1 ~]#
这样，当前的主库发生意外停掉的时候，MHA会自动检测到异常，并将备主切为新主，原来的旧的主库会作为从库加入到新的主库之中，并重新拉起MHA的manager监听进程。
五，还可以改进的地方
如上所述的MHA环境中只有一套MySQL的Replication环境，如果涉及多套，则，上面我写的脚本就需要做略微变化，才能适应这种场景。
这一部分，会在后面补充进来。
以上所有的脚本，均已在GITHUB上托管。
感兴趣的话，可以访问GITHUB，查阅最新的变更：
https://github.com/adamhuan/linux-script/tree/master/MySQL/MySQL%20MasterHA_%E5%A2%9E%E5%BC%BA
————————————————
Done。