Mysql高可用方案Mysql主主复制+Keepalived(探索版)

Mysql IT敢客 3个月前 (07-11) 915次浏览 已收录 3个评论 扫描二维码

       这里我为什么要写一个探索版的方案,其实是因为现在而言,这个方案是不完善的。还是先来说明一下 Mysql 高可用的需求吧。

       于生产环境而言,我们希望 mysql 数据库是不能有任何一秒钟的停止的,包括服务停止,数据丢失等等。综合安全考虑,我们还需要备份等等。对于一个正在意义上的高可用高性能的 mysql 方案,应该还包括读写分离,所以大致画了一下整个结构!

Mysql 高可用方案 Mysql 主主复制+Keepalived(探索版)

       上图中虚线框内表示如果应用层不区分读写,而交由数据库层处理,那么应用层只要填写一个 IP 即可,如果应用层配置了单独的读写分离 IP,那么就需要配置两个 IP 地址,分别配置读 IP 和写 IP!然后为了实现高可用,也就是一台挂了,另一台接着工作,所以写需要做主主复制,而两台读分别需要从写 1 和写 2 做主从复制!不过这里还是有个坑的地方需要注意,写 1 跪了后,读 1 还在工作,所以要判断写 1 跪了后,让虚 IP 飘到读 2 上才行。其他 4 个从库可以作为灾备,然后还可以做数据库中心运维查询作用!

       上述架构比较复杂,这里就先做一个简单的实验,先只有高可用功能,不做读写分离,也就需要两台服务器即可!

软件环境:

MySQL1:192.168.181.128   (安装 mysql+keepalived)

MySQL2:192.168.181.129  (安装 mysql+keepalived)

MySQL_VIP:192.168.181.130

两台服务器都安装 mysql,这里就不详细说明了,可以参考之前的文章Mysql5.7 安装的一些小记录Mysql 的主主复制

安装完成之后,要配置一下主主复制:

MySQL1 和 Mysql2 同时操作:

grant replication slave,replication client on *.* to repli@'192.168.181.%' identified by "!QAZ2wsx";
flush privileges;
show master status;
分别记录下如下信息
MySQL1
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2754 | | | |
+------------------+----------+--------------+------------------+-------------------+
MySQL2
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 3529 | | | |
+------------------+----------+--------------+------------------+-------------------+

MySQL1 操作:

change master to master_host='192.168.181.129',master_user='repli',master_password='!QAZ2wsx',master_log_file='mysql-bin.000001',master_log_pos=3529;
start slave;
show slave status\G;   #查看是否有两个 YES

MySQL2 操作:

change master to master_host='192.168.181.128',master_user='repli',master_password='!QAZ2wsx',master_log_file='mysql-bin.000001',master_log_pos=2754;
start slave;
show slave status\G;   #查看是否有两个 YES

以上就完成了主主配置!

接下来做几个实验验证一下,用工具分别连上 128 和 129 的数据库,然后再 128 建一个库,一个表,插入一条数据库,查询 129 上是否有数据,同样反过来操作一遍,查看 128 上是否有数据,如果都有,则表示主主复制配置成功!

这里我要做两个比较重要的实验,来演示一下 MySQL 主主复制的注意点!在实验之前,我们都设置一个参数,bin-log 过期时间为 7 天,默认是不过期!进入 mysql 后输入下面的语句.

mysql> show variables like '%expire%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 0     |
+--------------------------------+-------+
2 rows in set (0.05 sec)

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

mysql> show variables like '%expire%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 7     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

1、当 Mysql1 服务停掉后,MySQL 持续插数据,MySQL1 恢复后会不会同步数据一致。

MySQL1 操作:

[root@test ~]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
[root@test ~]# ps -ef|grep mysql
root 27450 1466 0 13:40 pts/0 00:00:00 grep --color=auto mysql
[root@test ~]# 

然后再 MySQL2 上插入几条数据,并多切换几下日志。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3548 |
| mysql-bin.000002 |       150 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('2', '2', '3');
flush logs;
Query OK, 1 row affected (0.02 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('3', '3', '2');
Query OK, 1 row affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('4', '2', '6');
Query OK, 1 row affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3548 |
| mysql-bin.000002 |       507 |
| mysql-bin.000003 |       507 |
| mysql-bin.000004 |       507 |
| mysql-bin.000005 |       150 |
+------------------+-----------+
5 rows in set (0.00 sec)

MySQL1 操作:

此时,mysql1 上的服务是停止的,这是我们启动 mysql1 上的 mysql 服务,并开启同步!

[root@test ~]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
[root@test ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22-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> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from huanqiu.hehe;
+------+------+------+
| id   | num  | name |
+------+------+------+
|    1 |    2 | 3    |
|    2 |    2 | 3    |
|    3 |    3 | 2    |
|    4 |    2 | 6    |
+------+------+------+
4 rows in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3091 |
| mysql-bin.000002 |       169 |
| mysql-bin.000003 |       150 |
+------------------+-----------+
3 rows in set (0.00 sec)

我们发现数据还是同步过来了,bin-log 上并未保持一致。接下来我们在 MySQL1 做另一个操作;

mysql> INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('6', '122', '3');
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('7', '312', '2');
Query OK, 1 row affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('8', '2', '6123');
Query OK, 1 row affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3091 |
| mysql-bin.000002 |       169 |
| mysql-bin.000003 |       509 |
| mysql-bin.000004 |       509 |
| mysql-bin.000005 |       510 |
| mysql-bin.000006 |       150 |
+------------------+-----------+
6 rows in set (0.00 sec) 

然后在 mysql2 上查询是否有新的数据进来。发现也是有新的数据进来,并且 bin-log 也不一样!

mysql> select * from huanqiu.hehe;
+------+------+------+
| id   | num  | name |
+------+------+------+
|    1 |    2 | 3    |
|    2 |    2 | 3    |
|    3 |    3 | 2    |
|    4 |    2 | 6    |
|    6 |  122 | 3    |
|    7 |  312 | 2    |
|    8 |    2 | 6123 |
+------+------+------+
7 rows in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3548 |
| mysql-bin.000002 |       507 |
| mysql-bin.000003 |       507 |
| mysql-bin.000004 |       507 |
| mysql-bin.000005 |       150 |
+------------------+-----------+
5 rows in set (0.00 sec) 

以上说明主主复制的断开操作是可以行的!接下来我们要做第二个实验,来说明这个日志过期时间的问题带来的影响。

2、模仿长时间断连 MySQL 主主服务

MySQL2 操作:停掉 mysql 服务

[root@test1 ~]# service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service
[root@test1 ~]# ps -ef|grep mysql
root      10206 100561  0 06:24 pts/0    00:00:00 grep --color=auto mysql
[root@test1 ~]#  

MySQL1 操作:先模拟插入几条数据,然后切日志,最后只保留一份 bin-log。来模拟操作日志过期天数的影响。

mysql> INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('10', '122', '32323');
flush logs;
INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('17', '312', '22323');
Query OK, 1 row affected (0.05 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('17', '312', '22323');
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('18', '2', '612323');
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `huanqiu`.`hehe` (`id`, `num`, `name`) VALUES ('18', '2', '612323');
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3091 |
| mysql-bin.000002 |       169 |
| mysql-bin.000003 |       509 |
| mysql-bin.000004 |       509 |
| mysql-bin.000005 |       510 |
| mysql-bin.000006 |       514 |
| mysql-bin.000007 |       514 |
| mysql-bin.000008 |       513 |
| mysql-bin.000009 |       150 |
+------------------+-----------+
9 rows in set (0.00 sec)

mysql> purge binary logs to 'mysql-bin.000009';
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000009 |       150 |
+------------------+-----------+
1 row in set (0.00 sec) 

MySQL2 操作,启动 mysql 服务,并开启同步。检查是否有数据同步过来。

[root@test1 ~]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
[root@test1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22-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> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from huanqiu.hehe;
+------+------+------+
| id   | num  | name |
+------+------+------+
|    1 |    2 | 3    |
|    2 |    2 | 3    |
|    3 |    3 | 2    |
|    4 |    2 | 6    |
|    6 |  122 | 3    |
|    7 |  312 | 2    |
|    8 |    2 | 6123 |
+------+------+------+
7 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.181.128
                  Master_User: repli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 150
               Relay_Log_File: test1-relay-bin.000018
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: No
            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: 150
              Relay_Log_Space: 150
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 7196a8e6-7f73-11e8-8772-000c29ebd0b9
             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: 180710 06:27:58
     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)

ERROR: 
No query specified

我们发现,数据并没有同步过来,查看同步状态发现有报错,这是因为要同步过来的 bin-log 文件在主库上被删除了导致的。所以这个隐患就是表示如果 MySQL 主主服务断连超过设置的日志过期天数 expire_logs_days 的值就会出现问题,这是一个隐患问题。

这里我们接着安装 keepalived,两台同时操作;

yum install -y autoconf* automake* cluster-glue* cluster-glue-libs* cluster-glue-libs-devel* glib2* glib2-devel* glib2-static* keyutils-libs* keyutils-libs-devel* krb5-devel* krb5-libs* libcom_err* libcom_err-devel* libselinux* libselinux-devel* libselinux-static* libselinux-utils* libsepol* libsepol-devel* libsepol-static* libtool* libtool-ltdl* libtool-ltdl-devel* openssl* openssl-devel* openssl-static* pkgconfig* zlib* zlib-devel zlib-static* gcc gcc-c++ automake pcre pcre-devel zlib zlib-devel open openssl-devel
cd /usr/local/src/
wget http://www.keepalived.org/software/keepalived-1.3.9.tar.gz
tar -zvxf keepalived-1.3.9.tar.gz
cd keepalived-1.3.9
./configure –-prefix=/usr/local/keepalived –-sysconf=/etc
make && make install

cp /usr/local/src/keepalived-1.3.9/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived/
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
echo "/etc/init.d/keepalived start" >> /etc/rc.local
 

MySQL1 机器上的 keepalived.conf 配置。(下面配置中没有使用 lvs 的负载均衡功能,所以不需要配置虚拟服务器 virtual server)

[root@test ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@test ~]# echo > /etc/keepalived/keepalived.conf
[root@test ~]# vim /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
notification_email {
ops@wangshibo.cn
tech@wangshibo.cn
}

notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}

vrrp_script chk_mysql_port { #检测 mysql 服务是否在运行。有很多方式,比如进程,用脚本检测等等
script "/opt/chk_mysql.sh" #这里通过脚本监测
interval 2 #脚本执行间隔,每 2s 检测一次
weight -5 #脚本结果导致的优先级变更,检测失败(脚本返回非 0)则优先级 -5
fall 2 #检测连续 2 次失败才算确定是真失败。会用 weight 减少优先级(1-255 之间)
rise 1 #检测 1 次成功就算成功。但不修改优先级
}

vrrp_instance VI_1 {
state MASTER 
interface eth0 #指定虚拟 ip 的网卡接口
mcast_src_ip 192.168.181.128
virtual_router_id 51 #路由器标识,MASTER 和 BACKUP 必须是一致的
priority 101 #定义优先级,数字越大,优先级越高,在同一个 vrrp_instance 下,MASTER 的优先级必须大于 BACKUP 的优先级。这样 MASTER 故障恢复后,就可以将 VIP 资源再次抢回来 
advert_int 1 
authentication { 
auth_type PASS 
auth_pass 1111 
}
virtual_ipaddress { 
192.168.181.130
}

track_script { 
chk_mysql_port 
}
}

编写切换脚本。KeepAlived 做心跳检测,如果 Master 的 MySQL 服务挂了(3306 端口挂了),那么它就会选择自杀。Slave 的 KeepAlived 通过心跳检测发现这个情况,就会将 VIP 的请求接管

[root@test ~]# vim /opt/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
/etc/init.d/keepalived stop
fi

[root@test ~]# chmod 755 /opt/chk_mysql.sh

启动 keepalived 服务
[root@test ~]# /etc/init.d/keepalived start
正在启动 keepalived: [确定]
[root@test ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> 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: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:eb:d0:b9 brd ff:ff:ff:ff:ff:ff
inet 192.168.181.128/24 brd 192.168.181.255 scope global eno16777736
valid_lft forever preferred_lft forever
inet <span style="color: #ff0000;">192.168.181.130</span>/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:feeb:d0b9/64 scope link 
valid_lft forever preferred_lft forever

4)MySQL2 机器上的 keepalived 配置。MySQL2 机器上的 keepalived.conf 文件只修改 priority 为 90、nopreempt 不设置、real_server 设置本地 IP。

[root@test1 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@test1 ~]# >/etc/keepalived/keepalived.conf
[root@test1 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
notification_email {
ops@wangshibo.cn
tech@wangshibo.cn
}

notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}

vrrp_script chk_mysql_port {
script "/opt/chk_mysql.sh"
interval 2 
weight -5 
fall 2 
rise 1 
}

vrrp_instance VI_1 {
state BACKUP
interface eth0 
mcast_src_ip 192.168.181.129
virtual_router_id 51 
priority 99 
advert_int 1 
authentication { 
auth_type PASS 
auth_pass 1111 
}
virtual_ipaddress { 
192.168.181.130
}

track_script { 
chk_mysql_port 
}
}

[root@master2 ~]# cat /opt/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
/etc/init.d/keepalived stop
fi

[root@master2 ~]# chmod 755 /opt/chk_mysql.sh

[root@master2 ~]# /etc/init.d/keepalived start
正在启动 keepalived: [确定]
[root@test1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> 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: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:ab:29:22 brd ff:ff:ff:ff:ff:ff
inet 192.168.181.129/24 brd 192.168.181.255 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:feab:2922/64 scope link 
valid_lft forever preferred_lft forever

上面 keepalived 也安装完成了。做个试验验证一下:

停掉 mysql1 服务,然后观察 mysql1 服务器上的虚 ip192.168.181.130 是否还在,是否已经到 MySQL2 的服务器上了,MySQL1 上的 keepalived 服务是否还在。

[root@test ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> 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: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:eb:d0:b9 brd ff:ff:ff:ff:ff:ff
    inet 192.168.181.128/24 brd 192.168.181.255 scope global eno16777736
       valid_lft forever preferred_lft forever
    inet 192.168.181.130/32 scope global eno16777736
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feeb:d0b9/64 scope link 
       valid_lft forever preferred_lft forever
[root@test ~]# service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service
[root@test ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> 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: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:eb:d0:b9 brd ff:ff:ff:ff:ff:ff
    inet 192.168.181.128/24 brd 192.168.181.255 scope global eno16777736
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feeb:d0b9/64 scope link 
       valid_lft forever preferred_lft forever
[root@test ~]# ps -ef|grep keepalived
root      29289   1466  0 15:12 pts/0    00:00:00 grep --color=auto keepalived 

MySQL2 上查看是否有虚 ip192.168.181.130,发现虚 ip 已经在 MySQL2 上面了。

[root@test1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> 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: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:ab:29:22 brd ff:ff:ff:ff:ff:ff
    inet 192.168.181.129/24 brd 192.168.181.255 scope global eno16777736
       valid_lft forever preferred_lft forever
    inet 192.168.181.130/32 scope global eno16777736
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feab:2922/64 scope link 
       valid_lft forever preferred_lft forever
[root@test1 ~]#  

另外 Mysql 服务没有启动,keepalived 也会起来一会就自动杀死 keepalived 进程。

然后我们可以用工具连接 192.168.181.131 这个 ip,停止 mysql1 或者 mysql2 发现没有断连,数据一直保持最新,这是因为 mysql 主主复制数据一致性!

注意点:MySQL 服务重新恢复后,要登录进去重新同步一下,start slave;然后要启动 keepalived 服务,/etc/init.d/keepalived start;

好了,大致的过程算是完成了!后面再来将详细一点的优化方案!


IT 敢客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:Mysql 高可用方案 Mysql 主主复制+Keepalived(探索版)
喜欢 (7)
[313176056@qq.com]
分享 (0)
IT敢客
关于作者:
“我所做的一切都是为了方便我的生活~~~“
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(3)个小伙伴在吐槽
  1. 你好,我也是做开发的,最近也在做个网站,可否交换个友链http://blog.hack-jiuling.cn/
    90之家2018-07-27 12:46 回复 Windows 7 | Chrome 67.0.3396.99
    • IT敢客
      可以的,你加上我的友链吧!你的我已经加上!
      友链名称:IT 敢客
      友链网站:http://www.itgank.com/
      友链图标:https://www.itgank.com/favicon.ico
      一句话介绍:IT 敢客,运维,技术分享,共同学习!
      IT敢客2018-07-27 12:52 回复 Windows 10 | Chrome 61.0.3163.79
    • IT敢客
      鉴于你还没有添加我的友链。我就删除你的友链了!请知悉!
      IT敢客2018-07-27 13:19 回复 Windows 10 | Chrome 61.0.3163.79