問題描述

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

（1）在生產(chǎn)環(huán)境中，當(dāng)MySQL處于運(yùn)行狀態(tài)時(shí)應(yīng)如何備份當(dāng)前數(shù)據(jù)？

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

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

問題分析與解決方案

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

MySQL主從復(fù)制原理

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

主服務(wù)器也叫作master服務(wù)器。當(dāng)主服務(wù)器上的數(shù)據(jù)發(fā)生改變時(shí)，主服務(wù)器會(huì)將數(shù)據(jù)的更改記錄存儲(chǔ)在二進(jìn)制日志中。

從服務(wù)器也叫作slave服務(wù)器。從服務(wù)器會(huì)定期對(duì)主服務(wù)器上的二進(jìn)制日志進(jìn)行探測(cè)，觀測(cè)其是否發(fā)生了改變。如果主服務(wù)器上的數(shù)據(jù)發(fā)生了改變，則從服務(wù)器會(huì)啟動(dòng)一個(gè)I/O線程，請(qǐng)求更新數(shù)據(jù)，具體過程如下所示：

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

1. 部署過程中需要注意的事項(xiàng)

（1）主服務(wù)器和從服務(wù)器中的MySQL版本必須相同，否則可能出現(xiàn)未知的異常與錯(cuò)誤

（2）主服務(wù)器和從服務(wù)器的時(shí)間必須同步，否則兩個(gè)線程的時(shí)間節(jié)點(diǎn)可能對(duì)不上，導(dǎo)致同步數(shù)據(jù)失敗。

（3）在MySQL中，一般最少包含兩個(gè)從服務(wù)器。當(dāng)主服務(wù)器與從服務(wù)器的數(shù)據(jù)不同時(shí)，可以與第三方進(jìn)行參照。

2. MySQL主從復(fù)制的架構(gòu)拓?fù)?/p>

（1）一主一從：一主一從指一臺(tái)服務(wù)器作為主服務(wù)器（M），另一臺(tái)服務(wù)器作為從服務(wù)器（S）。主服務(wù)器負(fù)責(zé)寫入或讀取數(shù)據(jù)，從服務(wù)器只負(fù)責(zé)讀取數(shù)據(jù)，并且從服務(wù)器會(huì)從主服務(wù)器上下載數(shù)據(jù)。一主一從使用場(chǎng)景較為有限，更多的時(shí)候是使用一主多從的形式，即主從復(fù)制至少由三臺(tái)服務(wù)器組成（一臺(tái)主服務(wù)器和兩臺(tái)從服務(wù)器）。當(dāng)一臺(tái)服務(wù)器的數(shù)據(jù)出現(xiàn)異常時(shí)，可以參考其他服務(wù)器上的數(shù)據(jù)。

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

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

（4）多主一從：多主一從適用于寫入較多，但讀取較少的場(chǎng)景，即由不同的主服務(wù)器進(jìn)行寫入，只由一臺(tái)從服務(wù)器進(jìn)行讀取。

（5）聯(lián)級(jí)復(fù)制：聯(lián)級(jí)復(fù)制指master A slave B slave C的架構(gòu)方式，slaveB和slaveC，會(huì)替換掉之前舊的masterA。同時(shí)，slaveB和slaveC是新的主從關(guān)系，因此，配置成聯(lián)級(jí)復(fù)制來遷移數(shù)據(jù)，另外也方便切換。架構(gòu)圖如下所示。

深入理解MySQL中的二進(jìn)制日志

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

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

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

結(jié)果如圖

模糊查詢命令如下：

結(jié)果如圖

log_bin和sql_log_bin的區(qū)別

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

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

開啟二進(jìn)制日志

查看MySQL的配置文件/etc/my.cnf，看看是否有與二進(jìn)制日志有關(guān)的配置：

如果沒有，則在/etc/my.cnf的[mysqld]選項(xiàng)中追加以下內(nèi)容：

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

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

（1）MySQL的同步數(shù)據(jù)中是包含server-id的，用于標(biāo)識(shí)該語句最初是從哪個(gè)server寫入的，所以server-id一定要有。

（2）每一個(gè)同步的slave在master上都有對(duì)應(yīng)的一個(gè)master線程，該線程就是通過slave的server-id來標(biāo)識(shí)的。

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

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

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

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

log-bin：打開二進(jìn)制日志功能。在復(fù)制（replication）配置中，master必須打開此項(xiàng)。

binlog-format：二進(jìn)制日志的模式與配置。在MySQL中，復(fù)制二進(jìn)制日志的方式主要有三種：

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

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

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

除此之外，還可以對(duì)二進(jìn)制日志進(jìn)行以下配置：

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

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

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

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

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

結(jié)果如圖所示。

重新查看log_bin啟動(dòng)結(jié)果，如圖所示。

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

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

每次重啟MySQL服務(wù)器都會(huì)生成一個(gè)新的二進(jìn)制日志文件，相當(dāng)于對(duì)二進(jìn)制日志進(jìn)行了切換。在切換二進(jìn)制日志時(shí)，會(huì)看到mysql-bin文件的number在不斷遞增。

除二進(jìn)制日志文件外，還生成了一個(gè).index文件。這個(gè)文件中存儲(chǔ)了所有二進(jìn)制日志文件的清單，又稱為二進(jìn)制文件的索引。

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

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

結(jié)果如圖所示。

也可以輸入如下命令進(jìn)行查看：

該命令等價(jià)于show binary logs;命令，結(jié)果如圖所示

查看當(dāng)前二進(jìn)制文件狀態(tài)的命令如下所示，結(jié)果如圖所示。

刪除某個(gè)日志之前的所有二進(jìn)制日志文件

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

結(jié)果如圖所示。

當(dāng)通過purge命令刪除mysql-bin.000002之前的所有二進(jìn)制日志文件時(shí)，該刪除操作會(huì)影響二進(jìn)制日志文件的索引部分的內(nèi)容，命令如下所示：

結(jié)果如圖所示。

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

結(jié)果如圖所示。

刪除某個(gè)時(shí)間點(diǎn)以前的二進(jìn)制日志文件

刪除某個(gè)時(shí)間點(diǎn)以前的二進(jìn)制日志文件的命令如下所示：

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

刪除所有的二進(jìn)制日志文件

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

結(jié)果如圖所示。

查看二進(jìn)制日志文件內(nèi)容

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

在MySQL的命令行中讀取相關(guān)的二進(jìn)制日志文件，命令如下所示，結(jié)果如圖所示。

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

再次查看二進(jìn)制日志，命令如下所示：

結(jié)果如圖所示。

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

結(jié)果如圖所示。

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

結(jié)果如圖所示。

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

結(jié)果如圖所示

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

結(jié)果如圖所示。

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

結(jié)果如圖所示。

復(fù)制二進(jìn)制日志，并將它轉(zhuǎn)換成文本文件（.txt），命令如下所示：

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