ProxySQL 配置详解及读写分离(+GTID)等功能说明2 (完整篇)
1. 實驗環(huán)境
| 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 | 172.16.60.211??? mysql-master?????? 安裝Mysql5.7 172.16.60.212??? mysql-slave1?????? 安裝Mysql5.7 172.16.60.213??? mysql-slave2?????? 安裝Mysql5.7 172.16.60.214??? mysql-proxy??????? 安裝ProxySQL，Mysql-client ? 系統(tǒng)都是CentOS7.5，MySQL版本是5.7，準備一主兩從架構(gòu)(基于GTID的同步,兩個從庫都要開啟read_only=on)來配合ProxySQL。 [root@mysql-master ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) ? 1) 三個節(jié)點各自設(shè)置主機名 [root@mysql-master ~]# hostnamectl --static set-hostname mysql-master [root@mysql-master ~]# hostname mysql-master ?? [root@mysql-slave1 ~]# hostnamectl --static set-hostname mysql-slave1 [root@mysql-slave1 ~]# hostname mysql-slave ? [root@mysql-slave2 ~]# hostnamectl --static set-hostname mysql-slave2 [root@mysql-slave2 ~]# hostname mysql-slave ? [root@mysql-proxy ~]# hostnamectl --static set-hostname mysql-proxy [root@mysql-proxy ~]# hostname mysql-proxy ?? 2) 所有節(jié)點關(guān)閉selinux和iptables防火墻 [root@mysql-master ~]# setenforce 0 [root@mysql-master ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled" SELINUX=disabled ?? [root@mysql-master ~]# iptables -F [root@mysql-master ~]# systemctl disable firewalld [root@mysql-master ~]# systemctl stop firewalld? [root@mysql-master ~]# firewall-cmd --state not running |
2. 安裝Mysql 5.7? (在三個mysql節(jié)點上安裝)
| 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 | 在三個mysql節(jié)點機上使用yum方式安裝Mysql5.7，參考：https://www.cnblogs.com/kevingrace/p/8340690.html ????? 安裝MySQL yum資源庫 [root@mysql-master ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm ????? 安裝MySQL 5.7 [root@mysql-master ~]# yum install -y mysql-community-server ????? 啟動MySQL服務(wù)器和MySQL的自動啟動 [root@mysql-master ~]# systemctl start mysqld.service [root@mysql-master ~]# systemctl enable mysqld.service ????? 設(shè)置登錄密碼 由于MySQL從5.7開始不允許首次安裝后使用空密碼進行登錄！為了加強安全性，系統(tǒng)會隨機生成一個密碼以供管理員首次登錄使用， 這個密碼記錄在/var/log/mysqld.log文件中，使用下面的命令可以查看此密碼： [root@mysql-master ~]# cat /var/log/mysqld.log|grep 'A temporary password' 2019-01-11T05:53:17.824073Z 1 [Note] A temporary password is generated?for?root@localhost: TaN.k:*Qw2xs ????? 使用上面查看的密碼TaN.k:*Qw2xs 登錄mysql，并重置密碼為123456 [root@mysql-master ~]# mysql -p???????????????? #輸入默認的密碼：TaN.k:*Qw2xs ............. mysql>?set?global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec) ????? mysql>?set?global validate_password_length=1; Query OK, 0 rows affected (0.00 sec) ????? mysql>?set?password=password("123456"); Query OK, 0 rows affected, 1 warning (0.00 sec) ????? mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) ????? 查看mysql版本 [root@mysql-master ~]# mysql -p123456 ........ mysql>?select?version(); +-----------+ | version() | +-----------+ | 5.7.24??? | +-----------+ 1 row?in?set?(0.00 sec) ???? ===================================================================== 溫馨提示 mysql5.7通過上面默認安裝后，執(zhí)行語句可能會報錯： ERROR 1819 (HY000): Your password does not satisfy the current policy requirements ???? 這個報錯與Mysql 密碼安全策略validate_password_policy的值有關(guān)，validate_password_policy可以取0、1、2三個值： 解決辦法： set?global validate_password_policy=0; set?global validate_password_length=1; |
3. 配置Mysql基于GTID的主從同步? (在mysql-master 和 mysql-slave1、mysql-slave2節(jié)點上)
| 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 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 | 1) 主數(shù)據(jù)庫mysql-master (172.16.60.211)的配置操作 [root@mysql-master ~]# >/etc/my.cnf [root@mysql-master ~]# 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 = 1 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 ? 配置完成之后，別忘了重啟Mysql [root@mysql-master ~]# systemctl restart mysqld ? 登錄mysql，查看一下master狀態(tài)， 發(fā)現(xiàn)多了一項"Executed_Gtid_Set " [root@mysql-master ~]# mysql -p123456 ......... mysql> show master status; +-------------------+----------+--------------+------------------+------------------------------------------+ | File????????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set??????????????????????? | +-------------------+----------+--------------+------------------+------------------------------------------+ | master-bin.000002 |????? 550 |????????????? |????????????????? | fc39b161-22ca-11e9-a638-005056ac6820:1-2 | +-------------------+----------+--------------+------------------+------------------------------------------+ 1 row?in?set?(0.00 sec) ? mysql> show global variables like?'%uuid%'; +---------------+--------------------------------------+ | Variable_name | Value??????????????????????????????? | +---------------+--------------------------------------+ | server_uuid?? | fc39b161-22ca-11e9-a638-005056ac6820 | +---------------+--------------------------------------+ 1 row?in?set?(0.00 sec) ? mysql> show global variables like?'%gtid%'; +----------------------------------+------------------------------------------+ | Variable_name??????????????????? | Value??????????????????????????????????? | +----------------------------------+------------------------------------------+ | binlog_gtid_simple_recovery????? | ON?????????????????????????????????????? | | enforce_gtid_consistency???????? | ON?????????????????????????????????????? | | gtid_executed??????????????????? | fc39b161-22ca-11e9-a638-005056ac6820:1-2 | | gtid_executed_compression_period | 1000???????????????????????????????????? | | gtid_mode??????????????????????? | ON?????????????????????????????????????? | | gtid_owned?????????????????????? |????????????????????????????????????????? | | gtid_purged????????????????????? |????????????????????????????????????????? | | session_track_gtids????????????? | OFF????????????????????????????????????? | +----------------------------------+------------------------------------------+ 8 rows?in?set?(0.00 sec) ? 主庫執(zhí)行從庫復制授權(quán) mysql> grant replication slave,replication client on *.* to slave@'172.16.60.212'?identified by?"slave@123"; Query OK, 0 rows affected, 1 warning (0.09 sec) ? mysql> grant replication slave,replication client on *.* to slave@'172.16.60.213'?identified by?"slave@123"; Query OK, 0 rows affected, 1 warning (0.03 sec) ? mysql> flush privileges; Query OK, 0 rows affected (0.03 sec) ? ? mysql> show grants?for?slave@'172.16.60.212'; +-------------------------------------------------------------------------------+ | Grants?for?slave@172.16.60.212??????????????????????????????????????????????? | +-------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO?'slave'@'172.16.60.212'?| +-------------------------------------------------------------------------------+ 1 row?in?set?(0.00 sec) ? mysql> show grants?for?slave@'172.16.60.213'; +-------------------------------------------------------------------------------+ | Grants?for?slave@172.16.60.213??????????????????????????????????????????????? | +-------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO?'slave'@'172.16.60.213'?| +-------------------------------------------------------------------------------+ 1 row?in?set?(0.00 sec) ? 在主數(shù)據(jù)庫機器上創(chuàng)建一個測試庫kevin（為了測試效果） mysql> show databases; +--------------------+ | Database?????????? | +--------------------+ | information_schema | | mysql????????????? | | performance_schema | | sys??????????????? | +--------------------+ 4 rows?in?set?(0.00 sec) ? mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;? Query OK, 1 row affected (0.02 sec) ? mysql> use kevin; Database changed mysql> create table?if?not exists haha (id?int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL); Query OK, 0 rows affected (0.17 sec) ? mysql> insert into kevin.haha values(1,"congcong"),(2,"huihui"),(3,"grace");? Query OK, 3 rows affected (0.16 sec) Records: 3? Duplicates: 0? Warnings: 0 ? mysql>?select?* from kevin.haha; +----+----------+ |?id?| name???? | +----+----------+ |? 1 | congcong | |? 2 | huihui?? | |? 3 | grace??? | +----+----------+ 3 rows?in?set?(0.00 sec) ? 2) 從數(shù)據(jù)庫mysql-slave1 (172.16.60.212)的配置操作 與主服務(wù)器配置大概一致，除了server_id不一致外，從服務(wù)器還可以在配置文件里面添加："read_only＝on"?, 使從服務(wù)器只能進行讀取操作，此參數(shù)對超級用戶無效，并且不會影響從服務(wù)器的復制； [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) ??? 在從數(shù)據(jù)庫里，使用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) ? 接著查看從數(shù)據(jù)庫的數(shù)據(jù)，發(fā)現(xiàn)kevin庫已經(jīng)同步過來了! 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) 從數(shù)據(jù)庫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) ? 接著查看從數(shù)據(jù)庫的數(shù)據(jù)，發(fā)現(xiàn)kevin庫已經(jīng)同步過來了! 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）再回到主數(shù)據(jù)庫mysql-master (172.16.60.211)上 ? 查看master狀態(tài)，發(fā)現(xiàn)已經(jīng)有兩個slave節(jié)點正常存在同步關(guān)系了 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）測試數(shù)據(jù)同步 在主數(shù)據(jù)庫mysql-master (172.16.60.211)上更新數(shù)據(jù) 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從數(shù)據(jù)庫上查看，發(fā)現(xiàn)已正常同步過來了 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
已經(jīng)在上面第一步中介紹了安裝方法，這里采用rpm包方式安裝，安裝過程省略........
4.1 ProxySQL實現(xiàn)讀寫分離
向ProxySQL中添加MySQL節(jié)點
| 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) ? ========================================================================================================== 如果在插入過程中，出現(xiàn)報錯： ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port ? 說明可能之前就已經(jīng)定義了其他配置，可以清空這張表 或者 刪除對應(yīng)host的配置 MySQL [(none)]>?select?* from mysql_servers; MySQL [(none)]> delete from mysql_servers; Query OK, 6 rows affected (0.000 sec) ========================================================================================================= ?? 查看這3個節(jié)點是否插入成功，以及它們的狀態(tài)。 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) |
監(jiān)控后端MySQL節(jié)點
添加Mysql節(jié)點之后，還需要監(jiān)控這些后端節(jié)點。對于后端是主從復制的環(huán)境來說，這是必須的，因為ProxySQL需要通過每個節(jié)點的read_only值來自動調(diào)整
它們是屬于讀組還是寫組。
首先在后端master主數(shù)據(jù)節(jié)點上創(chuàng)建一個用于監(jiān)控的用戶名(只需在master上創(chuàng)建即可，因為會復制到slave上)，這個用戶名只需具有USAGE權(quán)限即可。如果還需
要監(jiān)控復制結(jié)構(gòu)中slave是否嚴重延遲于master(這個俗語叫做"拖后腿"，術(shù)語叫做"replication lag")，則還需具備replication client權(quán)限。
| 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主數(shù)據(jù)庫節(jié)點行執(zhí)行： [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代理層節(jié)點上配置監(jiān)控 [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) ? 驗證監(jiān)控結(jié)果：ProxySQL監(jiān)控模塊的指標都保存在monitor庫的log表中。 ?? 以下是連接是否正常的監(jiān)控(對connect指標的監(jiān)控)： 注意：可能會有很多connect_error，這是因為沒有配置監(jiān)控信息時的錯誤，配置后如果connect_error的結(jié)果為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) ? 以下是對心跳信息的監(jiān)控(對ping指標的監(jiān)控) 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日志此時也為空(正常來說，新環(huán)境配置時，這個只讀日志是為空的) MySQL [(none)]>?select?* from mysql_server_read_only_log; Empty?set?(0.000 sec) ? replication_lag的監(jiān)控日志為空 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個節(jié)點都在hostgroup_id=10的組中。 現(xiàn)在，將剛才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模塊就會開始監(jiān)控后端的read_only值，當監(jiān)控到read_only值后，就會按照read_only的值將某些節(jié)點自動移動到讀/寫組。 例如，此處所有節(jié)點都在id=10的寫組，slave1和slave2都是slave，它們的read_only=1，這兩個節(jié)點將會移動到id=20的組。 如果一開始這3節(jié)點都在id=20的讀組，那么移動的將是Master節(jié)點，會移動到id=10的寫組。 ?? 現(xiàn)在看結(jié)果 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
上面的所有配置都是關(guān)于后端MySQL節(jié)點的，現(xiàn)在可以配置關(guān)于SQL語句的，包括：發(fā)送SQL語句的用戶、SQL語句的路由規(guī)則、SQL查詢的緩存、SQL語句的重寫等等。本小節(jié)是SQL請求所使用的用戶配置，例如root用戶。這要求我們需要先在后端MySQL節(jié)點添加好相關(guān)用戶。這里以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主數(shù)據(jù)庫節(jié)點上執(zhí)行：(只需master執(zhí)行即可，會復制給兩個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代理層節(jié)點，配置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：用戶名對應(yīng)的密碼。可以是明文密碼，也可以是hash密碼。如果想使用hash密碼，可以先在某個MySQL節(jié)點上執(zhí)行 ???select?password(PASSWORD)，然后將加密結(jié)果復制到該字段。 -? default_hostgroup：該用戶名默認的路由目標。例如，指定root用戶的該字段值為10時，則使用root用戶發(fā)送的SQL語句默認 ???情況下將路由到hostgroup_id=10組中的某個節(jié)點。 ? 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時，表示事務(wù)持久化：當某連接使用該用戶開啟了一個事務(wù)后，那么在事務(wù)提交/回滾之前， 所有的語句都路由到同一個組中，避免語句分散到不同組。在以前的版本中，默認值為0，不知道從哪個版本開始，它的默認值為1。 我們期望的值為1，所以在繼續(xù)下面的步驟之前，先查看下這個值，如果為0，則執(zhí)行下面的語句修改為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(它是一個寫組)讀、寫數(shù)據(jù)。 下面是通過轉(zhuǎn)發(fā)端口6033連接的，連接的是轉(zhuǎn)發(fā)到后端真正的數(shù)據(jù)庫! [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??????????????? | +--------------------+ |
讀寫分離：配置路由規(guī)則
ProxySQL的路由規(guī)則非常靈活，可以基于用戶、基于schema以及基于每個語句實現(xiàn)路由規(guī)則的定制。本案例作為一個入門配置，實現(xiàn)一個最簡單的語句級路由規(guī)則，從而實現(xiàn)讀寫分離。
必須注意:?這只是實驗，實際的路由規(guī)則絕不應(yīng)該僅根據(jù)所謂的讀、寫操作進行分離，而是從各項指標中找出壓力大、執(zhí)行頻繁的語句單獨寫規(guī)則、做緩存等等。和查詢規(guī)則有關(guān)的表有兩個:mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的擴展表，1.4.7之后才支持該快速路由表。本案例只介紹第一個表。插入兩個規(guī)則，目的是將select語句分離到hostgroup_id=20的讀組，但由于select語句中有一個特殊語句SELECT...FOR UPDATE它會申請寫鎖，所以應(yīng)該路由到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規(guī)則的rule_id必須要小于普通的select規(guī)則的rule_id，因為ProxySQL是根據(jù)rule_id的順序進行規(guī)則匹配的。 ???? 再來測試下，讀操作是否路由給了hostgroup_id=20的讀組, 如下發(fā)現(xiàn)server_id為2和3的節(jié)點 (即slave從節(jié)點)在讀組內(nèi) [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 | +-------------+ ? 讀操作已經(jīng)路由給讀組，再看看寫操作。這里以事務(wù)持久化進行測試。 [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 | ? 顯然，一切都按照預(yù)期進行。最后，如果想查看路由的信息，可查詢stats庫中的stats_mysql_query_digest表。 以下是該表的一個輸出格式示例(和本案例無關(guān))。 [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表內(nèi)。 為了測試讀寫分離的效果，可以先清空此表中之前的記錄 (即之前在實現(xiàn)讀寫分配路由配置之前的記錄) ? 下面這個命令是專門清空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代理層節(jié)點，通過proxysql進行數(shù)據(jù)寫入，并查看 [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主數(shù)據(jù)庫和mysql-slave1、mysql-slave2從數(shù)據(jù)上查看 [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) ? 發(fā)現(xiàn)在客戶端通過proxysql插件更新的數(shù)據(jù)，已經(jīng)寫到mysql-master主數(shù)據(jù)庫上，并同步到mysql-slave1和mysql-slave2兩個從數(shù)據(jù)庫上了！ ? 最后在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) ? 從上述結(jié)果就可以看出proxysql實現(xiàn)的讀寫分離配置是成功的，讀請求是轉(zhuǎn)發(fā)到group20的讀組內(nèi)，寫請求轉(zhuǎn)發(fā)到group10的寫組內(nèi)!! |
4.2 負載均衡測試? (加權(quán)輪詢)
如上已經(jīng)配置好一主(mysql-master，在hostgroup10寫組內(nèi))、兩從(mysql-slave1和mysql-slave2，在hostgroup20讀組內(nèi)) ，并且已經(jīng)在"mysql_query_rules"表中配置了路由規(guī)則，即寫操作轉(zhuǎn)發(fā)到hostgroup10組，讀操作轉(zhuǎn)發(fā)到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寫組內(nèi)只要一個節(jié)點(mysql-master節(jié)點)，hostgroup20讀組內(nèi)有兩個節(jié)點(mysql-slave1、mysql-slave2) 所以這里只能測試讀節(jié)點的負載均衡 ?? [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 | +--------------+ ?? 由以上結(jié)果可能會猜想并可印證： 在一個client的一個鏈接周期內(nèi)，所有query路由到同一臺后端! 即在同一個client的鏈接周期內(nèi)，query路由不會轉(zhuǎn)發(fā)到同組內(nèi)的不同后端節(jié)點機上，只能轉(zhuǎn)發(fā)到同一臺后端節(jié)點機上! ?? 但是這只是個假象!!!?? 是因為正好用到了select?@ 語句。 如官網(wǎng)所介紹:? 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的負載方式目前僅為加權(quán)輪詢一種（經(jīng)驗證所確認），并無其他機制! ? =============================================================================== 可以編寫一個負載均衡的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 ? 執(zhí)行測試腳本: [root@mysql-proxy ~]# sh -x /opt/test_proxysql_lb.sh > /dev/null 2>&1 ? 執(zhí)行后檢查結(jié)果 [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 ? 以上查詢結(jié)果符合預(yù)期 |
4.3 開啟ProxySQL的Web統(tǒng)計功能
| 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登錄即可查看一些統(tǒng)計信息。
4.4? scheduler打印proxysql狀態(tài)到日志
| 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 的運行結(jié)果了： [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"} |
?
***************當你發(fā)現(xiàn)自己的才華撐不起野心時，就請安靜下來學習吧***************
總結(jié)
以上是生活随笔為你收集整理的ProxySQL 配置详解及读写分离(+GTID)等功能说明2 (完整篇)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Eclipse 工具上Springboo
- 下一篇： 2022年3月语音合成（TTS)和语音识