MySQL 8 複製（一）——非同步複製

目錄

一、MySQL非同步複製介紹

1. 複製的用途

2. 複製如何工作

3. 兩階段提交

二、複製實驗環境

三、安裝mysql-8.0.16

四、配置非同步複製

1. 空庫

2. 離線

3. 聯機

一、MySQL非同步複製介紹

        簡單說，複製就是將來自一個MySQL資料庫伺服器（主庫）的資料複製到一個或多個MySQL資料庫伺服器（從庫）。傳統的MySQL複製提供了一種簡單的Primary-Secondary複製方法，預設情況下，複製是單向非同步的。MySQL支援兩種複製方式：基於行的複製和基於語句的複製。這兩種方式都是通過在主庫上記錄二進位制日誌（binlog）、在從庫重放中繼日誌（relylog）的方式來實現非同步的資料複製。二進位制日誌或中繼日誌中的記錄被稱為事件。所謂非同步包含兩層含義，一是主庫的二進位制日誌寫入與將其傳送到從庫是非同步進行的，二是從庫獲取與重放日誌事件是非同步進行的。這意味著，在同一時間點從庫上的資料更新可能落後於主庫，並且無法保證主從之間的延遲間隔。

        複製給主庫增加的開銷主要體現在啟用二進位制日誌帶來的I/O，但是開銷並不大，MySQL官方文件中稱開啟二進位制日誌會產生1%的效能損耗。出於對歷史事務備份以及從介質失敗中恢復的目的，這點開銷是非常必要的。除此之外，每個從庫也會對主庫產生一些負載，例如網路和I/O開銷。當從庫讀取主庫的二進位制日誌時，可能會造成一定的I/O開銷。如果從一個主庫上覆制到多個從庫，喚醒多個複製執行緒傳送二進位制日誌內容的開銷將會累加。但所有這些複製帶來的額外開銷相對於應用對MySQL伺服器造成的高負載來說是很小的。

1. 複製的用途

（1）橫向擴充套件

        通過複製可以將讀操作指向從庫來獲得更好的讀擴充套件。所有寫入和更新都在主庫上進行，但讀取可能發生在一個或多個從庫上。在這種讀寫分離模型中，主庫專用於更新，顯然比同時進行讀寫操作會有更好的寫效能。需要注意的是，對於寫操作並不適合通過複製來擴充套件。在一主多從架構中，寫操作會被執行多次，這時整個系統的寫效能取決於寫入最慢的那部分。

（2）負載均衡

        通過MySQL複製可以將讀操作分不到多個伺服器上，實現對讀密集型應用的優化。對於小規模的應用，可以簡單地對機器名做硬編碼或者使用DNS輪詢（將一個機器名指向多個IP地址）。當然也可以使用複雜的方法，例如使用LVS網路負載均衡器等，能夠很好地將負載分配到不同的MySQL伺服器上。

（3）提高資料安全性

        提高資料安全性可以從兩方面來理解。其一，因為資料被複制到從庫，並且從庫可以暫停複製過程，所以可以在從庫上執行備份服務而不會影響相應的主庫。其二，當主庫出現問題，還有從庫的資料可以被訪問。但是，對備份來說，複製僅是一項有意義的技術補充，它既不是備份也不能夠取代備份。例如，當使用者誤刪除一個表，而且此操作已經在從庫上被複制執行，這種情況下只能用備份來恢復。

（4）提高高可用性

        複製能夠幫助應用程式避免MySQL單點失敗，一個包含複製的設計良好的故障切換系統能夠顯著縮短當機時間。

（5）滾動升級

        比較普遍的做法是，使用一個高版本MySQL作為從庫，保證在升級全部例項前，查詢能夠在從庫上按照預期執行。測試沒有問題後，將高版本的MySQL切換為主庫，並將應用連線至該主庫，然後重新搭建高版本的從庫。

2. 複製如何工作

        如前所述，MySQL複製依賴二進位制日誌，所以要理解複製如何工作，先要了解MySQL的二進位制日誌。

（1）二進位制日誌

        二進位制日誌包含描述資料庫更改的事件，如建表操作或對錶資料的更改等。開啟二進位制日誌有兩個重要目的：

用於複製。主庫上的二進位制日誌提供要傳送到從庫的資料更改記錄。主庫將其二進位制日誌中包含的事件傳送到從庫，從庫執行這些事件以對主伺服器上的資料進行相同的更改。

用於恢復。當出現介質錯誤（如磁碟故障）時，資料恢復操作需要使用二進位制日誌。還原備份後，將重新執行備份後記錄的二進位制日誌中的事件。

        不難看出，MySQL二進位制日誌所起的作用與Oracle的歸檔日誌類似。二進位制日誌只記錄更新資料的事件，不用於SELECT或SHOW等語句。通過設定log-bin系統變數開啟二進位制日誌，MySQL 8中預設是開啟的。

        二進位制日誌有STATEMENT、ROW、MIXED三種格式，通過binlog-format系統變數設定：

STATMENT格式，基於SQL語句的複製（statement-based replication，SBR）。每一條會修改資料的SQL語句會記錄到binlog中。這種格式的優點是不需要記錄每行的資料變化，這樣二進位制日誌會比較少，減少磁碟I/O，提高效能。缺點是在某些情況下會導致主庫與從庫中的資料不一致，例如last_insert_id()、now()等非確定性函式，以及使用者自定義函式（user-defined functions，udf）等易出現問題。

ROW格式，基於行的複製（row-based replication，RBR）。不記錄每一條SQL語句的上下文資訊，僅需記錄哪條資料被修改了，修改成了什麼樣子，能清楚記錄每一行資料的修改細節。其優點是不會出現某些特定情況下的儲存過程、函式或觸發器的呼叫和觸發無法被正確複製的問題。缺點是通常會產生大量的日誌，尤其像大表上執行alter table操作時候會讓日誌暴漲。

MIXED格式，混合複製（mixed-based replication，MBR）。是語句和行兩種格式的混合使用，預設使用STATEMENT模式儲存二進位制日誌，對於STATEMENT模式無法正確複製的操作，會自動切換到基於行的格式，MySQL會根據執行的SQL語句選擇日誌儲存方式。

        MySQL 8預設使用ROW格式。二進位制日誌的存放位置最好設定到與MySQL資料目錄不同的磁碟分割槽，以降低磁碟I/O的競爭，提升效能，並且在資料磁碟故障的時候還可以利用備份和二進位制日誌恢復資料。

（2）複製步驟

        總的來說，MySQL複製有五個步驟：

在主庫上把資料更改事件記錄到二進位制日誌中。

從庫上的I/O執行緒向主庫詢問二進位制日誌中的事件。

主庫上的binlog dump執行緒向I/O執行緒傳送二進位制事件。

從庫上的I/O執行緒將二進位制日誌事件複製到自己的中繼日誌中。

從庫上的SQL執行緒讀取中繼日誌中的事件，並將其重放到從庫上。

        圖1更詳細地描述了複製的細節。

圖1 複製如何工作

 

        第一步是在主庫上記錄二進位制日誌。在每次準備提交事務完成資料更新前，主庫將資料更新的事件記錄到二進位制日誌中。MySQL會按事務提交的順序而非每條語句的執行順序來記錄二進位制日誌。在記錄二進位制日誌後，主庫會告訴儲存引擎可以提交事務了。

        下一步，從庫將主庫的二進位制日誌複製到其本地的中繼日誌中。首先，從庫會啟動一個工作執行緒，稱為I/O執行緒，I/O執行緒跟主庫建立一個普通的客戶端連線，然後在主庫上啟動一個特殊的二進位制日誌轉儲（binlog dump）執行緒，它會讀取主庫上二進位制日誌中的事件，但不會對事件進行輪詢。如果該執行緒追趕上了主庫，它將進入睡眠狀態，直到主庫傳送訊號通知其有新的事件時才會被喚醒，從庫I/O執行緒會將接收到的事件記錄到中繼日誌中。

        從庫的SQL執行緒執行最後一步，該執行緒從中繼日誌中讀取事件並在從庫上執行，從而實現從庫資料的更新。當SQL執行緒追趕I/O執行緒時，中繼日誌通常已經在系統快取中，所以重放中繼日誌的開銷很低。SQL執行緒執行的事件也可以通過log_slave_updates系統變數來決定是否寫入其自己的二進位制日誌中，這可以用於級聯複製的場景。

        這種複製架構實現了獲取事件和重放事件的解耦，允許這兩個過程非同步進行。也就是說I/O執行緒能夠獨立於SQL執行緒之外工作。但這種架構也限制了複製的過程，其中最重要的一點是在主庫上併發更新的查詢在從庫上通常只能序列化執行，因為預設只有一個SQL執行緒來重放中繼日誌中的事件。在MySQL 5.6以後已經可以通過配置slave_parallel_workers等系統變數進行並行複製，在後面討論與複製相關的效能問題時會介紹並行複製的相關細節。

        現在我們已經瞭解了MySQL複製是以二進位制日誌為基礎的，但是像Innodb這樣的事務引擎有自己的事務日誌，如ib_logfile，這些事務日誌通常被稱為重做日誌（redo log）。作為背景知識，簡單介紹下Innodb重做日誌的作用。

        對Innodb的任何修改操作都會首先在稱為緩衝池（innodb buffer pool）的記憶體頁面上進行，然後這樣的頁面將被標記為髒頁，並被放到專門的重新整理列表上，後續將由master thread或專門的刷髒執行緒階段性的將這些頁面寫入磁碟。這樣的好處是避免每次寫操作都操作磁碟導致大量的隨機I/O，階段性的刷髒可以將多次對頁面的修改合併成一次I/O操作，同時非同步寫入也降低了訪問時延。然而，如果在髒頁還未刷入磁碟時，伺服器非正常關閉，這些修改操作將會丟失，如果寫入操作正在進行，甚至會由於損壞資料檔案導致資料庫不可用。為了避免上述問題的發生，Innodb將所有對頁面的修改操作寫入一個專門的檔案，並在資料庫啟動時從此檔案進行例項恢復操作，這個檔案就是重做日誌檔案。每當有更新操作時，在資料頁變更之前將操作寫入重做日誌，這樣當發生掉電之類的情況時系統可以在重啟後繼續操作。這就是所謂的預寫日誌（Write-ahead logging，WAL）。這樣的技術推遲了緩衝區頁面的重新整理，從而提升了資料庫的吞吐量。同時由於重做日誌的寫操作是順序I/O，相對於寫資料檔案的隨機I/O要快得多。大多數資料庫系統都採用類似的技術實現。

        聰明如你可能已經有了這樣的疑問，在複製中二進位制日誌和重做日誌如何協同工作？假設Innodb寫完重做日誌後，服務異常關閉。主庫能夠根據重做日誌恢復資料，但由於二進位制日誌沒寫入，會導致從庫同步時少了這個事務麼？或者反之，二進位制日誌寫成功，而重做日誌沒有寫完，是否導致從庫執行事務，而主庫不執行？這些情況會不會產生主從資料不一致的問題呢？解決這些問題是MySQL的核心需求，讓我們從MySQL基本架構說起。圖2是MySQL的邏輯結構圖。

圖2 MySQL伺服器邏輯架構圖

 

        最上層用於處理客戶端連線、授權認證、安全等等。第二層架構是MySQL伺服器層。大多數MySQL的核心服務功能都在這一層，包括查詢解析、分析、優化、快取以及所有內建函式，所有跨儲存引擎的功能（儲存過程、觸發器、檢視等）都在這一層實現。如你所料，二進位制日誌也在這一層實現。第三層包含了儲存引擎，負責MySQL中資料的儲存和提取。伺服器通過API與儲存引擎進行通訊，儲存引擎只是簡單地響應上層伺服器的請求。顯然Innodb的重做日誌在這一層實現。

        由於MySQL的事務日誌包含二進位制日誌和重做日誌，當發生崩潰恢復時，MySQL主庫通過重做日誌進行恢復，而在主從複製的環境下，從庫是依據於主節點的二進位制日誌進行同步資料的。這樣的架構對兩種日誌有兩個基本要求：第一，保證二進位制日誌裡面存在的事務一定在重做日誌裡面存在，也就是二進位制日誌裡不會比重做日誌多事務（可以少，因為重做日誌裡面記錄的事務可能有部分沒有提交，這些事務最終可能會被回滾）。第二，兩種日誌中事務的順序一致，這也是很重要的一點，假設兩者記錄的事務順序不一致，那麼會出現類似於主庫事務執行的順序是ta、tb、tc、td，但是二進位制日誌中記錄的是ta、tc、tb、td，被複制到從庫後導致主從資料不一致。為了達到這兩點要求，MySQL使用內部XA來實現（XA是eXtended Architecture的縮寫，是X/Open分散式事務定義的事務中介軟體與資料庫之間的介面規範），其核心是兩階段提交（two phase commit，2PC）。

3. 兩階段提交

        在兩階段提交協議中一般分為事務管理器（協調者）和若干事務執行者（參與者）兩種角色。在MySQL內部實現的兩階段提交中，二進位制日誌充當了協調者角色，由它來通知Innodb執行準備、提交或回滾步驟。從實現角度分析，提交流程和程式碼框架分別如圖3、圖4所示。

圖3 MySQL兩階段提交流程

 

圖4 commit命令的MySQL程式碼框架

 

（1）先呼叫binglog_hton和innobase_hton的prepare方法完成第一階段，binlog_hton的papare方法實際上什麼也沒做，innodb的prepare持有prepare_commit_mutex，將重做日誌刷磁碟，並將事務狀態設為TRX_PREPARED。

（2）如果事務涉及的所有儲存引擎的prepare都執行成功，則呼叫TC_LOG_BINLOG::log_xid將事務（STATEMENT格式或ROW格式）寫到二進位制日誌，此時，事務已經鐵定要提交了。否則，呼叫ha_rollback_trans回滾事務，而事務實際上也不會寫到二進位制日誌。

（3）最後，呼叫引擎的commit完成事務的提交。實際上binlog_hton->commit什麼也不會做（上一步已經將二進位制日誌寫入磁碟），innobase_hton->commit則清除回滾資訊，向重做日誌中寫入COMMIT標記，釋放prepare_commit_mutex，並將事務設為TRX_NOT_STARTED狀態。

        如果資料庫系統發生崩潰，當重啟資料庫時會進行崩潰恢復操作。具體到程式碼層面，Innodb在恢復的時候，不同狀態的事務，會進行不同的處理：

對於TRX_COMMITTED_IN_MEMORY的事務，清除回滾段，然後將事務設為TRX_NOT_STARTED；

對於TRX_NOT_STARTED的事務，表示事務已經提交，跳過；

對於TRX_PREPARED的事務，要根據二進位制日誌來決定事務是否提交，暫時跳過;

對於TRX_ACTIVE的事務，回滾。

        簡單來講，當發生崩潰恢復時，資料庫根據重做日誌進行資料恢復，逐個檢視每條重做條目的事務狀態，根據圖3的流程，如果已進行到TRX_NOT_STARTED階段，也就是儲存引擎commit階段，那麼說明重做日誌和二進位制日誌是一致的，正常根據重做條目進行恢復即可；事務狀態為TRX_ACTIVE，沒寫到二進位制日誌中，直接回滾；如果事務狀態為TRX_PREPARED，要分兩種情況，先檢查二進位制日誌是否已寫入成功，如果沒寫入成功，那麼就算是TRX_PREPARED狀態，也要回滾。如果寫入成功了，那麼就進行最後一步，呼叫儲存引擎commit，更改事務狀態為TRX_NOT_STARTED，也就是真正提交狀態，可以用作資料恢復。

        可見，MySQL是以二進位制日誌的寫入與否作為事務提交成功與否的標誌，通過這種方式讓Innodb重做日誌和MySQL伺服器的二進位制日誌中的事務狀態保持一致。兩階段提交很好的保持了資料一致性和事務順序性。

        瞭解了所有這些技術細節後，當初的疑問自然也就有了答案。假設在階段（1）結束之後程式異常，此時沒有寫入二進位制日誌，則從庫不會同步這個事務。主庫上，崩潰恢復時重做日誌中這個事務沒有trx_commit，因此會被回滾。邏輯上主從庫都不會執行這個事務。假設在階段（2）結束後程式異常，此時二進位制日誌已經寫入，則從庫會同步這個事務。主庫上，根據重做日誌能夠正常恢復此事務。也就是說，若二進位制日誌寫入完成，則主從庫都會正常完成事務，反之則主從庫都回滾事務，都不會出現主從不一致的問題。

        MySQL通過innodb_support_xa系統變數控制Innodb是否支援XA事務的2PC，預設是TRUE。如果關閉，則Innodb在prepare階段就什麼也不做，這可能會導致二進位制日誌的順序與Innodb提交的順序不一致，繼而導致在恢復時或者從庫上產生不同的資料。在MySQL 8中，innodb_support_xa系統變數已被移除，因為始終啟用Innodb對XA事務中兩階段提交的支援，不再讓使用者來選擇。

        上述的MySQL兩階段提交流程並不是天衣無縫的，主從資料是否一致還與重做日誌和二進位制日誌的寫盤方式有關。innodb_flush_log_at_trx_commit和sync_binlog系統變數分別控制兩者的落盤策略。

innodb_flush_log_at_trx_commit：有0、1、2三個可選值。0表示每秒進行一次重新整理，但是每次事務提交不進行任何操作（每秒呼叫fsync使資料落地到磁碟，不過這裡需要注意如果底層儲存有cache，比如raid cache，那麼這時也不會真正落盤，但是由於一般raid卡都帶有備用電源，所以一般都認為此時資料是安全的）。1代表每次事務提交都會進行重新整理，這是最安全的模式。2表示每秒重新整理，每次事務提交時不重新整理，而是呼叫write將重做日誌緩衝區裡面的內容刷到作業系統頁面快取。從資料安全性和效能比較三種策略的優劣為：1由於每次事務提交都會是重做日誌落盤，所以是最安全的，但是由於fsync的次數增多導致效能下降比較嚴重。0表示每秒重新整理，每次事務提交不進行任何操作，所以MySQL或作業系統崩潰時最多丟失一秒的事務。2相對於0來說了多了每次事務提交時的一個write操作，此時資料雖然沒有落磁，但是隻要沒有作業系統崩潰，即使MySQL崩潰，那麼事務也是不會丟失的。

sync_binlog：MySQL在提交事務時呼叫MYSQL_LOG::write完成寫二進位制日誌，並根據sync_binlog決定是否進行重新整理。預設值是0，即不重新整理，從而把控制權交給作業系統。如果設為1，則每次提交事務，就會進行一次磁碟重新整理。

        這兩個引數不同的值會帶來不同的效果。兩者都設定為1，資料最安全，能保證主從一致，這也是MySQL 8的預設設定。innodb_flush_log_at_trx_commit非1，假設在二進位制日誌寫入完成後系統崩潰，則可能出現這樣的情況：從庫能夠執行事務，但主庫中trx_prepare的日誌沒有被寫入到重做日誌中，導致主庫不執行事務，出現主從不一致的情況。同理若sync_binlog非1，可能導致二進位制日誌丟失（作業系統異常當機），從而與Innodb層面的資料不一致，體現在複製上，從庫可能丟失事務。在資料一致性要求很高的場景下，建議就使用預設的全1配置。

二、複製實驗環境

1. 主機IP

172.16.1.125（主）

172.16.1.126（從）

172.16.1.127（從）

2. 軟體環境

OS：CentOS Linux release 7.2.1511 (Core)

MySQL：MySQL Community Server 8.0.16

glibc：glibc-2.17-105.el7.x86_64

3. 硬體環境

三臺虛擬機器，每臺基本配置為：

. 雙核雙CPU，Intel(R) Xeon(R) CPU E5-2420 0 @ 1.90GHz

. 8G實體記憶體，8G Swap

. 100G物理硬碟

三、安裝mysql-8.0.16

        從https://dev.mysql.com/downloads/mysql/下載二進位制安裝檔案mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz，相關選項如圖5所示。

圖5 下載mysql-8.0.16安裝包

 

然後用root使用者按順序執行下面的命令，在三臺主機上安裝MySQL。

# 進入安裝目錄

cd /usr/local

# 從tar包中把提取檔案

tar xvf /home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz 

# 建立軟連線

ln -s mysql-8.0.16-linux-glibc2.12-x86_64 mysql

# 進入mysql目錄

cd mysql

# 建立secure_file_priv系統變數指向的目錄

mkdir mysql-files

# 修改屬主為mysql

chown mysql:mysql mysql-files

# 修改目錄許可權

chmod 750 mysql-files

# mysql系統初始化

bin/mysqld --initialize --user=mysql

# 建立SSL/RSA相關檔案，如果不啟用SSL連線，這步可省略

bin/mysql_ssl_rsa_setup

# 啟動mysql伺服器

bin/mysqld_safe --user=mysql &

# 連線mysql伺服器

bin/mysql -u root -p

-- 修改root密碼

alter user user() identified by "123456";

-- 建立一個新的mysql管理員賬號

create user 'wxy'@'%' identified with mysql_native_password by '123456';

grant all on *.* to 'wxy'@'%' with grant option;

        命令說明：

（1）mysql-files目錄用作secure_file_priv系統變數的值。該變數將匯入和匯出操作限制到特定目錄。例如由LOAD DATA和SELECT ... INTO OUTFILE語句和LOAD_FILE()函式所執行的操作。僅允許具有FILE許可權的使用者執行這些操作。secure_file_priv系統變數設定如下：

空字串：變數不起作用，是不安全的設定。

目錄名：mysql限制匯入和匯出操作僅用於該目錄中的檔案。目錄必須已經存在，mysql不會建立它。

NULL：mysql禁用匯入匯出操作。

（2）mysqld --initialize 命令建立預設資料庫並退出。在過程中會建立一個超級使用者，併為該使用者產生一個隨機密碼。命令執行輸出如下所示：

[root@hdp2/usr/local/mysql]#bin/mysqld --initialize --user=mysql

2019-05-05T06:31:58.956385Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) initializing of server in progress as process 10256

2019-05-05T06:32:01.287093Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: w1SN3pgRPL*D

2019-05-05T06:32:02.901171Z 0 [System] [MY-013170] [Server] /usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) initializing of server has completed

[root@hdp2/usr/local/mysql]#

        mysql文件中說產生的臨時密碼會寫到.err日誌檔案裡，但在本次安裝中，這步並不生成.err檔案。保險的做法還是記下臨時密碼，這點很重要。在initialize情況下，臨時密碼預設標記為已過期，使用者必須在第一次進入mysql後首先修改密碼。

（3）mysql_ssl_rsa_setup程式將建立SSL證照和金鑰檔案，使用SSL進行安全連線所需的RSA金鑰對檔案。如果現有的SSL檔案已過期，mysql_ssl_rsa_setup也可用於建立新的SSL檔案。

        mysql_ssl_rsa_setup使用openssl命令，因此主機上必須安裝有OpenSSL。mysql_ssl_rsa_setup檢查資料目錄中的以下SSL檔案：

ca.pem

server-cert.pem

server-key.pem

        如果存在任何這些檔案，則mysql_ssl_rsa_setup不會建立任何SSL檔案。否則，它會呼叫openssl來建立它們，以及一些其他檔案：

ca.pem               自簽名CA證照

ca-key.pem           CA私鑰

server-cert.pem      伺服器證照

server-key.pem       伺服器私鑰

client-cert.pem      客戶端證照

client-key.pem       客戶端私鑰

        在啟用SSL安全客戶端連線時需要這些檔案。

        之後mysql_ssl_rsa_setup檢查資料目錄中的以下RSA檔案：

private_key.pem      私鑰/公鑰對的私有成員

public_key.pem       私鑰/公鑰對的公共成員

        如果存在任何這些檔案，則mysql_ssl_rsa_setup不會建立任何RSA檔案。否則，它會呼叫openssl來建立它們。對於sha256_password或caching_sha2_password外掛驗證的帳戶不加密連線時，通過這些檔案使用RSA進行安全密碼交換。

        mysql 8.0.16預設支援SSL加密連線：

mysql> show variables like 'have_ssl';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| have_ssl      | YES   |

+---------------+-------+

1 row in set (0.00 sec)

（4）第一次進入mysql後，執行任何命令都會報以下錯誤：

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

        提示很明顯，需要修改初始化時為使用者'root'@'localhost'生成的臨時密碼。mysql 8預設使用的認證外掛是caching_sha2_password：

mysql> show variables like 'default_authentication_plugin';

+-------------------------------+-----------------------+

| Variable_name                 | Value                 |

+-------------------------------+-----------------------+

| default_authentication_plugin | caching_sha2_password |

+-------------------------------+-----------------------+

1 row in set (0.01 sec)

        當用老版本mysql的客戶端連線mysql 8伺服器時，可能報以下錯誤：

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /home/mysql/mysql-5.6.14/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

        可以使用兩個方法解決此問題。第一種方法是在配置檔案中設定default_authentication_plugin='mysql_native_password'，然後重啟mysql伺服器使之生效。default_authentication_plugin是隻讀系統變數，不能動態修改。第二種方法是在建立使用者時，使用相容新老版本的認證方式，例如：

create user 'wxy'@'%' identified with mysql_native_password by '123456';

        通過以下查詢可以得到使用者所對應的認證外掛：

mysql> select host,user,plugin from mysql.user;

+-----------+------------------+-----------------------+

| host      | user             | plugin                |

+-----------+------------------+-----------------------+

| %         | wxy              | mysql_native_password |

| localhost | mysql.infoschema | caching_sha2_password |

| localhost | mysql.session    | caching_sha2_password |

| localhost | mysql.sys        | caching_sha2_password |

| localhost | root             | caching_sha2_password |

+-----------+------------------+-----------------------+

5 rows in set (0.00 sec)

四、配置非同步複製

        本實驗中分別針對空庫、離線、聯機三種方式，配置一主兩從的mysql標準非同步複製。只做整伺服器級別的複製，不考慮對個別庫表或使用過濾複製的情況。

1. 空庫

        初始安裝後，mysql中還沒有任何應用資料。此時事先配置好複製，再投入使用，是最理想也是最簡單的一種情況，具體配置步驟如下。

（1）修改server_id系統變數。

-- 主庫

set global server_id=1125;

 

-- 從庫1

set global server_id=1126;

 

-- 從庫2

set global server_id=1127;

        要求複製中所有MySQL例項的server_id都不相同，這裡將三個例項的server_id分別配置為1125、1126、1127。server_id系統變數可以動態修改，這樣做的好處是不需要重啟例項，配置即可在新連線中生效。但是為了避免MySQL重啟後配置資訊丟失，還需要同時在/etc/my.cnf配置檔案中設定server_id引數。

（2）檢視主庫二進位制日誌資訊，

-- 主庫

show master status;

        因為是一個靜態的空庫，二進位制資訊此時不會發生變化，可以作為複製的起始點。本例中重啟過MySQL例項，輸出的資訊為：

+---------------+----------+--------------+------------------+-------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000003 |      155 |              |                  |                   |

+---------------+----------+--------------+------------------+-------------------+

（3）在主庫上建立複製專屬使用者

-- 主庫

create user 'repl'@'%' identified with mysql_native_password by '123456';

grant replication client,replication slave on *.* to 'repl'@'%';

        前面說過MySQL 8的使用者認證機制，這裡仍然使用mysql_native_password方式。

（4）在從庫建立主庫資訊。

change master to

master_host='172.16.1.125',

master_port=3306,

master_user='repl',

master_password='123456',

master_log_file='binlog.000003',

master_log_pos=155;

        連線主庫時，需要使用change master to提供連線到主庫的連線選項，包括主機地址、埠、使用者名稱、密碼、二進位制檔名、複製起始事件位置等。change master to後，在mysql.slave_master_info表中就會生成一條記錄，此行為是由master_info_repository系統變數控制的。MySQL 8中，該變數的預設值為TABLE，即將與複製相關的主庫資訊記錄到mysql.slave_master_info表中。隨著複製的進行，表中的資料會隨之更新。change master to只是為I/O執行緒連線主庫時提供連線引數，這條語句本身不會連線主庫。以後啟動I/O執行緒時，I/O執行緒都會自動讀取這條記錄來連線主庫，不需要再執行change master to語句。

        類似地，MySQL 8預設會將中繼日誌的重放資訊存到mysql.slave_relay_log_info表中。該行為由系統變數relay_log_info_repository控制。中繼日誌資訊在首次啟動複製時生成，並隨複製即時改變。SQL執行緒再次啟動時就能從中獲取到從中繼日誌的的哪個地方繼續讀取、執行。

（5）在從庫啟動複製並檢視複製資訊

start slave;

show slave status\G

select * from mysql.user where user='repl'\G

        剛才我們並沒有在從庫上建立repl使用者，但由於create user語句是在起始位置點後執行的，因此可以正常複製到從庫，查詢mysql.user表即可確認。       

        start slave語句會啟動I/O執行緒和SQL執行緒，並建立一個到主庫的客戶端連線。該命令執行後，在主庫的processlist中會看到類似如下的執行緒，這些就是從庫上I/O執行緒所建立的，Binlog Dump表示由I/O執行緒在主庫上啟動了Binlog Dump執行緒，每個連線上來的從庫對應一個執行緒，如Id 32和56是兩個從庫的連線執行緒：

mysql> show processlist;

+----+-----------------+------------+------+-------------+--------+---------------------------------------------------------------+------------------+

| Id | User            | Host       | db   | Command     | Time   | State                                                         | Info             |

+----+-----------------+------------+------+-------------+--------+---------------------------------------------------------------+------------------+

  ...

| 32 | repl            | hdp4:3723  | NULL | Binlog Dump | 328879 | Master has sent all binlog to slave; waiting for more updates | NULL             |

| 56 | repl            | hdp3:57308 | NULL | Binlog Dump | 319204 | Master has sent all binlog to slave; waiting for more updates | NULL             |

  ...

        從庫上的processlist中會看到類似如下的執行緒，Id 325和326分別對應I/O執行緒與SQL執行緒：

mysql> show processlist;

+--------+-----------------+-----------+-------+---------+--------+--------------------------------------------------------+------------------+

| Id     | User            | Host      | db    | Command | Time   | State                                                  | Info             |

+--------+-----------------+-----------+-------+---------+--------+--------------------------------------------------------+------------------+

  ...

|    325 | system user     |           | NULL  | Connect | 320408 | Waiting for master to send event                       | NULL             |

|    326 | system user     |           | NULL  | Query   | 320408 | Slave has read all relay log; waiting for more updates | NULL             |

  ...

        從show slave status輸出中可以檢視複製狀態資訊：

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.16.1.125

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: binlog.000011

          Read_Master_Log_Pos: 155

               Relay_Log_File: hdp3-relay-bin.000002

                Relay_Log_Pos: 319

        Relay_Master_Log_File: binlog.000011

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: 

           Replicate_Do_Table: 

       Replicate_Ignore_Table: 

      Replicate_Wild_Do_Table: 

  Replicate_Wild_Ignore_Table: 

                   Last_Errno: 0

                   Last_Error: 

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 155

              Relay_Log_Space: 526

              Until_Condition: None

               Until_Log_File: 

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File: 

           Master_SSL_CA_Path: 

              Master_SSL_Cert: 

            Master_SSL_Cipher: 

               Master_SSL_Key: 

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1125

                  Master_UUID: 8eed0f5b-6f9b-11e9-94a9-005056a57a4e

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: 

                Auto_Position: 0

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

       Master_public_key_path: 

        Get_master_public_key: 0

            Network_Namespace: 

1 row in set (0.00 sec)

 

mysql> 

        在從庫上執行show slave status可以檢視從庫狀態，輸出資訊非常多，其中除了那些描述I/O執行緒、SQL執行緒狀態的行，還有幾個log_file和pos相關的行。理解這幾行的意義至關重要，所以這裡完整地描述它們：

Master_Log_File：I/O執行緒正在讀取的master binlog；

Read_Master_Log_Pos：I/O執行緒已經讀取到master binlog的哪個位置；

Relay_Log_File：SQL執行緒正在讀取和執行的relay log；

Relay_Log_Pos：SQL執行緒已經讀取和執行到relay log的哪個位置；

Relay_Master_Log_File：SQL執行緒最近執行的操作對應的是哪個master binlog；

Exec_Master_Log_Pos：SQL執行緒最近執行的操作對應的是master binlog的哪個位置。

        （Relay_Master_Log_File, Exec_Master_log_Pos）構成一個座標，這個座標表示從庫上已經將主庫上的哪些資料重放到自己的例項中，它可以用於下一次change master to時指定的二進位制日誌座標。與這個座標相對應的是從庫上SQL執行緒的中繼日誌座標（Relay_Log_File, Relay_Log_Pos）。這兩個座標位置不同，但它們對應的資料是一致的。

        還有一個延遲引數Seconds_Behind_Master需要說明一下，它的本質意義是SQL執行緒比I/O執行緒慢多少。如果主從之間的網路狀況優良，那麼從庫的I/O執行緒讀速度和主庫寫二進位制日誌的速度基本一致，所以這個引數也用來描述“SQL執行緒比主庫慢多少”，也就是說從庫比主庫少多少資料，只不過衡量的單位是秒。需要注意的是，該引數的描述並不標準，只是在網速很好的時候做個大概估計，很多種情況下它的值都是0，即使SQL執行緒比I/O執行緒慢了很多也是如此。

2. 離線

        如果資料庫已經存在應用資料，但允許一個可接受的離線時間視窗做複製，這種場景下常用的做法是先直接將主庫的資料目錄整體拷貝到從庫，再啟動複製。具體步驟如下。

（1）在主庫上建立複製專屬使用者

-- 主庫

create user 'repl'@'%' identified with mysql_native_password by '123456';

grant replication client,replication slave on *.* to 'repl'@'%';

（2）停掉複製涉及的例項

mysqladmin -uroot -p123456 shutdown

        本例中一主兩從三個例項都停止。

（3）複製將主庫的資料目錄整體拷貝到從庫

scp -r /usr/local/mysql/data/ 172.16.1.126:/usr/local/mysql

（4）保證所有參與複製例項的server-uuid和server_id都不同

        這是非常重要的一步，相同的server-uuid或server_id會造成複製錯誤。從MySQL 5.6開始，用 128 位的 server_uuid 代替了原本的 32 位 server_id 的大部分功能。原因很簡單，server_id 依賴於 my.cnf 的手工配置，有可能產生衝突。而自動產生 128 位 uuid 的演算法可以保證所有的 MySQL uuid 都不會衝突。首次啟動時 MySQL 會呼叫 generate_server_uuid() 自動生成一個 server_uuid，並且儲存到 auto.cnf 檔案。這個檔案目前存在的唯一目的就是儲存 server_uuid。在 MySQL 再次啟動時會讀取 auto.cnf 檔案，繼續使用上次生成的 server_uuid。使用 show global variables like 'server_uuid' 命令可以檢視 MySQL 例項當前使用的 server_uuid，它是一個全域性只讀變數。全域性唯一的 server_uuid 的一個好處是，可以解決由 server_id 配置衝突帶來的 MySQL 主從複製的異常終止。在從庫向主庫申請二進位制日誌時，會首先傳送自己的 server_uuid，主庫用從庫傳送的 server_uuid 代替 server_id 作為 kill_zombie_dump_threads 的引數，終止衝突或者僵死的 BINLOG_DUMP 執行緒。

        刪除data_dir/auto.cnf檔案，例項啟動時會自動生成server_uuid的值。本例中刪除從庫的/usr/local/mysql/data/auto.cnf檔案，並編輯/etc/my.cnf檔案修改server_id，保證三個例項的配置互不相同。

（5）重啟例項

mysqld_safe --user=mysql &

        本例中一主兩從三個例項都啟動。

（6）檢視主庫二進位制日誌資訊，

-- 主庫

show master status;

（7）使用上一步的輸出在從庫建立主庫資訊。

change master to

master_host='172.16.1.125',

master_port=3306,

master_user='repl',

master_password='123456',

master_log_file='binlog.000004',

master_log_pos=155;

（5）在從庫啟動複製並檢視複製資訊

start slave;

show slave status\G

        此時在從庫的err日誌檔案中可以看到複製正常啟動的資訊：

[mysql@hdp3~]$tail /usr/local/mysql/data/hdp3.err 

2019-05-10T02:54:22.226249Z mysqld_safe Logging to '/usr/local/mysql/data/hdp3.err'.

2019-05-10T02:54:22.277620Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

2019-05-10T02:54:22.710162Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.16) starting as process 20571

2019-05-10T02:54:23.370413Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2019-05-10T02:54:23.420160Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.16'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.

2019-05-10T02:54:23.547717Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060

2019-05-10T02:56:17.328454Z 144 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=hdp3-relay-bin' to avoid this problem.

2019-05-10T02:56:17.333635Z 144 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='172.16.1.125', master_port= 3306, master_log_file='binlog.000004', master_log_pos= 155, master_bind=''.

2019-05-10T02:56:25.235523Z 171 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2019-05-10T02:56:25.237851Z 171 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'repl@172.16.1.125:3306',replication started in log 'binlog.000004' at position 155

3. 聯機

        離線建立複製的需求太過理想化，大多數情況下，複製是被要求在不影響線上業務的情況下，聯機建立的，而且還要求對線上庫的影響越小越好。例如，複製過程化中對主庫加鎖會影響對主庫的訪問，因此通常是不被允許的。這種場景下有兩種備選的複製方案：使用mysqldump程式或使用如XtraBackup的第三方工具。這兩種方案有各自的適用場合。使用mysqldump聯機建立複製的過程如下。

（1）在主庫上建立複製專屬使用者

-- 主庫

create user 'repl'@'%' identified with mysql_native_password by '123456';

grant replication client,replication slave on *.* to 'repl'@'%';

（2）在從庫上建立主庫資訊

change master to

master_host='172.16.1.125',

master_port=3306,

master_user='repl',

master_password='123456';

        注意在上面這條命令中並沒有指定主庫二進位制檔案的檔名和位置。

（3）在從庫用mysqldump建立複製

mysqldump --single-transaction --all-databases --master-data=1 --host=172.16.1.125 --user=wxy --password=123456 --apply-slave-statements | mysql -uroot -p123456 -h127.0.0.1

        說明：

--single-transaction引數可以對Innodb表執行非鎖定匯出。此選項將事務隔離模式設定為REPEATABLE READ，並在轉儲資料之前向伺服器傳送START TRANSACTION SQL語句。它僅適用於Innodb等事務表，因為它會在發出START TRANSACTION時轉儲資料庫的一致狀態，而不會阻塞任何應用程式。因此這裡假定：1. 所有的應用資料表都使用Innodb引擎。2. 所有系統表資料在備份過程中不會發生變化。

--master-data引數會導致轉儲輸出包含類似 CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=1480; 的SQL語句，該語句指示主庫的二進位制日誌座標（檔名和位置）。如果選項值為2，則CHANGE MASTER TO語句將寫為SQL註釋，因此僅提供資訊，不會執行。如果引數值為1，則該語句不會寫為註釋，並在重新載入轉儲檔案時執行。如果未指定選項值，則預設值為1。

--apply-slave-statements引數會在CHANGE MASTER TO語句之前新增STOP SLAVE語句，並在輸出結尾處新增START SLAVE語句，用來自動開啟複製。

通過管道操作符，匯出匯入一步進行，不需要中間落盤生成檔案。

（4）確認複製狀態

-- 從庫

show slave status\G

        mysqldump方式的優點是可以進行部分複製，如在配置檔案中定義replicate-do-table=db1.*，則用這種方法可以只複製db1庫而忽略其它複製事件。缺點是由於mysqldump會生成主庫轉儲資料的SQL語句，實際是一種邏輯備份方式所以速度較慢，不適用於大庫。

        聯機建立複製的另一種可選方案是使用XtraBackup。XtraBackup是Percona公司的開源專案，用以實現類似Innodb官方的熱備份工具InnoDB Hot Backup的功能，它支援線上熱備份，備份時不影響資料讀寫。到目前為止，最新的版本為Percona XtraBackup 8.0.6，可以從https://www.percona.com/downloads/下載安裝包。XtraBackup有很多功能和優點，例如支援全備、增量備份、部分備份；支援壓縮備份；備份不影響資料讀寫、事務等，但是也有缺陷不足：例如不支援離線備份、不支援直接備份到磁帶裝置、不支援Cloud Back，MyISAM的備份也會阻塞。不過這些小瑕疵不影響XtraBackup成為一款流行的MySQL備份工具。另外，注意XtraBackup只支援Linux平臺，不支援Windows平臺。下面演示用XtraBackup聯機搭建主從複製的過程，主庫已經建立了用於執行復制的使用者repl。

（1）在主、從庫安裝XtraBackup

# 安裝依賴包

yum -y install libev

 

# 安裝XtraBackup

rpm -ivh percona-xtrabackup-80-8.0.6-1.el7.x86_64.rpm

（2）配置主庫到從庫的SSH免密碼連線

# 主庫執行

ssh-keygen    

... 一路回車 ...    

ssh-copy-id 172.16.1.126

（3）停止從庫，並清空從庫的資料目錄

# 從庫執行

mysqladmin -u root -p123456 shutdown

 

# 清空資料目錄

rm -rf /usr/local/mysql/data/*

（3）備份並傳輸

# 主庫執行

xtrabackup -uroot -p123456 --socket=/tmp/mysql.sock --no-lock --backup --compress --stream=xbstream --parallel=4 --target-dir=./ | ssh mysql@172.16.1.126 "xbstream -x -C /usr/local/mysql/data/ --decompress"

        這條命令連線主庫，進行並行壓縮流式備份，同時將備份通過管道操作符傳輸到從庫，並直接解壓縮到從庫的資料目錄。所有操作一條命令完成，不需要中間落盤生成檔案。

（4）在從庫恢復備份

# 應用日誌

xtrabackup --prepare --target-dir=/usr/local/mysql/data/

（5）檢視二進位制日誌座標

cat /usr/local/mysql/data/xtrabackup_binlog_info

（6）啟動從庫

mysqld_safe --user=mysql &

（7）登入從庫啟動複製

-- 建立主庫資訊，其中的master_log_file和master_log_pos值來自第（5）步

change master to

master_host='172.16.1.125',

master_port=3306,

master_user='repl',

master_password='123456',

master_log_file='binlog.000011',

master_log_pos=155;

 

-- 啟動複製

start slave;

 

-- 確認複製狀態

show slave status\G

        XtraBackup是物理複製，效能比mysqldump高的多，而且對主庫的影響極小，非常適用於從頭聯機建立高負載、大資料量、全例項從庫的場景。

————————————————

版權宣告：本文為CSDN博主「wzy0623」的原創文章，遵循 CC 4.0 BY-SA 版權協議，轉載請附上原文出處連結及本宣告。

原文連結： https://blog.csdn.net/wzy0623/article/details/90081518

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ xiaomaimiaolhr）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群號： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由 ● 於 2020-02-01 06:00 ~ 2020-02-31 24:00 在西安完成 ● 最新修改時間：2020-02-01 06:00 ~ 2020-02-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2675261/，如需轉載，請註明出處，否則將追究法律責任。