ProxySQL服务器配置

[root@proxysql ~]# yum install proxysql-2.0.8-1-centos67.x86_64.rpm -y     #提前下载好ProxySQL包
[root@proxysql ~]# rpm -ql proxysql
/etc/init.d/proxysql
/etc/logrotate.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
[root@proxysql ~]# vim /etc/proxysql.cnf    #可以修改对外提供服务的端口号为3306
[root@proxysql ~]# service proxysql start
[root@proxysql ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1       #连接proxysql的管理端口；
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.38.37',3306);    #插入主从节点的信息，默认都放在10组里
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.38.47',3306);
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 10           | 192.168.38.37 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |  
| 10           | 192.168.38.47 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
MySQL [(none)]> load mysql servers to runtime;     #加载到runtime中，生效
MySQL [(none)]> save mysql servers to disk;    #保存到磁盘中
MySQL [(none)]> set mysql-monitor_username='monitor';     #设置监控账号的用户名
MySQL [(none)]> set mysql-monitor_password='centos';    #密码
MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;
MySQL [(none)]> select * from mysql_server_connect_log;       #可以查到监控信息了，没指定连接数据库的监控账号之前，会显示拒绝
+---------------+------+------------------+-------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.168.38.37 | 3306 | 1574775423158525 | 873                     | NULL          |
| 192.168.38.47 | 3306 | 1574775423859699 | 701                     | NULL          |
+---------------+------+------------------+-------------------------+---------------+
MySQL [(none)]> select * from sqlite_master where name='mysql_replication_hostgroups'\G    #查询一下mysql_replication_hostgroups这个表的结构
MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,'read_only',"test");    #把10组添加到写组，把20组添加到读组下，针对read_only进行区分；
MySQL [(none)]> load mysql servers to runtime;
MySQL [(none)]> save mysql servers to disk;
MySQL [(none)]> select *  from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | test    |
+------------------+------------------+------------+---------+
MySQL [(none)]> MySQL [(none)]> select * from mysql_servers;     #47这台主机放到了20组下,区分开47这台机器为从节点（负责读），37为主节点（负责写）
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 10           | 192.168.38.37 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         
| 20           | 192.168.38.47 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
MySQL [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | test    |
+------------------+------------------+------------+---------+
MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup)values('sqluser','centos',10);     #指定proxysql真正代理到mysql服务器的用户名密码，默认操作都指定为10组的服务器
MySQL [(none)]> load mysql users to runtime;
MySQL [(none)]> save mysql servers to disk;
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);      #添加查询规则；select for update操作会申请写锁，所以默认放到10组里面；其余select开头的操作放到20组里面；其余所有默认操作都放到10组里面
MySQL [(none)]> load mysql query rules to runtime;
MySQL [(none)]> save mysql servers to disk;