# 一.mysql主從備份（復制）的基本原理 mysql支持單向、異步復制，復制過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。mysql復制基于主服務器在二進制日志中跟蹤所有對數據庫的更改(更新、刪除等等)。因此，要進行復制，必須在主服務器上啟用二進制日志。每個從服務器從主服務器接收主服務器已經記錄到其二進制日志的保存的更新。當一個從服務器連接主服務器時，它通知主服務器從服務器在日志中讀取的最后一次成功更新的位置。從服務器接收從那時起發生的任何更新，并在本機上執行相同的更新。然后封鎖并等待主服務器通知新的更新。從服務器執行備份不會干擾主服務器，在備份過程中主服務器可以繼續處理更新。 # 二.mysql主從備份配置方法 >[danger]**在進行mysql主從備份時，最好確保主從服務器的版本兼容。從服務器至少與主服務器版本相同或更高。** ## 2.1、主機（master）配置： ### 1.修改mysql配置文件my.cnf >[danger]`vi /etc/my.conf`; 在[mysqld]標簽下添加以下幾行 >[warning]log-bin=mysql-bin #開啟二進制日志 server-id=id #主服務器id號 binlog-do-db=db_nameA #指定對db_nameA記錄二進制日志 binlog-ignore-db=db_namB #指定不對db_namB記錄二進制日志 我們現在的配置是 ~~~ log-bin=mysql-bin server-id = 1 binlog-do-db=test ~~~ ### 2.為從服務器添加mysql賬戶并配置權限 在主服務器上，必須為從服務器創建一個用來連接主服務器的用戶，并設置replication slave權限。所用具體命令如下： >[warning]`grant replication slave on *.* to '帳號' @ '從服務器IP' identified by '密碼';` 其中服務器地址可以寫成通配符的形式 現在我的代碼是 >[danger]`grant replication slave on *.* to 'ncgis'@'%' identified by 'ncgis';` 登錄mysql數據庫 （mysql -uroot -p） 進入數據庫mysql（use mysql） 查詢 >[danger]`select * from user where user = 'ncgis' \G; ` 出現一下結果 ~~~ *************************** 1. row *************************** Host: % User: ncgis Password: *9EAEAC24C4B6C98D611B4E95A4EE54F86904B538 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: Y Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N 1 row in set (0.00 sec) ~~~ 可以看到Repl_slave_priv項對就的值為Y。 ###3、導出主服務器的數據 >[danger]`mysqldump test>test.bak` ### 4.查詢記錄File 及Position 項的值 >[danger]`show master status\G;` 得到的結果如下 ~~~ *************************** 1. row *************************** File: mysql-bin.000013 Position: 944 Binlog_Do_DB: test Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) ~~~ 從新啟動服務器 >[danger]~~~ service mysql stop service mysql start ~~~ 或者 >[danger]`service mysql reload （推薦試使用）` ## 2、2從服務器配置 ### 1.修改mysql配置文件my.cnf 在[mysqld]標簽下添加以下面一行： >[danger]`server-id=id #從機id ` 實際中，我們添加： >[danger]`server-id=2` ### 2、導入數據： >[danger]`mysql test < test.bak ` ### 3.重啟mysql數據庫并設置相關參數 >[danger]change master to master_host = '主服務器的地址', master_user = '使用的賬號', master_password = '密碼', master_log_file = 'file值', master_log_pos = position值; slave start; 我們的代碼是(此處的file值和position值請查看主服務器配置的第四步查詢出來的值) >[danger]change master to master_host = '192.168.10.105', master_user = 'ncgis', master_password = 'ncgis', master_log_file = 'mysql-bin.000013', master_log_pos = 944; slave start; ### 4.檢查數據庫查看相關參數 執行 >[danger]service mysql reload 重啟后進入mysql 執行 >[danger]show slave status \G; 我們查看到的結果是 ` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.105 Master_User: ncgis Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000013 Read_Master_Log_Pos: 944 Relay_Log_File: localhost-relay-bin.000003 Relay_Log_Pos: 1107 Relay_Master_Log_File: mysql-bin.000013 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: 944 Relay_Log_Space: 1284 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: 1 Master_UUID: b9d68e0d-6cca-11e5-92f0-000c2935acaa Master_Info_File: /usr/local/mysql/var/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 1 row in set (0.00 sec) ` 對部分參數的解釋： >[danger]Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes 以上參數說聽slave正常運行，正等待主服務器發來的消息。此時若用netstate命令可以看到從機與主機間已經建立了一條鏈接接。 特別需要注意的兩個參數是： >[danger]Master_Log_File代表主機上用于主備同步的日志文件名 Read_Master_Log_Pos代表上一次成功同步到的日志文件中的位置。 如果這兩項與先前在主服務器上看到的File及Position的值不相符，則無法正確進行同步。 如果 Slave_IO_Running: 顯示在連接中 Slave_SQL_Running: Yes 這種狀態的話 主要是網絡不通 需要檢查一下防火墻 # 三.主從備份的實現細節 mysql使用3個線程來執行復制功能(其中1個在主服務器上，另兩個在從服務器上）。當發出start slave時，從服務器創建一個I/O線程，以連接主服務器并讓它發送記錄在其二進制日志中的語句。主服務器創建一個線程將二進制日志中的內容發送到從服務器。該線程可以即為主服務器上show processlist輸出中的Binlog Dump線程。從服務器I/O線程讀取主服務器Binlog Dump線程發送的內容并將該數據拷貝到從服務器數據目錄中的本地文件中，即中繼日志。第3個線程是sql線程，由從服務器創建，用于讀取中繼日志并執行日志中包含的更新。在從服務器上，讀取和執行更新語句被分成兩個獨立的任務。當從服務器啟動時，其I/O線程可以很快地從主服務器索取所有二進制日志內容。  ## 1.復制線程狀態 　　通過show slave status\G和show master status可以查看復制線程狀態。常見的線程狀態有： ### （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線程將新事件寫入中繼日志。 ## 2.復制過程中使用的傳遞和狀態文件 　　默認情況，中繼日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是從服務器主機名，nnnnnn是序列號。中繼日志與二進制日志的格式相同，并且可以用mysqlbinlog讀取。 　　從服務器在data目錄中另外創建兩個小文件。這些狀態文件默認名為主master.info和relay-log.info。狀態文件保存在硬盤上，從服務器關閉時不會丟失。下次從服務器啟動時，讀取這些文件以確定它已經從主服務器讀取了多少二進制日志，以及處理自己的中繼日志的程度。 ## 附錄1 關于log_bin日志 　　my.conf 文件中的[mysqld]標簽下的log_bin指定日志文件，如果不提供文件名，mysql將自己產生缺省文件名。mysql會在文件名后面自動添加數字引，每次啟動服務時，都會重新生成一個新的二進制文件。此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定記錄的數據庫；使用binlog-ignore-db可以指定不記錄的數據庫。注意的是：binlog-do-db和binlog-ignore-db一次只指定一個數據庫，指定多個數據庫需要多個語句。而且，MySQL會將所有的數據庫名稱改成小寫，在指定數據庫時必須全部使用小寫名字，否則不會起作用。 　　以后對數據庫每做的一次操作，都會在binlog中有所記錄。