Top

NSD DBA2 DAY03

  1. 案例1:准备MHA集群环境
  2. 案例2:配置MHA集群环境
  3. 案例3:测试MHA集群

1 案例1:准备MHA集群环境

1.1 问题

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@localhost ~]# echo master51 > /etc/hostname
  2. [root@localhost ~]# nmcli connection modify eth0 ipv4.method manual ipv4.addresses 192.168.4.51/24 connection.autoconnect yes
  3. [root@localhost ~]# nmcli connection up eth0

2在所有主机上安装Perl依赖包(51-56操作)

  1. [root@master51 ~]# cd mysql/mha-soft-student/
  2. [root@master51 ~]# yum -y install perl-*.rpm

3)在所有数据库服务器上安装mha-node包(51-55操作)

  1. [root@master51 mha-soft-student]# yum -y install perl-DBD-mysql perl-DBI
  2. [root@master51 mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
  3. Preparing... ################################# [100%]
  4. Updating / installing...
  5. 1:mha4mysql-node-0.56-0.el6 ################################# [100%]

4)在管理主机上安装mha_node 和 mha-manager包(56操作)

  1. [root@mgm56 mha-soft-student]# yum -y install perl-DBD-mysql perl-DBI
  2. [root@mgm56 mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
  3. Preparing... ################################# [100%]
  4. Updating / installing...
  5. 1:mha4mysql-node-0.56-0.el6 ################################# [100%]
  6. [root@mgm56 mha-soft-student]# yum -y install perl-ExtUtils-* perl-CPAN-*
  7. [root@mgm56 mha-soft-student]# tar -zxf mha4mysql-manager-0.56.tar.gz
  8. [root@mgm56 mha-soft-student]# cd mha4mysql-manager-0.56/
  9. [root@mgm56 mha4mysql-manager-0.56]# perl Makefile.PL
  10. *** Module::AutoInstall version 1.03
  11. *** 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 good
  23. Writing Makefile for mha4mysql::manager
  24. Writing MYMETA.yml and MYMETA.json
  25. [root@mgm56 mha4mysql-manager-0.56]# make
  26. [root@mgm56 mha4mysql-manager-0.56]# make install

步骤二: 配置ssh密钥对认证登陆

1)所有节点之间可以互相以ssh密钥对方式认证登陆以(以51为例)

  1. [root@master51 mha-soft-student]# ssh-keygen
  2. [root@master51 mha-soft-student]# ssh-copy-id 192.168.4.52
  3. //除了传给52外,53,54,55也要传,52-55主机也是一样的

6)配置56主机 无密码ssh登录所有数据节点主机

  1. [root@mgm56 mha4mysql-manager-0.56]# ssh-keygen
  2. [root@mgm56 mha4mysql-manager-0.56]# ssh-copy-id 192.168.4.51
  3. //除传给51外,还要传给52-55

2 案例2:配置MHA集群环境

2.1 问题

2.2 步骤

实现此案例需要按照如下步骤进行。

步骤一:配置mha集群环境

1)安装数据库(51-55同样操作,以51为例)

  1. [root@master51 ~]# cd /root/mysql
  2. [root@master51 mysql]# tar -xf mysql-5.7.17.tar
  3. [root@master51 mysql]# yum -y install perl-JSON
  4. [root@master51 mysql]# rpm -Uvh mysql-community-*.rpm
  5. [root@master51 mysql]# rpm -qa | grep -i mysql

2)master51 数据库服务器配置文件

  1. [root@master51 mysql]# vim /etc/my.cnf
  2. plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
  3. rpl-semi-sync-master-enabled = 1
  4. rpl-semi-sync-slave-enabled = 1
  5. server_id=51
  6. log-bin=master51
  7. binlog-format="mixed"
  8.  
  9. [root@master51 mysql]# systemctl restart mysqld
  10.  
  11. [root@master51 mysql]# mysql -u root -p123456
  12.  
  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@master52 mysql]# vim /etc/my.cnf
  2. plugin-load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
  3. rpl-semi-sync-master-enabled = 1
  4. rpl-semi-sync-slave-enabled = 1
  5. server_id=52
  6. log-bin=master52
  7. binlog-format="mixed"
  8.  
  9. [root@master52 mysql]# systemctl restart mysqld
  10. [root@master52 mysql]# mysql -u root -p123456
  11. mysql> set global relay_log_purge=off;
  12. mysql> change master to
  13. -> 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: Yes
  24. Slave_SQL_Running: Yes
  25. ...

4)master53数据库服务器配置文件

  1. [root@master53 mysql]# vim /etc/my.cnf
  2. plugin-load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
  3. rpl-semi-sync-master-enabled = 1
  4. rpl-semi-sync-slave-enabled = 1
  5. server_id=53
  6. log-bin=master53
  7. binlog-format="mixed"
  8.  
  9. [root@master53 mysql]# systemctl restart mysqld
  10. [root@master53 mysql]# mysql -u root -p123456
  11. mysql> set global relay_log_purge=off;
  12. Query OK, 0 rows affected (0.00 sec)
  13.  
  14. mysql> change master to
  15. -> 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: Yes
  26. Slave_SQL_Running: Yes
  27. ...

5)slave54 数据库服务器配置文件

  1. [root@slave54 mysql]# vim /etc/my.cnf
  2. server_id=54
  3. [root@master54 mysql]# systemctl restart mysqld
  4. [root@master54 mysql]# mysql -u root -p123456
  5. mysql> change master to
  6. -> 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: Yes
  17. Slave_SQL_Running: Yes
  18. ...

6)slave55 数据库服务器配置文件

  1. [root@slave55 mysql]# vim /etc/my.cnf
  2. server_id=55
  3.  
  4. [root@master55 mysql]# systemctl restart mysqld
  5. [root@master55 mysql]# mysql -u root -p123456
  6. mysql> change master to
  7. -> 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: Yes
  18. Slave_SQL_Running: Yes
  19. ...

7)配置管理主机4.56

  1. [root@mgm56 ~]# cd mysql/mha-soft-student/mha4mysql-manager-0.56/
  2. [root@mgm56 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_manager
  6. //建立样板文件
  7. [root@mgm56 mha4mysql-manager-0.56]# vim /etc/mha_manager/app1.cnf
  8. //编辑主配置文件app1.cnf
  9. [server default]
  10. manager_workdir=/etc/mha_manager
  11. manager_log=/etc/mha_manager/manager.log
  12. master_ip_failover_script=/usr/local/bin/master_ip_failover
  13.  
  14. ssh_user=root
  15. ssh_port=22
  16. repl_user=repluser
  17. repl_password=123456
  18. user=root
  19. password=123456
  20.  
  21. [server1]
  22. hostname=192.168.4.51
  23. port=3306
  24.  
  25. [server2]
  26. hostname=192.168.4.52
  27. port=3306
  28. candidate_master=1
  29.  
  30. [server3]
  31. hostname=192.168.4.53
  32. port=3306
  33. candidate_master=1
  34.  
  35. [server4]
  36. hostname=192.168.4.54
  37. no_master=1
  38.  
  39. [server5]
  40. hostname=192.168.4.55
  41. no_master=1
  42. [root@mgm56 mha4mysql-manager-0.56]# cp samples/scripts/master_ip_failover
  43. /usr/local/bin/        //创建故障切换的脚本

3 案例3:测试MHA集群

3.1 问题

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@mgm56 mha4mysql-manager-0.56]# cd /usr/local/bin/
  2. [root@mgm56 bin]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf
  3. Wed Sep 19 09:09:33 2018 - [info] All SSH connection tests passed successfully.
  4. //出现这个为成功

4)验证数据节点的主从同步配置(先把自动failover时候的切换脚本注释掉)

  1. [root@mgm56 bin]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf
  2. MySQL Replication Health is OK. //验证成功

5)启动管理服务MHA_Manager

--remove_dead_master_conf //删除宕机主库配置

--ignore_last_failover //忽略xxx.health文件

  1. [root@mgm56 bin]# masterha_manager --conf=/etc/mha_manager/app1.cnf \
  2. --remove_dead_master_conf --ignore_last_failover
  3.  
  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@mgm56 ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf
  2. app1 (pid:15745) is running(0:PING_OK), master:192.168.4.51

7)停止服务

  1. [root@mgm56 ~]# masterha_stop --conf=/etc/mha_manager/app1.cnf
  2. Stopped app1 successfully.

步骤二:测试故障转移

1)在主库51上面配置VIP地址

  1. [root@master51 ~]# 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@mgm56 bin]# masterha_manager --conf=/etc/mha_manager/app1.cnf \
  2. --remove_dead_master_conf --ignore_last_failover
  3. 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@mgm56 ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf
  2. app1 master is down and failover is running(50:FAILOVER_RUNNING). master:192.168.4.52

验证数据节点的主从同步配置报错,如图-3所示:

  1. [root@mgm56 bin]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf

图-3

解决办法:

root用户没有授权,默认只能本地连接,在主机51上面授权root用户可以远程登录,其他主机会同步

  1. mysql> grant all on *.* to root@"%" identified by "123456";