ProxySQL 配置详解及读写分离(+GTID)等功能说明 (完整篇)2
| 2) 從數據庫mysql-slave1 (172.16.60.212)的配置操作 與主服務器配置大概一致，除了server_id不一致外，從服務器還可以在配置文件里面添加："read_only＝on"?, 使從服務器只能進行讀取操作，此參數對超級用戶無效，并且不會影響從服務器的復制； [root@mysql-slave1 ~]# >/etc/my.cnf [root@mysql-slave1 ~]# vim /etc/my.cnf [mysqld] datadir =?/var/lib/mysql socket =?/var/lib/mysql/mysql.sock ???????? symbolic-links = 0 ???????? log-error =?/var/log/mysqld.log pid-file?=?/var/run/mysqld/mysqld.pid ???? #GTID: server_id = 2 gtid_mode = on enforce_gtid_consistency = on ?????? #binlog log_bin = master-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 ?????? #relay log skip_slave_start = 1 read_only = on ? 配置完成之后，別忘了重啟Mysql [root@mysql-slave1 ~]# systemctl restart mysqld ? 接著登錄mysql，做主從同步 [root@mysql-slave1 ~]# mysql -p123456 ........ mysql> show databases; +--------------------+ | Database?????????? | +--------------------+ | information_schema | | mysql????????????? | | performance_schema | |?test???????????????| +--------------------+ 4 rows?in?set?(0.00 sec) ??? 在從數據庫里，使用change master 配置主從復制 mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) ? mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.24 sec) ? mysql> start slave; Query OK, 0 rows affected (0.02 sec) ? mysql> show slave status \G; *************************** 1. row *************************** ???????????????Slave_IO_State: Waiting?for?master to send event ??????????????????Master_Host: 172.16.60.211 ??????????????????Master_User: slave ??????????????????Master_Port: 3306 ????????????????Connect_Retry: 60 ??????????????Master_Log_File: master-bin.000002 ??????????Read_Master_Log_Pos: 2069 ???????????????Relay_Log_File: mysql-slave1-relay-bin.000002 ????????????????Relay_Log_Pos: 2284 ????????Relay_Master_Log_File: master-bin.000002 ?????????????Slave_IO_Running: Yes ????????????Slave_SQL_Running: Yes ............ ............ ???????????Retrieved_Gtid_Set: fc39b161-22ca-11e9-a638-005056ac6820:1-8 ????????????Executed_Gtid_Set: 2afbc2f5-22cb-11e9-b9c0-00505688047c:1-2, fc39b161-22ca-11e9-a638-005056ac6820:1-8 ????????????????Auto_Position: 1 ?????????Replicate_Rewrite_DB: ?????????????????Channel_Name: ???????????Master_TLS_Version: 1 row?in?set?(0.00 sec) ? ERROR: No query specified ? 查看從庫的gtid mysql> show global variables like?'%gtid%'; +----------------------------------+------------------------------------------------------------------------------------+ | Variable_name??????????????????? | Value????????????????????????????????????????????????????????????????????????????? | +----------------------------------+------------------------------------------------------------------------------------+ | binlog_gtid_simple_recovery????? | ON???????????????????????????????????????????????????????????????????????????????? | | enforce_gtid_consistency???????? | ON???????????????????????????????????????????????????????????????????????????????? | | gtid_executed??????????????????? | 2afbc2f5-22cb-11e9-b9c0-00505688047c:1-2, fc39b161-22ca-11e9-a638-005056ac6820:1-8 | | gtid_executed_compression_period | 1000?????????????????????????????????????????????????????????????????????????????? | | gtid_mode??????????????????????? | ON???????????????????????????????????????????????????????????????????????????????? | | gtid_owned?????????????????????? |??????????????????????????????????????????????????????????????????????????????????? | | gtid_purged????????????????????? | 2afbc2f5-22cb-11e9-b9c0-00505688047c:1-2?????????????????????????????????????????? | | session_track_gtids????????????? | OFF??????????????????????????????????????????????????????????????????????????????? | +----------------------------------+------------------------------------------------------------------------------------+ 8 rows?in?set?(0.01 sec) ? 接著查看從數據庫的數據，發現kevin庫已經同步過來了! mysql> show databases; +--------------------+ | Database?????????? | +--------------------+ | information_schema | | kevin????????????? | | mysql????????????? | | performance_schema | | sys??????????????? | +--------------------+ 5 rows?in?set?(0.00 sec) ? mysql>?select?* from kevin.haha; +----+----------+ |?id?| name???? | +----+----------+ |? 1 | congcong | |? 2 | huihui?? | |? 3 | grace??? | +----+----------+ 3 rows?in?set?(0.00 sec) ? 3) 從數據庫mysql-slave2 (172.16.60.213)的配置操作 [root@mysql-slave2 ~]# >/etc/my.cnf [root@mysql-slave2 ~]# vim /etc/my.cnf [mysqld] datadir =?/var/lib/mysql socket =?/var/lib/mysql/mysql.sock ???????? symbolic-links = 0 ???????? log-error =?/var/log/mysqld.log pid-file?=?/var/run/mysqld/mysqld.pid ???? #GTID: server_id = 3 gtid_mode = on enforce_gtid_consistency = on ?????? #binlog log_bin = master-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 ?????? #relay log skip_slave_start = 1 read_only = on ? 重啟mysqld [root@mysql-slave2 ~]#? systemctl restart mysqld? ? 登錄mysql，做主從復制 [root@mysql-slave2 ~]# mysql -p123456 ......... mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) ? mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.17 sec) ? mysql> start slave; Query OK, 0 rows affected (0.01 sec) ? mysql> show slave status \G; *************************** 1. row *************************** ???????????????Slave_IO_State: Waiting?for?master to send event ??????????????????Master_Host: 172.16.60.211 ??????????????????Master_User: slave ??????????????????Master_Port: 3306 ????????????????Connect_Retry: 60 ??????????????Master_Log_File: master-bin.000002 ??????????Read_Master_Log_Pos: 2069 ???????????????Relay_Log_File: mysql-slave2-relay-bin.000002 ????????????????Relay_Log_Pos: 2284 ????????Relay_Master_Log_File: master-bin.000002 ?????????????Slave_IO_Running: Yes ????????????Slave_SQL_Running: Yes .......... .......... ???????????Retrieved_Gtid_Set: fc39b161-22ca-11e9-a638-005056ac6820:1-8 ????????????Executed_Gtid_Set: 26e410b4-22cb-11e9-be44-005056880888:1-2, fc39b161-22ca-11e9-a638-005056ac6820:1-8 ????????????????Auto_Position: 1 ?????????Replicate_Rewrite_DB: ?????????????????Channel_Name: ???????????Master_TLS_Version: 1 row?in?set?(0.00 sec) ? ERROR: No query specified ? 查看從庫的gtid mysql> show global variables like?'%gtid%'; +----------------------------------+------------------------------------------------------------------------------------+ | Variable_name??????????????????? | Value????????????????????????????????????????????????????????????????????????????? | +----------------------------------+------------------------------------------------------------------------------------+ | binlog_gtid_simple_recovery????? | ON???????????????????????????????????????????????????????????????????????????????? | | enforce_gtid_consistency???????? | ON???????????????????????????????????????????????????????????????????????????????? | | gtid_executed??????????????????? | 26e410b4-22cb-11e9-be44-005056880888:1-2, fc39b161-22ca-11e9-a638-005056ac6820:1-8 | | gtid_executed_compression_period | 1000?????????????????????????????????????????????????????????????????????????????? | | gtid_mode??????????????????????? | ON???????????????????????????????????????????????????????????????????????????????? | | gtid_owned?????????????????????? |??????????????????????????????????????????????????????????????????????????????????? | | gtid_purged????????????????????? | 26e410b4-22cb-11e9-be44-005056880888:1-2?????????????????????????????????????????? | | session_track_gtids????????????? | OFF??????????????????????????????????????????????????????????????????????????????? | +----------------------------------+------------------------------------------------------------------------------------+ 8 rows?in?set?(0.01 sec) ? 接著查看從數據庫的數據，發現kevin庫已經同步過來了! mysql> show databases; +--------------------+ | Database?????????? | +--------------------+ | information_schema | | kevin????????????? | | mysql????????????? | | performance_schema | | sys??????????????? | +--------------------+ 5 rows?in?set?(0.00 sec) ? mysql>?select?* from kevin.haha; +----+----------+ |?id?| name???? | +----+----------+ |? 1 | congcong | |? 2 | huihui?? | |? 3 | grace??? | +----+----------+ 3 rows?in?set?(0.00 sec) ? 4）再回到主數據庫mysql-master (172.16.60.211)上 ? 查看master狀態，發現已經有兩個slave節點正常存在同步關系了 mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID?????????????????????????? | +-----------+------+------+-----------+--------------------------------------+ |???????? 3 |????? | 3306 |???????? 1 | 26e410b4-22cb-11e9-be44-005056880888 | |???????? 2 |????? | 3306 |???????? 1 | 2afbc2f5-22cb-11e9-b9c0-00505688047c | +-----------+------+------+-----------+--------------------------------------+ 2 rows?in?set?(0.00 sec) ? 5）測試數據同步 在主數據庫mysql-master (172.16.60.211)上更新數據 mysql> insert into kevin.haha values(10,"heifei"),(11,"huoqiu"),(12,"chengxihu"); Query OK, 3 rows affected (0.05 sec) Records: 3? Duplicates: 0? Warnings: 0 ? 然后在兩個slave從數據庫上查看，發現已正常同步過來了 mysql>?select?* from kevin.haha; +----+-----------+ |?id?| name????? | +----+-----------+ |? 1 | congcong? | |? 2 | huihui??? | |? 3 | grace???? | | 10 | heifei??? | | 11 | huoqiu??? | | 12 | chengxihu | +----+-----------+ 6 rows?in?set?(0.00 sec) |
4. 安裝配置ProxySQL
已經在上面第一步中介紹了安裝方法，這里采用rpm包方式安裝，安裝過程省略........
4.1 ProxySQL實現讀寫分離
向ProxySQL中添加MySQL節點
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | 使用insert語句添加主機到mysql_servers表中，其中：hostgroup_id 為10表示寫組，為20表示讀組。 ?? [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 ............ MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.211',3306); Query OK, 1 row affected (0.000 sec) ?? MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.212',3306); Query OK, 1 row affected (0.000 sec) ?? MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.213',3306); Query OK, 1 row affected (0.000 sec) ? ========================================================================================================== 如果在插入過程中，出現報錯： ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port ? 說明可能之前就已經定義了其他配置，可以清空這張表 或者 刪除對應host的配置 MySQL [(none)]>?select?* from mysql_servers; MySQL [(none)]> delete from mysql_servers; Query OK, 6 rows affected (0.000 sec) ========================================================================================================= ?? 查看這3個節點是否插入成功，以及它們的狀態。 MySQL [(none)]>?select?* from mysql_servers\G; *************************** 1. row *************************** ???????hostgroup_id: 10 ???????????hostname: 172.16.60.211 ???????????????port: 3306 ?????????????status: ONLINE ?????????????weight: 1 ????????compression: 0 ????max_connections: 1000 max_replication_lag: 0 ????????????use_ssl: 0 ?????max_latency_ms: 0 ????????????comment: *************************** 2. row *************************** ???????hostgroup_id: 10 ???????????hostname: 172.16.60.212 ???????????????port: 3306 ?????????????status: ONLINE ?????????????weight: 1 ????????compression: 0 ????max_connections: 1000 max_replication_lag: 0 ????????????use_ssl: 0 ?????max_latency_ms: 0 ????????????comment: *************************** 3. row *************************** ???????hostgroup_id: 10 ???????????hostname: 172.16.60.213 ???????????????port: 3306 ?????????????status: ONLINE ?????????????weight: 1 ????????compression: 0 ????max_connections: 1000 max_replication_lag: 0 ????????????use_ssl: 0 ?????max_latency_ms: 0 ????????????comment: 6 rows?in?set?(0.000 sec) ?? ERROR: No query specified ?? 如上修改后，加載到RUNTIME，并保存到disk MySQL [(none)]> load mysql servers to runtime; Query OK, 0 rows affected (0.006 sec) ?? MySQL [(none)]> save mysql servers to disk; Query OK, 0 rows affected (0.348 sec) |
監控后端MySQL節點
添加Mysql節點之后，還需要監控這些后端節點。對于后端是主從復制的環境來說，這是必須的，因為ProxySQL需要通過每個節點的read_only值來自動調整
它們是屬于讀組還是寫組。
首先在后端master主數據節點上創建一個用于監控的用戶名(只需在master上創建即可，因為會復制到slave上)，這個用戶名只需具有USAGE權限即可。如果還需
要監控復制結構中slave是否嚴重延遲于master(這個俗語叫做"拖后腿"，術語叫做"replication lag")，則還需具備replication client權限。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | 在mysql-master主數據庫節點行執行： [root@mysql-master ~]# mysql -p123456 .......... ? mysql> create user monitor@'172.16.60.%'?identified by?'P@ssword1!'; Query OK, 0 rows affected (0.03 sec) ? mysql> grant replication client on *.* to monitor@'172.16.60.%'; Query OK, 0 rows affected (0.02 sec) ? mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) ? 然后回到mysql-proxy代理層節點上配置監控 [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 .......... MySQL [(none)]>?set?mysql-monitor_username='monitor'; Query OK, 1 row affected (0.000 sec) ? MySQL [(none)]>?set?mysql-monitor_password='P@ssword1!'; Query OK, 1 row affected (0.000 sec) ? 修改后，加載到RUNTIME，并保存到disk MySQL [(none)]> load mysql variables to runtime; Query OK, 0 rows affected (0.001 sec) ? MySQL [(none)]> save mysql variables to disk; Query OK, 94 rows affected (0.079 sec) ? 驗證監控結果：ProxySQL監控模塊的指標都保存在monitor庫的log表中。 ?? 以下是連接是否正常的監控(對connect指標的監控)： 注意：可能會有很多connect_error，這是因為沒有配置監控信息時的錯誤，配置后如果connect_error的結果為NULL則表示正常。 MySQL [(none)]>?select?* from mysql_server_connect_log; +---------------+------+------------------+-------------------------+---------------+ |?hostname??????| port | time_start_us??? | connect_success_time_us | connect_error | +---------------+------+------------------+-------------------------+---------------+ | 172.16.60.211 | 3306 | 1548665195883957 | 762???????????????????? | NULL????????? | | 172.16.60.212 | 3306 | 1548665195894099 | 399???????????????????? | NULL????????? | | 172.16.60.213 | 3306 | 1548665195904266 | 483???????????????????? | NULL????????? | | 172.16.60.211 | 3306 | 1548665255883715 | 824???????????????????? | NULL????????? | | 172.16.60.212 | 3306 | 1548665255893942 | 656???????????????????? | NULL????????? | | 172.16.60.211 | 3306 | 1548665495884125 | 615???????????????????? | NULL????????? | | 172.16.60.212 | 3306 | 1548665495894254 | 441???????????????????? | NULL????????? | | 172.16.60.213 | 3306 | 1548665495904479 | 638???????????????????? | NULL????????? | | 172.16.60.211 | 3306 | 1548665512917846 | 487???????????????????? | NULL????????? | | 172.16.60.212 | 3306 | 1548665512928071 | 994???????????????????? | NULL????????? | | 172.16.60.213 | 3306 | 1548665512938268 | 613???????????????????? | NULL????????? | +---------------+------+------------------+-------------------------+---------------+ 20 rows?in?set?(0.000 sec) ? 以下是對心跳信息的監控(對ping指標的監控) MySQL [(none)]>?select?* from mysql_server_ping_log; +---------------+------+------------------+----------------------+------------+ |?hostname??????| port | time_start_us??? | ping_success_time_us | ping_error | +---------------+------+------------------+----------------------+------------+ | 172.16.60.211 | 3306 | 1548665195883407 | 98?????????????????? | NULL?????? | | 172.16.60.212 | 3306 | 1548665195885128 | 119????????????????? | NULL?????? | ........... | 172.16.60.213 | 3306 | 1548665415889362 | 106????????????????? | NULL?????? | | 172.16.60.213 | 3306 | 1548665562898295 | 97?????????????????? | NULL?????? | +---------------+------+------------------+----------------------+------------+ 110 rows?in?set?(0.001 sec) ? read_only日志此時也為空(正常來說，新環境配置時，這個只讀日志是為空的) MySQL [(none)]>?select?* from mysql_server_read_only_log; Empty?set?(0.000 sec) ? replication_lag的監控日志為空 MySQL [(none)]>?select?* from mysql_server_replication_lag_log; Empty?set?(0.000 sec) ? 指定寫組的id為10，讀組的id為20。 MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,1); Query OK, 1 row affected (0.000 sec) ? 在該配置加載到RUNTIME生效之前，先查看下各mysql server所在的組。 MySQL [(none)]>?select?hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+---------------+------+--------+--------+ | hostgroup_id |?hostname??????| port | status | weight | +--------------+---------------+------+--------+--------+ | 10?????????? | 172.16.60.211 | 3306 | ONLINE | 1????? | | 10?????????? | 172.16.60.212 | 3306 | ONLINE | 1????? | | 10?????????? | 172.16.60.213 | 3306 | ONLINE | 1????? | +--------------+---------------+------+--------+--------+ 3 rows?in?set?(0.000 sec) ? 3個節點都在hostgroup_id=10的組中。 現在，將剛才mysql_replication_hostgroups表的修改加載到RUNTIME生效。 MySQL [(none)]> load mysql servers to runtime; Query OK, 0 rows affected (0.003 sec) ? MySQL [(none)]> save mysql servers to disk; Query OK, 0 rows affected (0.361 sec) ? 一加載，Monitor模塊就會開始監控后端的read_only值，當監控到read_only值后，就會按照read_only的值將某些節點自動移動到讀/寫組。 例如，此處所有節點都在id=10的寫組，slave1和slave2都是slave，它們的read_only=1，這兩個節點將會移動到id=20的組。 如果一開始這3節點都在id=20的讀組，那么移動的將是Master節點，會移動到id=10的寫組。 ?? 現在看結果 MySQL [(none)]>?select?hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+---------------+------+--------+--------+ | hostgroup_id |?hostname??????| port | status | weight | +--------------+---------------+------+--------+--------+ | 10?????????? | 172.16.60.211 | 3306 | ONLINE | 1????? | | 20?????????? | 172.16.60.212 | 3306 | ONLINE | 1????? | | 20?????????? | 172.16.60.213 | 3306 | ONLINE | 1????? | +--------------+---------------+------+--------+--------+ 3 rows?in?set?(0.000 sec) ? MySQL [(none)]>?select?* from mysql_server_read_only_log; +---------------+------+------------------+-----------------+-----------+-------+ |?hostname??????| port | time_start_us??? | success_time_us | read_only | error | +---------------+------+------------------+-----------------+-----------+-------+ | 172.16.60.212 | 3306 | 1548665728919212 | 1684??????????? | 1???????? | NULL? | | 172.16.60.211 | 3306 | 1548665728918753 | 3538??????????? | 0???????? | NULL? | | 172.16.60.213 | 3306 | 1548665728919782 | 3071??????????? | 1???????? | NULL? | |
配置mysql_users
上面的所有配置都是關于后端MySQL節點的，現在可以配置關于SQL語句的，包括：發送SQL語句的用戶、SQL語句的路由規則、SQL查詢的緩存、SQL語句的重寫等等。本小節是SQL請求所使用的用戶配置，例如root用戶。這要求我們需要先在后端MySQL節點添加好相關用戶。這里以root和sqlsender兩個用戶名為例.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | 首先，在mysql-master主數據庫節點上執行：(只需master執行即可，會復制給兩個slave) [root@mysql-master ~]# mysql -p123456 ......... mysql> grant all on *.* to root@'172.16.60.%'?identified by?'passwd'; Query OK, 0 rows affected, 1 warning (0.04 sec) ? mysql> grant all on *.* to sqlsender@'172.16.60.%'?identified by?'P@ssword1!'; Query OK, 0 rows affected, 1 warning (0.03 sec) ? mysql> flush privileges; Query OK, 0 rows affected (0.03 sec) ? 然后回到mysql-proxy代理層節點，配置mysql_users表，將剛才的兩個用戶添加到該表中。 admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10); Query OK, 1 row affected (0.001 sec) ?? admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10); Query OK, 1 row affected (0.000 sec) ?? admin> load mysql?users?to runtime; Query OK, 0 rows affected (0.001 sec) ?? admin> save mysql?users?to disk; Query OK, 0 rows affected (0.108 sec) ?? mysql_users表有不少字段，最主要的三個字段為username、password和default_hostgroup： -? username：前端連接ProxySQL，以及ProxySQL將SQL語句路由給MySQL所使用的用戶名。 -? password：用戶名對應的密碼。可以是明文密碼，也可以是hash密碼。如果想使用hash密碼，可以先在某個MySQL節點上執行 ???select?password(PASSWORD)，然后將加密結果復制到該字段。 -? default_hostgroup：該用戶名默認的路由目標。例如，指定root用戶的該字段值為10時，則使用root用戶發送的SQL語句默認 ???情況下將路由到hostgroup_id=10組中的某個節點。 ? admin>?select?* from mysql_users\G *************************** 1. row *************************** ??????????????username: root ??????????????password:?passwd ????????????????active: 1 ???????????????use_ssl: 0 ?????default_hostgroup: 10 ????????default_schema: NULL ?????????schema_locked: 0 transaction_persistent: 1 ??????????fast_forward: 0 ???????????????backend: 1 ??????????????frontend: 1 ???????max_connections: 10000 *************************** 2. row *************************** ??????????????username: sqlsender ??????????????password: P@ssword1! ????????????????active: 1 ???????????????use_ssl: 0 ?????default_hostgroup: 10 ????????default_schema: NULL ?????????schema_locked: 0 transaction_persistent: 1 ??????????fast_forward: 0 ???????????????backend: 1 ??????????????frontend: 1 ???????max_connections: 10000 2 rows?in?set?(0.000 sec) ?? 雖然這里沒有詳細介紹mysql_users表，但上面標注了"注意本行"的兩個字段必須要引起注意。只有active=1的用戶才是有效的用戶。 至于transaction_persistent字段，當它的值為1時，表示事務持久化：當某連接使用該用戶開啟了一個事務后，那么在事務提交/回滾之前， 所有的語句都路由到同一個組中，避免語句分散到不同組。在以前的版本中，默認值為0，不知道從哪個版本開始，它的默認值為1。 我們期望的值為1，所以在繼續下面的步驟之前，先查看下這個值，如果為0，則執行下面的語句修改為1。 ? MySQL [(none)]> update mysql_users?set?transaction_persistent=1 where username='root'; Query OK, 1 row affected (0.000 sec) ? MySQL [(none)]> update mysql_users?set?transaction_persistent=1 where username='sqlsender'; Query OK, 1 row affected (0.000 sec) ? MySQL [(none)]> load mysql?users?to runtime; Query OK, 0 rows affected (0.001 sec) ? MySQL [(none)]> save mysql?users?to disk; Query OK, 0 rows affected (0.123 sec) ? 然后，分別使用root用戶和sqlsender用戶測試下它們是否能路由到默認的hostgroup_id=10(它是一個寫組)讀、寫數據。 下面是通過轉發端口6033連接的，連接的是轉發到后端真正的數據庫! [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id" +-------------+ | @@server_id | +-------------+ |?????????? 1 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test" [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;" +--------------------+ | Database?????????? | +--------------------+ | information_schema | | kevin????????????? | | mysql????????????? | | performance_schema | | proxy_test???????? | | sys??????????????? | +--------------------+ [root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'use proxy_test;create table t(id int);' [root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;' +----------------------+ | Tables_in_proxy_test | +----------------------+ | t??????????????????? | +----------------------+ [root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;'??????????? +--------------------+ | Database?????????? | +--------------------+ | information_schema | | kevin????????????? | | mysql????????????? | | performance_schema | | proxy_test???????? | | sys??????????????? | +--------------------+ ? 然后再刪除上面這個測試庫 [root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'drop database proxy_test;' [root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;'????????? +--------------------+ | Database?????????? | +--------------------+ | information_schema | | kevin????????????? | | mysql????????????? | | performance_schema | | sys??????????????? | +--------------------+ |
讀寫分離：配置路由規則
ProxySQL的路由規則非常靈活，可以基于用戶、基于schema以及基于每個語句實現路由規則的定制。本案例作為一個入門配置，實現一個最簡單的語句級路由規則，從而實現讀寫分離。
必須注意:?這只是實驗，實際的路由規則絕不應該僅根據所謂的讀、寫操作進行分離，而是從各項指標中找出壓力大、執行頻繁的語句單獨寫規則、做緩存等等。和查詢規則有關的表有兩個:mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的擴展表，1.4.7之后才支持該快速路由表。本案例只介紹第一個表。插入兩個規則，目的是將select語句分離到hostgroup_id=20的讀組，但由于select語句中有一個特殊語句SELECT...FOR UPDATE它會申請寫鎖，所以應該路由到hostgroup_id=10的寫組.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1??????????????????????? ............ 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.000 sec) ? MySQL [(none)]> load mysql query rules to runtime; Query OK, 0 rows affected (0.000 sec) ? MySQL [(none)]> save mysql query rules to disk; Query OK, 0 rows affected (0.272 sec) ? 需要注意：?select?...?for?update規則的rule_id必須要小于普通的select規則的rule_id，因為ProxySQL是根據rule_id的順序進行規則匹配的。 ???? 再來測試下，讀操作是否路由給了hostgroup_id=20的讀組, 如下發現server_id為2和3的節點 (即slave從節點)在讀組內 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+ | @@server_id | +-------------+ |?????????? 3 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+ | @@server_id | +-------------+ |?????????? 3 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+ | @@server_id | +-------------+ |?????????? 2 | +-------------+ ? 讀操作已經路由給讀組，再看看寫操作。這里以事務持久化進行測試。 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;' +-------------+ | @@server_id | +-------------+ |?????????? 1 | +-------------+ +-------------+ | @@server_id | +-------------+ |?????????? 3 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;' +-------------+ | @@server_id | +-------------+ |?????????? 1 | +-------------+ +-------------+ | @@server_id | +-------------+ |?????????? 2 | ? 顯然，一切都按照預期進行。最后，如果想查看路由的信息，可查詢stats庫中的stats_mysql_query_digest表。 以下是該表的一個輸出格式示例(和本案例無關)。 [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1??????????????????????? ............ MySQL [(none)]> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; +----+----------+------------+----------------------------------+ | hg | sum_time | count_star | digest_text????????????????????? | +----+----------+------------+----------------------------------+ | 10 | 283841?? | 1????????? | drop database proxy_test???????? | | 10 | 161020?? | 1????????? | create table t(id?int)?????????? | | 10 | 36002??? | 1????????? | create database proxy_test?????? | | 20 | 2719???? | 5????????? |?select?@@server_id?????????????? | | 10 | 1250???? | 3????????? |?select?@@server_id?????????????? | | 10 | 1102???? | 2????????? | show databases?????????????????? | | 10 | 789????? | 2????????? | start transaction??????????????? | | 10 | 655????? | 1????????? | SELECT DATABASE()??????????????? | | 10 | 629????? | 1????????? | show databases?????????????????? | | 10 | 564????? | 1????????? | show tables from proxy_test????? | | 10 | 286????? | 2????????? | commit?????????????????????????? | | 10 | 0??????? | 8????????? |?select?@@version_comment limit ? | | 10 | 0??????? | 5????????? |?select?@@version_comment limit ? | +----+----------+------------+----------------------------------+ 13 rows?in?set?(0.002 sec) |
測試讀寫分離效果
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | 由于讀寫操作都記錄在proxysql的stats_mysql_query_digest表內。 為了測試讀寫分離的效果，可以先清空此表中之前的記錄 (即之前在實現讀寫分配路由配置之前的記錄) ? 下面這個命令是專門清空stats_mysql_query_digest表的? (使用"delete from stats_mysql_query_digest"??清空不掉!) MySQL [(none)]> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row?in?set?(0.002 sec) ? MySQL [(none)]>?select?hostgroup,username,digest_text,count_star from stats_mysql_query_digest;????????????? Empty?set?(0.001 sec) ? 在mysql-proxy代理層節點，通過proxysql進行數據寫入，并查看 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from kevin.haha;' +----+-----------+ |?id?| name????? | +----+-----------+ |? 1 | congcong? | |? 2 | huihui??? | |? 3 | grace???? | | 11 | huoqiu??? | | 12 | chengxihu | | 21 | zhongguo? | +----+-----------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'delete from kevin.haha where id > 3;' [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'insert into kevin.haha values(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");' [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'update kevin.haha set name="hangzhou" where id=22 ;'???????????????? [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from kevin.haha;'?????????????????????????????????????????? +----+----------+ |?id?| name???? | +----+----------+ |? 1 | congcong | |? 2 | huihui?? | |? 3 | grace??? | | 21 | zhongguo | | 22 | hangzhou | | 23 | taiwan?? | +----+----------+ ? 在mysql-master主數據庫和mysql-slave1、mysql-slave2從數據上查看 [root@mysql-master ~]# mysql -p123456 ......... mysql>?select?* from kevin.haha; +----+----------+ |?id?| name???? | +----+----------+ |? 1 | congcong | |? 2 | huihui?? | |? 3 | grace??? | | 21 | zhongguo | | 22 | hangzhou | | 23 | taiwan?? | +----+----------+ 6 rows?in?set?(0.00 sec) ? 發現在客戶端通過proxysql插件更新的數據，已經寫到mysql-master主數據庫上，并同步到mysql-slave1和mysql-slave2兩個從數據庫上了！ ? 最后在proxysql管理端查看讀寫分離 [root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032??????????? ............ ............ MySQL [(none)]>?select?hostgroup,username,digest_text,count_star from stats_mysql_query_digest; +-----------+----------+------------------------------------------------+------------+ | hostgroup | username | digest_text??????????????????????????????????? | count_star | +-----------+----------+------------------------------------------------+------------+ | 10??????? | root???? | insert into kevin.haha values(?,?),(?,?),(?,?) | 1????????? | | 10??????? | root???? | delete from kevin.haha where?id?> ???????????? | 1????????? | | 10??????? | root???? | update kevin.haha?set?name=? where?id=???????? | 1????????? | | 20??????? | root???? |?select?* from kevin.haha?????????????????????? | 2????????? | | 10??????? | root???? |?select?@@version_comment limit ??????????????? | 5????????? | +-----------+----------+------------------------------------------------+------------+ 5 rows?in?set?(0.001 sec) ? 從上述結果就可以看出proxysql實現的讀寫分離配置是成功的，讀請求是轉發到group20的讀組內，寫請求轉發到group10的寫組內!! |
4.2 負載均衡測試? (加權輪詢)
如上已經配置好一主(mysql-master，在hostgroup10寫組內)、兩從(mysql-slave1和mysql-slave2，在hostgroup20讀組內) ，并且已經在"mysql_query_rules"表中配置了路由規則，即寫操作轉發到hostgroup10組，讀操作轉發到hostgroup20組.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 | MySQL [(none)]>?select?* from mysql_query_rules;??????????? +---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+ | rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest???????? | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment | +---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+ | 1?????? | 1????? | NULL???? | NULL?????? | 0????? | NULL??????? | NULL?????? | NULL?????? | NULL?? | ^SELECT.*FOR UPDATE$ | NULL????????? | 0??????????????????? | CASELESS???? | NULL??? | NULL??????????? | 10??????????????????? | NULL????? | NULL????? | NULL??? | NULL??? | NULL? | NULL????????????? | NULL?????????? | NULL???????????? | NULL????? | NULL?? | NULL??????? | NULL????? | NULL | 1???? | NULL??? | | 2?????? | 1????? | NULL???? | NULL?????? | 0????? | NULL??????? | NULL?????? | NULL?????? | NULL?? | ^SELECT????????????? | NULL????????? | 0??????????????????? | CASELESS???? | NULL??? | NULL??????????? | 20??????????????????? | NULL????? | NULL????? | NULL??? | NULL??? | NULL? | NULL????????????? | NULL?????????? | NULL???????????? | NULL????? | NULL?? | NULL??????? | NULL????? | NULL | 1???? | NULL??? | +---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+ 2 rows?in?set?(0.000 sec) ?? 由于hostgroup10寫組內只要一個節點(mysql-master節點)，hostgroup20讀組內有兩個節點(mysql-slave1、mysql-slave2) 所以這里只能測試讀節點的負載均衡 ?? [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave2 | +--------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave2 | +--------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave2 | +--------------+ ?? 再實驗下mysql -e跟多條語句，看看如何 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave2 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave2 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave2 | +--------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave1 | +--------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname" +--------------+ | @@hostname???| +--------------+ | mysql-slave2 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave2 | +--------------+ +--------------+ | @@hostname???| +--------------+ | mysql-slave2 | +--------------+ ?? 由以上結果可能會猜想并可印證： 在一個client的一個鏈接周期內，所有query路由到同一臺后端! 即在同一個client的鏈接周期內，query路由不會轉發到同組內的不同后端節點機上，只能轉發到同一臺后端節點機上! ?? 但是這只是個假象!!!?? 是因為正好用到了select?@ 語句。 如官網所介紹:? sends a query that implicitly disables multiplexing. For example,?if?you run “SELECT @a” , ProxySQL will disable multiplexing?for?that client and will always use the same backend connection ?? 最后可以知道: proxysql的負載方式目前僅為加權輪詢一種（經驗證所確認），并無其他機制! ? =============================================================================== 可以編寫一個負載均衡的shell測試腳本: [root@mysql-proxy ~]# which mysql /usr/bin/mysql [root@mysql-proxy ~]# vim /opt/test_proxysql_lb.sh #!/bin/bash ? i=0 while(($i<200)) do ????????/usr/bin/mysql?-uroot -ppasswd -P6033 -h127.0.0.1 -e?"select @@hostname;"?>>?/tmp/test_proxy_sql_lb.txt ????????let?"i++" ????????echo?"$i" ????????sleep?0.1 done ? 執行測試腳本: [root@mysql-proxy ~]# sh -x /opt/test_proxysql_lb.sh > /dev/null 2>&1 ? 執行后檢查結果 [root@mysql-proxy ~]# grep "mysql-slave1" /tmp/test_proxy_sql_lb.txt|wc -l 86 [root@mysql-proxy ~]# grep "mysql-slave2" /tmp/test_proxy_sql_lb.txt|wc -l 114 ? 以上查詢結果符合預期 |
4.3 開啟ProxySQL的Web統計功能
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 首先打開web功能 [root@mysql-proxy ~]#? mysql -uadmin -padmin -h127.0.0.1 -P6032? ............ ............ MySQL [(none)]> update global_variables?set?variable_value='true'?where variable_name='admin-web_enabled'; Query OK, 1 row affected (0.001 sec) ? MySQL [(none)]> LOAD ADMIN VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.001 sec) ? MySQL [(none)]> SAVE ADMIN VARIABLES TO DISK; Query OK, 31 rows affected (0.070 sec) ? 然后查看端口和登錄web界面的用戶名和密碼，用戶名和密碼與stat賬戶一致： MySQL [(none)]>?select?* from global_variables where variable_name LIKE?'admin-web%'?or variable_name LIKE?'admin-stats%'; +-----------------------------------+----------------+ | variable_name???????????????????? | variable_value | +-----------------------------------+----------------+ | admin-stats_credentials?????????? | stats:stats??? |????????????????#賬戶密碼 | admin-stats_mysql_connections???? | 60???????????? | | admin-stats_mysql_connection_pool | 60???????????? | | admin-stats_mysql_query_cache???? | 60???????????? | | admin-stats_system_cpu??????????? | 60???????????? | | admin-stats_system_memory???????? | 60???????????? | | admin-web_enabled???????????????? |?true???????????| | admin-web_port??????????????????? | 6080?????????? |?????????????????????#端口 +-----------------------------------+----------------+ 8 rows?in?set?(0.003 sec) |
查看web端口是否正常打開
| 1 2 3 | [root@mysql-proxy ~]# lsof -i:6080 COMMAND??? PID USER?? FD?? TYPE?? DEVICE SIZE/OFF?NODE NAME proxysql 22324 root?? 27u? IPv4 23010645????? 0t0? TCP *:6080 (LISTEN) |
訪問http://172.16.60.214:6080并使用stats:stats登錄即可查看一些統計信息。
4.4? scheduler打印proxysql狀態到日志
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [root@mysql-proxy ~]# mkdir -p /opt/proxysql/log [root@mysql-proxy ~]# vim /opt/proxysql/log/status.sh #!/bin/bash DATE=`date?"+%Y-%m-%d %H:%M:%S"` echo?"{\"dateTime\":\"$DATE\",\"status\":\"running\"}"?>>?/opt/proxysql/log/status_log ? [root@mysql-proxy ~]# chmod 777 /opt/proxysql/log/status.sh ? 然后在proxysql插入一條scheduler (定義每分鐘打印一次，即60000毫秒) [root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 ............ ............ MySQL [(none)]> insert into scheduler(active,interval_ms,filename) values (1,60000,'/opt/proxysql/log/status.sh'); Query OK, 1 row affected (0.000 sec) ? MySQL [(none)]> LOAD SCHEDULER TO RUNTIME; Query OK, 0 rows affected (0.001 sec) ? MySQL [(none)]> SAVE SCHEDULER TO DISK; Query OK, 0 rows affected (0.105 sec) ? 然后查看日志就可以看到proxysql 的運行結果了： [root@mysql-proxy ~]# tail -f /opt/proxysql/log/status_log {"dateTime":"2019-02-19 14:24:03","status":"running"} {"dateTime":"2019-02-19 14:25:03","status":"running"} {"dateTime":"2019-02-19 14:26:03","status":"running"} {"dateTime":"2019-02-19 14:27:03","status":"running"} |
?
***************當你發現自己的才華撐不起野心時，就請安靜下來學習吧***************
總結
以上是生活随笔為你收集整理的ProxySQL 配置详解及读写分离(+GTID)等功能说明 (完整篇)2的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 警惕消费陷阱和霸王条款：在携程网订的机票
- 下一篇： 【火炉炼AI】机器学习055-使用LBP