MYSQL雙機熱備份的配置實施(問題總結)

本文章算是對mysql雙機熱備份做了一個非常完整的講述了，包括有具體的配置實現例子及在配置過程中碰到的一些問題分析總結，希望文章對大家配置熱備份帶來幫助。

為了實現MYSQL數據庫的冗災、備份、恢復、負載均衡等功能，喻名堂這兩天一直在學習和研究mysql的雙機熱備，其實MYSQL的雙機熱備就是使用MYSQL同步功能兩種方式裡面的“主-主”同步方式實現的。在一開始搜索資料進行配置時沒有注意版本的問題，所以試了很多次都沒有成功，後來才知道搜索的方法不對，結果搜索出來的資料都是老版本的，最後通過搜索對應的版本號再根據資料終於配置成功，現將不同版本的雙機熱備的配置方法進行整理並分享出來方便其他想配置MYSQL雙機熱備的朋友能一次成功。

要實現熱備MYSQL的版本都要高於3.2，還有一個基本的原則就是作為從數據庫的數據庫版本可以高於主服務器數據庫的版本，但是不可以低於主服務器的數據庫版本，如果要實現雙機熱備就一定要兩個數據庫的版本一樣，如果不知道兩個數據庫的版本可以用如下命令查看

１、登陸前查看

$MYSQLHOME/bin/mysql –V　　　　　　//$MYSQLHOME指MYSQL的安裝目錄

２、登陸後查看

A、 mysql>status

B、 mysql>select version();

現在MYSQL的最新版為5.5.27，從版本5.1.7開始雙機熱備的方法就出現了變化，在版本5.1.7前的配置文件裡面的一些可以用的參數在版本5.1.7以後已經不可以用了。現在喻名堂就根據版本5.1.7以前和版本5.1.7以後兩種情況的雙機熱備的配置進行描述。

在對配置過程進行描述前先對實驗環境做一些定義，方便後面的描述

第一台MYSQL數據庫服務器的名稱為：MYSQL_A，IP地址為：192.168.100.100；第二台MYSQL數據庫服務器的名稱為：MYSQL_B，IP地址為：192.168.100.200，兩台服務器都是使用的centos5.6的操作系統，兩個MYSQL的安裝目錄都是：/mysql，數據目錄都是：/mysql/data，配置文件目錄都是：/etc/my.cnf，要同步的數據庫為asymt

一、版本5.1.7以前
1、設置MYSQL_A
編輯my.cnf，在[mysqld]下添加如下配置：（my.cnf裡面已經存在的配置不必重復配置）
Server-id=1                           //服務id，這個應該默認是1就不用改動
log-bin=log_name                     //日志文件的名稱，這裡可以制定日志到別的目錄 如果沒有設置則默認主機名的一個日志名稱
master-host=192.168.100.200   //主服務器的IP地址或者域名
master-port=3306                     //主數據庫的端口號
master-user=asymt                    //同步數據庫的用戶
master-password=123456               //同步數據庫的密碼
master-connect-retry=60               //如果從服務器發現主服務器斷掉，重新連接的時間差
binlog-do-db=asymt                   //記錄日志的數據庫
binlog-ignore-db=mysql                //不記錄日志的數據庫 , 這就避免了Master上的權限設置等被同步到Slave上，如果對這方面沒有限制，就可以不設置這個參數。
以上的如果有多個數據庫只要在下面添加就行，有多少個數據庫就添加幾行

然後設置同步數據庫的用戶帳號
mysql> GRANT REPLICATION SLAVE ON *.* TO 'asymt'@'192.168.100.200' IDENTIFIED BY '123456';
4.0.2以前的版本, 因為不支持REPLICATION 要使用下面的語句來實現這個功能
mysql> GRANT FILE ON *.*  TO 'asymt'@'192.168.100.200' IDENTIFIED BY '123456';
如果想要在Slave上有權限執行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 語句的話，必須授予全局的 FILE 和 SELECT 權限：
mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'asymt'@'192.168.100.200' IDENTIFIED BY '123456';
鎖定現有的數據庫、備份現在的數據並在MYSQL_B上恢復數據
鎖定數據庫
mysql> FLUSH TABLES WITH READ LOCK;
不要退出這個終端，否則這個鎖就不生效了；備份數據庫有兩種辦法一種是直接進入到mysql的data目錄然後打包你需要備份數據庫的文件夾，第二種是使用mysqldump的方式來備份數據庫但是要加上"--master-data " 這個參數，建議使用第一種方法來備份數據庫，然後在MYSQL_B上將MYSQL_A上的備份數據解壓到數據庫data目錄並設置好權限以及屬主，然後用“unlock tables”語句來釋放鎖，最後重啟數據庫。

2、設置MYSQL_B
同MYSQL_A一樣，編輯my.cnf，在[mysqld]下添加如下配置：（my.cnf裡面已經存在的配置不必重復配置）
Server-id=2                           //服務id，每個數據庫應該不一樣
log-bin=log_name                     //日志文件的名稱，這裡可以制定日志到別的目錄 如果沒有設置則默認主機名的一個日志名稱
master-host=192.168.100.100   //主服務器的IP地址或者域名
master-port=3306                     //主數據庫的端口號
master-user=asymt                    //同步數據庫的用戶
master-password=123456               //同步數據庫的密碼
master-connect-retry=60               //如果從服務器發現主服務器斷掉，重新連接的時間差
binlog-do-db=asymt                   //記錄日志的數據庫
binlog-ignore-db=mysql                //不記錄日志的數據庫 , 這就避免了Master上的權限設置等被同步到Slave上，如果對這方面沒有限制，就可以不設置這個參數。
以上的如果有多個數據庫只要在下面添加就行，有多少個數據庫就添加幾行

然後設置同步數據庫的用戶帳號
mysql> GRANT REPLICATION SLAVE ON *.* TO 'asymt'@'192.168.100.100' IDENTIFIED BY '123456';
4.0.2以前的版本, 因為不支持REPLICATION 要使用下面的語句來實現這個功能
mysql> GRANT FILE ON *.*  TO 'asymt'@'192.168.100.100' IDENTIFIED BY '123456';
如果想要在Slave上有權限執行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 語句的話，必須授予全局的 FILE 和 SELECT 權限：
mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'asymt'@'192.168.100.100' IDENTIFIED BY '123456';

最後重啟數據庫

3、分別登陸MYSQL_A和MYSQL_B上的數據庫，查看同步狀態：
mysql –h localhost –u root -p
mysql>SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.100
Master_User: asymt
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: localhost-bin.000002
Read_Master_Log_Pos: 1556
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 51
Relay_Master_Log_File: localhost-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: cicro,cicro
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: 1556
Relay_Log_Space: 51
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
1 row in set (0.00 sec)
可以看到，Slave_IO_Running 和 Slave_SQL_Running 兩列的值都為 "Yes"，這表明 Slave 的 I/O 和 SQL 線程都在正常運行

到此，MYSQL版本5.1.7以前的雙機熱備已經配置成功

二、版本5.1.7以後（以版本5.5.17為例）
同版本5.1.7以前一樣，先編輯MYSQL_A和MYSQL_B上的my.cnf配置文件，加入如下參數：

Server-id=n                           //服務id，每個數據庫都不一樣，“n”代表ID號，在MYSQL_A上設為1，在MYSQL_B上設為2。
log-bin=log_name                     //日志文件的名稱，這裡可以制定日志到別的目錄 如果沒有設置則默認主機名的一個日志名稱
binlog-do-db=asymt                   //記錄日志的數據庫
binlog-ignore-db=mysql                //不記錄日志的數據庫 , 這就避免了Master上的權限設置等被同步到Slave上，如果對這方面沒有限制，就可以不設置這個參數。

設置同步數據庫的用戶帳號
分別重啟MYSQL_A和MYSQL_B上的MYSQL數據庫，然後登陸mysql，運行如下命令

MYSQL_A：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'asymt'@'192.168.100.200' IDENTIFIED BY '123456';

如果想要在Slave上有權限執行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 語句的話，必須授予全局的 FILE 和 SELECT 權限：
mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'asymt'@'192.168.100.200' IDENTIFIED BY '123456';

MYSQL_B：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'asymt'@'192.168.100.100' IDENTIFIED BY '123456';

如果想要在Slave上有權限執行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 語句的話，必須授予全局的 FILE 和 SELECT 權限：
mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'asymt'@'192.168.100.100' IDENTIFIED BY '123456';

用上文的方法先鎖定數據庫並備份MYSQL_A上的數據然後還原到MYSQL_B上最後把數據庫的鎖定釋放。

分別根本法MYSQL_A和MYSQL_B上的mysql查看主服務器狀態
MYSQL_A：

mysql> show master statusG;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.002 | 70 | asymt | mysql |
+---------------+----------+--------------+------------------+
MYSQL_B：

mysql> show master statusG;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | asymt | mysql |
+---------------+----------+--------------+------------------+

記錄File 和 Position 項目的值，以後要用的。

分別登陸上MYSQL_A和MYSQL_B的MYSQL並設置主服務器的各種參數：
MYSQL_A：

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.100.200',     //同步數據庫的IP
-> MASTER_USER='asymt',              //同步數據庫的用戶
-> MASTER_PASSWORD='123456',        //同步數據庫的密碼
-> MASTER_LOG_FILE=' mysql-bin.003',  //主服務器二進制日志的文件名(前面要求記住的參數)
-> MASTER_LOG_POS=73;             //日志文件的開始位置(前面要求記住的參數)

MYSQL_B：

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.100.100',     //同步數據庫的IP
-> MASTER_USER='asymt',              //同步數據庫的用戶
-> MASTER_PASSWORD='123456',        //同步數據庫的密碼
-> MASTER_LOG_FILE=' mysql-bin.002',  //主服務器二進制日志的文件名(前面要求記住的參數)
-> MASTER_LOG_POS=70;             //日志文件的開始位置(前面要求記住的參數)

然後分別在MYSQL_A和MYSQL_B的mysql裡運行“slave start”來啟動同步數據庫的線程

查看主從服務器的狀態

mysql> SHOW PROCESSLISTG         //可以查看mysql的進程看看是否有監聽的進程

至此，版本5.5.17的雙機熱備配置完成

最後分別在MYSQL_A和MYSQL_B的數據庫裡面插入大量數據查看效果，如果在數據庫插入數據時提示“xx is read only”，請運行“$MYSQLHOME /bin/mysqladmin -u <username> -p flush-tables”命令解決

mysql雙機熱備實施

1、建立mysql數據庫雙向熱備，首先建立復制帳號，帳號名和密碼為“slaveuser”，執行的sql語句如下：

GRANT REPLICATION SLAVE ON *.* TO 'slaveuser' @ '%' IDENTIFIED BY 'slaveuser';

#GRANT REPLICATION SLAVE ON *.* TO 'slaveuser' @ 'RedunHost' IDENTIFIED BY 'slaveuser';

2、修改主備機的my.cnf配置文件

server-id = 1

log-bin=mysql-bin
relay-log=relay-bin

log-slave-updates

#slave-skip-errors = all(可選)

master-host     =   RedunHost
master-user     =   slaveuser
master-password =  slavepass

master-port=8004

 

#跨庫操作配置

replicate-wild-do-table=db_name.%

如果需要同步跨庫操作，需使用 –replicate-wild-do-table=db_name.% 選項

#replicate-do-db=db_name 指定要復制的數據庫，本身不支持跨庫更新

replicate-do-table=db_name.t_name (db_name.%) 同replicate-do-db功能類似，與replicate-do-db區別，支持跨庫更新
 

注解：

server-id = 1
服務器ID號，整數值，保證唯一標識一台服務器就可以
log-bin=mysql-bin
打開二進制日志

relay-log=relay-bin
中繼日志名稱
master-host     =   RedunHost
主服務器IP地址或者主機名，比如：172.20.16.204
master-user= slaveuser
主服務器上創建的復制用戶帳號名稱，比如以上創建的：slaveuser
master-password=slaveuser
主服務器上創建的復制用戶帳號密碼，比如以上創建的：slaveuser
relay-log-index=relay-bin
中繼索引文件名稱

slave-skip-errors =all 是跳過錯誤，繼續執行復制操作(可選)

log-slave-updates

log-slave-updates 這個參數一定要加上，否則不會給更新的記錄些到二進制文件裡

 主備機執行Reset master;stop slave;reset slave;start slave;

重啟主備機mysql服務，主備機執行show slave status/G，如果復制狀態Slave_IO_Running，Slave_SQL_Running都為Yes，說明復制已經配置正確。

 

注：以上配置針對主備機數據庫數據一致進行的操作，若要某些操作不記錄mysql同步日志，可以用“set SQL_LOG_BIN = 0或1;”設置打開記錄mysql同步日志的開關。

部署問題小結

1. mysql互為主從，但是在SLAVE執行CHANGE MASTER時，出現這個錯誤: ERROR 1201 (HY000): Could not initialize master!

mysql> slave stop;

mysql> reset slave;

 

出現這個問題的原因，應該是以前mysql做過主從

 

2. 啟動Cobar時報錯Error: JAVA_HOME environment variable is not set.

修改/etc/profile文件

export JAVA_HOME=/usr/java/jdk1.6.0_23

export JRE_HOME=/usr/java/jdk1.6.0_23/jre

export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

export PATH=$JAVA_HOME/bin:$PATH

 

ldconfig

注銷當前用戶

 

3. Start Slave後使用show slave statusG;檢查報錯error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 86400

 

然後登錄192.168.0.100主數據庫,允許從數據庫同步相關數據；

grant replication slave on *.* to [email protected] identified by '******'

再次重啟從數據庫即可

 

4. /etc/my.cnf無效，MySQL啟動時候不讀取該文件

mysqld_safe --defaults-file=/etc/my.cnf

 

5. 檢查rpm命令安裝軟件的路徑

rpm -ql 套件 #列出rpm軟件包的相關目錄

rpm －qc 套件 #列出rpm軟件包的設置文檔

rpm －qd 套件 #列出rpm軟件包的說明數據文件的完整路徑

 

6. Start slave後報錯ERROR 1200 (HY000): The server is not configured as slave

a) 使用命令SHOW VARIABLES LIKE 'server_id'檢查

b) 如果server_id與my.cnf中配置id不一樣，則使用SET GLOBAL server_id = 2;修改與配置中相同的值

c) 重啟slave