1.在主节点上创建一个账户让客户端连接调度器去访问主从服务器（此处授予的权限较大，实际生产中可以根据需要定义指定的那张表）
[root@Master ~]# mysql -e "GRANT ALL ON *.* TO 'sqluser'@'192.168.73.%' IDENTIFIED BY 'centos';"2.在ProxySQL服务器上，将sqluser用户添加至mysql_users表中
MySQL [(none)]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('sqluser','centos',10); Query OK, 1 row affected (0.00 sec)3.查看mysql_user表信息
MySQL [(none)]> SELECT * FROM mysql_users; +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | sqluser | centos | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 1 row in set (0.00 sec)4.生效存盘
MySQL [(none)]> load mysql users to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.00 sec)5.测试
目前尚未设置读写路由规则，所有的请求都是发往主节点
6.在ProxySQL上定义调度规则
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); Query OK, 2 rows affected (0.00 sec)7.查看定义规则
MySQL [(none)]> SELECT * FROM mysql_query_rules\G; *************************** 1. row *************************** rule_id: 1 active: 1 username: NULL schemaname: NULL flagIN: 0 client_addr: NULL proxy_addr: NULL proxy_port: NULL digest: NULL match_digest: ^SELECT.*FOR UPDATE$ match_pattern: NULL negate_match_pattern: 0 re_modifiers: CASELESS flagOUT: NULL replace_pattern: NULL destination_hostgroup: 10 cache_ttl: NULL reconnect: NULL timeout: NULL retries: NULL delay: NULL next_query_flagIN: NULL mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL OK_msg: NULL sticky_conn: NULL multiplex: NULL log: NULL apply: 1 comment: NULL *************************** 2. row *************************** rule_id: 2 active: 1 username: NULL schemaname: NULL flagIN: 0 client_addr: NULL proxy_addr: NULL proxy_port: NULL digest: NULL match_digest: ^SELECT match_pattern: NULL negate_match_pattern: 0 re_modifiers: CASELESS flagOUT: NULL replace_pattern: NULL destination_hostgroup: 20 cache_ttl: NULL reconnect: NULL timeout: NULL retries: NULL delay: NULL next_query_flagIN: NULL mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL OK_msg: NULL sticky_conn: NULL multiplex: NULL log: NULL apply: 1 comment: NULL 2 rows in set (0.00 sec)8.生效存盘
MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK; Query OK, 0 rows affected (0.00 sec) 四、在Client端测试1.查询操作
[root@Client ~]# mysql -usqluser -pcentos -h192.168.73.112 -P6033 -e "SELECT @@server_id;" +-------------+ | @@server_id | +-------------+ | 2 | +-------------+2.写操作
[root@Client ~]# mysql -usqluser -pcentos -h192.168.73.112 -P6033 -e "BEGIN;INSERT hellodb.teachers VALUE(5,'Long',30,'M');SELECT @@server_id;commit;" +-------------+ | @@server_id | +-------------+ | 1 | +-------------+Linux公社的RSS地址：https://www.linuxidc.com/rssFeed.aspx