中间件ProxySQL读写分离试验
主机 | IP | 作用 |
---|---|---|
Master | 192.168.37.7 | 主服务器 |
Slave | 192.168.37.17 | 从服务器 |
ProxySQL | 192.168.37.27 | 中间件服务器 |
Clinet | 192.168.37.37 | 客户主机 |
前期准备:
Master、Slave(从服务器my.cnf文件中必须要加上read_only,因为ProxSQL通过此语句判断主从服务器)先完成主从复制。ProxySQL安装Mariadb,Clinet最少要安装MySQL客户端
ProxySQL设置
- 设置ProxySQL yum源并安装,实验时版本为1.4
[root@poxysql ~]#vim /etc/yum.repos.d/proxysql.repo [proxysql] name=ProxySQLyum baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever gpgcheck=1 gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key [root@poxysql ~]#yum install proxysql
- ProxySQL文件
[root@poxysql ~]#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
-
启动ProxySQL
[root@poxysql ~]#service proxysql start Starting ProxySQL: 2019-05-10 14:15:18 [INFO] Using config file /etc/proxysql.cnf DONE!
-
登录服务,ProxySQL默认用户为admin,密码为admin,管理端口为6032,客户使用端口为6033
[root@poxysql ~]#mysql -uadmin -padmin -P6032 -h127.0.0.1 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, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>
- 设置读写分离的主机的地址
在mysql_servers表中添加记录MySQL [(none)]>insert into mysql_servers(hostgrup_id,hostname,prot)values(10,'192.168.37.7',3306); Query OK, 1 row affected (0.01sec) MySQL [(none)]>insert into mysql_servers(hostgrup_id,hostname,prot)values(10,'192.168.37.17',3306); Query OK, 1 row affected (0.01sec) MySQL [(none)]> load mysql servers to runtime; #加载到内存,使设置生效 Query OK, 0 row affected (0.00 sec) MySQL [(none)]> save mysql servers to disk; #保存到硬盘中 Query OK, 0 row affected (0.00 sec)
- ProxySQL配置监控,添加一个访问Master和Slave的用户:monitor密码:centos
Masetr主机(因为已经设置主从复制,所有Slave不用手动设置用户,通过主从自动复制)MariaDB [(none)]>grant replication client on *.* to monitor@'192.168.37.%' identified by 'centos';
- ProxySQL主机
MySQL[(none)]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.00 sec) MySQL [(none)]> set mysql-monitor_password='centos'; Query OK, 1 row affected (0.00 sec) MySQL [(none)]> load mysql variables to runtime; #加载到内存,使设置生效 Query OK, 0 row affected (0.00 sec) MySQL [(none)]> save mysql variables to disk; #保存到硬盘中 Query OK, 0 row affected (0.00 sec)
- 查看监控连接是否正常:如果connect_error的结果为NULL则表示正常
MySQL [(none)]> select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.168.37.7 | 3306 | 1557470898315003 | 1898 | NULL |
| 192.168.37.17 | 3306 | 1557470899044242 | 1950 | NULL |
.
.
.
| 192.168.37.7 | 3306 | 1557471439197688 | 4458 | NULL |
+---------------+------+------------------+-------------------------+---------------+
20 rows in set (0.01 sec)
查看监控心跳信息 (对ping指标的监控):
MySQL> select from mysql_server_ping_log;
查看read_only和replication_lag的监控日志
MySQL> select from mysql_server_read_only_log;
MySQL> select * from mysql_server_replication_lag_log;
- 设置分组信息(读写分离的读主机和写主机)
MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test"); #在分组表mysql_replication_hostgroups中添10(写组)组和20(读组)组,test为描述信息 Query 0K, 1 row affacted (0.00sec) MySQL [(none)]> load mysql servers to runtime; #加载,使生效 Query OK, 0 rows affected (0.01 sec) MySQL [(none)]> save mysql servers to disk; #保存到硬盘中 Query OK, 0 rows affected (0.01 sec)
- 设置读写分离规则
Master主机上设置客户使用用户MariaDB [(none)]> grant all on *.* to sqluser@'192.168.37.%' identified by 'centos';
- ProxySQL中添加用户
MySQL[(none)]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','centos',10); MySQL[(none)]> load mysql users to runtime; #加载,使设置生效 MySQL[(none)]> save mysql users to disk; #保存到硬盘
- 插入路由规则:将select语句分离到20的读组,select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁,应路由到10的写组
MySQL [(none)]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1); #路由规则,读语句到20组,其他到10组 MySQL [(none)]> load mysql query rules to runtime; #加载,使设置生效 MySQL [(none)]> save mysql query rules to disk; #保存到硬盘
测试
在client中执行命令查读和写的主机ID判断是否完成读写分离
在还没有设置规则时;所有的访问都发送到master机器上[root@Centos7 ~]#mysql -usqluser -pmagedu -P6033 -h192.168.37.27 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.30 (ProxySQL) 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)]> select @@server_id; +-------------+ | @@server_id | +-------------+ | 7 | #主服务器(Master)ID +-------------+ 1 row in set (0.01 sec)
规设置成功后,select 语句被发送到slave上执行,其他语句发到master上执行
MySQL [(none)]> select @@server_id; +-------------+ | @@server_id | +-------------+ | 17 | #从服务器(slave)ID +-------------+ 1 row in set (0.00 sec)
查询调度记录
MySQL [(none)]> SELECT hostgroup hg,sum_time, count_star, digest_text
-> FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------+
| 20 | 32464 | 5 | select @@server_id |
| 10 | 15435 | 12 | select @@server_id |
| 10 | 9941 | 1 | show @@server_id |
| 10 | 0 | 1 | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
4 rows in set (0.00 sec)
MySQL [(none)]>
转载于:https://blog.51cto.com/13749470/2392367