MySQL主從同步設置和同步錯誤處理 注意： 1、兩台服務器數據庫版本應一致，如果不一致，從服務器的版本要高於主服務器的版本。 2、MySQL進行實時數據同步，本質上是將mysql動作同步到Slave服務器，而不是對實質的數據進行同步。所以同步開始前，兩端的數據要保持一致。 Master服務器：192.168.0.1 Slave服務器：192.168.0.2 === Master停止運行時，Mysql同步配置=== 一、主服務器設置： 1、修改/etc/my.cnf 在[mysqld]區段內加入參數 server-id=1 log-bin sql-bin-update-same 2、為Slave服務器創建連接賬戶，用於同步 mysql>GRANT FILE ON *.* TO [email protected] IDENTIFIED BY ‘1234’; 3、重啟mysql服務：service mysqld restart 此時因為有加入log-bin參數,因此開始有index產生了,在/var/lib/mysql目錄下有.index檔案紀錄數據庫的異動log. 二、Slave服務器設置： 1、修改/etc/my.cnf 在[mysqld]區段加入 master-host=192.168.0.1 master-user=backup master-password=1234 master-port=3306 server-id=2 master-connect-retry=60 預設重試間隔60秒 replicate-do-db=vbb 告訴slave只做vbb數據庫的更新 replicate-ignore-db=mysql #不同步的數據庫 replicate-ignore-table=vbb.users #不同步vbb數據庫的users表 log-slave-updates=1 2、重啟Slave服務器mysql服務 三、Slave服務器從主服務器讀取數據，並開啟同步 1、mysql>FLUSH TABLES WITH READ LOCK; #首先執行這個，對主服務器加鎖 2、mysql>LOAD DATA FROM MASTER #語句的話，必須授予全局的 FILE 和 SELECT 權限，僅針對MyISAM引擎，對InnoDB表無用。 3、如果數據是InnoDB的，需要在服務器上將表結構和數據分開導出，然後拷貝至Slave服務器。 導出整個數據庫結構和數據：mysqldump -u用戶名 -p密碼 -h主機 database > filename.sql 導出某個表的數據和結構：mysqldump -u用戶名 -p密碼 -h主機 database table > filename.sql 只導出數據庫中表的數據：mysqldump -u用戶名 -p密碼 -h主機 -T database table > filename.sql 只導出數據庫中表的結構：mysqldump -u用戶名 -p密碼 -h主機 -d database table > filename.sql 4、確保兩端數據一致後，開啟同步 mysql>slave start; 5、查看Slave服務器同步信息 mysql>show slave status\G; 可以看到，Slave_IO_Running 和 Slave_SQL_Running 兩列的值都為 "Yes"，這表明 Slave 的 I/O 和 SQL 線程都在正常運行。 6、對Master服務器釋放鎖 mysql>UNLOCK TABLES; === Master還在運行中，如何在不停止它的情況下配置Slave === 注：主從服務器的配置同上。 1、首先在Master服務器查看同步日志信息 mysql>show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000054 | 680 | | mysql | +------------------+----------+--------------+------------------+ 可以看到日志文件是：mysql-bin.000054，同步點是680 2、在Slave服務器配置同步 mysql>slave stop; # mysql默認同步開始 mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.1',MASTER_USER='backup',MASTER_PASSWORD='1234',MASTER_LOG_FILE='mysql-bin.000054',MASTER_LOG_POS=690; #配置同步點 mysql>slave start; #開啟同步 3、查看同步信息 mysql>show slave status\G; 可以看到，Slave_IO_Running 和 Slave_SQL_Running 兩列的值都為 "Yes"，這表明 Slave 的 I/O 和 SQL 線程都在正常運行。 4、配置完成 MYSQL服務器復制配置 這是根據我之前看的MYSQL復制的文檔然後自己親自實驗的過程.配置的功能比較簡單. 環境: 主服務器: redhat9 mysql 5.0.16 機器名:dbmaster IP:192.168.0.111 從服務器: redhat9 mysql5.0.16 機器名:dbslave IP:192.168.0.100 配置過程: 整個過程與MYSQL文檔中的復制設置步驟一一對應,只是具體化和標明了一些要注意的地方..如果有興趣的朋友可以對照閱讀,會更加理解. 1.確認兩台服務器的MYSQL版本,用mysql –V命令查看 注意: MySQL不同版本之間的(二進制日志)binlog格式可能會不一樣，所以最好采用相同的版本.如果達不到要求,必須要保證Master的版本不能高於Slave版本 2.在主服務器上為服務器設置一個連接賬戶。該賬戶必須授予REPLICATION SLAVE權限。我為了簡單沒做這一步,直接用root賬戶. 正常的步驟是: 假定你的域為mydomain.com,想要創建用戶名為repl的一個賬戶，從服務器可以使用該賬戶從你的域內的任何主機使用密碼slavepass來訪問主服務器。要創建該 賬戶，可使用GRANT語句： mysql> GRANT REPLICATION SLAVE ON *.* ->TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; 如果想要在Slave上有權限執行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 語句的話，必須授予全局的 FILE 和 SELECT 權限： mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'rep'; 3.在主服務器上執行FLUSH TABLES WITH READ LOCK語句來刷新表,同時阻止寫操作.： mysql> FLUSH TABLES WITH READ LOCK； 然後對主服務器上的數據進行快照。(此時不要退出mysql的提示符,退出的話就解鎖了.新開一個ssh窗口進行下面所說的歸檔打包操作) 創建快照最簡單的途徑是使用歸檔程序對主服務器上的數據目錄中的數據庫進行二進制備份。要使用tar來創建包括所有數據庫的歸檔文件，進入主服務器的數據目錄，然後執行命令：(注意下面的目錄操作是在bash的提示符下進行,我用shell>來代表,還要注意執行的命令是在哪台服務器上) shell> tar -cvf /tmp/mysql-snapshot.tar . 然後將歸檔文件復制到從服務器的/tmp目錄。 在從服務器上，進入數據庫的數據目錄，並使用下述命令解壓縮歸檔文件： shell> tar -xvf /tmp/mysql-snapshot.tar 當FLUSH TABLES WITH READ LOCK所置讀鎖定有效時(這就是剛剛所說不要退出mysql提示符的原因)，讀取主服務器上當前的二進制日志名和偏移量值,直接輸入命令SHOW MASTER STATUS. mysql > SHOW MASTER STATUS; +------------------------+------------+-------------------+-----------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+------------+-------------------+------------------------+ | mysql-bin.000045 | 947 | | | +------------------------+------------+-------------------+-----------------------+ File列顯示日志名，而Position顯示偏移量。在該例子中，二進制日志值為mysql-bin.000045偏移量為947。記錄該值。以後設置從服務器時需要使用這些值。它們表示復制坐標，告訴從服務器應從該點開始從主服務器上進行更新。 取得快照並記錄日志名和偏移量後，可以在主服務器上重新啟用寫活動： mysql> UNLOCK TABLES； 4. 確保主服務器上my.cnf文件的[mysqld]部分包括一個log-bin選項。該部分還應有一個server-id=Master_id選項，其中master_id必須為1到232–1之間的一個正整數值。例如： [mysqld] log-bin=mysql-bin server-id=1 一般情況你打開my.cnf後,已經默認是這樣的設置了,裡面內容看起來像下面這樣: # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 如果這些選項不存在,添加它們並且重啟服務器.在binary logging(二進制日志)沒有啟用的情況下,服務器是不能稱為復制主服務器的 5.停止從服務器 shell> mysqladmin -u root shutdown -p 其my.cnf文件中添加下面的行： [mysqld] server-id=slave_id slave_id值同Master_id值一樣，必須為1到232–1之間的一個正整數值。並且，從服務器的ID必須與主服務器的ID不相同。例如： [mysqld] server-id=2 如果設置多個從服務器，每個從服務器必須有一個唯一的server-id值，必須與主服務器的以及其它從服務器的不相同。 注意:這裡的設置與上面的有很大不同.其實默認的my.cnf中已經有相應的設置,前面已經說過,對於主服務器基本不用修改,對於從服務器,修改也很簡單.(其實文件中說的滿清楚的,E文好的應該沒問題) 必須將主服務器打開的那兩個選項注釋掉 # Replication Master Server (default) # binary logging is required for replication # log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted # server-id = 1 然後把從服務器的對應的選項打開 # Replication Slave (comment out master section to use this) ……(省略) # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted server-id = 2 (對於多個從服務器,就依此類推,2,3,4…) 6.如果對主服務器的數據進行二進制備份，啟動從服務器之前將它復制到從服務器的數據目錄中。確保對這些文件和目錄的權限正確。你用來運行從服務器的系統帳號必須能夠讀寫這些文件，就如同在主服務器上一樣。 7.啟動從服務器。 mysqld_safe --user=mysql & 不同服務器的配置可能不一樣,一般會在/etc/rc.d/rc.local中有寫入. 如果前面已經復制了，用--skip-slave-start選項啟動從服務器，以便它不立即嘗試連接主服務器。你也可能想要用--logs-warnings選項啟動從服務器(默認設置啟用)，以便在錯誤日志中顯示更多的問題相關的信息(例如，網絡或連接問題)。放棄的連接將不會記入錯誤日志，除非這個option的值大於1。 8.如果使用mysqldump備份主服務器的數據，將轉儲文件裝載到從服務器： shell> mysql -u root -p < dump_file.sql 我沒有采用mysqldump這種方式備份,所以這步跳過. 9.在從服務器上執行下面的語句： mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.0.111', //寫主服務器的名稱或者IP -> MASTER_USER='root', //寫用來復制的帳號,前面說明過我直接用的root -> MASTER_PASSWORD='xxx', //復制帳號的密碼,這裡就是指root的密碼 -> MASTER_LOG_FILE='mysql-bin.000045', //之前記錄的日志名 -> MASTER_LOG_POS=947; //之前記錄的偏移量 返回: Query OK, 0 rows affected (0.01 sec) 下面的表顯示了字符串選項的最大長度： Master_Host 60 Master_USER 16 Master_PASSWORD 32 Master_Log_File 255 10.啟動從服務器線程： mysql> START SLAVE； 執行這些程序後，從服務器應連接主服務器，並補充自從快照以來發生的任何更新。 測試: 在從服務器上查看 mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.111 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000045 Read_Master_Log_Pos: 1064 Relay_Log_File: dbpi-relay-bin.000002 Relay_Log_Pos: 352 Relay_Master_Log_File: mysql-bin.000045 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: 1064 Relay_Log_Space: 352 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 紅色標示顯示出從服務器的兩個線程已經啟動. 在從服務器上可以查看線程運行狀態 mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 3 User: root Host: localhost db: test Command: Query Time: 0 State: NULL Info: SHOW PROCESSLIST *************************** 2. row *************************** Id: 18 User: system user Host: db: NULL Command: Connect Time: 43 State: Waiting for master to send event Info: NULL *************************** 3. row *************************** Id: 19 User: system user Host: db: NULL Command: Connect Time: 4294966771 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL 3 rows in set (0.00 sec) 在主服務器上做一個update的語句,從服務器立刻就會同步更新.配置完成. 說明:由於從服務器是通過讀主服務器的二進制日志來實現自我更新的,所以對於對數據庫進行修改的操作都要放在主服務器上執行,而從服務器只用來進行查詢.(也就是只讀不寫的數據庫操作).
