应用MySQL读写分离以提高MySQL服务器的读写性能

  读写分离是借助MySQL中间件 ProxySQL 实现的
  ProxySQL 有两个版本：官方版和percona版，percona版是基于官方版基础上修改C++语言开发，轻量级但性能优异(支持处理千亿级数据)具有中间件所需的绝大多数功能，包括：

1. 多种方式的读/写分离
2. 定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
3. 缓存查询结果
4. 后端节点监控

准备

  实现读写分离前，先实现主从复制

  注意：slave节点需要设置read_only=1

注：实验之前为保障实验顺利进行，请关闭主机的selinux以及防火墙服务

开始搭建

Master节点修改数据库配置文件

[root@Master ~]#cat /etc/my.cnf[mysqld]server_id=1     # 为Master节点设置一个全局唯一的ID号binlog_format=row       # 基于行复制的数据库语句log-bin=/data/bin/mysql-bin     # 启用二进制日志

重新启动数据库服务

[root@Master ~]#service mysqld restartRestarting mysqld (via systemctl):                         [  OK  ]

Master节点上创建带有复制权限的用户账号

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos';Query OK, 0 rows affected (0.00 sec)

查看Master的日志位置信息

MariaDB [mysql]> show master logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |    912372 |+------------------+-----------+1 row in set (0.00 sec)

Slave节点修改配置文件

[root@Slave-1 ~]#cat /etc/my.cnf[mysqld]server_id=2         # Slave节点设置全局唯一的ID号read_only           # 只读

重新启动数据库服务

[root@Slave-1 ~]#systemctl restart mariadb

使用Master创建的复制权限的用户账号进行同步

MariaDB [(none)]> CHANGE MASTER TO    -> MASTER_HOST='192.168.36.101',    ->  MASTER_USER='repluser',    ->  MASTER_PASSWORD='centos',    ->  MASTER_PORT=3306,    ->  MASTER_LOG_FILE='mysql-bin.000001',    ->  MASTER_LOG_POS=245;Query OK, 0 rows affected (0.01 sec)

启动Slave线程

MariaDB [(none)]> slave start;Query OK, 0 rows affected (0.00 sec)

查看线程是否启动

*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.36.101                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 7389               Relay_Log_File: mariadb-relay-bin.000002                Relay_Log_Pos: 7673        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes      # 从节点的IO线程            Slave_SQL_Running: Yes      # 从节点的SQL线程....        Seconds_Behind_Master: 0        # Master与SLave服务器差别延迟.....             Master_Server_Id: 11 row in set (0.00 sec)

检查数据同步情况

MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || hellodb            || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)

配置ProxySQL的YUM仓库

[root@ProxySQL ~]#cat < [proxysql_repo]> name= ProxySQL YUM repository> baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever> gpgcheck=1> gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key> EOF[proxysql_repo]name= ProxySQL YUM repositorybaseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasevergpgcheck=1gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

安装ProxySQL

[root@ProxySQL ~]#yum install -y proxysql mariadb

启动ProxySQL

[root@ProxySQL ~]#rpm -ql proxysql/etc/init.d/proxysql...由于proxysql启动脚本在init.d文件中，所以需要使用service启动[root@ProxySQL ~]#service proxysql startStarting ProxySQL: 2019-05-08 17:58:16 [INFO] Using config file /etc/proxysql.cnfDONE!

端口查看

[root@ProxySQL ~]#netstat -atActive Internet connections (servers and established)Proto Recv-Q Send-Q Local Address           Foreign Address         Statetcp        0      0 0.0.0.0:mysql           0.0.0.0:*               LISTENtcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTENtcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN# 6033端口是接收远程用户的连接、6032端口是连接，管理接口

连接proxysql管理接口

[root@ProxySQL ~]#mysql -uadmin -padmin -P6032 -h227.0.0.1Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.30 (ProxySQL Admin Module)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]>

ProxySQL相当于小型的MySQL,自带：用户名admin、密码admin

添加主从节点的地址

# 添加Master节点MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.36.101',3306);Query OK, 1 row affected (0.00 sec)# 添加Slave节点MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.36.103',3306);Query OK, 1 row affected (0.00 sec)# 查看添加信息MySQL [(none)]> select *from mysql_servers;+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 10           | 192.168.36.101 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0  |         || 10           | 192.168.36.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0  |         |+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+2 rows in set (0.00 sec)

在Master上创建ProxySQL管理的账号

MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.36.%' identified by 'magedu';Query OK, 0 rows affected (0.00 sec)

ProxySQL配置监控的用户名和密码设置，使其自动连接主从节点进行调整

MySQL [(none)]> set mysql-monitor_username='monitor';Query OK, 1 row affected (0.00 sec)MySQL [(none)]> set mysql-monitor_password='magedu';Query OK, 1 row affected (0.00 sec)

使配置加载到内存中生效，并保存到磁盘中

MySQL [(none)]> load mysql variables to runtime;Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> save mysql variables to disk;Query OK, 97 rows affected (0.01 sec)

查看监控连接是否正常

MySQL [(none)]> select *from mysql_server_connect_log;+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+| hostname       | port | time_start_us    | connect_success_time_us | connect_error                                                           |+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+| 192.168.36.101 | 3306 | 1557312316296707 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) || 192.168.36.101 | 3306 | 1557312557263893 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) || 192.168.36.101 | 3306 | 1557312616308042 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) || 192.168.36.103 | 3306 | 1557312617121004 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) || 192.168.36.103 | 3306 | 1557312676308396 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) || 192.168.36.101 | 3306 | 1557312676936371 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) || 192.168.36.101 | 3306 | 1557312694163848 | 2228                    | NULL                                                                    || 192.168.36.103 | 3306 | 1557312695077512 | 4613                    | NULL                                                                    || 192.168.36.103 | 3306 | 1557312754164398 | 1370                    | NULL                                                                    || 192.168.36.103 | 3306 | 1557312874168899 | 2204                    | NULL                                                                    || 192.168.36.101 | 3306 | 1557312874890981 | 2939                    | NULL                                                                    |+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+22 rows in set (0.00 sec)

设置分组信息

MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test");Query OK, 1 row affected (0.00 sec)

查看读写组信息

MySQL [(none)]> select *from mysql_replication_hostgroups;+------------------+------------------+---------+| writer_hostgroup | reader_hostgroup | comment |+------------------+------------------+---------+| 10               | 20               | test    |+------------------+------------------+---------+1 row in set (0.00 sec)

配置生效并保存到磁盘

MySQL [(none)]> save mysql servers to disk;Query OK, 0 rows affected (0.02 sec)

查看信息

MySQL [(none)]> select *from mysql_servers;+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 10           | 192.168.36.101 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0  |         || 20           | 192.168.36.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0  |         |+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+2 rows in set (0.00 sec)

在master上进行创建设置账户，让客户端连接中间的调度器

MariaDB [(none)]> grant all on *.* to sqluser@'192.168.36.%' identified by 'magedu';Query OK, 0 rows affected (0.00 sec)

ProxySQL添加记录，将用户添加到mysql_users表中

MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10);Query OK, 1 row affected (0.00 sec)

查看是否添加成功

MySQL [(none)]> select *from mysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections|+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+| sqluser  | magedu   | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000|+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1 row in set (0.00 sec)

保存配置

MySQL [(none)]> load mysql users to runtime;Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> save mysql users to disk;Query OK, 0 rows affected (0.01 sec)

启用一个客户端连接ProxySQL进行测试

[root@CentOS6 ~]# mysql -usqluser -pmagedu -h292.168.36.104 -P6033Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.5.30 (ProxySQL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

查看连接到哪个主机

mysql> select @@server_id;+-------------+| @@server_id |+-------------+|           1 |+-------------+1 row in set (0.00 sec)

ProxySQL上定义调度规则

MySQL [(none)]> insert into mysql_query_rules    -> (rule_id,active,match_digest,destination_hostgroup,apply) values    -> (1,1,'^select.*from update$',10,1),(2,1,'^select',20,1);Query OK, 2 rows affected (0.00 sec)

生效并存盘

MySQL [(none)]> load mysql query rules to runtime;Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> save mysql query rules to disk;Query OK, 0 rows affected (0.01 sec)

客户端进行读写分离测试

[root@CentOS6 ~]# mysql -usqluser -pmagedu -h292.168.36.104 -P6033 -e 'select @@server_id;'+-------------+| @@server_id |+-------------+|           2 |+-------------+[root@CentOS6 ~]# mysql -usqluser -pmagedu -h292.168.36.104 -P6033 -e 'begin;use db1;insert t1 values(1);select @@server_id;'+-------------+| @@server_id |+-------------+|           1 |+-------------+