點擊下載
本文文檔

當前位置：首頁 - 科技 - 知識百科 - 正文

MySQL雙主機雙Master提案測試

來源：懂視網責編：小采時間：2020-11-09 13:36:59

MySQL雙主機雙Master提案測試

MySQL雙主機雙Master提案測試:MySQL雙主機雙Master方案測試必須避免系統中任何一處出現單點故障。如果主MySQL所在的物理機器出現硬件故障，就是一個單點故障，雖然可以迅速的用一臺從屬機(Slave)升級為主機（Master). 但是也會比較慢。最好的方法還是用雙Master方案避免出現單

推薦度：

點擊下載本文 文檔為doc格式

導讀MySQL雙主機雙Master提案測試:MySQL雙主機雙Master方案測試必須避免系統中任何一處出現單點故障。如果主MySQL所在的物理機器出現硬件故障，就是一個單點故障，雖然可以迅速的用一臺從屬機(Slave)升級為主機（Master). 但是也會比較慢。最好的方法還是用雙Master方案避免出現單

MySQL雙主機雙Master方案測試必須避免系統中任何一處出現單點故障。? ? 如果主MySQL所在的物理機器出現硬件故障，就是一個單點故障，雖然可以迅速的用一臺從屬機(Slave)升級為主機（Master). 但是也會比較慢。最好的方法還是用雙Master方案避免出現單點故障

MySQL雙主機雙Master方案測試

必須避免系統中任何一處出現單點故障。?

如果主MySQL所在的物理機器出現硬件故障，就是一個單點故障，雖然可以迅速的用一臺從屬機(Slave)升級為主機（Master). 但是也會比較慢。最好的方法還是用雙Master方案避免出現單點故障。

此試驗預計花費時間 2天。?

需要解決下面二個問題

1.實現二臺主數據庫的循環同步,即Ａ庫有變化,Ｂ庫同步;同理Ｂ庫有變化,Ａ庫同步;

2.當Ａ庫發生故障后,及時報警,同時Ｂ庫替換為Ａ庫為主庫,與所有slave服務器通訊正常;

準備工作

寫道一般來說,master機與slave機的mysql版本盡量一致,或者master比slave高.但我們這里是雙master,即互為主從,所以mysql的版本要保持一致;

打算用我自己的機器與192.168.0.8的機器來做測試;

A:192.168.0.219(本機)
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

升級mysql

將本機的mysql升級到5.5版本

創建同步帳號

因為服務器Ａ及服務器Ｂ互為主從,所以要分別為其建立同步帳號,都授予REPLIATION SLAVE權限.

Ａ:
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;

如果不行直接進phpmyadmin去創建也一樣;

測試一下.
Ａ：
mysql -h192.168.0.8 -ureplicate -p123456 能進入mysql>　OK!!
B:
mysql -h192.168.0.219 -ureplicate -p123456 能進入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 #如果一個master掛掉,另一個馬上接管
 #下面3句,服務器頻繁的刷新日志,這個保證了在其中一臺掛掉的話,日志刷新到另外一臺,從而保證了數據的同步. 
 sync-binlog = 1 
 auto_increment_offset = 1
 auto_increment_increment = 2

重啟mysql服務: sudo /etc/init.d/mysql restart
查看master狀態
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 #如果一個master掛掉,另一個馬上接管
 #下面3句,服務器頻繁的刷新日志,這個保證了在其中一臺掛掉的話,日志刷新到另外一臺,從而保證了數據的同步. 
 sync-binlog = 1 
 auto_increment_offset = 2
 auto_increment_increment = 2

重啟mysql服務: sudo /usr/local/etc/rc.d/mysql-server restart
查看master狀態:
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服務器上的從服務線程

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



二臺服務器的：
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

均為Yes表示啟動成功;

開始測試數據

寫道 A:

先在Ａ建立一個test-xf的數據庫,我們發現B已經同步好了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'手機號碼',time timestamp DEFAULT now() comment'時間');
Query OK, 0 rows affected (0.17 sec)

建完之后我們發現Ｂ中的test-xf也已經存在了;

接下來去Ｂ服務器進行測試;

在B服務器插入數據;
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)

去Ａ服務器中查看也成功了.

＊＊＊我又在Ｂ服務器建了一個新庫叫test-007,Ａ服務器卻沒有同步,原因就是我們在my.cnf的時候設置的同步操作數據庫僅有test-xf

配置參數解釋

寫道 server-id
　　ID值唯一的標識了復制群集中的主從服務器，因此它們必須各不相同。master_id必須為1到232–1之間的一個正整數值，slave_id值必須為2到232–1之間的一個正整數值。

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

binlog-do-db
　　表示需要記錄進制日志的數據庫。如果有多個數據庫可用逗號分隔，或者使用多個binlog-do-db選項

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

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

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

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

slave-skip-errors
　　在復制過程，由于各種原因導致binlog中的sql出錯，默認情況下，從庫會停止復制，要用戶介入。可以設置Slave-skip-errors來定義錯誤號，如果復制過程中遇到的錯誤號是定義的錯誤號，便可以跳過。如果從庫是用來做備份，設置這個參數會存在數據不一致，不要使用。如果是分擔主庫的查詢壓力，可以考慮。

sync-binlog=1 or N
　　sync-binlog的默認值是0，這種模式下，MySQL不會同步到磁盤中去。這樣的話，MySQL依賴操作系統來刷新二進制日志binary log，就像操作系統刷其他文件的機制一樣。因此如果操作系統或機器(不僅僅是MySQL服務器)崩潰，有可能binlog中最后的語句丟失了。要想防止這種情況，你可以使用sync-binlog全局變量，使binlog在每N次binlog寫入后與硬盤同步。當sync_binlog變量設置為1是最安全的，因為在crash崩潰的情況下，你的二進制日志binary log只有可能丟失最多一個語句或者一個事務。但是，這也是最慢的一種方式（除非磁盤有使用帶蓄電池后備電源的緩存cache，使得同步到磁盤的操作非常快）。
　　即使sync-binlog設置為1,出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。如果使用InnoDB表，MySQL服務器處理COMMIT語句，它將整個事務寫入binlog并將事務提交到InnoDB中。如果在兩次操作之間出現崩潰，重啟時，事務被InnoDB回滾，但仍然存在binlog中。可以用–innodb-safe-binlog選項來增加InnoDB表內容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要–innodb-safe-binlog；由于引入了XA事務支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務的 binlog(sync_binlog =1)和(默認情況為真)InnoDB日志與硬盤同步，該選項的效果是崩潰后重啟時，在滾回事務后，MySQL服務器從binlog剪切回滾的 InnoDB事務。這樣可以確保binlog反饋InnoDB表的確切數據等，并使從服務器保持與主服務器保持同步(不接收回滾的語句)。

auto_increment_offset和auto_increment_increment
　　auto_increment_increment和auto_increment_offset用于主－主服務器(master-to-master)復制，并可以用來控制AUTO_INCREMENT列的操作。兩個變量均可以設置為全局或局部變量，并且假定每個值都可以為1到65,535之間的整數值。將其中一個變量設置為0會使該變量為1。
　　這兩個變量影響AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值,auto_increment_offset確定AUTO_INCREMENT列值的起點。
　　如果auto_increment_offset的值大于auto_increment_increment的值，則auto_increment_offset的值被忽略。例如：表內已有一些數據，就會用現在已有的最大的自增值做為初始值。

管理運行命令

寫道

A.用于控制主服務器的SQL語句
PURGE MASTER LOGS
　　用于刪除列于在指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引文件中的清單中被刪除，這樣被給定的日志成為第一個。

RESET MASTER
　　可以刪除列于索引文件中的所有二進制日志，把二進制日志索引文件重新設置為空，并創建一個新的二進制日志文件。

SET SQL_LOG_BIN
　　如果客戶端使用一個有SUPER權限的賬戶連接，則可以禁用或啟用當前連接的二進制日志記錄。如果客戶端沒有此權限，則語句被拒絕，并伴隨有錯誤。

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

SHOW MASTER LOGS
　　用于列出服務器中的二進制日志文件。

SHOW MASTER STATUS　
　　用于提供主服務器二進制日志文件的狀態信息。

SHOW SLAVE HOSTS
　　用于顯示當前使用主服務器注冊的復制從屬服務器的清單。
　　
B、用于控制從服務器的SQL語句
CHANGE MASTER TO
　　可以更改從屬服務器用于與主服務器進行連接和通訊的參數。
　　
LOAD DATA FROM MASTER
　　用于對主服務器進行快照，并拷貝到從屬服務器上。

LOAD TABLE tbl_name FROM MASTER
　　用于把表的拷貝從主服務器轉移到從屬服務器。

MASTER_POS_WAIT()
　　這實際上是一個函數，而不是一個語句。它被用于確認，從屬服務器已讀取并執行了到達主服務器二進制日志的給定位置。

RESET SLAVE
　　用于讓從屬服務器忘記其在主服務器的二進制日志中的復制位置。

SET GLOBAL SQL_SLAVE_SKIP_COUNTER
　　從主服務器中跳過后面的n個事件。要復原由語句導致的復制中止，這是有用的。

SHOW SLAVE STATUS
　　用于提供有關從屬服務器線程的關鍵參數的信息。

START SLAVE
　　用于啟動從屬服務器線程

STOP SLAVE
　　用于中止從屬服務器線程

主從復制如何提高可靠性

寫道主從單向復制，從服務器只是實時的保存了主服務器的一個副本。當主服務器發生故障時，可以切換到從服務器繼續做查詢，但不能更新。

如果采用雙向復制，即兩臺mysql服務器即作為主服務器，又作為從服務器。那么兩者都可以執行更新操作并能實現負載均衡，當一方出現故障時，另一方不受影響。
但是，除非能保證任何更新操作順序都是安全的，否則雙向復制會導致失敗。

為了更好的提高可靠性和可用性，需要當主服務器不可用時，令從服務器成為Master。
原來的主服務器設定為Slave，并從新的Master上同步更新。現在已經有了一個這樣開源解決方法[MySQL Master-Master Replication Manager]

帶從服務器的MySql主主復制