MySQL高可用之MHA的搭建

最新推荐文章于 2024-08-29 22:08:43 发布

that is it

最新推荐文章于 2024-08-29 22:08:43 发布

阅读量253

点赞数

本文链接：https://blog.csdn.net/lj_11111/article/details/81008602

版权

官方介绍：https://code.google.com/p/mysql-master-ha/
参考文档：https://www.cnblogs.com/panwenbin-logs/p/8306906.html

测试环境

四台虚拟机：

server2：master              写入
server3：Candicate master    读
server4：slave               读
server5：Monitor host        监控复制组

其中master对外提供写服务，备选master（实际的slave，主机名server7）提供读服务，slave也提供相关的读服务，一旦master宕机，将会把备选master提升为新的master，slave指向新的master

一、数据库配置

1、server2：master

[root@server2 mysql]# mysql -pwestos
mysql> grant replication slave on *.* to natasha@'172.25.12.%' identified by 'westos';

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |     1006 |              |                  | 2a0a9397-83ea-11e8-b141-52540091ccfd:1-4 |
+---------------+----------+--------------+------------------+------------------------------------------+

mysql> grant all on *.*  to root@'172.25.32.%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.04 sec)

2、server3、4：slave

[root@server3 ~]# mysql -pwestos
mysql> change master to master_host='172.25.12.2',master_user='natasha',master_password='westos',master_auto_position=1;

mysql> start slave;
mysql> show slave status\G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.12.2
                  Master_User: natasha
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1006
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 1213
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

##server4同server3即可，需测试主从复制ok

mysql> grant replication slave on *.* to natasha@'172.25.12.%' identified by 'westos';

二、软件安装

1、3 个节点上安装

mha4mysql-node-0.56-0.el6.noarch.rpm
perl-DBD-MySQL.x86_64

## 依赖性软件:
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm

2、管理节点上安装

mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm

## 依赖性软件:
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm

三、配置 4 台主机 ssh 免密链接

注意:安装 openssh-clients-5.3p1-94.el6.x86_64

第一种方法：

1、server2、3、4 节点操作相似,以 server2 主机为例

[root@server2 ~]# ssh-keygen -t rsa  ##Enter 即可,选择默认方式
[root@server2 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.25.12.3
[root@server2 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.25.12.4
##server3 主机 copy 密钥至 server2、4 主机;server4 主机 copy 密钥
至 server2、3 主机

2、server5 主机:管理节点

[root@server5 ~]# ssh-keygen -t rsa
[root@server5 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.25.12.2
[root@server5 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.25.12.3
[root@server5 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.25.12.4

3、此时测试免密连接

server2、3、4 主机可互相免密登陆,server5 主机免密登陆其他主机

第二种方法：

server5 主机:管理节点

[root@server5 masterha]# ssh-keygen
[root@server5 masterha]# ssh-copy-id server5  ## 本机生成私钥
[root@server5 masterha]# ls /root/.ssh/
authorized_keys  id_rsa  id_rsa.pub  known_hosts

[root@server5 masterha]# scp /root/.ssh/* server2:/root/.ssh/
[root@server5 masterha]# scp /root/.ssh/* server3:/root/.ssh/
[root@server5 masterha]# scp /root/.ssh/* server4:/root/.ssh/

此时测试免密连接

server2、3、4 主机可互相免密登陆,server5 主机免密登陆其他主机

四、MHA-Manger配置

1、创建 MHA 的工作目录,并做相应配置

[root@server5 ～]# mkdir /etc/masterha
[root@server5 masterha]# pwd
/etc/masterha
[root@server5 masterha]# vim app.cnf

[server default]
manager_log=/etc/masterha/mha.log   //设置manager的日志
manager_workdir=/etc/masterha/      //设置manager的工作目录
master_binlog_dir=/var/lib/mysql    //设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录
#master_ip_online_change_script=/etc/masterha/master_ip_online_change //设置自动failover时候的切换脚本
#master_ip_online_change_script=/etc/masterha/master_ip_online_change //设置手动切换时候的切换脚本
password=westos     //设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码
ping_interval=1     //设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover
remote_workdir=/tmp //设置远端mysql在发生切换时binlog的保存位置
repl_password=westos    //设置复制用户的密码
repl_user=natasha   //设置复制用户
ssh_user=root       //设置ssh的登录用户名
user=root   //设置监控用户root
#report_script=/etc/masterha/send_report //设置发生切换后发送的报警的脚本

[server2]
hostname=172.25.12.2
port=3306

[server3]
candidate_master=1   //设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
check_repl_delay=0   //默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master
hostname=172.25.12.3
port=3306

[server4]
hostname=172.25.12.4
port=3306

2、每个 slave 节点设置 relay log 的清除方式

mysql> set global relay_log_purge=0;
Query OK, 0 rows affected (0.00 sec)
## 建议写入配置文件/etc/my.cnf中,以防重启数据库失效
relay_log_purge=0;

3、检测ssh配置

[root@server5 ~]# masterha_check_ssh –conf=/etc/masterha/app.cnf

Tue Jul 10 10:54:59 2018 - [info] All SSH connection tests passed successfully.
## 显示以上内容，则ssh免密连接成功

4、检测repl环境

[root@server5 masterha]# masterha_check_repl --conf=/etc/masterha/app.cnf 

MySQL Replication Health is OK.     ## 主从复制环境配置成功

五、测试

1、开启MHA后台监控

[root@server5 masterha]# nohup masterha_manager --conf=/etc/masterha/app.cnf &
[1] 1112
[root@server5 masterha]# nohup: ignoring input and appending output to `nohup.out'    ## 此处直接回车
[root@server5 masterha]#

2、server2：master down

关闭mysql进程：

[root@server2 ~]# ps ax
1392 pts/0    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.
 1641 pts/0    Sl     0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mys

[root@server2 mysql]# kill -9 1392
[root@server2 mysql]# kill -9 1641

此时 MHA manager 自动生成 app.failover.complete 文件

[root@server5 masterha]# ls
app.cnf                master_ip_failover       mha.log
app.failover.complete  master_ip_online_change  nohup.out

3、master down后server3自动接管

server3：

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                  |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| binlog.000002 |     2017 |              |                  | 2a0a9397-83ea-11e8-b141-52540091ccfd:1-6,
a5ddab64-83ea-11e8-9661-5254008d996b:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show slave status\G;
Empty set (0.00 sec)

ERROR: 
No query specified

4、server4：master指向server3

mysql> change master to master_host='172.25.12.3',master_user='natasha',master_password='westos',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.12.3
                  Master_User: natasha
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 2017
               Relay_Log_File: server4-relay-bin.000002
                Relay_Log_Pos: 961
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

六、MHA手动切换

1、server2：mysql 开启（此时3为master，状态alive）

[root@server2 ~]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]

mysql> change master to master_host='172.25.12.3',master_user='natasha',master_password='westos',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.95 sec)

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.12.3

MHA管理： ##注意：配置文件不能指定备用master

[root@server5 masterha]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.12.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.25.12.3(172.25.12.3:3306)? (YES/no): YES

Starting master switch from 172.25.12.3(172.25.12.3:3306) to 172.25.12.2(172.25.12.2:3306)? (yes/NO): yes

master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes

Tue Jul 10 13:48:16 2018 - [info] Switching master to 172.25.12.2(172.25.12.2:3306) completed successfully.

查看状态：

server2恢复master：

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                  |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| binlog.000003 |      750 |              |                  | 2a0a9397-83ea-11e8-b141-52540091ccfd:1-6,
a5ddab64-83ea-11e8-9661-5254008d996b:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+

server3恢复slave：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.12.2

server4 master指向server2：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.12.2

2、若master down，没有 nohup masterha_manager，则需手动切换

关闭MHA Manage监控

[root@server5 ~]$ masterha_stop --conf=/etc/masterha/app.cnf
Stopped app1 successfully.
[1]+  Exit 1                  nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1

关闭server2数据库：

[root@server2 ~]# ps ax
  884 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.
 1131 ?        Sl     0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mys

[root@server2 ~]# kill -9 884
[root@server2 ~]# kill -9 1131

MHA管理：

[root@server5 masterha]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=dead --dead_master_host=172.25.12.2 -dead_master_port=3306 --new_master_host=172.25.12.4 --new_master_port=3306 --ignore_last_failover

----- Failover Report -----

app: MySQL Master failover 172.25.12.2(172.25.12.2:3306) to 172.25.12.4(172.25.12.4:3306) succeeded

Master 172.25.12.2(172.25.12.2:3306) is down!

Check MHA Manager logs at server5 for details.

Master failover to 172.25.12.4(172.25.12.4:3306) completed successfully.

查看状态：

server4：切换master

mysql> show slave status\G;
Empty set (0.00 sec)

ERROR: 
No query specified

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                            |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------+
| binlog.000002 |     2573 |              |                  | 2a0a9397-83ea-11e8-b141-52540091ccfd:1-6,
a5ddab64-83ea-11e8-9661-5254008d996b:1-3,
ce6734ff-83ea-11e8-9786-525400eec4fb:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------+

server3：master指向server4

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.12.4

server2:master指向server4

mysql> change master to master_host='172.25.12.4',master_user='natasha',master_password='westos',master_auto_position=1;

mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.12.4

3、配置VIP

[root@server4 masterha]# vim app1.cnf 
[server default]
manager_log=/etc/masterha/mha.log
manager_workdir=/etc/masterha/
master_binlog_dir=/var/lib/mysql
master_ip_online_change_script=/etc/masterha/master_ip_online_change
master_ip_failover_script= /etc/masterha/master_ip_failover
report_script=/etc/masterha/send_report
password=westos
ping_interval=1
remote_workdir=/tmp
repl_password=westos
repl_user=natasha
ssh_user=root
user=root

[server2]
hostname=172.25.12.2
port=3306

[server3]
#candidate_master=1
#check_repl_delay=0
hostname=172.25.12.3
port=3306

[server4]
hostname=172.25.12.4
port=3306
candidate_master=1
check_repl_delay=0

目前server4为master，server2、3为slave

[root@server4 ~]# ip addr add 172.25.12.100/24 dev eth0
[root@server4 ~]# ip addr
    inet 172.25.12.4/24 brd 172.25.12.255 scope global eth0
    inet 172.25.12.100/24 scope global secondary eth0

修改脚本master_ip_failover master_ip_online_change

my $vip = '172.25.12.100/24';  ##注意：两个脚本都要改！
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";

[root@server5 masterha]# chmod +x master_ip_failover
[root@server5 masterha]# chmod +x master_ip_online_change

MHA管理：

[root@server5 masterha]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.12.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

***************************************************************
Disabling the VIP - 172.25.12.100/24 on old master: 172.25.12.4
***************************************************************

***************************************************************
Enabling the VIP - 172.25.12.100/24 on new master: 172.25.12.2 
***************************************************************

Tue Jul 10 14:21:42 2018 - [info] Switching master to 172.25.12.2(172.25.12.2:3306) completed successfully.

此时server2成为master，VIP漂移到server2主机：

[root@server2 ~]# ip addr
    inet 172.25.12.2/24 brd 172.25.12.255 scope global eth0
    inet 172.25.12.100/24 scope global secondary eth0

4、发送邮件

修改配置文件 app.cnf

## 在[server default]中添加
report_script=/etc/masterha/send_report

## 修改send_report
my $smtp='smtp.163.com';
my $mail_from='xxx@163.com';
my $mail_user='xxx@163.com';
my $mail_pass='password';
my $mail_to='xxx@163.com';

七、Mysql其他功能

1、慢查询

mysql> show variables like '%slow%';
+---------------------------+---------------------------------+
| Variable_name             | Value                           |
+---------------------------+---------------------------------+
| log_slow_admin_statements | OFF                             |
| log_slow_slave_statements | OFF                             |
| slow_launch_time          | 2                               |
| slow_query_log            | OFF                             |
| slow_query_log_file       | /var/lib/mysql/server3-slow.log |
+---------------------------+---------------------------------+

##sql语句执行超过10s，会记录为慢语句，存放在/var/lib/mysql/server3-slow.log
mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+

2、索引

mysql> show variables like '%index%';
+----------------------------------------+----------------------------------------+
| Variable_name                          | Value                                  |
+----------------------------------------+----------------------------------------+
| eq_range_index_dive_limit              | 200                                    |
| innodb_adaptive_hash_index             | ON                                     |
| innodb_adaptive_hash_index_parts       | 8                                      |
| innodb_cmp_per_index_enabled           | OFF                                    |
| log_bin_index                          | /var/lib/mysql/binlog.index            |
| log_queries_not_using_indexes          | OFF                                    |
| log_throttle_queries_not_using_indexes | 0                                      |
| performance_schema_max_index_stat      | -1                                     |
| relay_log_index                        | /var/lib/mysql/server3-relay-bin.index |
+----------------------------------------+----------------------------------------+

3、日志截断 mysqlbinlog –help

4、二进制文件

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |      2573 |
+---------------+-----------+

reset master；   ##清空master的二进制日志
reset slave；    ##清空slave的二进制日志