mysql雙機熱備的實現(xiàn)

mysql雙機熱備的實現(xiàn)

轉(zhuǎn)：http://blog.csdn.net/qq394829044/article/details/53203645
Mysql數(shù)據(jù)庫沒有增量備份的機制，當(dāng)數(shù)據(jù)量太大的時候備份是一個很大的問題。還好mysql數(shù)據(jù)庫提供了一種主從備份的機制，其實就是把主數(shù)據(jù)庫的所有的數(shù)據(jù)同時寫到備份的數(shù)據(jù)庫中。實現(xiàn)mysql數(shù)據(jù)庫的熱備份。
要想實現(xiàn)雙機的熱備，首先要了解主從數(shù)據(jù)庫服務(wù)器的版本的需求。要實現(xiàn)熱備mysql的版本都高于3.2。還有一個基本的原則就是作為從數(shù)據(jù)庫的數(shù)據(jù)版本可以高于主服務(wù)器數(shù)據(jù)庫的版本，但是不可以低于主服務(wù)器的數(shù)據(jù)庫版本。

當(dāng)然要實現(xiàn)mysql雙機熱備，除了mysql本身自帶的REPLICATION功能可以實現(xiàn)外，也可以用Heartbeat這個開源軟件來實現(xiàn)。不過本文主要還是講如何用mysql自帶的REPLICATION來實現(xiàn)mysql雙機熱備的功能。

1. 準(zhǔn)備服務(wù)器

由于Mysql不同版本之間的（二進制日志）binlog格式可能會不太一樣，因此最好的搭配組合是主（Master）服務(wù)器的Mysql版本和從（Slave）服務(wù)器版本相同或者更低，主服務(wù)器的版本肯定不能高于從服務(wù)器版本。
本次我用于測試的兩臺服務(wù)器版本都是Mysql-5.5.17。

2. Mysql 建立主－從服務(wù)器雙機熱備配置步驟

2.1環(huán)境描述

A服務(wù)器（主服務(wù)器Master）：59.151.15.36
B服務(wù)器（從服務(wù)器Slave）：218.206.70.146
主從服務(wù)器的Mysql版本皆為5.5.17
Linux環(huán)境下
將主服務(wù)器需要同步的數(shù)據(jù)庫內(nèi)容進行備份一份，上傳到從服務(wù)器上，保證始初時兩服務(wù)器中數(shù)據(jù)庫內(nèi)容一致。
不過這里說明下，由于我是利用Mysql在安裝后就有的數(shù)據(jù)庫test進行測試的，所以兩臺服務(wù)器里面是沒有建立表的，只不分別在test里面建立了同樣的一張空表tb_mobile;
Sql語句如下：
mysql> create table tb_mobile( mobile VARCHAR(20) comment'手機號碼', time timestamp DEFAULT now() comment'時間' );

2.2 主服務(wù)器Master配置

2.2.1 創(chuàng)建同步用戶

進入mysql操作界面，在主服務(wù)器上為從服務(wù)器建立一個連接帳戶，該帳戶必須授予REPLICATION SLAVE權(quán)限。因為從mysql版本3.2以后就可以通過REPLICATION對其進行雙機熱備的功能操作。
操作指令如下：

mysql> grant replication slave on *.* to 'replicate'@'218.206.70.146' identified by '123456';
mysql> flush privileges;

創(chuàng)建好同步連接帳戶后，我們可以通過在從服務(wù)器（Slave）上用replicat帳戶對主服務(wù)器（Master）數(shù)據(jù)庫進行訪問下，看下是否能連接成功。
在從服務(wù)器（Slave）上輸入如下指令：

[root@YD146 ~]# mysql -h59.151.15.36 -ureplicate -p123456

如果出現(xiàn)下面的結(jié)果，則表示能登錄成功，說明可以對這兩臺服務(wù)器進行雙機熱備進行操作。

2.2.2 修改mysql配置文件

如果上面的準(zhǔn)備工作做好，那邊我們就可以進行對mysql配置文件進行修改了，首先找到mysql配置所有在目錄，一般在安裝好mysql服務(wù)后，都會將配置文件復(fù)制一一份出來放到/ect目錄下面，并且配置文件命名為：my.cnf。即配置文件準(zhǔn)確目錄為/etc/my.cnf
(Linux下用rpm包安裝的MySQL是不會安裝/etc/my.cnf文件的，
至于為什么沒有這個文件而MySQL卻也能正常啟動和作用，在點有兩個說法，
第一種說法，my.cnf只是MySQL啟動時的一個參數(shù)文件，可以沒有它，這時MySQL會用內(nèi)置的默認(rèn)參數(shù)啟動，
第二種說法，MySQL在啟動時自動使用/usr/share/mysql目錄下的my-medium.cnf文件，這種說法僅限于rpm包安裝的MySQL，
解決方法，只需要復(fù)制一個/usr/share/mysql目錄下的my-medium.cnf文件到/etc目錄，并改名為my.cnf即可。)
找到配置文件my.cnf打開后，在[mysqld]下修改即可：

[mysqld]
server-id = 1　　　　　　　　//唯一id
log-bin=mysql-bin              //其中這兩行是本來就有的，可以不用動，添加下面兩行即可.指定日志文件
binlog-do-db = test　　　　 //記錄日志的數(shù)據(jù)庫
binlog-ignore-db = mysql    //不記錄日志的數(shù)據(jù)庫

2.2.3 重啟mysql服務(wù)

修改完配置文件后，保存后，重啟一下mysql服務(wù)，如果成功則沒問題。

service mysql restart

進入mysql服務(wù)后，可通過指令查看Master狀態(tài)，輸入如下指令：

flush tables with read lock;

注意看里面的參數(shù)，特別前面兩個File和Position，在從服務(wù)器（Slave）配置主從關(guān)系會有用到的。
注：這里使用了鎖表，目的是為了產(chǎn)生環(huán)境中不讓進新的數(shù)據(jù)，好讓從服務(wù)器定位同步位置，初次同步完成后，記得解鎖。

unlock tables;

2.3 從服務(wù)器Slave配置

2.3.1修改配置文件

因為這里面是以主－從方式實現(xiàn)mysql雙機熱備的，所以在從服務(wù)器就不用在建立同步帳戶了，直接打開配置文件my.cnf進行修改即可，道理還是同修改主服務(wù)器上的一樣，只不過需要修改的參數(shù)不一樣而已。如下：

[mysqld]
server-id = 2
log-bin=mysql-bin
replicate-do-db = test
replicate-ignore-db = mysql,information_schema,performance_schema

2.3.2重啟mysql服務(wù)

修改完配置文件后，保存后，重啟一下mysql服務(wù)，如果成功則沒問題。

service mysql restart

2.3.3用change mster 語句指定同步位置

這步是最關(guān)鍵的一步了，在進入mysql操作界面后，輸入如下指令：

mysql>stop slave;          //先停步slave服務(wù)線程，這個是很重要的，如果不這樣做會造成以下操作不成功。
mysql>change master to
>master_host='59.151.15.36',master_user='replicate',master_password='123456',
> master_log_file=' mysql-bin.000016 ',master_log_pos=107;

注：master_log_file, master_log_pos由主服務(wù)器（Master）查出的狀態(tài)值中確定。也就是剛剛叫注意的。master_log_file對應(yīng)File, master_log_pos對應(yīng)Position。Mysql 5.x以上版本已經(jīng)不支持在配置文件中指定主服務(wù)器相關(guān)選項。
遇到的問題，如果按上面步驟之后還出現(xiàn)如下情況：

stop slave

could not initialize master info structure: more error nessaes can be found in the MySQL error log

則要重新設(shè)置slave。指令如下

mysql>stop slave;
mysql>reset slave;

之后停止slave線程重新開始。成功后，則可以開啟slave線程了。

mysql>start slave;

2.3.4查看從服務(wù)器（Slave）狀態(tài)

用如下指令進行查看

mysql> show slave status\G;

查看下面兩項值均為Yes，即表示設(shè)置從服務(wù)器成功。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

2.4 測試同步

略

3. Mysql 建立主－主服務(wù)器雙機熱備配置步驟

服務(wù)器還是用回現(xiàn)在這兩臺服務(wù)器

3.1創(chuàng)建同步用戶

同時在主從服務(wù)器建立一個連接帳戶，該帳戶必須授予REPLIATION SLAVE權(quán)限。這里因為服務(wù)器A和服務(wù)器B互為主從，所以都要分別建立一個同步用戶。
服務(wù)器A：

mysql> grant replication slave on *.* to 'replicate'@'218.206.70.146' identified by '123456';
mysql> flush privileges;

服務(wù)器B：

mysql> grant replication slave on *.* to 'replicate'@'59.151.15.36' identified by '123456';
mysql> flush privileges;

3.2修改配置文件my.cnf
服務(wù)器A

[mysqld]
       server-id = 1
log-bin=mysql-bin 
binlog-do-db = test
       binlog-ignore-db = mysql

主－主形式需要多添加的部分

        log-slave-updates
       sync_binlog = 1
       auto_increment_offset = 1
       auto_increment_increment = 2
       replicate-do-db = test
       replicate-ignore-db = mysql,information_schema

服務(wù)器B：

[mysqld]
server-id = 2
log-bin=mysql-bin 
       replicate-do-db = test
       replicate-ignore-db = mysql,information_schema,performance_schema

主－主形式需要多添加的部分

       binlog-do-db = test
       binlog-ignore-db = mysql
       log-slave-updates
       sync_binlog = 1
       auto_increment_offset = 2
       auto_increment_increment = 2

3.3分別重啟A服務(wù)器和B服務(wù)器上的mysql服務(wù)

重啟服務(wù)器方式和上面的一樣，這里就不做講解了。

3.4分別查A服務(wù)器和B服務(wù)器作為主服務(wù)器的狀態(tài)

服務(wù)器A

show master status\G

服務(wù)器B

show master status\G

3.5分別在A服務(wù)器和B服務(wù)器上用change master to 指定同步位置

服務(wù)器A：

mysql>change master to
>master_host='218.206.70.146',master_user='replicate',master_password='123456',
> master_log_file=' mysql-bin.000011 ',master_log_pos=497;

服務(wù)器B：

mysql>change master to
>master_host='59.151.15.36',master_user='replicate',master_password='123456',
> master_log_file=' mysql-bin.000016 ',master_log_pos=107;

3.6 分別在A和B服務(wù)器上重啟從服務(wù)線程

mysql>start slave;

3.7 分別在A和B服務(wù)器上查看從服務(wù)器狀態(tài)

mysql>show slave status\G;

查看下面兩項值均為Yes，即表示設(shè)置從服務(wù)器成功。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.8 測試主－主同步例子

略

4. 配置參數(shù)說明

Server-id

ID值唯一的標(biāo)識了復(fù)制群集中的主從服務(wù)器，因此它們必須各不相同。Master_id必須為1到232－1之間的一個正整數(shù)值，slave_id值必須為2到232－1之間的一個正整數(shù)值。

Log-bin

表示打開binlog，打開該選項才可以通過I/O寫到Slave的relay-log，也是可以進行replication的前提。

Binlog-do-db

表示需要記錄二進制日志的數(shù)據(jù)庫。如果有多個數(shù)據(jù)可以用逗號分隔，或者使用多個binlog-do-dg選項。

Binglog-ingore-db

表示不需要記錄二進制日志的數(shù)據(jù)庫，如果有多個數(shù)據(jù)庫可用逗號分隔，或者使用多binglog-ignore-db選項。

Replicate-do-db

表示需要同步的數(shù)據(jù)庫，如果有多個數(shù)據(jù)可用逗號分隔，或者使用多個replicate-do-db選項。

Replicate-ignore-db

表示不需要同步的數(shù)據(jù)庫，如果有多個數(shù)據(jù)庫可用逗號分隔，或者使用多個replicate-ignore-db選項。

Master-connect-retry

master-connect-retry=n表示從服務(wù)器與主服務(wù)器的連接沒有成功，則等待n秒（s）后再進行管理方式（默認(rèn)設(shè)置是60s）。如果從服務(wù)器存在mater.info文件，它將忽略些選項。

Log-slave-updates

配置從庫上的更新操作是否寫入二進制文件，如果這臺從庫，還要做其他從庫的主庫，那么就需要打這個參數(shù)，以便從庫的從庫能夠進行日志同步。

Slave-skip-errors

在復(fù)制過程，由于各種原因?qū)е耣inglo中的sql出錯，默認(rèn)情況下，從庫會停止復(fù)制，要用戶介入?？梢栽O(shè)置slave-skip-errors來定義錯誤號，如果復(fù)制過程中遇到的錯誤是定義的錯誤號，便可以路過。如果從庫是用來做備份，設(shè)置這個參數(shù)會存在數(shù)據(jù)不一致，不要使用。如果是分擔(dān)主庫的查詢壓力，可以考慮。

--slave-skip-errors=[err_code1,err_code2,...|all|ddl_exist_errors]

MySQL 5.6 as well as MySQL Cluster NDB 7.3 support an additional shorthand value 
ddl_exist_errors, which is equivalent to the error code list 1007,1008,1050,1051,
1054,1060,1061,1068,1094,1146.
Examples:


--slave-skip-errors=1062,1053
--slave-skip-errors=all
--slave-skip-errors=ddl_exist_errors
Sync_binlog=1 Or N

Sync_binlog的默認(rèn)值是0，這種模式下，MySQL不會同步到磁盤中去。這樣的話，Mysql依賴操作系統(tǒng)來刷新二進制日志binary log，就像操作系統(tǒng)刷新其他文件的機制一樣。因此如果操作系統(tǒng)或機器（不僅僅是Mysql服務(wù)器）崩潰，有可能binlog中最后的語句丟失了。要想防止這種情況，可以使用sync_binlog全局變量，使binlog在每Ｎ次binlog寫入后與硬盤同步。當(dāng)sync_binlog變量設(shè)置為１是最安全的，因為在crash崩潰的情況下，你的二進制日志binary log只有可能丟失最多一個語句或者一個事務(wù)。但是，這也是最慢的一種方式（除非磁盤有使用帶蓄電池后備電源的緩存cache,使得同步到磁盤的操作非?？欤?br> 即使sync_binlog設(shè)置為１，出現(xiàn)崩潰時，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性。如果使用InnoDB表，Mysql服務(wù)器處理COMMIT語句，它將整個事務(wù)寫入binlog并將事務(wù)提交到InnoDB中。如果在兩次操作之間出現(xiàn)崩潰，重啟時，事務(wù)被InnoDB回滾，但仍然存在binlog中?？梢杂?innodb-safe-binlog選項來增加InnoDB表內(nèi)容和binlog之間的一致性。（注釋：在Mysql 5.1版本中不需要-innodb-safe-binlog；由于引入了XA事務(wù)支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務(wù)的binlog(sync_binlog=1)和（默認(rèn)情況為真）InnoDB日志與硬盤同步，該選項的效果是崩潰后重啟時，在滾回事務(wù)后，Mysql服務(wù)器從binlog剪切回滾的InnoDB事務(wù)。這樣可以確保binlog反饋InnoDB表的確切數(shù)據(jù)等，并使從服務(wù)器保持與主服務(wù)器保持同步（不接收回滾的語句）。
Auto_increment_offset和Auto_increment_increment
Auto_increment_increment和auto_increment_offset用于主－主服務(wù)器（master-to-master）復(fù)制，并可以用來控制AUTO_INCREMENT列的操作。兩個變量均可以設(shè)置為全局或局部變量，并且假定每個值都可以為1到65,535之間的整數(shù)值。將其中一個變量設(shè)置為0會使該變量為1。
這兩個變量影響AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值，auto_increment_offset確定AUTO_INCREMENT列值的起點。
如果auto_increment_offset的值大于auto_increment_increment的值，則auto_increment_offset的值被忽略。例如：表內(nèi)已有一些數(shù)據(jù)，就會用現(xiàn)在已有的最大自增值做為初始值。

如何解決MySQL主從同步錯誤的SQL
解決：

stop slave;

表示跳過一步錯誤，后面的數(shù)字可變

set global sql_slave_skip_counter =1;
start slave;

之后再用mysql> show slave status\G 查看：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

ok，現(xiàn)在主從同步狀態(tài)正常了。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容