標簽：主從概述本篇文章主要介紹mysql主從的搭建過程和中間涉及的一些概念知識，希望能最全面的將mysql主從所涉及到的知識都概況進來；環境已經安裝好了mysql,這裡就不介紹mysql的安裝方法。測試環境：主：mysql（5.6.21），linux:redhat 6.0,ip:192.168.1....
標簽：主從
概述
本篇文章主要介紹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
測試
在主庫上執行
#在主庫上創建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許可權的用戶的更新。可以確保從伺服器不接受來自客戶的更新。 –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線程當語句返回任何選項值中所列的錯誤時繼續複製
複製線程狀態
通過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 '[email protected]: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
總結
在企業的環境中主從複製是最基礎也是非常普遍的一種形式，相對來說配置管理也比較簡單。上面講的是一主一從，在這基礎上只要再添加從伺服器就可以做到一主多從的結構，但是這種結構對於主伺服器的可靠性要求很高，這就又有了主主複製，後面會花點時間寫一篇關於主主複製的文章，歡迎關註。
|
備註： 作者：pursuer.chen 博客：http://www.cnblogs.com/chenmh 本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明鏈接，否則保留追究責任的權利。 《歡迎交流討論》 |
---恢復內容結束---
