標簽：主從
本篇文章主要介紹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
本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須注明文章來源，且在文章開頭明顯處給明鏈接，否則保留追究責任的權利。
《歡迎交流討論》
---恢復內容結束---
