mysql+proxysql+keepalived实现高可用的数据库读写分离

    为了降低一台数据库的IO，远程连接数据库的时候，可以实现读写分离进行调度。这里就出现了一个单点，所以必须要做一个高可用。当然数据库服务器也需要做主从复制。

实验结构

    说明：上图的拓扑只是整个架构中的一个小部分，其余功能的实现此图并未规划出来。此拓扑实现的目的是利用proxysql实现数据读写分离，并对proxysql高可用。两台安装了Keepalived和proxysql虚拟成一个VIP对外提供服务。这两台mysql服务器做的半同步复制，192.168.32.111是主节点负责用户的写操作，192.168.32.112从节点负责用户的读操作。

实验步骤

1、安装数据库并配置半同步复制

    主mysql：192.168.32.111

]#yum install mariadb -y                                       <===centos6为mysql
]#vim /etc/my.cnf.d/server.cnf
[server]
skip_name_resolve = ON
innodb_file_per_table = ON
max_connection = 2000

log_bin = master-log
server_id = 1
]#systemctl start mariadb
]#mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘ma‘@‘192.168.32.%‘ IDENTIFIED BY ‘centos‘;    <===授权从节点复制的帐号
MariaDB [(none)]> SHOW MASTER LOGS;                           <===查看当前二进制日志文件
+-------------------+-----------+
| Log_name          |File_size |
+-------------------+-----------+
| master-log.000001 |      490 |
+-------------------+-----------+
MariaDB [(none)]> SHOW BINLOG EVENTS IN ‘master-log.000001‘;  <===查看日志内容保存点，从节点可以根据需要选择从哪开始复制，我们这里是测试环境，数据库为空，先从最开始复制。
+-------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------------+
| Log_name          | Pos |Event_type  | Server_id | End_log_pos |Info                                                                                           |
+-------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------------+
| master-log.000001 |  4 | Format_desc |         1 |         245 | Server ver: 5.5.52-MariaDB, Binlog ver: 4                                                      |
| master-log.000001 | 245 | Query       |        1 |         415 | grant replication slave,replication client on *.* to‘ma‘@‘192.168.32.%‘ identified by ‘centos‘ |
| master-log.000001 | 415 | Query       |        1 |         490 | flush privileges                                                                               |
+-------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------------+
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master‘;  <===安装插件
MariaDB [(none)]> SHOW GLOBALVARIABLES LIKE ‘%rpl%‘;               <===安装完以后不代表开启。
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | OFF  |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32   |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘%rpl%‘;                 <===查看复制各种状态
MariaDB [(none)]> SET  @@GLOBAL.rpl_semi_sync_master_enabled=ON;   <===开启插件
MariaDB [(none)]> FLUSH PRIVILEGES;

    从mysql配置：192.168.32.112

]#yum install mariadb -y                                           <===centos6为mysql
]#vim /etc/my.cnf.d/server.cnf
[server]
skip_name_resolve = ON
innodb_file_per_table = ON
max_connection = 2000

relay_log = relay-log
server_id = 2
]#systemctl start mariadb
]#mysql
> CHANGE MASTER TO MASTER_HOST=‘192.168.32.111‘,MASTER_USER=‘ma‘,MASTER_PASSWORD=‘centos‘,MASTER_LOG_FILE=‘master-log.000001‘,MASTER_LOG_POS=245;
>START SLAVE;
> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.32.111         <===主节点
                 Master_User: ma                     <===用户
                 Master_Port: 3306                   <===端口
               Connect_Retry: 60            
             Master_Log_File: master-log.000001      <===从哪个文件复制
         Read_Master_Log_Pos: 490                    <===哪个位置
              Relay_Log_File: relay-log.000002       <===复制到本地的中继日志
               Relay_Log_Pos: 775                    <===中继日志的位置
       Relay_Master_Log_File: master-log.000001
            Slave_IO_Running: Yes                    <===复制要靠IO线程
           Slave_SQL_Running: Yes                    <===重放要靠SQL线程
           …
> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave‘;
> SET  @@GLOBAL.rpl_semi_sync_slave_enabled=ON;
> SHOW GLOBAL VARIABLES LIKE ‘%rpl%‘;                <===查看变量里已经开启了
+---------------------------------+-------+
| Variable_name                  | Value |
+---------------------------------+-------+
| rpl_recovery_rank              | 0     |
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
> SHOW GLOBAL STATUS LIKE ‘%rpl%‘;                   <===但是，这个时候看状态依然是关闭的！
+----------------------------+-------------+
| Variable_name             | Value       |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | OFF         |
| Rpl_status                | AUTH_MASTER |
+----------------------------+-------------+
> STOP SLAVE IO_THREAD;
> START SLAVEIO_THREAD;                               <===需要重启下复制线程

        总结：到这里我们已经实现了数据库的半同步复制，如果在主节点上创建数据从节点能同步到，证明实验第一步成功了。为了后面读写分离可以看到效果，可以考虑在从节点上加个过滤器，某一个数据库中数据不

同步到从节点上。过滤机制有两种方法：1、主节点二进制日志中进行过滤；2、在从节点的中继日志中进行过滤。这里我们使用第二种方法。

SET @@GLOBAL.replicate_ignore_DB=hidb;               <===在从节点上设置过滤hidb的库

2、配置proxysql：192.168.32.104

]#yum install -y \.proxysql-1.4.2-1-centos7.x86_64.rpm    <===我是下载好上传到本地的，安装包见附件。不排除有依赖关系，所以用yum安装。
]#rpm -ql proxysql                                       <===包组很少
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
]# vim /etc/proxysql.cnf
admin_variables=                             <===管理接口的用户和账户：mysql -S /tmp/proxysql_admin.sock -uadmin -padmin.安全起见，建议更改
{
        admin_credentials="admin:admin"
        mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
        interfaces="0.0.0.0:3306;/tmp/mysql.sock"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
        monitor_username="monitor"
        monitor_password="monitor"
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
}
mysql_servers =                         <===定义两个mysql服务器地址、端口、属于哪个组（下面有定义）、是否压缩、权重    
(
        {
                address = "192.168.32.111"
                port = 3306
                hostgroup = 0 
                status = "ONLINE"
                weight = 1 
                compression = 0 
        },
        {
                address = "192.168.32.112"
                port = 3306
                hostgroup = 1 
                status = "ONLINE"
                weight = 1 
                compression = 0 
        }
)
mysql_users:                            <===连接数据库的用户和账户，和之前授权复制主从的一致
(
        {
                username = "dbadmin" 
                password = "centos" 
                default_hostgroup = 0 
                active = 1            
        }
)
mysql_query_rules:                       <===数据查询规则定义
(
        {
                rule_id=1
                active=1
                match_pattern="^SELECT .* FOR UPDATE$"
                destination_hostgroup=0
                apply=1
        },
        {
                rule_id=2
                active=1
                match_pattern="^SELECT"
                destination_hostgroup=1
                apply=1
        }
)
mysql_replication_hostgroups=
(
        {
                writer_hostgroup=0        <===0组为写
                reader_hostgroup=1        <===1组为读
                comment="test repl 1"     <===描述信息
       }
)
]#service proxysql start 
]# mysql -udbadmin -pcentos -h172.18.32.104    <===通过本地访问数据库
>INSERT INTO hidb.tal1 VALUES (4,‘li‘);        <===插入一个数据,写入主节点
>SELECT * FROM hidb.tal1;                      <===读数据是从从点读，而从节点做了hidb的过滤，因此是读不到插入的这一行数据。实现了读写分离。

        总结：到这里我们是已经实现了数据库的读写分离。下面我们还需要对proxysql做高可用。

3、在另外一台主机192.168.32.105上配置proxysql，具体配置和上面相同。

4、电脑配置有限，这里就在proxysql服务器上配置Keepalived。

    keepalived：192.168.32.104

]# yum install -y keepalived
]# vim /etc/keepalived/keepalived.conf 
! Configuration File for keepalived
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id node1                            <===添加
   vrrp_mcast_group4 224.100.100.100          <===组播地址
}
vrrp_script chk_nginx {                       <===检测服务的脚本
        script "killall -0 proxysql &> /dev/null && exit 0 || exit 1"
        interval 1
        weight -30
        fall 3 
        rise 3
}
vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 22
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.32.99/24      <===虚拟一个VIP对外提供服务
    }
    track_script {            <===调用上面的脚本
        chk_nginx
         }
}
]#systemctl start keepalived

    keepalived：192.168.32.104 

]# yum install -y keepalived
]# vim /etc/keepalived/keepalived.conf 
! Configuration File for keepalived
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id node2
   vrrp_mcast_group4 224.100.100.100
}
vrrp_script chk_nginx {
        script "killall -0 proxysql &> /dev/null && exit 0 || exit 1"
        interval 1
        weight -10
        fall 3 
        rise 3
}
vrrp_instance VI_1 {
    state backup
    interface ens33
    virtual_router_id 22
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.32.99/24
    }
    track_script {
        chk_nginx
         }
}
]#systemctl start keepalived

5、测试

]#tcpdump -i ens33 -nn host 224.100.100.100        <===对广播地址进行抓包

   在客户端使用VIP使用，实现效果：当主proxysql服务down掉以后，从proxysql服务器拿到VIP开始提供服务。实现了高可用

]# mysql -udbadmin -pcentos -h192.168.32.99

6、补充

    我们可以在proxysql服务器上使用管理接口登录上去看看状态

]# mysql -S /tmp/proxysql_admin.sock -uadmin -padmin
> SHOW DATABASES;
+-----+---------+-------------------------------+
| seq | name    | file                          |
+-----+---------+-------------------------------+
| 0   | main    |                               |
| 2   | disk    | /var/lib/proxysql/proxysql.db |
| 3   | stats   |                               |
| 4   | monitor |                               |
+-----+---------+-------------------------------+
> SELECT * FROM mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | 192.168.32.111 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.32.112 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

    总结：还有很多表就不一一展示了。这里通过proxysql+keepalived实现了数据库简单的读写分离，简单的高可用。实际应用中比这复杂的多，并不是一个人能完成的。

本文出自 “沉默是金” 博客，请务必保留此出处http://maguofu.blog.51cto.com/12431016/1981824

mysql+proxysql+keepalived实现高可用的数据库读写分离

标签：mysql读写分离 主从复制 proxysql