MySQL proxy MySQL Router

[yehlu]

MySQL
https://dev.mysql.com/downloads/file/?id=466852
https://downloads.mysql.com/archives/proxy/

Maria
https://mariadb.com/downloads/maxscale

ProxySQL
http://www.proxysql.com/compare

http://webnas.bhes.ntpc.edu.tw/wordpress/archives/2691
https://highdb.com/mysql-router-%E6%B5% ... %E7%94%A8/
http://netkiller.sourceforge.net/databa ... outer.html
http://netkiller.sourceforge.net/databa ... proxy.html
https://kknews.cc/zh-tw/other/j5gmg6.html

[yehlu]

https://github.com/sysown/proxysql/wiki ... -interface

list available schemas

Admin> SHOW DATABASES;
+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)
list available tables in main

Admin> SHOW TABLES FROM main;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_servers |
| runtime_mysql_servers |
| mysql_users |
| runtime_mysql_users |
| runtime_mysql_replication_hostgroups |
| mysql_replication_hostgroups |
| mysql_query_rules |
| runtime_mysql_query_rules |
| global_variables |
| runtime_global_variables |
| mysql_collations |
| scheduler |
| runtime_scheduler |
+--------------------------------------+
13 rows in set (0.00 sec)
list available tables in disk

Admin> SHOW TABLES FROM disk;
+-----------------------------------+
| tables |
+-----------------------------------+
| mysql_servers_v110 |
| mysql_users |
| global_variables |
| mysql_collations |
| debug_levels |
| mysql_query_rules_v110 |
| mysql_query_rules_v120a |
| scheduler_v122a |
| scheduler_v122b |
| mysql_servers_v120 |
| mysql_replication_hostgroups_v100 |
| mysql_query_rules_v120g |
| scheduler_v120 |
| mysql_servers |
| mysql_replication_hostgroups |
| mysql_query_rules |
| scheduler |
+-----------------------------------+
17 rows in set (0.00 sec)
Note: the above output may vary depending which version you are using and from which version you have upgraded.

list available tables in stats

Admin> SHOW TABLES FROM stats;
+--------------------------------+
| tables |
+--------------------------------+
| stats_mysql_query_rules |
| stats_mysql_commands_counters |
| stats_mysql_processlist |
| stats_mysql_connection_pool |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_global |
+--------------------------------+
7 rows in set (0.00 sec)
list available tables in monitor

Admin> SHOW TABLES FROM monitor;
+----------------------------------+
| tables |
+----------------------------------+
| mysql_server_connect |
| mysql_server_connect_log |
| mysql_server_ping |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+----------------------------------+
6 rows in set (0.00 sec)

[yehlu]

/etc/proxysql.cnf
只有第一次初始化才有作用

以後必須用管理 port 來維護設定

代碼: 選擇全部

mysql -u admin -h 127.0.0.1 -padmin -P6032

First, changes in mysql_users:

代碼: 選擇全部

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.03 sec)
Next, query rules:

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.05 sec)
Variables:

MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
 
MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.02 sec)
And finally, mysql_servers and mysql_replication_hostgroups:

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