ProxySQL+Mysql实现数据库读写分离实战
proxysql默认的表信息如下 MySQL > show tables;+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
20 rows in set (0.00 sec)
#这里是使用insert into语句来动态配置，而可以不需要重启
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'db1','3306',1,'Write Group');
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'db2','3307',1,'Read Group');
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> select * from mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1 | db1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group |
| 2 | db2 | 3307 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group|
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
2 rows in set (0.00 sec)
#接下来将刚刚在mysql客户端创建的用户写入到proxy sql主机的mysql_users表中，它也是用于proxysql客户端访问数据库，默认组是写组，当读写分离规则出现问题时，它会直接访问默认组的数据库。
MySQL > INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',1);
Query OK, 1 row affected (0.00 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 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxysql | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)
在mysql上添加监控的用户
mysql> GRANT SELECT ON *.* TO 'monitor'@'192.168.22.%'> Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#在proxysql主机端配置监控用户
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)
#参考文章：https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
页:
[1]