ProxySQL实现MySQL读写分离(3)

7.在Proxy上配置监控账号

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)

8.将配置加载至内存，将配置保存至磁盘

MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK; Query OK, 97 rows affected (0.00 sec)

9.测试
9.1查看连接状态

MySQL [(none)]> select * from mysql_server_connect_log; +----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+ | 192.168.73.110 | 3306 | 1557296528658352 | 0 | Access denied for user 'monitor'@'192.168.73.112' (using password: YES) | | 192.168.73.111 | 3306 | 1557296648056186 | 0 | Access denied for user 'monitor'@'192.168.73.112' (using password: YES) | | 192.168.73.110 | 3306 | 1557296649025169 | 0 | Access denied for user 'monitor'@'192.168.73.112' (using password: YES) | | 192.168.73.110 | 3306 | 1557296708057600 | 0 | Access denied for user 'monitor'@'192.168.73.112' (using password: YES) | | 192.168.73.111 | 3306 | 1557296708872496 | 0 | Access denied for user 'monitor'@'192.168.73.112' (using password: YES) | | 192.168.73.110 | 3306 | 1557296758752550 | 2763 | NULL | #此前由于没有创建监控账号所以连接一直失败 | 192.168.73.111 | 3306 | 1557296759862679 | 3205 | NULL | | 192.168.73.110 | 3306 | 1557296818752346 | 1014 | NULL | | 192.168.73.111 | 3306 | 1557296819498108 | 3120 | NULL | | 192.168.73.110 | 3306 | 1557296878752978 | 3245 | NULL | | 192.168.73.111 | 3306 | 1557296879410404 | 3063 | NULL | +----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+ 22 rows in set (0.00 sec)

9.2测试连接ping

MySQL [(none)]> select * from mysql_server_ping_log; +----------------+------+------------------+----------------------+-------------------------------------------------------------------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +----------------+------+------------------+----------------------+-------------------------------------------------------------------------+ | 192.168.73.111 | 3306 | 1557296508118738 | 0 | Access denied for user 'monitor'@'192.168.73.112' (using password: YES) | | 192.168.73.110 | 3306 | 1557296508302837 | 0 | Access denied for user 'monitor'@'192.168.73.112' (using password: YES) | ...中间省略... | 192.168.73.110 | 3306 | 1557297088874658 | 675 | NULL | | 192.168.73.111 | 3306 | 1557297089037256 | 435 | NULL | | 192.168.73.110 | 3306 | 1557297098875954 | 1144 | NULL | | 192.168.73.111 | 3306 | 1557297099069333 | 1252 | NULL | +----------------+------+------------------+----------------------+-------------------------------------------------------------------------+ 122 rows in set (0.00 sec) #已经可以联通

10.设置读写分组

MySQL [(none)]> INSERT INTO mysql_replication_hostgroups VALUES(10,20,"test"); Query OK, 1 row affected (0.00 sec) MySQL [(none)]> SELECT * FROM mysql_replication_hostgroups; +------------------+------------------+---------+ | writer_hostgroup | reader_hostgroup | comment | +------------------+------------------+---------+ | 10 | 20 | test | +------------------+------------------+---------+ 1 row in set (0.00 sec)

11.让读写表生效

MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec)

12.查看mysql_server表此时已经将服务器分组

MySQL [(none)]> SELECT * FROM mysql_servers; +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.73.110 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.73.111 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.00 sec)

13.保存配置至磁盘

MySQL [(none)]> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.02 sec)

至此读写分离配置完毕，接下来需要定义读写分离的规则

三、定义读写分离规则