標籤：主從
概述
本篇文章主要介紹mysql主從的搭建過程和中間涉及的一些概念知識，希望能最全面的將mysql主從所涉及到的知識都概況進來；環境已經安裝好了mysql,這裡就不介紹mysql的安裝方法。
測試環境：
主：mysql（5.6.21），linux:redhat 6.0,ip:192.168.1.6
從：mysql（5.6.21），linux:redhat 6.0,ip:192.168.1.7
原理和概念
主從複製原理
1)主庫在事務提交時會把變更作為事件記錄（Events）到二進位制檔案（Binlog）當中
2)主庫將二進位制檔案中的事件推送到從庫的中繼日誌檔案中（Relay-bin）,從庫根據中繼日誌中事件做變更操作。
執行緒
Binlog Dump執行緒：該執行緒執行在主庫上，當主從都配置好後，從庫執行START SLAVE啟動複製後，會在主庫上生成一個Binlog Dump執行緒，該執行緒的主要作用就是讀取主庫Binlog事件傳送到從庫（從庫的I/O執行緒）。
I/O執行緒：該執行緒執行在從庫上，I/O執行緒的作用是向主資料庫要資料並且將主庫傳送過來的變更事件寫入到從庫的中繼日誌中。
SQL執行緒：該執行緒執行在從庫上，該執行緒的主要作用是讀取中繼日誌中的變更事件並更新從庫。
該圖來自深入淺出mysql資料庫開發這本書中。
步驟
主庫
在這裡主庫是執行的，主庫的配置檔案也是已經配置好了的。
1.配置my.cnf
server-id=6 log-bin=/var/lib/mysql/mysql-bin max_binlog_size = 100M sync_binlog=0 binlog-format=MIXED binlog-ignore-db=test replicate-ignore-db=test
server-id必須是唯一的，預設設定當前IP主機
log-bin是開啟binlog且配置路徑，預設是不開啟的
max_binlog_size設定binlog檔案的最大值，這裡設定最大為100M，當達到這個值會自動生成一個新的binlog檔案，當然生成環境會設定的比這個大一點。
sync_binlog:配置是否每次事務提交都需要重新整理binlog到磁碟，預設0是不每次重新整理，有檔案系統自己控制，如果設定為1預設每次事務提交都會重新整理binlog到磁碟，這樣的好處是當系統突然down掉了系統損傷的會少一點，因為binlog也有快取，預設事務提交是先寫快取這樣當系統突然down掉了就有可能會丟失快取中的記錄，但是如果每次事務提交都寫磁碟會對效能造成影響，可以通過半同步複製解決因系統突然down掉導致binlog快取資料丟失的問題。
binlog-format:二進位制日誌記錄的方法，有三種方式：row(記錄每一行的變更操作，優點：對複製的相容性高，缺點：日誌記錄量大，對IO的影響也很大，也不容易用來做分析)，STATEMENT(記錄操作的sql語句，這也是預設的格式，優點：日誌量小，便於用來做分析，IO影響小，缺點：可能會導致複製出錯例如有時候使用的某些函式)，MIXED(混合了上面兩種格式,預設採用STATEMENT記錄，當出現不確定函式時就採取row記錄例如curret_user(),now()等)
binlog-ignore-db:不記錄指定資料庫的binlog，如果指定多個資料庫可以在配置檔案中重複多行。反過來如果配置了binglog-do-db那麼久只記錄指定的這一關資料庫的binlog其它的資料庫都不記錄。
replicate-ignore-db:不復制指定的資料庫的binlog
2.建立複製使用者
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.7' IDENTIFIED BY 'repl';
在主庫上執行，授予192.168.1.7伺服器使用使用者repl的REPLICATION SLAVE許可權。
3.重新整理表並設定資料庫只讀
FLUSH TABLES WITH READ LOCK;
當前主庫只能讀不允許更新操作
4.記錄主庫二進位制檔名和偏移量
SHOW MASTER STATUS;
記錄日誌名和便宜量的目的是為了後面從庫用的
5.備份主庫
停止主庫服務，需要將主庫的資料庫備份還原到從伺服器中去
service mysql stop
備份的方法有很多種：1.如果主庫是線上不能停止服務，可以通過熱備份方式，使用dump、ibbackup、xtrabackup等熱備份工具備份資料庫然後到從庫還原
2.如果主庫允許停止服務那麼可以直接cp主庫資料目錄下的所有檔案到從庫的路徑下，可以使用xftp工具比較方便。
做好了這步就可以重啟主庫服務了。
從庫
這裡從庫的服務是停止的。
1.配置my.cnf
log-bin=/var/lib/mysql/mysql-bin server-id=7
max_binlog_size = 100M sync_binlog=0
binlog-format=MIXED
binlog-ignore-db=test
replicate-ignore-db=test
注意server-id不能和主相同
2.使用--skip-slave-start方式啟動從庫服務
使用--skip-slave-start啟動的目的是為了不立即啟動從伺服器上面的複製程式，方便對後面的配置操作。
mysqld_safe --skip-slave-start &
3.登入mysql
mysql -uroot -p123456
登入mysql後執行以下操作
change master to master_host = '192.168.1.6', master_user = 'repl', master_password = 'repl', master_log_file = 'mysql-bin.000046', master_log_pos = 211991;
啟動從庫slave執行緒
start slave
4.檢查
在從庫上執行
show processlist\G;
表明已經連線上面了master
注意執行緒3中的Time欄位：該時間表示上次執行的語句在主庫二進位制檔案中記錄的時間和更新到從庫的時候的當前時間的時間差，如果主庫更新非常頻繁而從庫又跟不上主庫更新的速度的時候該時間差值會增大（影響的因素有：從庫的硬體和主庫的差距、網路傳輸、早期版本的從庫sql執行緒是單執行緒寫而主庫應用前端的寫的多執行緒併發寫）。
測試
在主庫上執行
#在主庫上建立repltest資料庫並在資料庫下建立test表插入資料 create database repltest; use repltest create table test(id int); insert into test() values(1),(2); #測試在已有的test資料庫下建立norepl表並插入資料 use test create table norepl(id int); insert into norepl() values(1);
在從庫執行
select * from repltest.test;
select * from test.norepl;
結果除了test資料庫的操作不會被複制以外其他的資料操作都會被複制到從庫。這也正符合前面的設定。
補充
從伺服器啟動引數
–read_only 該選項讓從伺服器只允許來自從伺服器執行緒或具有SUPER許可權的使用者的更新（ALL PRIVILEGES許可權的使用者也不行，必須是超級使用者）。可以確保從伺服器不接受來自客戶的更新。 –replicate_do_db=db_name 告訴從伺服器只做預設資料庫(由USE所選擇)為db_name的語句的複製。要指定多個資料庫，應多次使用該選項，每個資料庫使用一次。請注意不復制跨資料庫的語句 –replicate_do_table=db_name.tbl_name 告訴從伺服器執行緒只做對指定表的複製。要指定多個表，應多次使用該選項，每個表使用一次。同–replicate-do-db對比，允許跨資料庫更新。 –replicate_ignore_db=db_name 告訴從伺服器不要複製預設資料庫(由USE所選擇)為db_name的語句。要想忽略多個資料庫，應多次使用該選項，每個資料庫使用一次。 –replicate-ignore-table=db_name.tbl_name 告訴從伺服器執行緒不要複製更新指定表的任何語句(即使該語句可能更新其它的表)。要想忽略多個表，應多次使用該選項，每個表使用一次。 –replicate_wild_do_table=db_name.tbl_name 告訴從伺服器執行緒限制複製更新的表匹配指定的資料庫和表名模式的語句。模式可以包含‘%'和‘_'萬用字元，與LIKE模式匹配操作符具有相同的含義。要指定多個表，應多次使用該選項，每個表使用一次。該選項可以跨資料庫進行更新。 –replicate_wild_ignore_table=db_name.tbl_name 告訴從伺服器執行緒不要複製表匹配給出的萬用字元模式的語句。要想忽略多個表，應多次使用該選項，每個表使用一次。該選項可以跨資料庫進行更新。 –replicate_rewrite_db=from_name->to_name 告訴從伺服器如果預設資料庫(由USE所選擇)為主伺服器上的from_name，則翻譯為to_name。隻影響含有表的語句 –report_host=slave_name 從伺服器註冊過程中報告給主伺服器的主機名或IP地址。該值出現在主伺服器上SHOW SLAVE HOSTS的輸出中。如果不想讓從伺服器自己在主伺服器上註冊，則不設定該值。 –report_port=slave_port 連線從伺服器的TCP/IP埠號，從伺服器註冊過程中報告給主伺服器。 –skip_slave_start 告訴從伺服器當伺服器啟動時不啟動從伺服器執行緒。使用START SLAVE語句在以後啟動執行緒。 –slave_skip_errors=[err_code1,err_code2,… | all] 通常情況，當出現錯誤時複製停止，這樣給你一個機會手動解決資料中的不一致性問題。該選項告訴從伺服器SQL執行緒當語句返回任何選項值中所列的錯誤時繼續複製
log_slave_updates
配置從庫的更新操作是否寫二進位制日誌，預設從庫讀取主庫過來的二進位制日誌只寫入中繼日誌檔案中（mysqld-relay-bin.000001）檔案中不會寫入從庫的二進位制檔案中，無論從庫的二進位制檔案是否開啟，如果你需要主庫傳遞過來的二進位制日誌寫入從庫的二進位制檔案中就必須開啟此引數，該引數不能線上開啟，只能修改配置檔案“log_slave_updates=1”，預設不開啟，除非你的從庫還需要作為其它從庫的主庫，如果開啟次引數需要和bin-log一起開啟。
master_connect_retry
配置從庫和主庫連線中斷後重試連線的時間間隔，預設是60S
複製執行緒狀態
通過show processlist \G可以檢視複製執行緒狀態。常見的執行緒狀態有： （1）主伺服器Binlog Dump執行緒 Has sent all binlog to slave; waiting for binlog to be updated 執行緒已經從二進位制日誌讀取所有主要的更新並已經傳送到了從伺服器。執行緒現在正空閒，等待由主伺服器上新的更新導致的出現在二進位制日誌中的新事件。 （2）從伺服器I/O執行緒狀態 Waiting for master to send event 執行緒已經連線上主伺服器，正等待二進位制日誌事件到達。如果主伺服器正空閒，會持續較長的時間。如果等待持續slave_read_timeout秒，則發生超時。此時，執行緒認為連線被中斷並企圖重新連線。 （3）從伺服器SQL執行緒狀態 Reading event from the relay log 執行緒已經從中繼日誌讀取一個事件，可以對事件進行處理了。 Has read all relay log; waiting for the slave I/O thread to update it 執行緒已經處理了中繼日誌檔案中的所有事件，現在正等待I/O執行緒將新事件寫入中繼日誌。
檔案
在從庫的資料庫路徑下會發現生成了三個檔案：master.info,relay-log.info,relay-bin
master.info:用來記錄從庫的I/O執行緒當前讀取到主庫的binglog的位置。
relay-log.info：用來記錄從庫的SQL執行緒當前讀取到中繼日誌(relay-bin)的位置。
relay-bin:中繼日誌，中繼日誌記錄的格式和主庫的二進位制日誌是一樣的，但是中繼日誌在SQL執行緒執行完當前中繼日誌中的事件之後會刪除中繼日誌中的內容。
從庫複製狀態
可以在從庫上通過show slave status \G檢視
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.6 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000047 Read_Master_Log_Pos: 763952 Relay_Log_File: localhost-relay-bin.000003 Relay_Log_Pos: 764115 Relay_Master_Log_File: mysql-bin.000047 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: 763952 Relay_Log_Space: 764455 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: 6 Master_UUID: d58e2793-8534-11e5-b224-000c2908cc04 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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
Slave_IO_State: 執行緒已經連線上主伺服器，正等待二進位制日誌事件到達 Master_Host: 主伺服器ip Master_User: 連線主伺服器使用的使用者 Master_Port: 主伺服器的埠 Connect_Retry: 當重新建立主從連線時，如果連線建立失敗，間隔多久後重試，預設60s。 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_Table:複製表 Replicate_Ignore_Table:不復制的表 Replicate_Wild_Do_Table: 限制複製更新的表匹配指定的資料庫和表名模式的語句 Replicate_Wild_Ignore_Table: 不要複製表匹配給出的萬用字元模式的語句 Last_Errno:錯誤程式碼 Last_Error:錯誤資訊 Skip_Counter: SQL_SLAVE_SKIP_COUNTER的值 Exec_Master_Log_Pos: 主伺服器上一個被執行的位置 Relay_Log_Space: 中繼日誌檔案大小 Until_Condition: 在START SLAVE語句的UNTIL子句中指定的值 Until_Log_File: 用於指示日誌檔名 Until_Log_Pos: 位置值 Master_SSL_Allowed: 如果允許對主伺服器進行SSL連線，則值為Yes 否則NO Master_SSL_CA_File:下面的這些都是SSL連線的一些資訊 Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 本欄位是從屬伺服器落後多少的一個指示(這個狀態是一個很重要的效能指標，正常為0，如果從伺服器的I/O執行緒無法連線主伺服器顯示null) Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 最近的IO執行緒錯誤程式碼，其中2003代表I/o執行緒無法連線主伺服器 Last_IO_Error: 最近的IO執行緒錯誤資訊（例如：error reconnecting to master 'repl@192.168.1.6:3306' - retry-time: 60 retries: 3） Last_SQL_Errno: 最近的SQL執行緒錯誤程式碼 Last_SQL_Error: 最近的SQL執行緒錯誤資訊 Replicate_Ignore_Server_Ids: Master_Server_Id: 主伺服器的伺服器ID Master_UUID: 主伺服器的UUID值 Master_Info_File: 從伺服器的master.info檔案路徑 SQL_Delay: 正數表明slave有延遲了 SQL_Remaining_Delay: 整數表明延遲時間 Slave_SQL_Running_State: SQL執行緒執行狀態（SQL執行緒已經處理了中繼日誌檔案中的所有事件，現在正等待I/O執行緒將新事件寫入中繼日誌。 ） Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp:最近的I/O執行緒錯誤時間 Last_SQL_Error_Timestamp:最近的SQL執行緒報錯時間 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0
其中需要注意比較重要的狀態：Slave_SQL_Running之前的這十幾個狀態再加上Last_IO_Errno，Last_IO_Error，Last_SQL_Errno，Last_SQL_Error，SQL_Delay，SQL_Remaining_Delay，Slave_SQL_Running_State，Last_IO_Error_Timestamp，Last_SQL_Error_Timestamp，Seconds_Behind_Master
解決主從延時的方案
從前面總結的延時導致的原因主要有可能來自以下三個原因：
網路傳輸：對應網路傳輸這塊只能從硬體方面解決，避免千兆閘道器用百兆網線、百兆網路卡等情況，主從在同一網段。
硬體問題：對應因為主從的硬體相差非常大的情況，可以適當提高從庫的硬體。
併發寫的問題：在5.6版本之後從庫的sql執行緒改成了併發寫，有助於提高從庫的寫延時的問題；
查詢從庫配置的多個SQL執行緒 show variables like '%slave_parallel_workers%';
還有一種方案是對於一主多從的方案可以將主庫的複製資料庫或者表分散到每個從庫上面，例如每個從庫複製一個資料庫來減少從庫的壓力，但是這種方案對於主當機之後由於每個從庫只有單個資料庫的資料如果需要組合多個從庫的資料會有點麻煩。
總結
在企業的環境中主從複製是最基礎也是非常普遍的一種形式，相對來說配置管理也比較簡單。上面講的是一主一從，在這基礎上只要再新增從伺服器就可以做到一主多從的結構，但是這種結構對於主伺服器的可靠性要求很高，這就又有了主主複製，後面會花點時間寫一篇關於主主複製的文章，歡迎關注。
|
備註： 作者：pursuer.chen 部落格：http://www.cnblogs.com/chenmh 本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。 《歡迎交流討論》 |
---恢復內容結束---