MySQL数据库主从复制的读写分离--ProxySQL

本实验使用一主一从一代理，三台设备，具体步骤如下：

1、完成主从复制的配置，请参考此文http://panpangao.blog.51cto.com/10624093/1981418 

2、在代理服务器上安装proxysql软件包 proxysql-1.4.2-1-centos7.x86_64.rpm

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

3、在代理服务器上修改proxysql配置文件

vim /etc/proxysql.cnf
datadir="/var/lib/proxysql"
#代理服务器的数据目录，此目录下包含proxysql的日志文件、进程文件、数据库文件
admin_variables=
{
        admin_credentials="admin:admin"
        mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
#此配置段定义proxysql的管理信息，只有admin用户才能管理并且只能在本机操作，监听端口是6032，并且还监听套接字

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"  //监听本机所有ip地址的3306端口，同时监听本机套接字
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
        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 =
(
        {
                address = "192.168.1.101" #后端主服务器地址
                port = 3306               # 后端服务器端口
                hostgroup = 0             # 主服务器所在组，与从服务器不一样
                status = "ONLINE"         # default: ONLINE
                weight = 1                # default: 1
                compression = 0           # default: 0
        },
         
        {
                address="192.168.1.106"
                port=3306
                hostgroup=1
                status = "ONLINE"
                weight = 1
                compression = 0
        }
)
#此配置段定义后端MySQL服务器信息，每个服务器配置在一个花括号内，不同服务器花括号之间以逗号隔开

mysql_users:
(
        {
                username = "dbadmin"   # no default , required
                password = "centos"    # default: ‘‘
                default_hostgroup = 0  # default: 0，即主MySQL
                active = 1        # default: 1
        }
)
#此配置段指明访问代理时连接数据库的账号信息，要提前在MySQL服务器上授权此用户，不出就不再赘述如何授权的操作

mysql_query_rules:
(
        {
                rule_id=1
                active=1
                match_pattern="^SELECT .* FOR UPDATE$" #当查询任何条目且以UPDATE结尾的语句，代理会将此操作交由主MySQL服务器处理
                destination_hostgroup=0
                apply=1
        },
        {
                rule_id=2
                active=1
                match_pattern="^SELECT"    #当以SELECT语句开始进行查询操作，代理会将此操作交由从MySQL处理
                destination_hostgroup=1
                apply=1
        }
)
#此配置段指明对数据库查询的规则

mysql_replication_hostgroups=
(
        {
                writer_hostgroup=0
                reader_hostgroup=1
                comment="test repl 1"  #仅是提示信息而已
       }

)
#本配置段实现读写分离，即读操作交给从MySQL处理，写操作交给主MySQL处理

以上将proxysql配置完成，启动服务 

service  proxysql  start
ss -ntlp
#此时能够看到3306端口和6032端口是打开的，3306用于连接数据库处理数据，6032在本机进行管理数据库
mysql -S /tmp/proxysql_admin.sock -uadmin -padmin
#此操作就可以管理数据库

4、测试

为了直观显示proxysql代理到主MySQL与从MySQL的效果，我们按照下面的步骤来测试：

1）在从MySQL服务器上配置复制过滤器，详细请参考此文http://panpangao.blog.51cto.com/10624093/1981556

SET @@global.replicate_ignore_db=mydb;
#从服务器上在执行SQL线程时就不会从中继日志中重放此数据库，即从服务器上是没有此数据库的
#此步骤的目的就是在从服务器上执行SELECT命令时是失败的，这样proxysql调度就能够看到效果

2）在主服务器上创建mydb数据库并生成相应的表

create database mydb;  
use mydb;  
CREATE TABLE s1 (id int ,name varchar(30));
insert into s1 values(1,‘tom‘);
select * from s1;   
#创建数据库、表，插入字段，查看表内容
#在主服务器上SELECT语句是能够执行的

3）在客户端测试

mysql -udbadmin -pcentos -h192.168.1.107
#此账号密码是proxysql的配置段‘mysql_users’定义的
MySQL [(none)]> USE mydb;
Database changed
MySQL [mydb]> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| s1             |
+----------------+
1 row in set (0.00 sec)
#进入我们创建的数据库中能查到s1表

MySQL [mydb]> SELECT id,name FROM s1;
ERROR 1049 (42000): Unknown database ‘mydb‘
#但是在执行SELECT查询时却显示无mydb数据库，这说明proxysql已经将请求调度到从mysql上了，而从服务器是复制过滤掉了

MySQL [mydb]> select id,name from s1;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
+------+------+
1 row in set (0.00 sec)
#但是我们将指令换成小写就可以查看，因为配置段mysql_query_rules中定义以SELECT开头调度到从服务器注意是大写字母
#也就是说，当我们使用小写的select查询语句时proxysql并不识别而是调度到主服务器上，所以能够查询到

通过上述测试，我们实现了主从复制的读写分离功能

本文出自 “a_pan” 博客，谢绝转载！

MySQL数据库主从复制的读写分离--ProxySQL

标签：linux、mysql