MySQL 實戰

本書以MySQL 8.0為主，系統地闡述了MySQL日常使用及管理過程中的一些常用知識點：安裝、復制、binlog、備份、監控、DDL、線程池、中間件、常用工具、組復制、InnoDB Cluster、JSON、MySQL 8.0的新特性。

本書定位於實戰，目的是讓讀者拿來即用，快速上手MySQL。除了實戰，本書還花費了大量的篇幅來講解MySQL中一些常見操作、常用工具的實現原理。

組復制是MySQL官方推薦的高可用方案，本書會從源碼角度分析組復制的一些核心模塊的實現細節，包括分佈式恢復、沖突檢測、事務一致性以及流量控制機制等。

陈臣

甲骨文（Oracle）公司首席工程师，前vivo互联网数据库专家，有超过10年的数据库管理和架构经验，擅长MySQL数据库日常操作的原理剖析。公众号“MySQL实战”作者，博客园推荐博客博主iVictor，博客全网阅读量超300万。

第 1章　MySQL入門、安裝與服務的管理 1

1.1　MySQL的歷史 1

1.2　MySQL的安裝 2

1.2.1　下載MySQL 3

1.2.2　基於二進制包的安裝 8

1.2.3　基於源碼包的安裝 14

1.2.4　配置文件的讀取順序 19

1.3　MySQL服務的管理 21

1.3.1　使用/etc/init.d/mysqld管理MySQL服務 22

1.3.2　使用systemd管理MySQL服務 27

1.4　本章總結 33

第 2章　復制 34

2.1　復制的原理及搭建 35

2.1.1　復制的搭建 36

2.1.2　參考資料 41

2.2　GTID復制 41

2.2.1　GTID出現的背景 41

2.2.2　GTID的搭建 42

2.2.3　GTID的原理 42

2.2.4　GTID的相關參數 44

2.2.5　GTID的相關函數 50

2.2.6　在線修改復制模式 51

2.2.7　設置@@GLOBAL.GTID_PURGED時的註意事項 54

2.2.8　參考資料 55

2.3　半同步復制 55

2.3.1　事務的兩階段提交協議 56

2.3.2　半同步復制的原理 57

2.3.3　半同步復制的安裝 58

2.3.4　半同步復制的註意事項 60

2.3.5　半同步復制的常用參數 60

2.4　並行復制 63

2.4.1　並行復制方案 63

2.4.2　如何開啟並行復制 71

2.4.3　參考資料 71

2.5　多源復制 72

2.5.1　多源復制的搭建 72

2.5.2　多源復制搭建過程中的註意事項 73

2.5.3　多源復制的管理 74

2.6　延遲復制 74

2.6.1　如何開啟延遲復制 75

2.6.2　如何使用延遲復制恢復誤刪的表 75

2.6.3　延遲復制的總結 77

2.7　本章總結 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的內容 82

3.2.1　解析STATEMENT格式的二進制日誌 82

3.2.2　解析ROW格式的二進制日誌 88

3.3　如何解讀 relay log 的內容 92

3.4　binlog 中的事件類型 94

3.5　基於 python-mysql-replication 打造一個 binlog解析器 101

3.6　本章總結 109

第4章　深入MySQL的復制管理 110

4.1　常見的管理操作 110

4.1.1　查看主庫的狀態 110

4.1.2　查看從庫復制的狀態 111

4.1.3　搭建復制 113

4.1.4　開啟復制 115

4.1.5　停止復制 117

4.1.6　在主庫上查看從庫IP和埠信息 117

4.1.7　查看實例當前擁有的binlog 118

4.1.8　刪除binlog 118

4.1.9　查看binlog的內容 118

4.1.10　RESET MASTER、RESET SLAVE和RESET SLAVE ALL的區別 119

4.1.11　跳過指定事務 120

4.1.12　操作不寫入binlog 121

4.1.13　判斷主庫的某個操作是否已經在從庫上執行 121

4.1.14　在線設置復制的過濾規則 122

4.2　復制的監控 123

4.2.1　連接 123

4.2.2　事務重放 125

4.2.3　多線程復制 126

4.2.4　過濾規則 128

4.2.5　組復制 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　復制中的常見問題及解決方法 142

4.4.1　I/O線程連接不上主庫 142

4.4.2　server_id重復 143

4.4.3　包的大小超過slave_max_allowed_packet的限制 143

4.4.4　從庫需要的binlog在主庫上不存在 144

4.4.5　從庫的GTID多於主庫的 147

4.4.6　在執行插入操作時，提示唯一鍵沖突 149

4.4.7　在執行刪除或更新操作時，提示記錄不存在 150

4.4.8　主從數據不一致 150

4.5　本章總結 150

第5章　備份 152

5.1　mysqldump 155

5.1.1　mysqldump的實現原理 155

5.1.2　mysqldump的常用選項 160

5.1.3　mysqldump的常見用法 165

5.1.4　總結 166

5.2　mydumper 166

5.2.1　mydumper的安裝 167

5.2.2　mydumper的實現原理 167

5.2.3　mydumper的參數解析 170

5.2.4　myloader的參數解析 177

5.2.5　mydumper和myloader的常見用法 179

5.2.6　總結 180

5.3　XtraBackup 180

5.3.1　XtraBackup的安裝 181

5.3.2　基於源碼分析XtraBackup的實現原理 182

5.3.3　XtraBackup的常見用法 192

5.3.4　Xtrabackup的重要參數 205

5.3.5　XtraBackup的註意事項 206

5.3.6　備份用戶需要的權限 207

5.3.7　參考資料 208

5.4　克隆插件 208

5.4.1　克隆插件的安裝 209

5.4.2　克隆插件的使用 209

5.4.3　查看克隆操作的進度 212

5.4.4　基於克隆數據搭建從庫 214

5.4.5　克隆插件的實現細節 214

5.4.6　克隆插件的限制 215

5.4.7　克隆插件與XtraBackup的異同 216

5.4.8　克隆插件的參數解析 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的關鍵特性 221

5.5.3　util.dumpInstance的實現原理 221

5.5.4　util.dumpInstance的參數解析 222

5.5.5　util.loadDump的參數解析 224

5.5.6　MySQL Shell Dump & Load的註意事項 226

5.5.7　參考資料 226

5.6　使用XtraBackup搭建從庫 226

5.6.1　使用XtraBackup搭建從庫的基本步驟 227

5.6.2　基於從庫備份搭建從庫的註意事項 230

5.6.3　設置GTID_PURGED的註意事項 230

5.6.4　使用XtraBackup 8.0搭建從庫的註意事項 231

5.6.5　總結 233

5.7　指定時間點（位置點）的恢復 234

5.8　搭建binlog server 236

5.8.1　基於mysqlbinlog搭建binlog server 236

5.8.2　參考資料 238

5.9　檢測備份的有效性 238

5.10　本章總結 239

第6章　監控 241

6.1　Zabbix 242

6.1.1　安裝Zabbix Server 242

6.1.2　安裝Zabbix Agent 250

6.2　安裝MySQL監控插件PMP 251

6.3　深入理解PMP 256

6.3.1　ss_get_mysql_stats.php源碼分析 258

6.3.2　基於ss_get_mysql_stats.php自定義監控項 263

6.4　Zabbix常見問題定位及性能優化 266

6.4.1　定位監控項的狀態Not supported 266

6.4.2　分區表 268

6.4.3　Zabbix Server的參數優化 270

6.4.4　Zabbix API 273

6.4.5　參考資料 278

6.5　PMM 278

6.5.1　PMM的體系架構 279

6.5.2　安裝PMM Server 280

6.5.3　安裝PMM Client 283

6.5.4　添加MySQL服務 288

6.5.5　Query Analytics 290

6.5.6　深入理解PMM Server 292

6.5.7　設置告警 297

6.5.8　PMM的常見問題 299

6.5.9　參考資料 305

6.6　MySQL中常用的監控指標 306

6.6.1　連接相關 306

6.6.2　Com相關 307

6.6.3　Handler相關 308

6.6.4　臨時表相關 309

6.6.5　Table Cache相關 310

6.6.6　文件相關 311

6.6.7　主從復制相關 312

6.6.8　緩沖池相關 312

6.6.9　redo log相關 313

6.6.10　鎖相關 314

6.6.11　排序相關 315

6.6.12　查詢相關 316

6.6.13　其他重要指標 316

6.7　本章總結 317

第7章　DDL 318

7.1　Online DDL 319

7.1.1　Online DDL的分類 320

7.1.2　Online DDL的實現原理 324

7.1.3　如何檢查DDL的進度 325

7.1.4　MySQL 8.0.12引入的秒級加列特性 327

7.1.5　Online DDL的優缺點 329

7.1.6　Online DDL的註意事項 329

7.1.7　參考資料 331

7.2　pt-online-schema-change 331

7.2.1　pt-online-schema-change的實現原理 331

7.2.2　pt-online-schema-change的參數解析 337

7.2.3　pt-online-schema-change的優缺點 345

7.2.4　pt-online-schema-change的註意事項 345

7.3　gh-ost 346

7.3.1　gh-ost的實現原理 346

7.3.2　gh-ost的參數解析 354

7.3.3　與gh-ost進行交互 358

7.3.4　gh-ost的優缺點 359

7.4　元數據鎖 360

7.4.1　元數據鎖引入的背景 360

7.4.2　元數據鎖的基本概念 362

7.4.3　在MySQL 5.7和8.0中如何定位DDL被阻塞的問題 363

7.4.4　在MySQL 5.6中如何定位DDL被阻塞的問題 365

7.5　本章總結 367

第8章　連接池和線程池 369

8.1　連接池 369

8.1.1　連接池的運行原理 369

8.1.2　常用的JDBC連接池 370

8.1.3　c3p0連接池 371

8.1.4　DBCP連接池 374

8.1.5　參考配置 377

8.1.6　總結 378

8.2　MySQL線程池 378

8.2.1　線程池的實現原理 379

8.2.2　如何開啟線程池功能 380

8.2.3　MySQL企業版線程池參數解析 380

8.2.4　Percona Server線程池參數解析 381

8.2.5　MySQL企業版線程池和Percona Server線程池的對比 382

8.2.6　線程池的適用場景 384

8.2.7　線程池的壓測結果 384

8.2.8　線程池的監控 385

8.2.9　參考資料 389

8.3　MySQL server has gone away深度解析 389

8.3.1　出現MySQL server has gone away錯誤的常見原因 389

8.3.2　interactive_timeout和wait_timeout的區別 390

8.3.3　wait_timeout設置為多大比較合適 393

8.4　本章總結 395

第9章　MySQL的常用工具 396

9.1　sysbench 398

9.1.1　安裝sysbench 398

9.1.2　sysbench用法講解 399

9.1.3　對MySQL進行基準測試的基本步驟 399

9.1.4　如何分析MySQL的基準測試結果 401

9.1.5　如何使用sysbench對服務器性能進行測試 402

9.1.6　MySQL常見的測試場景及對應的SQL語句 404

9.1.7　如何自定義sysbench測試腳本 407

9.1.8　總結 411

9.2　pt-archiver 411

9.2.1　安裝 411

9.2.2　實現原理 411

9.2.3　常見用法 413

9.2.4　常用參數 416

9.2.5　總結 418

9.3　pt-config-diff 418

9.4　pt-ioprofile 419

9.5　pt-kill 420

9.5.1　實現原理 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　常用參數 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　實現原理 435

9.11.2　常見用法 440

9.11.3　常用參數 440

9.12　pt-table-sync 443

9.12.1　實現原理 443

9.12.2　常見用法 446

9.12.3　常用參數 446

9.13　pt-upgrade 448

9.14　本章總結 453

第 10章　中間件 455

10.1　ProxySQL的安裝 458

10.2　ProxySQL入門 458

10.3　多層配置系統 460

10.4　讀寫分離 463

10.5　深入理解ProxySQL表 470

10.6　ProxySQL的高級特性 475

10.6.1　定時器 475

10.6.2　SQL審計 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　添加一個新的節點 493

10.9　ProxySQL的常見參數 494

10.9.1　管理參數 495

10.9.2　監控參數 496

10.9.3　MySQL參數 498

10.9.4　如何修改參數 501

10.10　ProxySQL中的常見問題 501

10.10.1　如何自定義ProxySQL的數據目錄 501

10.10.2　通過USE DBNAME切換數據庫 502

10.10.3　ProxySQL的高可用性 502

10.11　本章總結 502

第 11章　組復制 504

11.1　部署組復制 506

11.1.1　準備安裝環境 506

11.1.2　初始化MySQL實例 507

11.1.3　啟動組復制 510

11.1.4　添加節點 511

11.2　單主模式和多主模式 512

11.2.1　單主模式和多主模式的區別 512

11.2.2　單主模式和多主模式的在線切換 515

11.3　監控組復制 516

11.3.1　replication_group_members 516

11.3.2　replication_group_member_stats 517

11.4　組復制的要求和限制 518

11.5　組復制的常見管理操作 521

11.5.1　強制組成員的重新配置 521

11.5.2　如何設置IP白名單 523

11.5.3　如何查找單主模式下的Primary節點 524

11.5.4　新主選舉算法 524

11.5.5　如何查看Secondary節點的延遲情況 529

11.5.6　大事務 530

11.5.7　查看組復制的內存使用 531

11.6　組復制的實現原理 533

11.6.1　數據庫狀態機 533

11.6.2　事務在組復制中的處理流程 533

11.6.3　參考資料 535

11.7　組復制的實現細節 536

11.8　組復制的分佈式恢復 545

11.8.1　分佈式恢復的實現原理 545

11.8.2　分佈式恢復的相關參數 552

11.9　組復制的沖突檢測 553

11.9.1　write_set 553

11.9.2　沖突檢測數據庫 556

11.9.3　沖突檢測的實現細節 557

11.9.4　沖突檢測數據庫的清理邏輯 560

11.10　組復制的故障檢測 562

11.10.1　模擬網絡分區 562

11.10.2　故障檢測流程 566

11.10.3　XCom Cache 567

11.10.4　註意事項 569

11.10.5　參考資料 569

11.11　組復制的事務一致性 569

11.11.1　group_replication_consistency 570

11.11.2　總結 573

11.11.3　參考資料 573

11.12　組復制的流量控制機制 573

11.12.1　觸發流控的條件 574

11.12.2　配額的計算邏輯 576

11.12.3　配額的作用時機 581

11.12.4　流控的相關參數 583

11.12.5　總結 584

11.12.6　參考資料 585

11.13　組復制的重點參數 585

11.14　本章總結 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的關鍵特性 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　啟動MySQL Router 612

12.2.4　測試MySQL Router 612

12.2.5　MySQL Router的註意事項 613

12.3　InnoDB Cluster的搭建 614

12.3.1　準備安裝環境 614

12.3.2　初始化MySQL實例 614

12.3.3　創建超級管理員賬號 616

12.3.4　配置實例 616

12.3.5　創建InnoDB Cluster 618

12.3.6　添加節點 619

12.3.7　查看集群的狀態 622

12.3.8　部署MySQL Router 623

12.4　InnoDB Cluster的管理操作 624

12.4.1　dba對象支持的操作 624

12.4.2　cluster對象支持的操作 630

12.5　本章總結 643

附錄A　JSON 645

附錄B　MySQL 8.0的新特性 672