[root@VM_8_24_centos ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 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)]> 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.01 sec)
MySQL [(none)]>
二.ProxySQL配置
1.架构 node1 (vip 192.168.0.9:3306) , mysql master node2 (vip 192.168.0.42:3306) , mysql slave prxoy (192.168.8.24:6033) , proxysql clb (vip 192.168.9.27:6033), clb proxysql app (192.168.0.26), mysql client
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(0,'192.168.0.9',3306,1,2000,10,'TESTuser'); insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1,'192.168.0.42',3306,1,2000,10,'TESTuser'); select * from mysql_servers;
例子:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,ax_replication_lag,comment) values(0,'192.168.0.9',3306,1,2000,10,'TESTuser'); Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,ax_replication_lag,comment) values(1,'192.168.0.42',3306,1,2000,10,'TESTuser'); Query OK, 1 row affected (0.00 sec)
3.mysql主库上添加proxysql监控账号及应用程序操作账号 mysql master上执行,注意应用程序操作账号根据实际情况授予权限,最好只授权给proxysql主机 语法如下:
1 2 3 4
grant usage on *.* to 'proxysql'@'192.168.8.24' identified by 'ProxySQL@20191226'; grant select, insert, update, delete ON *.* TO 'testuser'@'192.168.8.24' identified by 'TESTuser@20191226'; flush privileges; select host,user from mysql.user;
MySQL [(none)]> grant usage on *.* to 'proxysql'@'192.168.8.24' identified by 'ProxySQL@20191226'; Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [(none)]> grant select, insert, update, delete ON *.* TO 'testuser'@'192.168.8.24' identified by 'TESTuser@20191226'; Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [(none)]> save mysql users to mem; Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> select username,password from mysql_users; +----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | testuser | *59D7241B6C5F951F331FF88505C582CBCD42482F | +----------+-------------------------------------------+ 1 row in set (0.00 sec)
5.设置监控账号 proxysql上操作,账号密码与mysql主库上账号密码保持一致 语法如下:
1 2
update global_variables set variable_value='proxysql' where variable_name='mysql-monitor_username'; update global_variables set variable_value='ProxySQL@20191226' where variable_name='mysql-monitor_password';
例子:
1 2 3 4 5
MySQL [(none)]> update global_variables set variable_value='proxysql' where variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> update global_variables set variable_value='ProxySQL@20191226' where variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.00 sec)
6.应用到线上 proxysql上操作 语法如下:
1 2 3
load mysql servers to runtime; load mysql users to runtime; load mysql variables to runtime;
例子:
1 2 3 4 5 6 7 8
MySQL [(none)]> load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> load mysql users to runtime; Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec)
7.持久化 proxysql上操作 语法如下:
1 2 3
save mysql servers to disk; save mysql users to disk; save mysql variables to disk;
例子:
1 2 3 4 5 6 7 8
MySQL [(none)]> save mysql servers to disk; Query OK, 0 rows affected (0.07 sec)
MySQL [(none)]> save mysql users to disk; Query OK, 0 rows affected (0.04 sec)
MySQL [(none)]> save mysql variables to disk; Query OK, 97 rows affected (0.02 sec)
8.自动读写分离配置 proxysql上操作,定义路由规则,如:除select * from tb for update的select全部发送到slave,其他的的语句发送到master。
语法如下:
1 2 3 4
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',0,1); insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',1,1); load mysql query rules to runtime; save mysql query rules to disk;
例子:
1 2 3 4 5 6 7 8 9 10 11
MySQL [(none)]> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',0,1); Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',1,1); Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> load mysql query rules to runtime; Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> save mysql query rules to disk; Query OK, 0 rows affected (0.10 sec)
[root@VM_8_24_centos ~]# mysql -utestuser -pTESTuser@20191226 -h 127.0.0.1 -P6033 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)]> show databases; +-------------------------+ | Database | +-------------------------+ | information_schema | | mysql | | performance_schema | | TESTuser_data_services_dev | | TESTuser_data_services_test | | TESTuser_rule | +-------------------------+ 7 rows in set (0.01 sec)
MySQL [(none)]> use TESTuser_rule; 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 [TESTuser_rule]> show tables; +-------------------------------+ | Tables_in_TESTuser_rule | +-------------------------------+ | organ_1_b_12 | | organ_1_b_13 | | organ_1_b_14 | | organ_1_b_15 | | organ_1_b_16 | | organ_1_b_19 | +-------------------------------+ 6 rows in set (0.00 sec)
[root@VM_0_26_centos ~]# mysql -utestuser -p -h 192.168.9.27 -P6033 Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2699 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)]> show databases; +-------------------------+ | Database | +-------------------------+ | information_schema | | mysql | | performance_schema | | TESTuser_data_services_dev | | TESTuser_data_services_test | | TESTuser_rule | +-------------------------+ 7 rows in set (0.01 sec)