MySQL雙主機(jī)雙Master方案測(cè)試 必須避免系統(tǒng)中任何一處出現(xiàn)單點(diǎn)故障。? ? 如果主MySQL所在的物理機(jī)器出現(xiàn)硬件故障，就是一個(gè)單點(diǎn)故障，雖然可以迅速的用一臺(tái)從屬機(jī)(Slave)升級(jí)為主機(jī)（Master). 但是也會(huì)比較慢。 最好的方法還是用雙Master方案避免出現(xiàn)單點(diǎn)故障

必須避免系統(tǒng)中任何一處出現(xiàn)單點(diǎn)故障。?

?

如果主MySQL所在的物理機(jī)器出現(xiàn)硬件故障，就是一個(gè)單點(diǎn)故障，雖然可以迅速的用一臺(tái)從屬機(jī)(Slave)升級(jí)為主機(jī)（Master). 但是也會(huì)比較慢。 最好的方法還是用雙Master方案避免出現(xiàn)單點(diǎn)故障。

?

此試驗(yàn)預(yù)計(jì)花費(fèi)時(shí)間 2天。?

?

?

需要解決下面二個(gè)問題

?

1.實(shí)現(xiàn)二臺(tái)主數(shù)據(jù)庫的循環(huán)同步,即Ａ庫有變化,Ｂ庫同步;同理Ｂ庫有變化,Ａ庫同步;

?

2.當(dāng)Ａ庫發(fā)生故障后,及時(shí)報(bào)警,同時(shí)Ｂ庫替換為Ａ庫為主庫,與所有slave服務(wù)器通訊正常;

?

?

準(zhǔn)備工作

打算用我自己的機(jī)器與192.168.0.8的機(jī)器來做測(cè)試;

A:192.168.0.219(本機(jī))
B:192.168.0.8

查看一下版本
A:$ mysql -V
mysql Ver 14.14 Distrib 5.1.62, for debian-linux-gnu (i486) using readline 6.1

B:$ mysql -V
mysql Ver 14.14 Distrib 5.5.13, for FreeBSD8.1 (amd64) using 5.2

?

升級(jí)mysql

將本機(jī)的mysql升級(jí)到5.5版本

?

創(chuàng)建同步帳號(hào)

因?yàn)榉?wù)器Ａ及服務(wù)器Ｂ互為主從,所以要分別為其建立同步帳號(hào),都授予REPLIATION SLAVE權(quán)限.

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

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

如果不行直接進(jìn)phpmyadmin去創(chuàng)建也一樣;

測(cè)試一下.
Ａ：
mysql -h192.168.0.8 -ureplicate -p123456 能進(jìn)入mysql>　OK!!
B:
mysql -h192.168.0.219 -ureplicate -p123456 能進(jìn)入mysql>　OK!!

?

修改配置文件my.cnf

A:
[mysqld]
 server-id = 1
 log-bin = mysql-bin
 binlog-do-db = test-xf
 binlog-ignore-db = mysql
 #主-主需要多添加的部分
 replicate-do-db = test-xf　　
 replicate-ignore-db = mysql,information_schema
 log-slave-updates #如果一個(gè)master掛掉,另一個(gè)馬上接管
 #下面3句,服務(wù)器頻繁的刷新日志,這個(gè)保證了在其中一臺(tái)掛掉的話,日志刷新到另外一臺(tái),從而保證了數(shù)據(jù)的同步. 
 sync-binlog = 1 
 auto_increment_offset = 1
 auto_increment_increment = 2

重啟mysql服務(wù): sudo /etc/init.d/mysql restart
查看master狀態(tài)
mysql> show master status\G;
*************************** 1. row ***************************
 File: mysql-bin.000006
 Position: 977
 Binlog_Do_DB: test-xf
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

ERROR: 
No query specified


B:
[mysqld]
 server-id = 2
 log-bin = mysql-bin
 binlog-do-db = test-xf
 binlog-ignore-db = mysql
 #主-主需要多添加的部分
 replicate-do-db = test-xf
 replicate-ignore-db = mysql,information_schema
 log-slave-updates #如果一個(gè)master掛掉,另一個(gè)馬上接管
 #下面3句,服務(wù)器頻繁的刷新日志,這個(gè)保證了在其中一臺(tái)掛掉的話,日志刷新到另外一臺(tái),從而保證了數(shù)據(jù)的同步. 
 sync-binlog = 1 
 auto_increment_offset = 2
 auto_increment_increment = 2

重啟mysql服務(wù): sudo /usr/local/etc/rc.d/mysql-server restart
查看master狀態(tài):
mysql> show master status\G;
*************************** 1. row ***************************
 File: mysql-bin.000005
 Position: 348
 Binlog_Do_DB: test-xf
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

ERROR: 
No query specified

?

指定同步位置

?

A:
mysql> change master to
 -> master_host = '192.168.0.8',
 -> master_user = 'replicate',
 -> master_password = '123456',
 -> master_log_file = 'mysql-bin.000005',
 -> master_log_pos = 348;
Query OK, 0 rows affected (0.44 sec)

B:
mysql> change master to
 -> master_host = '192.168.0.219',
 -> master_user = 'replicate',
 -> master_password = '123456',
 -> master_log_file = 'mysql-bin.000006',
 -> master_log_pos = 977;
Query OK, 0 rows affected (0.05 sec)

?

?

?

重啟A,B服務(wù)器上的從服務(wù)線程

A:
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.0.8
 Master_User: replicate
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000005
 Read_Master_Log_Pos: 348
 Relay_Log_File: xiaofei-desktop-relay-bin.000002
 Relay_Log_Pos: 253
 Relay_Master_Log_File: mysql-bin.000005
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: test-xf
 Replicate_Ignore_DB: mysql,information_schema
 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: 107
 Relay_Log_Space: 419
 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
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error: 
 Last_SQL_Errno: 0
 Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 2
1 row in set (0.01 sec)

ERROR: 
No query specified


B:
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.0.219
 Master_User: replicate
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000006
 Read_Master_Log_Pos: 977
 Relay_Log_File: queen-relay-bin.000002
 Relay_Log_Pos: 253
 Relay_Master_Log_File: mysql-bin.000006
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: test-xf
 Replicate_Ignore_DB: mysql,information_schema
 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: 107
 Relay_Log_Space: 409
 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
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error: 
 Last_SQL_Errno: 0
 Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR: 
No query specified



二臺(tái)服務(wù)器的：
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

均為Yes表示啟動(dòng)成功;

?

?

開始測(cè)試數(shù)據(jù)

先在Ａ建立一個(gè)test-xf的數(shù)據(jù)庫,我們發(fā)現(xiàn)B已經(jīng)同步好了test-xf庫;

建tb_mobile表：
mysql> use test-xf;
Database changed
mysql> set names 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> create table tb_mobile(mobile VARCHAR(20) comment'手機(jī)號(hào)碼',time timestamp DEFAULT now() comment'時(shí)間');
Query OK, 0 rows affected (0.17 sec)

建完之后我們發(fā)現(xiàn)Ｂ中的test-xf也已經(jīng)存在了;

接下來去Ｂ服務(wù)器進(jìn)行測(cè)試;

在B服務(wù)器插入數(shù)據(jù);
mysql> insert into tb_mobile(mobile) values('12254123876');
Query OK, 1 row affected (0.05 sec)

mysql> select * from tb_mobile;
+-------------+---------------------+
| mobile | time |
+-------------+---------------------+
| 12254123876 | 2012-06-01 14:40:15 |
+-------------+---------------------+
1 row in set (0.03 sec)

去Ａ服務(wù)器中查看也成功了.

＊＊＊我又在Ｂ服務(wù)器建了一個(gè)新庫叫test-007,Ａ服務(wù)器卻沒有同步,原因就是我們?cè)趍y.cnf的時(shí)候設(shè)置的同步操作數(shù)據(jù)庫僅有test-xf

?

?

配置參數(shù)解釋

log-bin
　　表示打開binlog,打開該選項(xiàng)才可以通過I/O寫到Slave的relay-log,也是可以進(jìn)行replication的前提;

binlog-do-db
　　表示需要記錄進(jìn)制日志的數(shù)據(jù)庫。如果有多個(gè)數(shù)據(jù)庫可用逗號(hào)分隔，或者使用多個(gè)binlog-do-db選項(xiàng)

binlog-ignore-db
　　表示不需要記錄二進(jìn)制日志的數(shù)據(jù)庫。如果有多個(gè)數(shù)據(jù)庫可用逗號(hào)分隔，或者使用多個(gè)binlog-do-db選項(xiàng)

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

replicate-ignore-db=mysql
　　表示不需要同步的數(shù)據(jù)庫，如果有多個(gè)數(shù)據(jù)庫可用逗號(hào)分隔，或者使用多個(gè)replicate-ignore-db=mysql選項(xiàng)

log-slave-updates
　　配置從庫上的更新操作是否寫入二進(jìn)制文件，如果這臺(tái)從庫，還要做其他從庫的主庫，那么就需要打這個(gè)參數(shù)，以便從庫的從庫能夠進(jìn)行日志同步

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

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

?

?

管理運(yùn)行命令

?

?

RESET MASTER
　　可以刪除列于索引文件中的所有二進(jìn)制日志，把二進(jìn)制日志索引文件重新設(shè)置為空，并創(chuàng)建一個(gè)新的二進(jìn)制日志文件。

SET SQL_LOG_BIN
　　如果客戶端使用一個(gè)有SUPER權(quán)限的賬戶連接，則可以禁用或啟用當(dāng)前連接的二進(jìn)制日志記錄。如果客戶端沒有此權(quán)限，則語句被拒絕，并伴隨有錯(cuò)誤。

SHOW BINLOG EVENTS
　　用于在二進(jìn)制日志中顯示事件。如果您不指定’log_name’，則顯示第一個(gè)二進(jìn)制日志。

SHOW MASTER LOGS
　　用于列出服務(wù)器中的二進(jìn)制日志文件。

SHOW MASTER STATUS　
　　用于提供主服務(wù)器二進(jìn)制日志文件的狀態(tài)信息。

SHOW SLAVE HOSTS
　　用于顯示當(dāng)前使用主服務(wù)器注冊(cè)的復(fù)制從屬服務(wù)器的清單。
　　
B、用于控制從服務(wù)器的SQL語句
CHANGE MASTER TO
　　可以更改從屬服務(wù)器用于與主服務(wù)器進(jìn)行連接和通訊的參數(shù)。
　　
LOAD DATA FROM MASTER
　　用于對(duì)主服務(wù)器進(jìn)行快照，并拷貝到從屬服務(wù)器上。

LOAD TABLE tbl_name FROM MASTER
　　用于把表的拷貝從主服務(wù)器轉(zhuǎn)移到從屬服務(wù)器。

MASTER_POS_WAIT()
　　這實(shí)際上是一個(gè)函數(shù)，而不是一個(gè)語句。它被用于確認(rèn)，從屬服務(wù)器已讀取并執(zhí)行了到達(dá)主服務(wù)器二進(jìn)制日志的給定位置。

RESET SLAVE
　　用于讓從屬服務(wù)器忘記其在主服務(wù)器的二進(jìn)制日志中的復(fù)制位置。

SET GLOBAL SQL_SLAVE_SKIP_COUNTER
　　從主服務(wù)器中跳過后面的n個(gè)事件。要復(fù)原由語句導(dǎo)致的復(fù)制中止，這是有用的。

SHOW SLAVE STATUS
　　用于提供有關(guān)從屬服務(wù)器線程的關(guān)鍵參數(shù)的信息。

START SLAVE
　　用于啟動(dòng)從屬服務(wù)器線程

STOP SLAVE
　　用于中止從屬服務(wù)器線程

?

?

主從復(fù)制如何提高可靠性

如果采用雙向復(fù)制，即兩臺(tái)mysql服務(wù)器即作為主服務(wù)器，又作為從服務(wù)器。那么兩者都可以執(zhí)行更新操作并能實(shí)現(xiàn)負(fù)載均衡，當(dāng)一方出現(xiàn)故障時(shí)，另一方不受影響。
但是，除非能保證任何更新操作順序都是安全的，否則雙向復(fù)制會(huì)導(dǎo)致失敗。

為了更好的提高可靠性和可用性，需要當(dāng)主服務(wù)器不可用時(shí)，令從服務(wù)器成為Master。
原來的主服務(wù)器設(shè)定為Slave，并從新的Master上同步更新。現(xiàn)在已經(jīng)有了一個(gè)這樣開源解決方法[MySQL Master-Master Replication Manager]

?

?

?

帶從服務(wù)器的MySql主主復(fù)制

http://www.litvip.com/2011/06/29/326

?

http://www.cnblogs.com/czh-liyu/archive/2012/06/01/2530482.html

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com