問題描述

在程序上線運行一段時間之后，隨著用戶量的逐漸增多，單臺MySQL開始無法承受所有的壓力，為了承載更大的數據庫并發(fā)，避免單臺MySQL宕機，即無法正常提供服務，出現整體應用程序崩潰的情況，此時需要使用MySQL集群，此階段會出現的典型問題如下：

（1）在生產環(huán)境中，當MySQL處于運行狀態(tài)時應如何備份當前數據？

（2）在某場景下，某個接口需要鎖表以便修改數據，而其他讀取的線程都處于阻塞等待狀態(tài)，此時應如何對其進行優(yōu)化？

（3）并發(fā)讀取越來越多，單臺無法滿足業(yè)務需求，如何進行處理？

問題分析與解決方案

針對在問題描述中提出的問題，都可以使用MySQL主從復制解決。MySQL主從復制是最常見的解決單臺MySQL性能瓶頸的方案之一。在業(yè)務復雜的系統(tǒng)中，架構的發(fā)展導致業(yè)務量越來越大、I/O訪問次數越來越多，單臺MySQL開始無法滿足需求，此時就需要做多庫的存儲，以便降低磁盤I/O的訪問次數，提高單臺I/O的訪問性能。

MySQL主從復制原理

MySQL主從復制指我們可以把數據從一個MySQL服務器（主服務器、主節(jié)點）復制到一個或多個從節(jié)點，即從節(jié)點可以復制主服務器中的所有數據庫實例、特定數據庫實例或特定表等。MySQL默認采用異步的復制方式，也就是說，從節(jié)點無須一直訪問主服務器，而是可以在遠程服務器上更新自己的數據。

主服務器也叫作master服務器。當主服務器上的數據發(fā)生改變時，主服務器會將數據的更改記錄存儲在二進制日志中。

從服務器也叫作slave服務器。從服務器會定期對主服務器上的二進制日志進行探測，觀測其是否發(fā)生了改變。如果主服務器上的數據發(fā)生了改變，則從服務器會啟動一個I/O線程，請求更新數據，具體過程如下所示：

• （1）客戶端SQL更新命令。
• （2）主服務器執(zhí)行SQL語句。
• （3）主服務器寫二進制日志。
• （4）從服務器啟動I/O線程。
• （5）從服務器從I/O線程寫盤（relay-log）。
• （6）從服務器啟動SQL線程讀（relay-log）。
• （7）從服務器執(zhí)行更新命令（relay-info）。

1. 部署過程中需要注意的事項

（1）主服務器和從服務器中的MySQL版本必須相同，否則可能出現未知的異常與錯誤

（2）主服務器和從服務器的時間必須同步，否則兩個線程的時間節(jié)點可能對不上，導致同步數據失敗。

（3）在MySQL中，一般最少包含兩個從服務器。當主服務器與從服務器的數據不同時，可以與第三方進行參照。

2. MySQL主從復制的架構拓撲

（1）一主一從：一主一從指一臺服務器作為主服務器（M），另一臺服務器作為從服務器（S）。主服務器負責寫入或讀取數據，從服務器只負責讀取數據，并且從服務器會從主服務器上下載數據。一主一從使用場景較為有限，更多的時候是使用一主多從的形式，即主從復制至少由三臺服務器組成（一臺主服務器和兩臺從服務器）。當一臺服務器的數據出現異常時，可以參考其他服務器上的數據。

（2）主主復制：主主復制類似于常見的集群模式，指把兩臺服務器都設置為主服務器，即兩臺服務器既可以分別寫入數據，也可以分別從對方那里下載數據。該架構還可以擴展成master+slave+master+slave的形式，即兩臺主服務器進行主主復制，每臺主服務器下面各有一臺個人服務器進行主從復制。此架構方案將壓力平分給多臺服務器，但不是按照寫入或讀取的方式分配的。

（3）一主多從：一主多從適合寫入較少，但讀取較多的場景。

（4）多主一從：多主一從適用于寫入較多，但讀取較少的場景，即由不同的主服務器進行寫入，只由一臺從服務器進行讀取。

（5）聯級復制：聯級復制指master A slave B slave C的架構方式，slaveB和slaveC，會替換掉之前舊的masterA。同時，slaveB和slaveC是新的主從關系，因此，配置成聯級復制來遷移數據，另外也方便切換。架構圖如下所示。

深入理解MySQL中的二進制日志

MySQL中的二進制日志是一個二進制文件，主要用于記錄修改數據或有可能引起數據變更的SQL語句。二進制日志記錄了對MySQL進行更改的所有操作，并且記錄了語句發(fā)生時間、執(zhí)行時長、操作數據等其他額外信息，但是它不記錄SELECT、SHOW等那些不修改數據的SQL語句。二進制日志主要用于數據庫恢復和主從復制，以及審計操作。在MySQL主從復制解決方案中，二進制日志是主從復制解決方案的基礎。

查看MySQL二進制日志狀態(tài)

當系統(tǒng)變量log_bin的值為OFF時，表示沒有開啟二進制日志；當系統(tǒng)變量log_bin的值為ON時，表示開啟了二進制日志。在MySQL控制臺輸入如下命令即可查看二進制日志是否開啟：

結果如圖

模糊查詢命令如下：

結果如圖

log_bin和sql_log_bin的區(qū)別

log_bin主要用于數據恢復，以及在主從服務器之間同步數據。當MySQL啟動時，可以通過配置文件開啟二進制日志，而log_bin這個變量僅僅是報告當前二進制日志的狀態(tài)（是否開啟）。如果想要更改二進制日志的開啟狀態(tài)，則需要在更改配置文件后重新啟動MySQL。

sql_log_bin是一個動態(tài)變量，該變量既可以是局部變量，即只對當前會話生效（Session），也可以是全局變量（Global）。當sql_log_bin為全局變量時，如果修改這個變量，則sql_log_bin只會對新的會話生效，這意味著sql_log_bin對當前會話不再生效。因此一般在全局修改sql_log_bin之后，都要把原來的所有連接關閉（kill）。如果在一連接中將該值設置為OFF，則該連接上的客戶端的所有更新操作在MySQL的二進制日志中不會記錄日志。因此，當通過log_bin還原數據庫時，為了防止將還原的UPDATE命令寫入二進制日志中，出現循環(huán)復制的現象，可以選擇關閉sql_log_bin變量。

開啟二進制日志

查看MySQL的配置文件/etc/my.cnf，看看是否有與二進制日志有關的配置：

如果沒有，則在/etc/my.cnf的[mysqld]選項中追加以下內容：

如果上述內容不是在[mysqld]選項中新增的，而是在其他選項中新增的，那么即使更改了配置文件/etc/my.cnf，二進制日志也無法啟動。下面解釋一下代碼中各項的含義。

server-id：MySQL的ID屬性是唯一值，作用如下。

（1）MySQL的同步數據中是包含server-id的，用于標識該語句最初是從哪個server寫入的，所以server-id一定要有。

（2）每一個同步的slave在master上都有對應的一個master線程，該線程就是通過slave的server-id來標識的。

? 每個slave在master上最多有一個master線程，如果兩個slave的server-id相同，則后一個連接成功時，前一個會被“踢”掉。

? 在slave主動連接master之后，如果在slave上執(zhí)行了slave stop，則連接斷開，但是master上對應的線程并沒有退出。

? 在slave運行之后，master不能再創(chuàng)建一個線程而保留原來的線程，否則在數據同步時可能出現問題。

（3）在MySQL中做主主同步時，多個主需要構成一個環(huán)狀，但在同步時又要保證一條數據不會陷入死循環(huán)，這就是靠server-id來實現的。

log-bin：打開二進制日志功能。在復制（replication）配置中，master必須打開此項。

binlog-format：二進制日志的模式與配置。在MySQL中，復制二進制日志的方式主要有三種：

• 基于SQL語句的復制（Statement-Based Replication，SBR）。
• 基于行的復制（Row-Based Replication，RBR）。
• 混合模式復制（Mixed-Based Replication，MBR）。

對應的二進制日志模式有三種：Statement Level模式、Row Level模式和Mixed模式，其優(yōu)點和缺點如表所示。

MySQL默認使用Statement Level模式，推薦使用Mixed模式。對于一些特殊使用，可以考慮使用Row Level模式。例如，通過二進制日志同步數據的修改，會節(jié)省很多相關操作，所以對于二進制日志數據處理會變得非常輕松。如 果 采 用 INSERT 、 UPDATE 、 DELETE 等 直 接 操 作 表 ， 則 日 志 格 式 根 據binlog_format的設定而記錄。如果采用GRANT、REVOKE、SET PASSWORD等管理語句來操作表，那么一定要采用Statement Level模式記錄。

除此之外，還可以對二進制日志進行以下配置：

binlog_cache_size：在一個事務中，二進制日志記錄了SQL狀態(tài)所持有的緩存大小。如果經常使用大的、多聲明的事務，則可以把此值設置得大一些，以獲取更好的性能。所有從事務來的狀態(tài)都先被緩存在二進制日志中，在提交后再一次性寫入二進制日志中。如果事務比此值大，則使用磁盤上的臨時文件來替代。此緩存是在每個連接的事務第一次更新狀態(tài)時被創(chuàng)建的，屬于session級別，通常采用默認值即可，編寫方式如下所示：

max_binlog_cache_size：最大二進制日志緩存大小，通常采用默認值即可，編寫方式如下所示：

max_binlog_size：如果二進制日志寫入的內容超出給定值，則日志就會發(fā)生滾動。注意不能把該變量設置為大于1GB或小于4096字節(jié)，默認值是1GB。如果正在提交比較大的事務，則二進制日志的大小有可能會超過max_binlog_size值，從而引發(fā)報錯，通常采用默認值即可，編寫方式如下所示：

expire_logs_days：刪除超過N天的二進制日志，通常采用默認值即可，編寫方式如下所示：

在更改配置文件/etc/my.cnf之后，通過如下命令可重啟MySQL服務器，檢查是否開啟了MySQL的二進制日志文件：

結果如圖所示。

重新查看log_bin啟動結果，如圖所示。

如果在重啟MySQL之后，無法正常啟動MySQL，或者log-bin沒有正常開啟，則可以查看Linux系統(tǒng)下的兩個日志文件是否有錯誤：

一 般 來 說 ， 在 輸 入 相 對 路 徑 時 ， 二 進 制 日 志 的 存 放 地 址為/var/lib/mysql，如圖所示。

每次重啟MySQL服務器都會生成一個新的二進制日志文件，相當于對二進制日志進行了切換。在切換二進制日志時，會看到mysql-bin文件的number在不斷遞增。

除二進制日志文件外，還生成了一個.index文件。這個文件中存儲了所有二進制日志文件的清單，又稱為二進制文件的索引。

查看二進制日志文件的名稱、大小和狀態(tài)

查看二進制日志文件的名稱和大小的命令如下所示：

結果如圖所示。

也可以輸入如下命令進行查看：

該命令等價于show binary logs;命令，結果如圖所示

查看當前二進制文件狀態(tài)的命令如下所示，結果如圖所示。

刪除某個日志之前的所有二進制日志文件

在前面介紹過，可以通過expire_logs_days參數設定根據時間自動刪除二進制日志。下面介紹如何通過purge命令手動刪除某日志之前的所有二進制日志文件。首先，查看當前MySQL中的二進制日志文件，命令如下所示：

結果如圖所示。

當通過purge命令刪除mysql-bin.000002之前的所有二進制日志文件時，該刪除操作會影響二進制日志文件的索引部分的內容，命令如下所示：

結果如圖所示。

在執(zhí)行purge命令之后，再次查看MySQL中的二進制日志文件可以發(fā)現，名為mysql-bin.000002的二進制日志文件已經被刪除了，命令如下所示：

結果如圖所示。

刪除某個時間點以前的二進制日志文件

刪除某個時間點以前的二進制日志文件的命令如下所示：

刪除7天前的二進制日志文件的命令如下所示：

刪除所有的二進制日志文件

在執(zhí)行刪除所有的二進制日志文件的命令后，所有的二進制日志文件都會被刪除，并重新生成新的mysql-bin.000001文件，命令如下所示：

結果如圖所示。

查看二進制日志文件內容

在查看二進制日志文件內容之前，首先創(chuàng)建一張表，以便讓二進制日志文件中包含一些可以閱讀的參數，創(chuàng)建表的命令如下所示：

在MySQL的命令行中讀取相關的二進制日志文件，命令如下所示，結果如圖所示。

從圖中可以看到，創(chuàng)建表的命令也在其中，同時包含各種配置信息。執(zhí)行INSERT語句：

再次查看二進制日志，命令如下所示：

結果如圖所示。

查看二進制日志文件的部分輸出，命令如下所示：

結果如圖所示。

當發(fā)現上述輸出內容不是十分容易觀察之后，也可以使用如下命令繼續(xù)觀察二進制日志：

結果如圖所示。

在生產環(huán)境下，通常會對MySQL進行很多增刪改等操作，此時可以通過Pos參數，指定查詢某個時間點之后的數據，命令如下所示：

結果如圖所示

如果該數據仍然十分龐大，則可以使用limit分頁參數，命令如下所示：

結果如圖所示。

在limit分頁參數中包含隱藏參數，即如果輸入為limit 1,2，則會讓該查詢語句先跳過一行，再輸出兩行結果，命令如下所示：

結果如圖所示。

復制二進制日志，并將它轉換成文本文件（.txt），命令如下所示：

通過cat /log.txt|grep ”drop ”命令可以正常查詢log.test中的二進制日志內容，結果如圖所示。