Mysql的高可用架构的搭建(同时实现读写分离)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接: https://blog.csdn.net/weixin_44889616/article/details/97616654

一.Mysql架构的简介

MHA:

MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案,它由日本DeNA公司youshimaton(现就职于Facebook公司)开发,是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中,MHA能做到在0~30秒之内自动完成数据库的故障切换操作,并且在进行故障切换的过程中,MHA能在最大程度上保证数据的一致性,以达到真正意义上的高可用。
该软件由两部分组成:MHA Manager(管理节点)和MHA Node(数据节点)。
MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群,也可以部署在一台slave节点上。
MHA Node运行在每台MySQL服务器上,MHA Manager会定时探测集群中的master节点,当master出现故障时,它可以自动将最新数据的slave提升为新的master,然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

MHA优点:

在MHA自动故障切换过程中,MHA试图从宕机的主服务器上保存二进制日志,最大程度的保证数据的不丢失,但这并不总是可行的。例如,如果主服务器硬件故障或无法通过ssh访问,MHA没法保存二进制日志,只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制,可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志,MHA可以将最新的二进制日志应用于其他所有的slave服务器上,因此可以保证所有节点的数据一致性。

MHA处理流程:

从宕机崩溃的master保存二进制日志事件(binlog events);
识别含有最新更新的slave;
应用差异的中继日志(relay log)到其他的slave;
应用从master保存的二进制日志事件(binlog events);
提升一个slave为新的master;
使其他的slave连接新的master进行复制;

二.mysql的高可用集群架构的搭建

实验环境:

主机名(IP) 服务
server1(172.25.16.1) master
server2(172.25.16.2) slave(备master)
server3(172.25.16.3) slave
server4(172.25.16.4) MHA

在server1、server2、server3配置基于gtid的主从复制

  1. 刷新数据库,server1、server2、server3的操作相同

在这里插入图片描述

  1. 编辑配置文件
    server1:

在这里插入图片描述
server2:
在这里插入图片描述
server3:
在这里插入图片描述

  1. 重启数据库,获取初始密码
systemctl start mysqld
cat /var/log/mysql.log | grep password
  1. 配置主从

server1(主):

[[email?protected] mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log

Copyright (c) 2000, 2018, 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> alter user [email?protected] identified by 'Szy+123en';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to [email?protected]'172.25.16.%' identified by 'Szy+123en';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.04 sec)

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)

mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global rpl_semi_sync_master_timeout=10000000000000;
Query OK, 0 rows affected (0.00 sec)

server2(从):

[[email?protected] mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log

Copyright (c) 2000, 2018, 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> alter user [email?protected] identified by 'Szy+123en';
Query OK, 0 rows affected (0.14 sec)
mysql> change master to master_host='172.25.16.1', master_user='repl',master_password='Szy+123en',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.17 sec)

mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.16.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 691
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 898
        Relay_Master_Log_File: binlog.000002
             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: 691
              Relay_Log_Space: 1107
              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: c1d68221-b782-11e9-9293-5254004772f0
             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: c1d68221-b782-11e9-9293-5254004772f0:1-2
            Executed_Gtid_Set: c1d68221-b782-11e9-9293-5254004772f0:1-2,
ef766a82-b783-11e9-a2e0-52540039676f:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

server3(从):

mysql> alter user [email?protected] identified by 'Szy+123en';
Query OK, 0 rows affected (0.08 sec)

mysql> change master to master_host='172.25.16.1', master_user='repl',master_password='Szy+123en',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.15 sec)

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)

mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.16.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 691
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 898
        Relay_Master_Log_File: binlog.000002
             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: 691
              Relay_Log_Space: 1107
              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: c1d68221-b782-11e9-9293-5254004772f0
             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: c1d68221-b782-11e9-9293-5254004772f0:1-2
            Executed_Gtid_Set: 2988fbc1-b785-11e9-ab3c-52540046c65d:1,
c1d68221-b782-11e9-9293-5254004772f0:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

测试:
在server1写入数据:

mysql> create database westos;
Query OK, 1 row affected (0.01 sec)

mysql> use westos;
Database changed
mysql> create table userlist(
    -> username varchar(10) not null,
    -> password varchar(15) not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into userlist values ('user1','123')
    -> ;
Query OK, 1 row affected (0.02 sec)

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

在这里插入图片描述
server2查看
在这里插入图片描述
server3查看
在这里插入图片描述

三.在server4上安装管理节点

[[email?protected] MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-manager-0.58.tar.gz
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[[email?protected] MHA-7]# yum install *

在这里插入图片描述
2.添加解析

vim /etc/hosts

在这里插入图片描述
3.生成server4的ssh密钥,并发送给server1、server2、server3

ssh-keygen
ssh-copy-id server1
ssh-copy-id server2
ssh-copy-id server3

在这里插入图片描述

4.server1、server2、server3安装节点

[[email?protected] MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:
mha4mysql-node-0.58-0.el7.centos.noarch. 100%   35KB   9.1MB/s   00:00    
[[email?protected] MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:
mha4mysql-node-0.58-0.el7.centos.noarch. 100%   35KB   9.4MB/s   00:00    
[[email?protected] MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:
mha4mysql-node-0.58-0.el7.centos.noarch. 100%   35KB  10.6MB/s   00:00   

[[email?protected] ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

在这里插入图片描述在这里插入图片描述
5.编辑mha配置文件

[[email?protected] ~]# mkdir /etc/masterha
[[email?protected] ~]# ls
MHA-7
[[email?protected] ~]# cd /etc/masterha/
[[email?protected] masterha]# ls
[[email?protected] masterha]# 
[[email?protected] masterha]# vim master.cnf

[server default]
manager_workdir=/etc/masterha
manager_log=/var/log/masterha.log
master_binlog_dir=/etc/masterha

password=Szy+123en
user=root
ping_interval=1
remote_workdir=/tmp
repl_password=Szy+123en
repl_user=repl
ssh_user=root

[server1]
hostname=172.25.16.1
port=3306

[server2]
hostname=172.25.16.2
port=3306
candidate_master=1
check_repl_delay=0
candidate_master=1
check_repl_delay=0

[server3]
hostname=172.25.16.3
port=3306
no_master=1

6.密钥互相传递

[[email?protected] masterha]# scp -r ~/.ssh server1:
id_rsa                                   100% 1679     5.8KB/s   00:00    
id_rsa.pub                               100%  394     6.6KB/s   00:00    
known_hosts                              100%  543   384.3KB/s   00:00    
[[email?protected] masterha]# scp -r ~/.ssh server2:
id_rsa                                   100% 1679     1.3MB/s   00:00    
id_rsa.pub                               100%  394   381.2KB/s   00:00    
known_hosts                              100%  543    58.7KB/s   00:00    
[[email?protected] masterha]# scp -r ~/.ssh server3:
id_rsa                                   100% 1679     1.1MB/s   00:00    
id_rsa.pub                               100%  394   349.5KB/s   00:00    
known_hosts                              100%  543    95.2KB/s   00:00 

7.检查ssh是否出错

[[email?protected] masterha]# masterha_check_ssh --conf=/etc/masterha/master.cnf

server1:

mysql> grant all on *.* to [email?protected]'%' identified by 'Szy+123en';

server2:

mysql> set global read_only=1;

server3:

mysql> set global read_only=1;

在这里插入图片描述
8.查看mysql的复制情况

[[email?protected] masterha]# masterha_check_repl --conf=/etc/masterha/master.cnf

在这里插入图片描述在这里插入图片描述

四.MHA测试

(1). 手动测试:

1.关闭server1的mysql

[[email?protected] ~]# systemctl stop mysqld

2.手动将master节点转换到server2上

[[email?protected] masterha]# masterha_master_switch --master_state=dead --conf=/etc/masterha/master.cnf --dead_master_host=172.25.16.1 --dead_master_port=3306 --new_master_host=172.25.16.2 --new_master_port=3306 
输入yes yes

在这里插入图片描述在这里插入图片描述
3.server2查看slave状态为空
在这里插入图片描述
4.server3查看slave状态(master的ip转到server2)
在这里插入图片描述5.打开server1的mysql将slave添加进群组

[[email?protected] ~]# systemctl start mysqld
[[email?protected] ~]# mysql -pSzy+123en
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 2
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> change master to master_host='172.25.16.2', master_user='repl', master_password='Szy+123en', master_auto_position=1;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.16.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 3026
               Relay_Log_File: server1-relay-bin.000002
                Relay_Log_Pos: 942
        Relay_Master_Log_File: binlog.000002
             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: 3026
              Relay_Log_Space: 1151
              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: 2
                  Master_UUID: ef766a82-b783-11e9-a2e0-52540039676f
             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: ef766a82-b783-11e9-a2e0-52540039676f:1-2
            Executed_Gtid_Set: c1d68221-b782-11e9-9293-5254004772f0:1-9,
ef766a82-b783-11e9-a2e0-52540039676f:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

在这里插入图片描述
6.手动将matser转到server1上

[[email?protected] masterha]# masterha_master_switch --master_state=alive --conf=/etc/masterha/master.cnf --new_master_host=172.25.16.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=1000

在这里插入图片描述在这里插入图片描述server1:
在这里插入图片描述
server2:
在这里插入图片描述server3:
在这里插入图片描述
(2)自动转换
1.在server4下创建一个检测进程,来创建监控master的进程并查看进程,即执行自动转换命令

[[email?protected] masterha]# nohup masterha_manager --conf=/etc/masterha/master.cnf &> /dev/null &
[1] 1639
[[email?protected] masterha]# ps a
  PID TTY      STAT   TIME COMMAND
 1108 tty1     Ss+    0:00 -bash
 1131 pts/0    Ss     0:00 -bash
 1639 pts/0    S      0:00 perl /usr/bin/masterha_manager --conf=/etc/mas
 1663 pts/0    R+     0:00 ps a

2.关掉server1的mysql

[[email?protected] ~]# systemctl stop mysqld

3.此时master服务自动调转到server2
在这里插入图片描述
在这里插入图片描述3.server4中的脚本进程挂掉了
在这里插入图片描述
(3)通过脚本控制(通过vip的漂移查看)
1.官网下载mha高可用的manager的安装包并且解压
在这里插入图片描述2.编辑master_ip_failover文件(脚本文件一)

my $vip = '172.25.16.100/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";

在这里插入图片描述
3.编辑master_ip_online_change文件(脚本文件二)

my $vip = '172.25.16.100/24';  # Virtual IP  
my $key = "1";
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";

在这里插入图片描述
4.将两个脚本文件拷贝到/usr/local/bin目录下,并添加权限

在这里插入代码片[[email?protected] scripts]# cp master_ip_* /usr/local/bin/
[[email?protected] scripts]# chmod +x /usr/local/bin/master_ip_*
[[email?protected] scripts]# ll /usr/local/bin/
total 16
-rwxr-xr-x 1 root root  3802 Aug  5 23:58 master_ip_failover
-rwxr-xr-x 1 root root 10041 Aug  5 23:58 master_ip_online_change

在这里插入图片描述
给server2添加vip172.25.16.100

在这里插入图片描述
手动转换master到server1上

[[email?protected] bin]# masterha_master_switch --conf=/etc/masterha/master.cnf --master_state=alive --new_master_host=172.25.16.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

在server1中查看ip,会查看到vip漂移到了server1上
在这里插入图片描述在server4下创建监控master的进程,当master节点宕机,server4会自动执行/usr/local/bin/的两个脚本,两个脚本会自动选择一个新的节点作为master

[[email?protected] masterha]# nohup masterha_manager --conf=/etc/masterha/master.cnf &> /dev/null &
[[email?protected] masterha]# ps a

在server1中关闭mysqld

[[email?protected] ~]# systemctl stop mysqld

在server3中查看master,发现此时master变成了server2,vip回到server2
在这里插入图片描述

猜你喜欢

转载自blog.csdn.net/weixin_44889616/article/details/97616654