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，每個資料庫應該不一樣

master-host=192.168.100.100 //主伺服器的IP位址或者域名

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

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

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。

設定同步資料庫的使用者帳号

分别重新開機MYSQL_A和MYSQL_B上的MYSQL資料庫，然後登陸mysql，運作如下指令

MYSQL_A：

MYSQL_B：

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

分别根本法MYSQL_A和MYSQL_B上的mysql檢視主伺服器狀态

mysql> show master statusG;

+---------------+----------+--------------+------------------+

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

分别登陸上MYSQL_A和MYSQL_B的MYSQL并設定主伺服器的各種參數：

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; //日志檔案的開始位置(前面要求記住的參數)

-> MASTER_HOST='192.168.100.100', //同步資料庫的IP

-> 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差別，支援跨庫更新

注解：

伺服器ID号，整數值，保證唯一辨別一台伺服器就可以

打開二進制日志

relay-log=relay-bin

中繼日志名稱

主伺服器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 這個參數一定要加上，否則不會給更新的記錄些到二進制檔案裡

主備機執行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雙機熱備份的配置實施(問題總結)

繼續閱讀

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

配置網頁内容通路

艱難安裝LDAP,SSL認證

Apache配置SSLApache配置SSL

Windows下配置Apache的SSL服務

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述