MySQL show slave status命令參數

標籤：

? Slave_IO_State

SHOW PROCESSLIST輸出的State欄位的拷貝。SHOW PROCESSLIST用於從屬I/O線程。如果線程正在試圖串連到主伺服器，正在等待來自主伺服器的時間或正在串連到主伺服器等，本語句會通知您

? Master_User

被用於串連主伺服器的目前使用者。

? Master_Port

當前的主伺服器介面。

? Connect_Retry

–master-connect-retry選項的當前值

? Master_Log_File

I/O線程當前正在讀取的主伺服器二進位記錄檔的名稱。

? Read_Master_Log_Pos

在當前的主伺服器二進位日誌中，I/O線程已經讀取的位置。

? Relay_Log_File

SQL線程當前正在讀取和執行的中繼記錄檔的名稱。

? Relay_Log_Pos

在當前的中繼日誌中，SQL線程已讀取和執行的位置。

? Relay_Master_Log_File

由SQL線程執行的包含多數近期事件的主伺服器二進位記錄檔的名稱。

? Slave_IO_Running

I/O線程是否被啟動並成功地串連到主伺服器上。

? Slave_SQL_Running

SQL線程是否被啟動。

? Replicate_Do_DB,Replicate_Ignore_DB

使用–replicate-do-db和–replicate-ignore-db選項指定的資料庫清單。

? Replicate_Do_Table,Replicate_Ignore_Table,Replicate_Wild_Do_Table,Replicate_Wild_Ignore_Table

使用–replicate-do-table,–replicate-ignore-table,–replicate-wild-do-table和–replicate-wild-ignore_table選項指定的表清單。

? Last_Errno,Last_Error

被多數最近被執行的查詢返回的錯誤數量和錯誤訊息。錯誤數量為0並且訊息為空白字串意味著“沒有錯誤”。如果Last_Error值不是空值，它也會在從屬伺服器的錯誤記錄檔中作為訊息顯示。

舉例說明：

Last_Errno: 1051

Last_Error: error ‘Unknown table ‘z” on query ‘drop table z’

該訊息指示，表z曾經存在於在主伺服器中並已被取消了，但是它沒有在從屬伺服器中存在過，因此對於從屬伺服器，DROP TABLE失敗。（舉例說明，在設定複製時，如果您忘記了把此表拷貝到從屬伺服器中，則這有可能發生。）

? Skip_Counter

最近被使用的用於SQL_SLAVE_SKIP_COUNTER的值。

? Exec_Master_Log_Pos

來 自主伺服器的二進位日誌的由SQL線程執行的上一個時間的位置（Relay_Master_Log_File）。在主伺服器的二進位日誌中的 (Relay_Master_Log_File, Exec_Master_Log_Pos)對應於在中繼日誌中的(Relay_Log_File,Relay_Log_Pos)。

? Relay_Log_Space

所有原有的中繼日誌結合起來的總大小。

? Until_Condition,Until_Log_File,Until_Log_Pos

在START SLAVE語句的UNTIL子句中指定的值。

Until_Condition具有以下值：

o 如果沒有指定UNTIL子句，則沒有值

o 如果從屬伺服器正在讀取，直到達到主伺服器的二進位日誌的給定位置為止，則值為Master

o 如果從屬伺服器正在讀取，直到達到其中繼日誌的給定位置為止，則值為Relay

Until_Log_File和Until_Log_Pos用於指示記錄檔名和位置值。記錄檔名和位置值定義了SQL線程在哪個點中止執行。

? Master_SSL_Allowed,Master_SSL_CA_File,Master_SSL_CA_Path,Master_SSL_Cert,Master_SSL_Cipher,Master_SSL_Key

這些欄位顯示了被從屬伺服器使用的參數。這些參數用於串連主伺服器。

Master_SSL_Allowed具有以下值：

o 如果允許對主伺服器進行SSL串連，則值為Yes

o 如果不允許對主伺服器進行SSL串連，則值為No

o 如果允許SSL串連，但是從屬伺服器沒有讓SSL支援被啟用，則值為Ignored。

與SSL有關的欄位的值對應於–master-ca,–master-capath,–master-cert,–master-cipher和–master-key選項的值。

? Seconds_Behind_Master

本 欄位是從屬伺服器“落後”多少的一個指示。當從屬SQL線程正在運行時（處理更新），本欄位為在主伺服器上由此線程執行的最近的一個事件的時間標記開始， 已經過的秒數。當此線程被從屬伺服器I/O線程趕上，並進入閑置狀態，等待來自I/O線程的更多的事件時，本欄位為零。總之，本欄位測量從屬伺服器SQL 線程和從屬伺服器I/O線程之間的時間差距，單位以秒計。

如果主伺服器和從屬伺服器之間的網路連接較快，則從屬伺服器I/O線程會非常 接近主伺服器，所以本欄位能夠十分近似地指示，從屬伺服器SQL線程比主伺服器落後多少。如果網路較慢，則這種指示不準確；從屬SQL線程經常會趕上讀取 速度較慢地從屬伺服器I/O線程，因此，Seconds_Behind_Master經常顯示值為0。即使I/O線程落後於主伺服器時，也是如此。換句話 說，本列只對速度快的網路有用。

即使主伺服器和從屬伺服器不具有相同的時鐘，時間差計算也會起作用（當從屬伺服器I/O線程啟動時，計 算時間差。並假定從此時以後，時間差保持不變）。如果從屬SQL線程不運行，或者如果從屬伺服器I/O線程不運行或未與主伺服器串連，則 Seconds_Behind_Master為NULL（意義為“未知”）。舉例說明，如果在重新串連之前，從屬伺服器I/O線程休眠了master- connect-retry秒，則顯示NULL，因為從屬伺服器不知道主伺服器正在做什麼，也不能有把握地說落後多少

 

MySQL複製的概述、安裝、故障、技巧、工具

同MongoDB，Redis這樣的NoSQL資料庫的複製相比，MySQL複製顯得相當複雜！接下來就是詳細的介紹，相信下文中的內容對大家掌握MySQL資料庫的複製是非常有協助的。

概述

首先主伺服器把資料變化記錄到主日誌，然後從伺服器通過I/O線程讀取主伺服器上的主日誌，並且把它寫入到從伺服器的中繼日誌中，接著SQL線程讀取中繼日誌，並且在從伺服器上重放，從而實現MySQL複製。具體如所示：

MySQL複製

整個過程反映到從伺服器上，對應三套日誌資訊，可在從伺服器上用如下命令查看：

mysql> SHOW SLAVE STATUS;

Master_Log_File & Read_Master_Log_Pos：下一個傳輸的主日誌資訊。

Relay_Master_Log_File & Exec_Master_Log_Pos：下一個執行的主日誌資訊。

Relay_Log_File & Relay_Log_Pos：下一個執行的中繼日誌資訊。

理解這些日誌資訊的含義對於解決故障至關重要，後文會詳細闡述。

安裝

先在主伺服器上建立複製帳號：

mysql> GRANT REPLICATION SLAVE ON *.*

TO ‘<SLAVE_USER>‘@‘<SLAVE_HOST>‘

IDENTIFIED BY ‘<SLAVE_PASSWORD>‘;

註：出於安全性和靈活性的考慮，不要把root等具有SUPER許可權使用者作為複製帳號。

然後設定主伺服器設定檔（預設：/etc/my.cnf）：

[mysqld]

server_id = 100

log_bin = mysql-bin

log_bin_index = mysql-bin.index

sync_binlog = 1

innodb_flush_log_at_trx_commit = 1

innodb_support_xa = 1

註：一定要保證主從伺服器各自的server_id唯一，避免衝突。

註：如果沒有指定log_bin的話，預設會使用主機名稱作為名字，如此一來一旦主機名稱發生改變，就會出問題，所以推薦指定log_bin（從伺服器的relay_log存在一樣的問題）。

註：sync_binlog，innodb_flush_log_at_trx_commit，innodb_support_xa三個選項都是出於安全目的設定的，不是複製的必須選項。

接著設定從伺服器設定檔（預設：/etc/my.cnf）：

[mysqld]

server_id = 200

log_bin = mysql-bin

log_bin_index = mysql-bin.index

relay_log = mysql-relay-bin

relay_log_index = mysql-relay-bin.index

read_only = 1

skip_slave_start = 1

log_slave_updates = 1

註：如果使用者有SUPER許可權，則read_only無效。

註：有了skip_slave_start，除非使用START SLAVE命令，否則從伺服器不會開始複製。

註：設定log_slave_updates，讓從伺服器記錄日誌，有助於在必要時把從切換成主。

下面最重要的步驟是如何複製一份主伺服器的資料：

如果資料庫使用的是MyISAM表類型的話，可按如下方式操作：

shell> mysqldump --all-databases --master-data=1 > data.sql

註：master-data選項預設會開啟lock-all-tables，並寫入CHANGE MASTER TO語句。

如果資料庫使用的是InnoDB表類型的話，則應該使用single-transcation：

shell> mysqldump --all-databases --single-transaction --master-data=1 > data.sql

有了資料檔案，傳輸到從伺服器上並匯入：

shell> mysql < data.sql

如果資料量很大的話，mysqldump會非常慢，此時直接拷貝資料檔案能節省不少時間：

在拷貝之前要先鎖定資料，然後再獲得相關的日誌資訊（FILE & POSITION）：

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

接下來拷貝資料檔案時，如果是MyISAM表類型的話，直接拷貝即可；如果是InnoDB表類型的話，一定要先停止MySQL服務再拷貝，否則拷貝檔案可能無法使用。把拷貝的資料檔案直接複製到從伺服器的資料目錄。

最後還需要再指定一下日誌資訊：

mysql> CHANGE MASTER TO

MASTER_HOST=‘<MASTER_HOST>‘,

MASTER_USER=‘<SLAVE_USER>‘,

MASTER_PASSWORD=‘<SLAVE_PASSWORD>‘,

MASTER_LOG_FILE=‘<FILE>‘,

MASTER_LOG_POS=<POSITION>;

註：不要在my.cnf設定檔裡設定MASTER_USER和MASTER_PASSWORD，因為最終生效的是CHANGE MASTER TO產生的master.info檔案裡的資訊。

在主伺服器上直接拷貝資料檔案雖然很快，但需要鎖表或者停止服務，這會影響線上服務。如果先前已經有了從伺服器，那麼可以用舊的從伺服器做母本來複製新的從伺服器：

先在舊的從伺服器上查詢日誌資訊：

mysql> SHOW SLAVE STATUS;

我們需要的是其中的Relay_Master_Log_File & Exec_Master_Log_Pos。

然後在舊的從伺服器上按照前面的方法得到資料，並在新的從伺服器上還原。

接著在新的從伺服器上設定日誌資訊：

mysql> CHANGE MASTER TO

MASTER_HOST=‘<MASTER_HOST>‘,

MASTER_USER=‘<SLAVE_USER>‘,

MASTER_PASSWORD=‘<SLAVE_PASSWORD>‘,

MASTER_LOG_FILE=‘<Relay_Master_Log_File>‘,

MASTER_LOG_POS=<Exec_Master_Log_Pos>;

不管用那個方法，最後記得在從伺服器上啟動複製，並檢查工作是否正常：

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS;

如果IO線程和SQL線程都顯示Yes，就可以感謝上帝了：

Slave_IO_Running 對應：Master_Log_File & Read_Master_Log_Pos

Slave_SQL_Running 對應：Relay_Master_Log_File & Exec_Master_Log_Pos

如果顯示No，則說明前面某些配置步驟出錯，或者對應的記錄檔有問題。

故障

問題：主從複製不止何故停止了，我該怎麼辦？

答案：複製錯誤多半是因為日誌錯誤引起的，所以首先要搞清楚是主日誌錯誤還是中繼日誌錯誤，從錯誤資訊裡一般就能判斷，如果不能可以使用類似下面的mysqlbinlog命令：

shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null

shell> mysqlbinlog <SLAVE_BINLOG_FILE> > /dev/null

如果沒有錯誤，則不會有任何輸出，反之如果有錯誤，則會顯示出來。

如果是主日誌錯誤，則需要在從伺服器使用SET GLOBAL sql_slave_skip_counter，如下：

mysql> SET GLOBAL sql_slave_skip_counter = 1;

mysql> START SLAVE;

註：如果有多個錯誤，可能需要執行多次（提醒：主從伺服器資料可能因此不一致）。

如果是中繼日誌錯誤，只要在從伺服器使用SHOW SLAVE STATUS結果中的日誌資訊重新CHANGE MASTER TO即可，系統會拋棄當前的中繼日誌，重新下載：

mysql> CHANGE MASTER TO

MASTER_LOG_FILE=‘<Relay_Master_Log_File>‘,

MASTER_LOG_POS=<Exec_Master_Log_Pos>;

mysql> START SLAVE;

至於為什麼使用的是Relay_Master_Log_File & Exec_Master_Log_Pos，參見概述。

問題：主伺服器宕機了，如何把從伺服器提升會主伺服器？

答案：在一主多從的環境總，需選擇資料最新的從伺服器做新的主伺服器。如所示：

提升從伺服器為主伺服器

在 一主（Server1）兩從（Server2，、Server3）的環境中，Server1宕機後，等到Server1和Server2把宕機前同步到的 日誌都執行完，比較Master_Log_File和Read_Master_Log_Pos就可以判斷出誰快誰慢，因為Server2從 Server1同步的資料（1582）比Server3從Server1同步的資料（1493）新，所以應該提升Server2為新的主伺服器，那麼 Server3在CHANGE MASTER TO到Server2的時候應該使用什麼樣的參數呢？1582-1493=89，而Server2的最後的二進位日誌位置是8167，所以答案是 8167-89=8078。

技巧

主從伺服器中的表可以使用不同的表類型。比如主 伺服器可以使用InnoDB表類型，提供事務，行鎖等進階特性，從伺服器可以使用MyISAM表類型，記憶體消耗少，易備份等優點。還有一個例子，一台主服 務器如果同時帶很多個從伺服器的話，勢必會影響其效能，此時可以拿出一台伺服器作為從伺服器代理，使用BLACKHOLE表類型，只記錄日誌，不寫資料， 由它帶多台從伺服器，從而提升效能。

主從伺服器中的表可以使用不同的鍵類型。比如主伺服器用InnoDB，鍵用VARCHAR的話節省空間的，從伺服器使用MyISAM，鍵用CHAR提高速度，因為MyISAM有靜態表一說。

主從伺服器中的表可以使用不同的索引。主伺服器主要用來應付寫操作，所以除了主鍵和唯一索引等保證資料關係的索引一般都可以不加，從伺服器一般用來應付讀操作，所以可以針對查詢特徵設定索引，再進一步，不同的從伺服器可以針對不同的查詢設定不同的索引。

工具

有一些優秀的工具可以讓你的複製工作得到事半功倍的效果，詳細內容請參考各自文檔：

Multi-Master Replication Manager for MySQL

Percona XtraBackup

Maatkit

Tungsten-replicator

MySQL show slave status命令參數