前面也写过几篇关于Mysql数据的文章：
MySQL集群高可用架构之MHA
MySQL 同步复制及高可用方案总结
官方工具｜MySQL Router 高可用原理与实战
今天给大家带来的是关于数据库读写分离相关的实战操作。
ProxySQL介绍
ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。具有以下特性：http://www.proxysql.com/
1、连接池，而且是multiplexing
2、主机和用户的最大连接数限制
3、自动下线后端DB
延迟超过阀值
ping 延迟超过阀值
网络不通或宕机
4、强大的规则路由引擎
实现读写分离
查询重写
sql流量镜像
5、支持prepared statement
6、支持Query Cache
7、支持负载均衡，与gelera结合自动failover
整体环境介绍
1、系统环境
三台服务器系统环境一致如下
复制
[root@db1 ~]# cat /etc/redhat-release CentOS Linux release 7.4.1708 (Core) [root@db1 ~]# uname -r 3.10.0-693.el7.x86_64
1.
2.
3.
4.
2、IP地址与软件版本
proxy 192.168.22.171
db1 192.168.22.173
db2 192.168.22.174
mysql 5.7.17
proxy sql 1.4.8
3、关闭防火墙、selinux
复制
systemctl stop firewalld #停止防火墙服务 systemctl disable firewalld #禁止开机自启动 sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/conf && reboot #用sed命令替换的试修改selinux的配置文件
1.
2.
3.
4.
4、mysql安装与主从同步
安装请参考以下文章
主从同步请参以下文章
安装布署过程
1、数据库主从同步
查看主从同步状态
复制
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.22.173 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: db2-relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-log.000001 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: 526 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: 1 Master_UUID: 70a61633-63ae-11e8-ab86-000c29fe99ea Master_Info_File: /mysqldata/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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
检测主从同步
复制
[root@db1 ~]# mysql -uroot -p -e "create database testdb; "Enter password: [root@db1 ~]# mysql -uroot -p -e "show databases;" |grep testdb Enter password: testdb #db2上查看是否同步 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.01 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
2、准备proxySQL软件
复制
[root@proxy ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm [root@proxy ~]# ll proxysql-1.4.8-1-centos7.x86_64.rpm -rw-r--r-- 1 root root 5977168 Apr 10 11:38 proxysql-1.4.8-1-centos7.x86_64.rpm
1.
2.
3.
3、安装配置
复制
[root@proxy ~]# yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm [root@proxy ~]# rpm -ql proxysql /etc/init.d/proxysql #启动脚本 /etc/proxysql.cnf #配置文件，仅在第一次(/var/lib/proxysql/proxysql.db文件不存在)启动时有效。启#动后可以在proxysql管理端中通过修改数据库的方式修改配置并生效(官方推荐方式。) /usr/bin/proxysql #主程序文件 /usr/share/proxysql/tools/proxysql_galera_checker.sh /usr/share/proxysql/tools/proxysql_galera_writer.pl
1.
2.
3.
4.
5.
6.
7.
4、配置文件详解
复制
[root@proxy ~]# egrep -v "^#|^$" /etc/proxysql.cnf datadir="/var/lib/proxysql" #数据目录 admin_variables= { admin_credentials="admin:admin" #连接管理端的用户名与密码 mysql_ifaces="0.0.0.0:6032" #管理端口，用来连接proxysql的管理数据库 } 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:6033" #指定转发端口，用于连接后端mysql数据库的，相当于代理作用 default_schema="information_schema" stacksize=1048576 server_version="5.5.30" #指定后端mysql的版本 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_users: ( ) mysql_query_rules: ( ) scheduler= ( ) mysql_replication_hostgroups= ( ) #因此我们使用官方推荐的方式来配置proxy sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
5、启动服务并查看
复制
[root@proxy ~]# /etc/init.d/proxysql startStarting ProxySQL: DONE! [root@proxy ~]# ss -lntup|grep proxy tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=1199,fd=23)) tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=1199,fd=22)) tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=1199,fd=21)) tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=1199,fd=20)) tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=1199,fd=19)) #可以看出转发端口6033是启动了四个线程
1.
2.
3.
4.
5.
6.
7.
8.
9.
6、在mysql上配置账号并授权
复制
mysql> GRANT ALL ON *.* TO 'proxysql'@'192.168.22.%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec)
1.
2.
3.
4.
7、proxysql默认数据库说明
复制
[root@proxy ~]# yum install mysql -y [root@proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
main：内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。
disk：是持久化到硬盘的配置，sqlite数据文件。
stats：是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
monitor：库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。
8、proxysql的配置系统
ProxySQL具有一个复杂但易于使用的配置系统，可以满足以下需求：
1、允许轻松动态更新配置（这是为了让ProxySQL用户可以在需要零宕机时间配置的大型基础架构中使用它）。与MySQL兼容的管理界面可用于此目的。
2、允许尽可能多的配置项目动态修改，而不需要重新启动ProxySQL进程
3、可以毫不费力地回滚无效配置
4、这是通过多级配置系统实现的，其中设置从运行时移到内存，并根据需要持久保存到磁盘。
3级配置由以下几层组成：
参考文章：https://github.com/sysown/pro...
9、配置proxysql管理用户
proxysql默认的表信息如下
复制
MySQL [main]> show tables; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------------+ 20 rows in set (0.00 sec) #这里是使用insert into语句来动态配置，而可以不需要重启 MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'db1','3306',1,'Write Group'); Query OK, 1 row affected (0.01 sec) MySQL [(none)]> insert intomysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'db2','3307',1,'Read Group'); 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 | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+ | 1 | db1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group | | 2 | db2 | 3307 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+ 2 rows in set (0.00 sec) #接下来将刚刚在mysql客户端创建的用户写入到proxy sql主机的mysql_users表中，它也是用于proxysql客户端访问数据库，默认组是写组，当读写分离规则出现问题时，它会直接访问默认组的数据库。 MySQL [main]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',1); Query OK, 1 row affected (0.00 sec) MySQL [main]> select * from mysql_users; +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | proxysql | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1 row in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
在mysql上添加监控的用户
复制
mysql> GRANT SELECT ON *.* TO 'monitor'@'192.168.22.%' IDENTIFIED BY 'monitor'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) #在proxysql主机端配置监控用户 MySQL [main]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.00 sec) MySQL [main]> set mysql-monitor_password='monitor'; Query OK, 1 row affected (0.00 sec) #参考文章：https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
10、配置proxysql的转发规则
复制
MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1); Query OK, 1 row affected (0.01 sec) MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1); Query OK, 1 row affected (0.00 sec) MySQL [main]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules; +---------+--------+----------------------+-----------------------+-------+ | rule_id | active | match_digest | destination_hostgroup | apply | +---------+--------+----------------------+-----------------------+-------+ | 1 | 1 | ^SELECT.*FOR UPDATE$ | 1 | 1 | | 2 | 1 | ^SELECT | 2 | 1 | +---------+--------+----------------------+-----------------------+-------+ 2 rows in set (0.00 sec) #配置查询select的请求转发到hostgroup_id=2组上（读组）#征对select * from table_name for update这样的修改语句，我们是需要将请求转到写组，也就是hostgroup_id=1#对于其它没有被规则匹配的请求全部转发到默认的组（mysql_users表中default_hostgroup）
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
11、更新配置到RUNTIME中
由上面的配置系统层级关系可以得知所有进来的请求首先是经过RUNTIME层
复制
MySQL [main]> load mysql users to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main]> load mysql servers to runtime; Query OK, 0 rows affected (0.02 sec) MySQL [main]> load mysql query rules to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main]> load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main]> load admin variables to runtime; Query OK, 0 rows affected (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
12、将所有配置保存至磁盘上
所有配置数据保存到磁盘上，也就是永久写入/var/lib/proxysql/proxysql.db这个文件中
复制
MySQL [main]> save mysql users to disk; Query OK, 0 rows affected (0.03 sec) MySQL [main]> save mysql servers to disk; Query OK, 0 rows affected (0.04 sec) ySQL [main]> save mysql query rules to disk; Query OK, 0 rows affected (0.03 sec) MySQL [main]> save mysql variables to disk; Query OK, 94 rows affected (0.02 sec) MySQL [main]> save admin variables to disk; Query OK, 31 rows affected (0.02 sec) MySQL [main]> load mysql users to runtime; Query OK, 0 rows affected (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13、测试读写分离
复制
[root@proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033 Welcome to the MariaDB monitor.Commands end with ; or \g. Your MySQL connection id is 2Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.02 sec)#这才是我们真正的数据库啊
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
创建数据与表，测试读写分离情况
复制
MySQL [(none)]> create database test_proxysql; Query OK, 1 row affected (0.02 sec) MySQL [(none)]> use test_proxysql; Database changed MySQL [test_proxysql]> create table test_tables(name varchar(20),age int(4)); Query OK, 0 rows affected (0.07 sec) MySQL [test_proxysql]> insert into test_tables values('zhao','30'); Query OK, 1 row affected (0.09 sec) MySQL [test_proxysql]> select * from test_tables; +------+------+ | name | age | +------+------+ | zhao | 30 | +------+------+ 1 row in set (0.02 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
在proxysql管理端查看读写分离
复制
MySQL [main]> select * from stats_mysql_query_digest; +-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | 2 | test_proxysql | proxysql | 0x57CF7EC26C91DF9A | select * from test_tables |1 | 1527667635 | 1527667635 | 14253 | 14253 | 14253 | | 1 | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1527667214 | 1527667214 | 0 | 0 | 0 | | 1 | test_proxysql | proxysql | 0xFF9877421CFBDA6F | insert into test_tables values(?,?) | 1 | 1527667623 | 1527667623 | 89033 | 89033 | 89033 | | 1 | information_schema | proxysql | 0xE662AE2DEE853B44 | create database test-proxysql | 1 | 1527667316 | 1527667316 | 8470 | 8470 | 8470 | | 1 | information_schema | proxysql | 0x02033E45904D3DF0 | show databases | 1 | 1527667222 | 1527667222 | 19414 | 19414 | 19414 | | 1 | information_schema | proxysql | 0xB9EF28C84E4207EC | create database test_proxysql | 1 | 1527667332 | 1527667332 | 15814 | 15814 | 15814 | | 2 | information_schema | proxysql | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1527667342 | 1527667342 | 23386 | 23386 | 23386 | | 1 | test_proxysql | proxysql | 0x02033E45904D3DF0 | show databases | 1 | 1527667342 | 1527667342 | 2451 | 2451 | 2451 | | 1 | test_proxysql | proxysql | 0x59F02DA280268525 | create table test_tables | 1 | 1527667360 | 1527667360 | 9187 | 9187 | 9187 | | 1 | test_proxysql | proxysql | 0x99531AEFF718C501 | show tables | 1 | 1527667342 | 1527667342 | 1001 | 1001 | 1001 | | 1 | test_proxysql | proxysql | 0xC745E37AAF6095AF | create table test_tables(name varchar(?),age int(?)) | 1 | 1527667558 | 1527667558 | 68935 | 68935 | 68935 | +-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+ 11 rows in set (0.01 sec)#从上述结果就可以看出读写分离配置是成功的，读请求是转发到2组，写请求转发到1组
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
整个读写分离的架构配置到此就完成了，但是此架构存在需要优化的地方，那就是此架构存在单点问题。实际生产环境中可采用MHA+ProxySQL+Mysql这类架构解决此问题，请持续关注!