MySQL實(shí)戰(zhàn)

第 1章　MySQL入門、安裝與服務(wù)的管理 1
1.1　MySQL的歷史 1
1.2　MySQL的安裝 2
1.2.1　下載MySQL 3
1.2.2　基于二進(jìn)制包的安裝 8
1.2.3　基于源碼包的安裝 14
1.2.4　配置文件的讀取順序 19
1.3　MySQL服務(wù)的管理 21
1.3.1　使用/etc/init.d/mysqld管理MySQL服務(wù) 22
1.3.2　使用systemd管理MySQL服務(wù) 27
1.4　本章總結(jié) 33
第 2章　復(fù)制 34
2.1　復(fù)制的原理及搭建 35
2.1.1　復(fù)制的搭建 36
2.1.2　參考資料 41
2.2　GTID復(fù)制 41
2.2.1　GTID出現(xiàn)的背景 41
2.2.2　GTID的搭建 42
2.2.3　GTID的原理 42
2.2.4　GTID的相關(guān)參數(shù) 44
2.2.5　GTID的相關(guān)函數(shù) 50
2.2.6　在線修改復(fù)制模式 51
2.2.7　設(shè)置@@GLOBAL.GTID_PURGED時(shí)的注意事項(xiàng) 54
2.2.8　參考資料 55
2.3　半同步復(fù)制 55
2.3.1　事務(wù)的兩階段提交協(xié)議 56
2.3.2　半同步復(fù)制的原理 57
2.3.3　半同步復(fù)制的安裝 58
2.3.4　半同步復(fù)制的注意事項(xiàng) 60
2.3.5　半同步復(fù)制的常用參數(shù) 60
2.4　并行復(fù)制 63
2.4.1　并行復(fù)制方案 63
2.4.2　如何開啟并行復(fù)制 71
2.4.3　參考資料 71
2.5　多源復(fù)制 72
2.5.1　多源復(fù)制的搭建 72
2.5.2　多源復(fù)制搭建過程中的注意事項(xiàng) 73
2.5.3　多源復(fù)制的管理 74
2.6　延遲復(fù)制 74
2.6.1　如何開啟延遲復(fù)制 75
2.6.2　如何使用延遲復(fù)制恢復(fù)誤刪的表 75
2.6.3　延遲復(fù)制的總結(jié) 77
2.7　本章總結(jié) 77
第3章　深入解析binlog 79
3.1　binlog的格式 79
3.1.1　STATEMENT 79
3.1.2　ROW 80
3.1.3　MIXED 81
3.2　如何解讀binlog的內(nèi)容 82
3.2.1　解析STATEMENT格式的二進(jìn)制日志 82
3.2.2　解析ROW格式的二進(jìn)制日志 88
3.3　如何解讀 relay log 的內(nèi)容 92
3.4　binlog 中的事件類型 94
3.5　基于 python-mysql-replication 打造一個(gè) binlog解析器 101
3.6　本章總結(jié) 109
第4章　深入MySQL的復(fù)制管理 110
4.1　常見的管理操作 110
4.1.1　查看主庫的狀態(tài) 110
4.1.2　查看從庫復(fù)制的狀態(tài) 111
4.1.3　搭建復(fù)制 113
4.1.4　開啟復(fù)制 115
4.1.5　停止復(fù)制 117
4.1.6　在主庫上查看從庫IP和端口信息 117
4.1.7　查看實(shí)例當(dāng)前擁有的binlog 118
4.1.8　刪除binlog 118
4.1.9　查看binlog的內(nèi)容 118
4.1.10　RESET MASTER、RESET SLAVE和RESET SLAVE ALL的區(qū)別 119
4.1.11　跳過指定事務(wù) 120
4.1.12　操作不寫入binlog 121
4.1.13　判斷主庫的某個(gè)操作是否已經(jīng)在從庫上執(zhí)行 121
4.1.14　在線設(shè)置復(fù)制的過濾規(guī)則 122
4.2　復(fù)制的監(jiān)控 123
4.2.1　連接 123
4.2.2　事務(wù)重放 125
4.2.3　多線程復(fù)制 126
4.2.4　過濾規(guī)則 128
4.2.5　組復(fù)制 129
4.3　主從延遲 129
4.3.1　如何分析主從延遲 129
4.3.2　主從延遲的常見原因及解決方法 132
4.3.3　如何解讀Seconds_Behind_Master 134
4.3.4　參考資料 142
4.4　復(fù)制中的常見問題及解決方法 142
4.4.1　I/O線程連接不上主庫 142
4.4.2　server_id重復(fù) 143
4.4.3　包的大小超過slave_max_allowed_packet的限制 143
4.4.4　從庫需要的binlog在主庫上不存在 144
4.4.5　從庫的GTID多于主庫的 147
4.4.6　在執(zhí)行插入操作時(shí)，提示唯一鍵沖突 149
4.4.7　在執(zhí)行刪除或更新操作時(shí)，提示記錄不存在 150
4.4.8　主從數(shù)據(jù)不一致 150
4.5　本章總結(jié) 150
第5章　備份 152
5.1　mysqldump 155
5.1.1　mysqldump的實(shí)現(xiàn)原理 155
5.1.2　mysqldump的常用選項(xiàng) 160
5.1.3　mysqldump的常見用法 165
5.1.4　總結(jié) 166
5.2　mydumper 166
5.2.1　mydumper的安裝 167
5.2.2　mydumper的實(shí)現(xiàn)原理 167
5.2.3　mydumper的參數(shù)解析 170
5.2.4　myloader的參數(shù)解析 177
5.2.5　mydumper和myloader的常見用法 179
5.2.6　總結(jié) 180
5.3　XtraBackup 180
5.3.1　XtraBackup的安裝 181
5.3.2　基于源碼分析XtraBackup的實(shí)現(xiàn)原理 182
5.3.3　XtraBackup的常見用法 192
5.3.4　Xtrabackup的重要參數(shù) 205
5.3.5　XtraBackup的注意事項(xiàng) 206
5.3.6　備份用戶需要的權(quán)限 207
5.3.7　參考資料 208
5.4　克隆插件 208
5.4.1　克隆插件的安裝 209
5.4.2　克隆插件的使用 209
5.4.3　查看克隆操作的進(jìn)度 212
5.4.4　基于克隆數(shù)據(jù)搭建從庫 214
5.4.5　克隆插件的實(shí)現(xiàn)細(xì)節(jié) 214
5.4.6　克隆插件的限制 215
5.4.7　克隆插件與XtraBackup的異同 216
5.4.8　克隆插件的參數(shù)解析 216
5.4.9　參考資料 217
5.5　MySQL Shell Dump & Load 217
5.5.1　MySQL Shell Dump & Load的用法 218
5.5.2　MySQL Shell Dump & Load的關(guān)鍵特性 221
5.5.3　util.dumpInstance的實(shí)現(xiàn)原理 221
5.5.4　util.dumpInstance的參數(shù)解析 222
5.5.5　util.loadDump的參數(shù)解析 224
5.5.6　MySQL Shell Dump & Load的注意事項(xiàng) 226
5.5.7　參考資料 226
5.6　使用XtraBackup搭建從庫 226
5.6.1　使用XtraBackup搭建從庫的基本步驟 227
5.6.2　基于從庫備份搭建從庫的注意事項(xiàng) 230
5.6.3　設(shè)置GTID_PURGED的注意事項(xiàng) 230
5.6.4　使用XtraBackup 8.0搭建從庫的注意事項(xiàng) 231
5.6.5　總結(jié) 233
5.7　指定時(shí)間點(diǎn)（位置點(diǎn)）的恢復(fù) 234
5.8　搭建binlog server 236
5.8.1　基于mysqlbinlog搭建binlog server 236
5.8.2　參考資料 238
5.9　檢測備份的有效性 238
5.10　本章總結(jié) 239
第6章　監(jiān)控 241
6.1　Zabbix 242
6.1.1　安裝Zabbix Server 242
6.1.2　安裝Zabbix Agent 250
6.2　安裝MySQL監(jiān)控插件PMP 251
6.3　深入理解PMP 256
6.3.1　ss_get_mysql_stats.php源碼分析 258
6.3.2　基于ss_get_mysql_stats.php自定義監(jiān)控項(xiàng) 263
6.4　Zabbix常見問題定位及性能優(yōu)化 266
6.4.1　定位監(jiān)控項(xiàng)的狀態(tài)Not supported 266
6.4.2　分區(qū)表 268
6.4.3　Zabbix Server的參數(shù)優(yōu)化 270
6.4.4　Zabbix API 273
6.4.5　參考資料 278
6.5　PMM 278
6.5.1　PMM的體系架構(gòu) 279
6.5.2　安裝PMM Server 280
6.5.3　安裝PMM Client 283
6.5.4　添加MySQL服務(wù) 288
6.5.5　Query Analytics 290
6.5.6　深入理解PMM Server 292
6.5.7　設(shè)置告警 297
6.5.8　PMM的常見問題 299
6.5.9　參考資料 305
6.6　MySQL中常用的監(jiān)控指標(biāo) 306
6.6.1　連接相關(guān) 306
6.6.2　Com相關(guān) 307
6.6.3　Handler相關(guān) 308
6.6.4　臨時(shí)表相關(guān) 309
6.6.5　Table Cache相關(guān) 310
6.6.6　文件相關(guān) 311
6.6.7　主從復(fù)制相關(guān) 312
6.6.8　緩沖池相關(guān) 312
6.6.9　redo log相關(guān) 313
6.6.10　鎖相關(guān) 314
6.6.11　排序相關(guān) 315
6.6.12　查詢相關(guān) 316
6.6.13　其他重要指標(biāo) 316
6.7　本章總結(jié) 317
第7章　DDL 318
7.1　Online DDL 319
7.1.1　Online DDL的分類 320
7.1.2　Online DDL的實(shí)現(xiàn)原理 324
7.1.3　如何檢查DDL的進(jìn)度 325
7.1.4　MySQL 8.0.12引入的秒級(jí)加列特性 327
7.1.5　Online DDL的優(yōu)缺點(diǎn) 329
7.1.6　Online DDL的注意事項(xiàng) 329
7.1.7　參考資料 331
7.2　pt-online-schema-change 331
7.2.1　pt-online-schema-change的實(shí)現(xiàn)原理 331
7.2.2　pt-online-schema-change的參數(shù)解析 337
7.2.3　pt-online-schema-change的優(yōu)缺點(diǎn) 345
7.2.4　pt-online-schema-change的注意事項(xiàng) 345
7.3　gh-ost 346
7.3.1　gh-ost的實(shí)現(xiàn)原理 346
7.3.2　gh-ost的參數(shù)解析 354
7.3.3　與gh-ost進(jìn)行交互 358
7.3.4　gh-ost的優(yōu)缺點(diǎn) 359
7.4　元數(shù)據(jù)鎖 360
7.4.1　元數(shù)據(jù)鎖引入的背景 360
7.4.2　元數(shù)據(jù)鎖的基本概念 362
7.4.3　在MySQL 5.7和8.0中如何定位DDL被阻塞的問題 363
7.4.4　在MySQL 5.6中如何定位DDL被阻塞的問題 365
7.5　本章總結(jié) 367
第8章　連接池和線程池 369
8.1　連接池 369
8.1.1　連接池的運(yùn)行原理 369
8.1.2　常用的JDBC連接池 370
8.1.3　c3p0連接池 371
8.1.4　DBCP連接池 374
8.1.5　參考配置 377
8.1.6　總結(jié) 378
8.2　MySQL線程池 378
8.2.1　線程池的實(shí)現(xiàn)原理 379
8.2.2　如何開啟線程池功能 380
8.2.3　MySQL企業(yè)版線程池參數(shù)解析 380
8.2.4　Percona Server線程池參數(shù)解析 381
8.2.5　MySQL企業(yè)版線程池和Percona Server線程池的對(duì)比 382
8.2.6　線程池的適用場景 384
8.2.7　線程池的壓測結(jié)果 384
8.2.8　線程池的監(jiān)控 385
8.2.9　參考資料 389
8.3　MySQL server has gone away深度解析 389
8.3.1　出現(xiàn)MySQL server has gone away錯(cuò)誤的常見原因 389
8.3.2　interactive_timeout和wait_timeout的區(qū)別 390
8.3.3　wait_timeout設(shè)置為多大比較合適 393
8.4　本章總結(jié) 395
第9章　MySQL的常用工具 396
9.1　sysbench 398
9.1.1　安裝sysbench 398
9.1.2　sysbench用法講解 399
9.1.3　對(duì)MySQL進(jìn)行基準(zhǔn)測試的基本步驟 399
9.1.4　如何分析MySQL的基準(zhǔn)測試結(jié)果 401
9.1.5　如何使用sysbench對(duì)服務(wù)器性能進(jìn)行測試 402
9.1.6　MySQL常見的測試場景及對(duì)應(yīng)的SQL語句 404
9.1.7　如何自定義sysbench測試腳本 407
9.1.8　總結(jié) 411
9.2　pt-archiver 411
9.2.1　安裝 411
9.2.2　實(shí)現(xiàn)原理 411
9.2.3　常見用法 413
9.2.4　常用參數(shù) 416
9.2.5　總結(jié) 418
9.3　pt-config-diff 418
9.4　pt-ioprofile 419
9.5　pt-kill 420
9.5.1　實(shí)現(xiàn)原理 420
9.5.2　過濾邏輯 422
9.5.3　常見用法 424
9.6　pt-pmp 425
9.7　pt-query-digest 426
9.7.1　常見用法 426
9.7.2　常用參數(shù) 429
9.8　pt-show-grants 430
9.9　pt-slave-restart 431
9.10　pt-stalk 433
9.11　pt-table-checksum 435
9.11.1　實(shí)現(xiàn)原理 435
9.11.2　常見用法 440
9.11.3　常用參數(shù) 440
9.12　pt-table-sync 443
9.12.1　實(shí)現(xiàn)原理 443
9.12.2　常見用法 446
9.12.3　常用參數(shù) 446
9.13　pt-upgrade 448
9.14　本章總結(jié) 453
第 10章　中間件 455
10.1　ProxySQL的安裝 458
10.2　ProxySQL入門 458
10.3　多層配置系統(tǒng) 460
10.4　讀寫分離 463
10.5　深入理解ProxySQL表 470
10.6　ProxySQL的高級(jí)特性 475
10.6.1　定時(shí)器 475
10.6.2　SQL審計(jì) 476
10.6.3　查詢重寫 477
10.6.4　mirroring 479
10.6.5　SQL黑名單 481
10.7　ProxySQL連接池 482
10.8　ProxySQL Cluster 490
10.8.1　搭建ProxySQL Cluster 491
10.8.2　添加一個(gè)新的節(jié)點(diǎn) 493
10.9　ProxySQL的常見參數(shù) 494
10.9.1　管理參數(shù) 495
10.9.2　監(jiān)控參數(shù) 496
10.9.3　MySQL參數(shù) 498
10.9.4　如何修改參數(shù) 501
10.10　ProxySQL中的常見問題 501
10.10.1　如何自定義ProxySQL的數(shù)據(jù)目錄 501
10.10.2　通過USE DBNAME切換數(shù)據(jù)庫 502
10.10.3　ProxySQL的高可用性 502
10.11　本章總結(jié) 502
第 11章　組復(fù)制 504
11.1　部署組復(fù)制 506
11.1.1　準(zhǔn)備安裝環(huán)境 506
11.1.2　初始化MySQL實(shí)例 507
11.1.3　啟動(dòng)組復(fù)制 510
11.1.4　添加節(jié)點(diǎn) 511
11.2　單主模式和多主模式 512
11.2.1　單主模式和多主模式的區(qū)別 512
11.2.2　單主模式和多主模式的在線切換 515
11.3　監(jiān)控組復(fù)制 516
11.3.1　replication_group_members 516
11.3.2　replication_group_member_stats 517
11.4　組復(fù)制的要求和限制 518
11.5　組復(fù)制的常見管理操作 521
11.5.1　強(qiáng)制組成員的重新配置 521
11.5.2　如何設(shè)置IP白名單 523
11.5.3　如何查找單主模式下的Primary節(jié)點(diǎn) 524
11.5.4　新主選舉算法 524
11.5.5　如何查看Secondary節(jié)點(diǎn)的延遲情況 529
11.5.6　大事務(wù) 530
11.5.7　查看組復(fù)制的內(nèi)存使用 531
11.6　組復(fù)制的實(shí)現(xiàn)原理 533
11.6.1　數(shù)據(jù)庫狀態(tài)機(jī) 533
11.6.2　事務(wù)在組復(fù)制中的處理流程 533
11.6.3　參考資料 535
11.7　組復(fù)制的實(shí)現(xiàn)細(xì)節(jié) 536
11.8　組復(fù)制的分布式恢復(fù) 545
11.8.1　分布式恢復(fù)的實(shí)現(xiàn)原理 545
11.8.2　分布式恢復(fù)的相關(guān)參數(shù) 552
11.9　組復(fù)制的沖突檢測 553
11.9.1　write_set 553
11.9.2　沖突檢測數(shù)據(jù)庫 556
11.9.3　沖突檢測的實(shí)現(xiàn)細(xì)節(jié) 557
11.9.4　沖突檢測數(shù)據(jù)庫的清理邏輯 560
11.10　組復(fù)制的故障檢測 562
11.10.1　模擬網(wǎng)絡(luò)分區(qū) 562
11.10.2　故障檢測流程 566
11.10.3　XCom Cache 567
11.10.4　注意事項(xiàng) 569
11.10.5　參考資料 569
11.11　組復(fù)制的事務(wù)一致性 569
11.11.1　group_replication_consistency 570
11.11.2　總結(jié) 573
11.11.3　參考資料 573
11.12　組復(fù)制的流量控制機(jī)制 573
11.12.1　觸發(fā)流控的條件 574
11.12.2　配額的計(jì)算邏輯 576
11.12.3　配額的作用時(shí)機(jī) 581
11.12.4　流控的相關(guān)參數(shù) 583
11.12.5　總結(jié) 584
11.12.6　參考資料 585
11.13　組復(fù)制的重點(diǎn)參數(shù) 585
11.14　本章總結(jié) 588
第 12章　InnoDB Cluster 590
12.1　MySQL Shell 591
12.1.1　MySQL Shell的安裝 592
12.1.2　MySQL Shell的使用 592
12.1.3　X DevAPI的關(guān)鍵特性 595
12.1.4　MySQL Shell工具集 598
12.1.5　MySQL Shell的使用技巧 605
12.2　MySQL Router 605
12.2.1　MySQL Router的安裝 606
12.2.2　MySQL Router的使用 606
12.2.3　啟動(dòng)MySQL Router 612
12.2.4　測試MySQL Router 612
12.2.5　MySQL Router的注意事項(xiàng) 613
12.3　InnoDB Cluster的搭建 614
12.3.1　準(zhǔn)備安裝環(huán)境 614
12.3.2　初始化MySQL實(shí)例 614
12.3.3　創(chuàng)建超級(jí)管理員賬號(hào) 616
12.3.4　配置實(shí)例 616
12.3.5　創(chuàng)建InnoDB Cluster 618
12.3.6　添加節(jié)點(diǎn) 619
12.3.7　查看集群的狀態(tài) 622
12.3.8　部署MySQL Router 623
12.4　InnoDB Cluster的管理操作 624
12.4.1　dba對(duì)象支持的操作 624
12.4.2　cluster對(duì)象支持的操作 630
12.5　本章總結(jié) 643
附錄A　JSON 645
附錄B　MySQL 8.0的新特性 672