爱站程序员基地1 案例1：准备MHA集群环境
1.1 问题
• 准备6台虚拟机，并按照本节规划配置好IP参数
• 在这些虚拟机之间实现SSH免密登录
• 在相应节点上安装好MHA相关的软件包
1.2 方案
使用6台RHEL 7虚拟机，如图-1所示。准备集群环境，安装依赖包，授权用户，配置ssh密钥对认证登陆，所有节点之间互相以root秘钥对认证登录，管理主机以root密钥对认证登录所有数据节点主机，配置mha集群。
图－1
IP规划，如图-2所示：
图-2
1.3
1.4 步骤
实现此案例需要按照如下步骤进行。
步骤一： 准备集群环境
1）修改主机名，配置IP（其余几台请按照图-2修改IP和主机名，这里以master51为例）
1. [root@zlz ~]# echo master51 > /etc/hostname2. [root@zlz ~]# nmcli connection modify eth0 ipv4.method manual ipv4.addresses 192.168.4.51/24 connection.autoconnect yes3. [root@zlz ~]# nmcli connection up eth0
2）在所有主机上安装Perl依赖包（51-56操作）
1. [root@zlz ~]# cd mysql/mha-soft-student/2. [root@zlz ~]# yum -y install perl-*.rpm
3）在所有数据库服务器上安装mha-node包（51-55操作）
1. [root@zlz mha-soft-student]# yum -y install perl-DBD-mysql perl-DBI2. [root@zlz mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm3. Preparing... ################################# [100%]4. Updating / installing...5. 1:mha4mysql-node-0.56-0.el6 ################################# [100%]
4）在管理主机上安装mha_node 和 mha-manager包（56操作）
1. [root@zlz mha-soft-student]# yum -y install perl-DBD-mysql perl-DBI2. [root@zlz mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm3. Preparing... ################################# [100%]4. Updating / installing...5. 1:mha4mysql-node-0.56-0.el6 ################################# [100%]6. [root@zlz mha-soft-student]# yum -y install perl-ExtUtils-* perl-CPAN-*7. [root@zlz mha-soft-student]# tar -zxf mha4mysql-manager-0.56.tar.gz8. [root@zlz mha-soft-student]# cd mha4mysql-manager-0.56/9. [root@zlz mha4mysql-manager-0.56]# perl Makefile.PL10. *** Module::AutoInstall version 1.0311. *** Checking for Perl dependencies...12. [Core Features]13. - DBI ...loaded. (1.627)14. - DBD::mysql ...loaded. (4.023)15. - Time::HiRes ...loaded. (1.9725)16. - Config::Tiny ...loaded. (2.14)17. - Log::Dispatch ...loaded. (2.41)18. - Parallel::ForkManager ...loaded. (1.18)19. - MHA::NodeConst ...loaded. (0.56)20. *** Module::AutoInstall configuration finished. //配置完成21. Checking if your kit is complete...22. Looks good23. Writing Makefile for mha4mysql::manager24. Writing MYMETA.yml and MYMETA.json25. [root@zlz mha4mysql-manager-0.56]# make26. [root@zlz mha4mysql-manager-0.56]# make install
步骤二： 配置ssh密钥对认证登陆
1）所有节点之间可以互相以ssh密钥对方式认证登陆以（以51为例）
1. [root@zlz mha-soft-student]# ssh-keygen2. [root@zlz mha-soft-student]# ssh-copy-id 192.168.4.523. //除了传给52外，53，54，55也要传，52-55主机也是一样的
6）配置56主机 无密码ssh登录所有数据节点主机
1. [root@zlz mha4mysql-manager-0.56]# ssh-keygen2. [root@zlz mha4mysql-manager-0.56]# ssh-copy-id 192.168.4.513. //除传给51外，还要传给52-55
2 案例2：配置MHA集群环境
2.1 问题
• 配置主节点 master51
• 配置两个备用主节点 master52、master53
• 配置两个从节点 slave54、slave55
• 配置管理节点 mgm56
2.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：配置mha集群环境
1）安装数据库（51-55同样操作，以51为例）
1. [root@zlz ~]# cd /root/mysql2. [root@zlz mysql]# tar -xf mysql-5.7.17.tar3. [root@zlz mysql]# yum -y install perl-JSON4. [root@zlz mysql]# rpm -Uvh mysql-community-*.rpm5. [root@zlz mysql]# rpm -qa | grep -i mysql
2）master51 数据库服务器配置文件
1. [root@zlz mysql]# vim /etc/my.cnf2. plugin-load = \"rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so\"3. rpl-semi-sync-master-enabled = 14. rpl-semi-sync-slave-enabled = 15. server_id=516. log-bin=master517. binlog-format=\"mixed\"8.9. [root@zlz mysql]# systemctl restart mysqld10.11. [root@zlz mysql]# mysql -u root -p12345612.13. mysql> set global relay_log_purge=off; //不自动删除本机的中继日志文件14. Query OK, 0 rows affected (0.00 sec)15.16. mysql> grant replication slave on *.* to repluser@\"%\" identified by \"123456\";17. //添加主从同步授权用户18. Query OK, 0 rows affected, 1 warning (10.01 sec)19.20. mysql> show master status;21. +-----------------+----------+--------------+------------------+-------------+22. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |23. +-----------------+----------+--------------+------------------+--------------+24. | master51.000003 | 441 | | | |25. +-----------------+----------+--------------+------------------+--------------+26. 1 row in set (0.00 sec)
3）master52数据库服务器配置文件
1. [root@zlz mysql]# vim /etc/my.cnf2. plugin-load =\"rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so\"3. rpl-semi-sync-master-enabled = 14. rpl-semi-sync-slave-enabled = 15. server_id=526. log-bin=master527. binlog-format=\"mixed\"8.9. [root@zlz mysql]# systemctl restart mysqld10. [root@zlz mysql]# mysql -u root -p12345611. mysql> set global relay_log_purge=off;12. mysql> change master to13. -> master_host=\"192.168.4.51\",14. -> master_user=\"repluser\",15. -> master_password=\"123456\",16. -> master_log_file=\"master51.000003\",17. -> master_log_pos=441;18. Query OK, 0 rows affected, 2 warnings (0.01 sec)19. mysql> start slave;20. Query OK, 0 rows affected (0.01 sec)21. mysql> show slave status\\G;22. ...23. Slave_IO_Running: Yes24. Slave_SQL_Running: Yes25. ...
4）master53数据库服务器配置文件
1. [root@zlz mysql]# vim /etc/my.cnf2. plugin-load =\"rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so\"3. rpl-semi-sync-master-enabled = 14. rpl-semi-sync-slave-enabled = 15. server_id=536. log-bin=master537. binlog-format=\"mixed\"8.9. [root@zlz mysql]# systemctl restart mysqld10. [root@zlz mysql]# mysql -u root -p12345611. mysql> set global relay_log_purge=off;12. Query OK, 0 rows affected (0.00 sec)13.14. mysql> change master to15. -> master_host=\"192.168.4.51\",16. -> master_user=\"repluser\",17. -> master_password=\"123456\",18. -> master_log_file=\"master51.000003\",19. -> master_log_pos=441;20. Query OK, 0 rows affected, 2 warnings (0.01 sec)21. mysql> start slave;22. Query OK, 0 rows affected (0.00 sec)23. mysql> show slave status\\G;24. ...25. Slave_IO_Running: Yes26. Slave_SQL_Running: Yes27. ...
5）slave54 数据库服务器配置文件
1. [root@zlz mysql]# vim /etc/my.cnf2. server_id=543. [root@zlz mysql]# systemctl restart mysqld4. [root@zlz mysql]# mysql -u root -p1234565. mysql> change master to6. -> master_host=\"192.168.4.51\",7. -> master_user=\"repluser\",8. -> master_password=\"123456\",9. -> master_log_file=\"master51.000003\",10. -> master_log_pos=441;11. Query OK, 0 rows affected, 2 warnings (0.01 sec)12. mysql> start slave;13. Query OK, 0 rows affected (0.00 sec)14. mysql> show slave status\\G;15. ...16. Slave_IO_Running: Yes17. Slave_SQL_Running: Yes18. ...
6）slave55 数据库服务器配置文件
1. [root@zlz mysql]# vim /etc/my.cnf2. server_id=553.4. [root@master55 mysql]# systemctl restart mysqld5. [root@master55 mysql]# mysql -u root -p1234566. mysql> change master to7. -> master_host=\"192.168.4.51\",8. -> master_user=\"repluser\",9. -> master_password=\"123456\",10. -> master_log_file=\"master51.000003\",11. -> master_log_pos=441;12. Query OK, 0 rows affected, 2 warnings (0.01 sec)13. mysql> start slave;14. Query OK, 0 rows affected (0.00 sec)15. mysql> show slave status\\G;16. ...17. Slave_IO_Running: Yes18. Slave_SQL_Running: Yes19. ...
7）配置管理主机4.56
1. [root@zlz ~]# cd mysql/mha-soft-student/mha4mysql-manager-0.56/2. [root@zlz mha4mysql-manager-0.56]# cp bin/* /usr/local/bin/3. //提示覆盖，说明安装的时候有，没有可以拷贝过来4. [root@mgm56 mha4mysql-manager-0.56]# mkdir /etc/mha_manager //创建工作目录5. [root@mgm56 mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /etc/mha_manager6. //建立样板文件7. [root@mgm56 mha4mysql-manager-0.56]# vim /etc/mha_manager/app1.cnf8. //编辑主配置文件app1.cnf9. [server default]10. manager_workdir=/etc/mha_manager11. manager_log=/etc/mha_manager/manager.log12. master_ip_failover_script=/usr/local/bin/master_ip_failover13.14. ssh_user=root15. ssh_port=2216. repl_user=repluser17. repl_password=12345618. user=root19. password=12345620.21. [server1]22. hostname=192.168.4.5123. port=330624.25. [server2]26. hostname=192.168.4.5227. port=330628. candidate_master=129.30. [server3]31. hostname=192.168.4.5332. port=330633. candidate_master=134.35. [server4]36. hostname=192.168.4.5437. no_master=138.39. [server5]40. hostname=192.168.4.5541. no_master=142. [root@mgm56 mha4mysql-manager-0.56]# cp samples/scripts/master_ip_failover43. /usr/local/bin/ //创建故障切换的脚本
3 案例3：测试MHA集群
3.1 问题
• 查看MHA集群状态
• 测试节点之间的SSH登录
• 测试集群VIP的故障切换功能
3.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：验证配置
1）检查配置环境，在主机52，53检查是否有同步数据的用户repluser
主机52:
1. mysql> select user,host from mysql.user where user=\"repluser\";2. +----------+------+3. | user | host |4. +----------+------+5. | repluser | % |6. +----------+------+7. 1 row in set (0.00 sec)8.9. mysql> show grants for repluser@\"%\";10. +--------------------------------------------------+11. | Grants for repluser@% |12. +--------------------------------------------------+13. | GRANT REPLICATION SLAVE ON *.* TO \'repluser\'@\'%\' |14. +--------------------------------------------------+15. 1 row in set (0.00 sec
主机53:
1. mysql> select user,host from mysql.user where user=\"repluser\";2. +----------+------+3. | user | host |4. +----------+------+5. | repluser | % |6. +----------+------+7. 1 row in set (0.00 sec)8.9. mysql> show grants for repluser@\"%\";10. +--------------------------------------------------+11. | Grants for repluser@% |12. +--------------------------------------------------+13. | GRANT REPLICATION SLAVE ON *.* TO \'repluser\'@\'%\' |+--------------------------------------------------+1. 1 row in set (0.00 sec)
2）在51的主机上做root的授权，其他的会同步（如果不做，在验证数据节点的主从同步配置时会出错）
1. mysql> grant all on *.* to root@\"%\" identified by \"123456\";2. mysql> select user,host from mysql.user where user=\"root\";3. +------+-----------+4. | user | host |5. +------+-----------+6. | root | % |7. | root | localhost |8. +------+-----------+9. 2 rows in set (0.00 sec)
3）验证ssh 免密登陆数据节点主机
1. [root@zlz mha4mysql-manager-0.56]# cd /usr/local/bin/2. [root@zlz bin]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf3. Wed Sep 19 09:09:33 2018 - [info] All SSH connection tests passed successfully.4. //出现这个为成功
4）验证数据节点的主从同步配置（先把自动failover时候的切换脚本注释掉）
1. [root@zlz bin]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf2. MySQL Replication Health is OK. //验证成功
5）启动管理服务MHA_Manager
–remove_dead_master_conf //删除宕机主库配置
–ignore_last_failover //忽略xxx.health文件
1. [root@zlz bin]# masterha_manager --conf=/etc/mha_manager/app1.cnf \\2. --remove_dead_master_conf --ignore_last_failover3.4. Wed Sep 19 09:24:41 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.5. Wed Sep 19 09:24:41 2018 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..6. Wed Sep 19 09:24:41 2018 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..
6）查看状态（另开一个终端）
1. [root@zlz ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf2. app1 (pid:15745) is running(0:PING_OK), master:192.168.4.51
7）停止服务
1. [root@zlz ~]# masterha_stop --conf=/etc/mha_manager/app1.cnf2. Stopped app1 successfully.
步骤二：测试故障转移
1）在主库51上面配置VIP地址
1. [root@zlz ~]# ifconfig eth0:1 192.168.4.100/24
2）在配置文件里面把自动failover时候的切换脚本去掉注释
3）修改 master_ip_failover 脚本，设置如下内容
1. 34 my $vip = \'192.168.4.100/24\';2. 35 my $key = \"1\";3. 36 my $ssh_start_vip = \"/sbin/ifconfig eth0:$key $vip\";4. 37 my $ssh_stop_vip = \"/sbin/ifconfig eth0:$key down\";
4）启动服务
1. [root@zlz bin]# masterha_manager --conf=/etc/mha_manager/app1.cnf \\2. --remove_dead_master_conf --ignore_last_failover3. Wed Sep 19 09:50:33 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.4. Wed Sep 19 09:50:33 2018 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..5. Wed Sep 19 09:50:33 2018 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..
5）查看状态
1. [root@zlz ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf2. app1 master is down and failover is running(50:FAILOVER_RUNNING). master:192.168.4.52
验证数据节点的主从同步配置报错，如图-3所示：
1. [root@zlz bin]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf
图-3
解决办法：
root用户没有授权，默认只能本地连接，在主机51上面授权root用户可以远程登录，其他主机会同步
1. mysql> grant all on *.* to root@\"%\" identified by \"123456\";
