mysql主從備份

一、準備伺服器 

由於MySQL不同版本之間的(二進位日誌)binlog格式可能會不一樣，因此最好的搭配組合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能高於Slave版本。

我們假設主伺服器(以下簡稱Master)和從伺服器(以下簡稱Slave)的版本都是5.0.63。 

假設同步Master的主機名稱為：A(IP:192.168.0.1)，Slave主機名稱為：B(IP:192.168.0.2)，2個MySQL的basedir目錄都是/usr/local/mysql，datadir都是：/var/mysql。 

二、設定同步伺服器 

1、設定同步Master 

修改 my.cnf 檔案，在 
# Replication Master Server (default) 
# binary logging is required for replication 
添加如下內容： 

Java代碼  

1. log-bin=mysql-bin  
2. server-id = 1  
3. binlog-do-db=test  
4. binlog-ignore-db=mysql  

重啟MySQL,建立一個MySQL帳號為同步專用 

Sql代碼  

1. GRANT REPLICATION SLAVE,RELOAD,SUPER, ON *.* TO backup@192.168.0.2 IDENTIFIED BY 'slavepass';  
2. FLUSH PRIVILEGES ;  

複製資料庫： 

Sql代碼  

1. FLUSH TABLES WITH READ LOCK;  

鎖定表清除寫入操作。 

Sql代碼  

1. SHOW MASTER STATUS;  

File列顯示日誌名，而Position顯示位移量，記錄備用。 
備份資料庫，建議對於MYI儲存的MYSQL採用直接檔案複製，效率會好很多。備份完解鎖。 

Sql代碼  

1. UNLOCK TABLES;  

備份後對於從伺服器匯入資料庫 

以上複製過程也可以用mysqldump，如果資料庫在提供服務，必須加入--lock-all-tables以保證資料為同一時間的。加入--master-data=1在dump時會記錄binlog名稱和位移值，以CHANGE MASTER TO形式存在檔案中，這樣匯入slave後不用自己設定master的binlog檔案和位移值了。 

2、設定同步Slave 

修改my.cnf檔案，添加 

Java代碼  

1. server-id = 2  
2. master-host = 192.168.0.1  
3. master-user = backup  
4. master-password = slavepass  
5. master-port = 3306  
6.   
7. replicate-ignore-db=mysql  
8. replicate-do-db=test  

server-id不能與master相同 

重啟MySQL 

3、啟動同步 

在主伺服器A MySQL命令符下： 

Sql代碼  

1. show master status;  

顯示（當然這個是我機器的情況，你的不可能跟我一樣哈，只是個例子）： 
+------------------+----------+-------------------+------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+-------------------+------------------+ 
| mysql-bin.000028 | 313361 | test | mysql | 
+------------------+----------+-------------------+------------------+ 

在從伺服器B MySQL命令符下： 

Sql代碼  

1. slave stop;  
2. MySQL> CHANGE MASTER TO  
3. -> MASTER_HOST='master_host_name',  
4. -> MASTER_USER='replication_user_name',  
5. -> MASTER_PASSWORD='replication_password',  
6. -> MASTER_LOG_FILE='recorded_log_file_name',  
7. -> MASTER_LOG_POS=recorded_log_position;  
8. slave start;  

用show slave status;看一下從伺服器的同步情況 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
如果都是yes，那代表已經在同步 

往表裡面寫點資料測試一下看是否同步成功，如果不成功，絕對不是你的RP問題，再檢查一下操作步驟！ 

4、設定雙向同步 

其實也就是A->B單向同步的反向操作！雙向同步，就這麼簡單啦！ 

三、同步錯誤處理 

發現mysql slave伺服器經常因為一些特殊字元或者符號產生的更新語句報錯，整個同步也會因此而卡在那，最初的辦法只是手動去出錯的機器，執行下面三條sql語句，跳過錯誤即可。 

Sql代碼  

1. slave stop;  
2. set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;  
3. slave start;  

四、備份底層知識 

1、binlog 
mysql的binlog記錄了資料庫的所有操作，比如我有個新的資料庫bbs，建立資料庫時候也開啟了binlog，那麼mysql會在var目錄產生個mysql-bin.000001，這個檔案記錄了對資料庫bbs的所有操作sql命令。每個binlog檔案預設1G，超過了會自動換到mysql-bin.000002。mysql-bin.index記錄了所有mysql-bin的名字。 

mysql-bin.index樣本： 

Java代碼  

1. ./mysql-bin.000001  
2. ./mysql-bin.000002  
3. ./mysql-bin.000003  
4. ./mysql-bin.000004  
5. ./mysql-bin.000005  
6. ./mysql-bin.000006  
7. ./mysql-bin.000007  
8. ./mysql-bin.000008  
9. ./mysql-bin.000009  
10. ./mysql-bin.000010  

資料主從備份其實就是master把binlog發給slave，然後slave在本地執行這些sql語句。 

2、master.info 
master.info檔案存在於slave的var目錄，記錄了master的資訊。 
master.info樣本： 

Java代碼  

1. 15  
2. mysql-bin.000105  #master的binlog檔案  
3. 498027148         #位移值  
4. 192.168.0.1  
5. backup  
6. slavepass  
7. 3306  
8. 60  
9. 0  
10.   
11.   
12.   
13.   
14.   
15. 0  

3、relay-log.info 
relay-log.info檔案存在於slave的var目錄，記錄了slave執行binlog檔案情況的資訊。 
relay-log.info樣本： 

Java代碼  

1. ./lab-relay-bin.000050     #slave存放master的binlog的檔案  
2. 210263408                               #slave位移值  
3. mysql-bin.000105  
4. 498027148  
5. 2  

從master的mysql-bin.000105發送資訊寫到lab-relay-bin.000050裡，slave再從lab-relay-bin.000050取sql語句執行。