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

標籤：reconnect   時間   應該   方法   記錄   主伺服器   eve   tool   中繼   

為了實現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

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