利用keepalived搭建mysql的HA高可用环境

本来想写一点关于mysql数据库优化的文章，但毕竟接触mysql的时间不是特别长，不敢说对mysql很熟悉，更还不敢妄言数据库调优，还是谨慎一点好了，不然被大家拍砖就没脸混了
这里要说的是keepalived+mysql的双主环境搭建
按照官方文档，mysql的Cluster其实是有一个专门的组件叫：NDB，有兴趣的可以在官网找一下
但好像NDB用的不是那么多（个人觉得哈），目前比较火的是keepalived+mysql
原理也很简单，首先：通过mysql的主从（主主）复制实现HA两边数据库的数据同步
然后：通过keepalived来监控进程的状态、监控浮动ip并配置交易转发策略等，就这样，这么简单
keepalived原来是配合LVS的一个HA实现方案，现在已经被广泛应用于其它用途，比如nginx、mysql等轻量级的应用
用mysql的主主复制有一个好处哈，搭建HA的时候不需要共享磁盘，是share nothing的数据库，但通过mysql的主主同步机制，实现数据及时更新，从这一点来说，比oracle的rac简单的多了去了
本文是从上一章（mysql主从复制）的基础上做的，即本文从主从复制的基础上，将其改为主主复制，然后再配合keepalived实现HA
如果没看过上一章，建议先看上一章:http://blog.itpub.net/694276/viewspace-2120943/
好了，不絮叨了，let's go!

上一章中主从复制的ip信息如下
主：192.168.1.130
备：192.168.1.131

1、192.168.1.131上修改my.cnf，增加log-bin等参数并重启
其中auto-increment-increment、auto-increment-offset是为了确保auto_increment取值两边不冲突

[root@ct-test1 ~]# echo log-bin=mysql-bin >>/etc/my.cnf
[root@ct-test1 ~]# echo replicate-same-server-id = 0 >>/etc/my.cnf
[root@ct-test1 ~]# echo auto-increment-increment = 2 >>/etc/my.cnf
[root@ct-test1 ~]# echo auto-increment-offset = 1 >>/etc/my.cnf
[root@ct-test1 ~]# systemctl restart mysqld

2、192.168.1.130上修改my.cnf，增加复制相关参数并重启

[root@ct-test1 ~]# echo replicate-same-server-id = 0 >>/etc/my.cnf
[root@ct-test1 ~]# echo auto-increment-increment = 2 >>/etc/my.cnf
[root@ct-test1 ~]# echo auto-increment-offset = 2 >>/etc/my.cnf
[root@ct-test1 ~]# systemctl restart mysqld

3、在192.168.1.131上检查同步状态

[root@ct-test1 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: ct-test1-relay-bin.000013
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000005
             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: 154
              Relay_Log_Space: 743
              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: 101
                  Master_UUID: 3f93c491-39ee-11e6-8726-000c29916128
             Master_Info_File: /mysql/data/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: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql>

4、在192.168.1.130上检查同步状态

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

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

mysql> 

5、在192.168.1.130上修改MASTER为192.168.1.131，并检查同步状态
其实将主从改为主主，很简单了，只要在原来的主上，将master的指向改为备机即可，具体见下
其中：mysql-bin.000002、154是来自于192.168.1.131中show master status中的值
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.131', MASTER_USER='repl', MASTER_PASSWORD='Zxt1234!', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

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: 192.168.1.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: ct-test1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.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: 154
              Relay_Log_Space: 530
              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: 2db46c13-39ee-11e6-85f6-000c29e9a9c4
             Master_Info_File: /mysql/data/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: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql>

6、主主同步测试
I：在192.168.1.130执行DDL、DML
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxt                |
+--------------------+
5 rows in set (0.26 sec)

mysql> create database zxttest;
Query OK, 1 row affected (0.04 sec)

mysql> use zxttest;
Database changed
mysql> create table test1(id1 varchar(10));
Query OK, 0 rows affected (0.18 sec)

mysql> insert into test1 values('123321');
Query OK, 1 row affected (0.02 sec)

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

II：在192.168.1.131上检查，并执行新的DML操作
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxt                |
| zxttest            |
+--------------------+
6 rows in set (0.00 sec)

mysql> use zxttest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables
    -> ;
+-------------------+
| Tables_in_zxttest |
+-------------------+
| test1             |
+-------------------+
1 row in set (0.00 sec)


mysql> select * from test1\G
*************************** 1. row ***************************
id1: 123321
1 row in set (0.00 sec)

mysql> create table test2(id1 varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test2 values('321123');
Query OK, 1 row affected (0.16 sec)

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

mysql>

III：回到192.168.1.130上检查是否有新数据回来
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxt                |
| zxttest            |
+--------------------+
6 rows in set (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_zxttest |
+-------------------+
| test1             |
| test2             |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from test2\G
*************************** 1. row ***************************
id1: 321123
1 row in set (0.00 sec)

mysql> 

7、keepalived的安装
下载地址：http://www.keepalived.org/
我这边下载的是keepalived-1.2.19.tar.gz，由于是源码文件，需要编译，这里需要先安装openssl等
[root@ct-test1 ~]# yum install -y openssl* gcc*
[root@ct-test1 ~]# yum install -y libnl-devel popt-devel
[root@ct-test1 ~]# cd /home/soft
[root@ct-test1 soft]# ls
keepalived-1.2.19.tar.gz                        mysql-community-embedded-5.7.12-1.el7.x86_64.rpm         mysql-community-minimal-debuginfo-5.7.12-1.el7.x86_64.rpm
mysql-5.7.12-1.el7.x86_64.rpm-bundle.tar        mysql-community-embedded-compat-5.7.12-1.el7.x86_64.rpm  mysql-community-server-5.7.12-1.el7.x86_64.rpm
mysql-community-client-5.7.12-1.el7.x86_64.rpm  mysql-community-embedded-devel-5.7.12-1.el7.x86_64.rpm   mysql-community-server-minimal-5.7.12-1.el7.x86_64.rpm
mysql-community-common-5.7.12-1.el7.x86_64.rpm  mysql-community-libs-5.7.12-1.el7.x86_64.rpm             mysql-community-test-5.7.12-1.el7.x86_64.rpm
mysql-community-devel-5.7.12-1.el7.x86_64.rpm   mysql-community-libs-compat-5.7.12-1.el7.x86_64.rpm
[root@ct-test1 soft]# tar -xvf *.gz
[root@ct-test1 soft]# cd keepalived-1.2.19/
[root@ct-test1 keepalived-1.2.19]# mkdir /usr/local/keepalived
[root@ct-test1 keepalived-1.2.19]# ./configure --prefix=/usr/local/keepalived
[root@ct-test1 keepalived-1.2.19]# make;make install

8、keepalived默认检查/etc/keepalived/keepalived.conf 这个配置文件
拷贝启动命令到用户目录下：
[root@ct-test1 keepalived-1.2.19]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
[root@ct-test1 keepalived-1.2.19]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@ct-test1 keepalived-1.2.19]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

拷贝配置文件到默认加载目录下
[root@ct-test1 keepalived-1.2.19]# mkdir /etc/keepalived
[root@ct-test1 keepalived-1.2.19]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

9、keepalive开机自动运行
echo /usr/local/keepalived/sbin/keepalived -D >>/etc/rc.local

10、启动keepalive，并检查进程状态--正常启动应该有三个进程
[root@ct-test1 keepalived-1.2.19]# service keepalived restart
Reloading systemd:                                         [  OK  ]
Restarting keepalived (via systemctl):                     [  OK  ]
[root@ct-test1 keepalived-1.2.19]# ps aux |grep keepalived
root       8189  0.0  0.0  49928  1016 ?        Ss   17:39   0:00 /usr/local/keepalived/sbin/keepalived -D
root       8190  0.0  0.1  50052  2112 ?        S    17:39   0:00 /usr/local/keepalived/sbin/keepalived -D
root       8191  0.0  0.0  49928  1244 ?        S    17:39   0:00 /usr/local/keepalived/sbin/keepalived -D
root       8262  0.0  0.0 112644   956 pts/0    R+   17:40   0:00 grep --color=auto keepalived

11、打开keepalived 的详细日志
A、vi /etc/sysconfig/keepalived 修改KEEPALIVED_OPTIONS如下
KEEPALIVED_OPTIONS="-D -d -S 0"

B、为keepalived分配独立的日志
[root@ct-test1 keepalived-1.2.19]# echo local0.*/var/log/keepalived.log >>/etc/rsyslog.conf
[root@ct-test1 keepalived-1.2.19]# service rsyslog restart
Redirecting to /bin/systemctl restart  rsyslog.service
[root@ct-test1 keepalived-1.2.19]# 

12、编辑keepalive的配置文件keepalived.conf 
我的配置文件如下：
A：192.168.1.130上
[root@ct-test1 ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   notification_email {
     xin-tao.zhao@hpe.com
   }
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    interface eno16777736
    virtual_router_id 51  
    priority 100  --备机为80
    advert_int 1
    nopreempt --无抢占设置
    virtual_ipaddress {
        192.168.1.132
    }
}

virtual_server 192.168.1.132 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.1.130 3306 {  --192.168.1.130为本地ip
        weight 3
notify_down /usr/bin/killKeepalived_My.sh  --keepalived检测到服务down时的操作
TCP_CHECK {
connect_timeout 10 
nb_get_retry 3
delay_before_retry 3
connect_port 3306
    }
}

A：192.168.1.131上
[root@ct-test1 ~]# cat /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
     xin-tao.zhao@hpe.com
   }
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    interface eno16777736
    virtual_router_id 51
    priority 80
    advert_int 1
    virtual_ipaddress {
        192.168.1.132
    }
}

virtual_server 192.168.1.132 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.1.131 3306 {
        weight 3
notify_down /usr/bin/killKeepalived_My.sh
TCP_CHECK {
connect_timeout 10 
nb_get_retry 3
delay_before_retry 3
connect_port 3306
    }
}

13、创建killKeepalived_My.sh文件
当服务down掉的时候会执行此shell
[root@ct-test1 ~]# touch /usr/bin/killKeepalived_My.sh
[root@ct-test1 ~]# echo #!/bin/sh >>/usr/bin/killKeepalived_My.sh
[root@ct-test1 ~]# echo pkill keepalived >>/usr/bin/killKeepalived_My.sh
[root@ct-test1 ~]# chmod +x /usr/bin/killKeepalived_My.sh

14、重启keepalived
[root@ct-test1 ~]# service keepalived restart
Reloading systemd:                                         [  OK  ]
Restarting keepalived (via systemctl):                     [  OK  ]

15、搭建成功，下面为测试

I，查看keepalived的状态
[root@ct-test1 keepalived]# ip a|grep 192.168.1
    inet 192.168.1.130/24 brd 192.168.1.255 scope global eno16777736
    inet 192.168.1.132/32 scope global eno16777736
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
[root@ct-test1 keepalived]# service keepalived status
● keepalived.service - SYSV: Start and stop Keepalived
   Loaded: loaded (/etc/rc.d/init.d/keepalived)
   Active: active (running) since Wed 2016-06-29 18:40:37 CST; 46s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 9224 ExecStart=/etc/rc.d/init.d/keepalived start (code=exited, status=0/SUCCESS)
 Main PID: 9227 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─9227 keepalived -D -d -S 0
           ├─9229 keepalived -D -d -S 0
           └─9230 keepalived -D -d -S 0

II，测试keepalived是否已经启用端口3306，在两台主机上用curl keepalived的浮动地址和端口来测试
[root@ct-test1 keepalived]# curl 192.168.1.132:3306
5.7.12-log乱码 packets out of order[root@ct-test1 keepalived]# 
[root@ct-test1 keepalived]# 

III，两台主机上测试是否启用了虚地址192.168.1.132
192.168.1.130上如下：
[root@ct-test1 keepalived]# ip a|grep 192.168.1
    inet 192.168.1.130/24 brd 192.168.1.255 scope global eno16777736
    inet 192.168.1.132/32 scope global eno16777736
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
[root@ct-test1 keepalived]# 
192.168.1.131上如下：
[root@ct-test1 ~]# ip a|grep 192.168.1
    inet 192.168.1.131/24 brd 192.168.1.255 scope global eno16777736
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0

IV，用192.168.1.132登录mysql测试
[root@ct-test1 keepalived]# mysql -u root -pZxt1234! -h 192.168.1.132
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 972
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxt                |
| zxttest            |
+--------------------+
6 rows in set (0.00 sec)

mysql> quit
Bye

V, 在192.168.1.130上停止mysql，查看本地keepalived的状态、ip、是否在192.168.1.131上启动，是否可以访问数据库
A:192.168.1.130上
[root@ct-test1 keepalived]# systemctl stop mysqld
[root@ct-test1 keepalived]# ip a|grep 192.168.1
    inet 192.168.1.130/24 brd 192.168.1.255 scope global eno16777736
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
[root@ct-test1 keepalived]# service keepalived status
● keepalived.service - SYSV: Start and stop Keepalived
   Loaded: loaded (/etc/rc.d/init.d/keepalived)
   Active: inactive (dead)
     Docs: man:systemd-sysv-generator(8)

B:192.168.1.131上
[root@ct-test1 ~]# ip a|grep 192.168.1
    inet 192.168.1.131/24 brd 192.168.1.255 scope global eno16777736
    inet 192.168.1.132/32 scope global eno16777736
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
[root@ct-test1 ~]# service keepalived status
● keepalived.service - SYSV: Start and stop Keepalived
   Loaded: loaded (/etc/rc.d/init.d/keepalived)
   Active: active (running) since Wed 2016-06-29 18:44:47 CST; 2min 9s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 9487 ExecStart=/etc/rc.d/init.d/keepalived start (code=exited, status=0/SUCCESS)
 Main PID: 9490 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─9490 keepalived -D -d -S 0
           ├─9492 keepalived -D -d -S 0
           └─9493 keepalived -D -d -S 0

C:192.168.1.130上检查mysql是否可用
[root@ct-test1 keepalived]# mysql -u root -pZxt1234! -h 192.168.1.132
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 80
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxt                |
| zxttest            |
+--------------------+
6 rows in set (0.07 sec)

mysql> 

ok，搞定！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/694276/viewspace-2121219/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/694276/viewspace-2121219/