环境介绍:
在本实验中,我用的是两台虚拟机(192.169.10.241,192.169.10.242)搭建环境。
其中:
192.169.10.241上安装一主(3306)、一从(3307)、ProxySQL
192.169.10.242上安装一从(3306)
Mysql版本:5.7.19
ProxySQL安装与启动:
ProxySQL下载地址:
https://www.percona.com/downloads/proxysql/
https://github.com/sysown/proxysql
安装:
rpm -ivh proxysql-1.4.9-1.1.el6.x86_64.rpm
配置文件位置:
/etc/proxysql.cnf
启动ProxySQL,观察运行情况:
[root@rhel6 ~]# service proxysql start
Starting ProxySQL: DONE!
[root@rhel6 ~]# ps -ef|grep proxysql
proxysql 6450 1 0 11:56 ? 00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
proxysql 6451 6450 0 11:56 ? 00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
root 6474 6349 0 11:56 pts/11 00:00:00 grep proxysql
[root@rhel6 ~]# netstat -anlp|grep proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 6451/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 6451/proxysql
登录ProxySQL(登录信息在配置文件中配置)
[root@rhel6 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 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.10 sec)
ProxySQL监控配置:
首先在我的主从环境中创建监控用户和对外访问用户:
mysql> create user 'monitor'@'%' identified by 'monitor';
Query OK, 0 rows affected (0.26 sec)
mysql> grant all privileges on *.* to 'monitor'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'proxyuser'@'%' identified by 'proxyuser';
Query OK, 0 rows affected (0.26 sec)
mysql> grant all privileges on *.* to 'proxyuser'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
在ProxySQL中配置主从服务器信息:
mysql> use main;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from mysql_servers;
Empty set (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(1,'192.169.10.241',3306);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(1,'192.169.10.241',3307),(1,'192.169.10.242',3306);
Query OK, 2 rows affected (0.00 sec)
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.11 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.169.10.241 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.169.10.241 | 3307 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.169.10.242 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
配置监控账号:
mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.09 sec)
mysql> save mysql variables to disk;
Query OK, 95 rows affected (0.01 sec)
查看监控信息,验证配置:
mysql> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 10;
+----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.169.10.242 | 3306 | 1564713971041574 | 1474 | NULL |
| 192.169.10.241 | 3307 | 1564713971030954 | 1813 | NULL |
| 192.169.10.241 | 3306 | 1564713971020374 | 605 | NULL |
| 192.169.10.242 | 3306 | 1564713911041250 | 2280 | NULL |
| 192.169.10.241 | 3307 | 1564713911030804 | 478 | NULL |
| 192.169.10.241 | 3306 | 1564713911020225 | 695 | NULL |
| 192.169.10.242 | 3306 | 1564713892495124 | 1840 | NULL |
| 192.169.10.241 | 3307 | 1564713892484660 | 2628 | NULL |
| 192.169.10.241 | 3306 | 1564713892474069 | 720 | NULL |
| 192.169.10.242 | 3306 | 1564713832494958 | 2164 | NULL |
+----------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)
mysql> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 10;
+----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.169.10.242 | 3306 | 1564714011078199 | 531 | NULL |
| 192.169.10.241 | 3307 | 1564714011076144 | 60 | NULL |
| 192.169.10.241 | 3306 | 1564714011074308 | 82 | NULL |
| 192.169.10.242 | 3306 | 1564714001077933 | 503 | NULL |
| 192.169.10.241 | 3307 | 1564714001075931 | 57 | NULL |
| 192.169.10.241 | 3306 | 1564714001073905 | 82 | NULL |
| 192.169.10.242 | 3306 | 1564713991078404 | 539 | NULL |
| 192.169.10.241 | 3307 | 1564713991075946 | 120 | NULL |
| 192.169.10.241 | 3306 | 1564713991073807 | 86 | NULL |
| 192.169.10.242 | 3306 | 1564713981077951 | 530 | NULL |
+----------------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)
ProxySQL主从分组配置:
mysql> show create table mysql_replication_hostgroups\G
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)
mysql> insert into mysql_replication_hostgroups values(1,2,'proxy');
Query OK, 1 row affected (0.00 sec)
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.169.10.241 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.169.10.241 | 3307 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.169.10.242 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
ProxySQL会根据server的read_only值来进行分组,read_only=0的放到writer_hostgroup,read_only=1的放到reader_hostgroup。(所以我们在一主二从配置中,从库一定要设置read_only=1)。
配置对外访问账号:
mysql> show create table mysql_users\G
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)
mysql> insert into mysql_users(username,password,default_hostgroup) values ('proxyuser','proxyuser',1);
Query OK, 1 row affected (0.00 sec)
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql_users;
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxyuser | proxyuser | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)
mysql> save mysql users to mem;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql_users;
+-----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxyuser | *1E6567D49FB42C02423CCE5DC28510EE1F6A166B | 1 | 0 | 1 | | 0 | 1 | 0 | 1 | 1 | 10000 |
+-----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)
使用对外登录账号,登录验证一下:
[root@rhel6 ~]# mysql -uproxyuser -pproxyuser -h192.169.10.241 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave hosts;
+-----------+----------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+----------------+------+-----------+--------------------------------------+
| 10243 | | 3306 | 10241 | d21170f1-aa92-11e9-89bd-000c29d8ba4d |
| 10242 | 192.169.10.241 | 3307 | 10241 | d812b5b4-b13c-11e9-a0a8-000c296ee978 |
+-----------+----------------+------+-----------+--------------------------------------+
2 rows in set (0.12 sec)
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 10241 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| rhel6 |
+------------+
1 row in set (0.00 sec)
ProxySQL读写分离策略配置:
mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',2,1);
Query OK, 1 row affected (0.00 sec)
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)
在ProxySQL进行查询操作,然后查询stats_mysql_query_digest观察读写分配策略:
mysql> 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 | information_schema | proxyuser | 0x848F114A754B3578 | select * from test.test where id=? | 4 | 1564717404 | 1564717406 | 133772 | 535 | 131406 |
| 2 | information_schema | proxyuser | 0x44DE3BEBB4726FE0 | select c from test.test where id=? | 1 | 1564717455 | 1564717455 | 879 | 879 | 879 |
| 1 | information_schema | proxyuser | 0xDB3A841EF5443C35 | commit | 1 | 1564717305 | 1564717305 | 147414 | 147414 | 147414 |
| 1 | information_schema | proxyuser | 0x905802020202A472 | update test.test set d=? where id=? | 1 | 1564717298 | 1564717298 | 96988 | 96988 | 96988 |
| 1 | information_schema | proxyuser | 0x848F114A754B3578 | select * from test.test where id=? | 2 | 1564717280 | 1564717301 | 204771 | 307 | 204464 |
| 1 | information_schema | proxyuser | 0xA592C94A099E89DC | begin | 1 | 1564717269 | 1564717269 | 181 | 181 | 181 |
| 2 | information_schema | proxyuser | 0x2E767D4A1E8566DC | select count(?) from test.test | 1 | 1564717056 | 1564717056 | 976 | 976 | 976 |
+-----------+--------------------+-----------+--------------------+-------------------------------------+------------+------------+------------+----------+----------+----------+
7 rows in set (0.00 sec)