快捷導(dǎo)航

Mysql雙主配置的詳細(xì)步驟

更新時(shí)間：2022年06月23日 09:29:19 作者：shaunqing

雙主可以在一定程度上保證主庫(kù)的高可用,在一臺(tái)主庫(kù)down掉之后,可以在極短的時(shí)間內(nèi)切換到另一臺(tái)主庫(kù)上,減少了主從同步給線(xiàn)上主庫(kù)帶來(lái)的壓力,這篇文章主要給大家介紹了關(guān)于Mysql雙主配置的詳細(xì)步驟,需要的朋友可以參考下

前言

特點(diǎn)：在雙主配置中，兩臺(tái)mysql互為主從節(jié)點(diǎn)。節(jié)點(diǎn)A是節(jié)點(diǎn)B的master，同時(shí)節(jié)點(diǎn)B也是節(jié)點(diǎn)A的master。

安裝mysql步驟略過(guò)

一、mysql配置文件

（1）節(jié)點(diǎn)A配置

# 設(shè)置server-id，兩節(jié)點(diǎn)必須不一樣
server-id = 100
 
 
# 開(kāi)啟bin_log，模式為ROW，允許最大日志為1G
log_bin = mysql-bin
binlog_format = ROW
max_binlog_size = 1024M
 
 
# mysql5.7的版本不需要配置log-slave-updates=1
# log-slave-updates = 1
 
 
# 實(shí)現(xiàn)數(shù)據(jù)庫(kù)宕機(jī)恢復(fù)后，自動(dòng)同步缺少的數(shù)據(jù)
relay_log = mysql-relay-bin
 
 
# 設(shè)置自增ID初始值為2，每次自增量為2。即都是偶數(shù)2,4,6,8,...
auto_increment_offset = 2
auto_increment_increment = 2
 
 
# 開(kāi)啟gtid
gtid_mode = ON
enforce_gtid_consistency = 1
 
 
# 忽略不需要同步的schema
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

（2）節(jié)點(diǎn)B配置

只列出了與節(jié)點(diǎn)A不同的配置

# 設(shè)置server-id，兩節(jié)點(diǎn)必須不一樣
server-id = 200
 
# 設(shè)置自增ID初始值為1，每次自增量為2。即都是奇數(shù)1,3,5,7,...
auto_increment_offset = 1
auto_increment_increment = 2

二、配置節(jié)點(diǎn)A為節(jié)點(diǎn)B的master（主從模式）

節(jié)點(diǎn)A為master，節(jié)點(diǎn)B為slave。

（1）登錄節(jié)點(diǎn)A，創(chuàng)建slave（節(jié)點(diǎn)B）使用的賬號(hào)。

# 在節(jié)點(diǎn)A上創(chuàng)建賬號(hào)repl_user，允許從任意IP訪(fǎng)問(wèn)，密碼為slave@100，
grant replication slave on *.* to 'repl_user'@'%' identified by 'slave@100';
flush privileges;

（2）查看節(jié)點(diǎn)A的master信息

如下圖所屬，節(jié)點(diǎn)A的bin_log日志為mysql-bin.000001，位置是154

# 清空master信息。初次配置可以使用，若已運(yùn)行了同步，切記不要使用！
reset master;
 
# 查看master信息
show master status;
 
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000001 |     154 |              |                  | c2cf218e-2317-11ec-a36f-5cf9dd4fd6a8:1-5    |
+------------------+----------+--------------+------------------+---------------------------------------------+

（3）登錄節(jié)點(diǎn)B，設(shè)置節(jié)點(diǎn)B的master為節(jié)點(diǎn)A

# 停止節(jié)點(diǎn)B上的slave
stop slave;
 
# 將節(jié)點(diǎn)B的master設(shè)為節(jié)點(diǎn)A，以下的配置信息均表示節(jié)點(diǎn)A
# MASTER_LOG_FILE和MASTER_LOG_POS，是在第（2）步中查看節(jié)點(diǎn)A的master信息
CHANGE MASTER TO 
       MASTER_HOST='127.0.0.1', 
       MASTER_PORT=3307,
       MASTER_USER='repl_user', 
       MASTER_PASSWORD='slave@100', 
       MASTER_LOG_FILE='mysql-bin.000001', 
       MASTER_LOG_POS=154;
 
# 開(kāi)啟節(jié)點(diǎn)B上的slave
start slave;

（4）確認(rèn)節(jié)點(diǎn)B的slave是否運(yùn)行成功。當(dāng)返回的結(jié)果中，Slave_IO_Running和Slave_SQL_Running都為YES時(shí)，表示成功。如果為NO或Connecting時(shí)，請(qǐng)?jiān)趍ysql的data目錄中查看.err結(jié)尾的日志文件，找出[ERROR]級(jí)別的日志查看。初次配置時(shí)，一般都是my.ini配置問(wèn)題，或同步賬號(hào)創(chuàng)建問(wèn)題導(dǎo)致的。

# 在節(jié)點(diǎn)B中查看slave狀態(tài)
show slave status\G;
 
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl_user
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2207
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 1143
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                           ......

（5）若slave配置出現(xiàn)異常，可使用以下語(yǔ)句重置

reset slave all;

三、完成雙主配置

此時(shí)，已成功配置了節(jié)點(diǎn)A與節(jié)點(diǎn)B的主從模式，即節(jié)點(diǎn)A是節(jié)點(diǎn)B的master。接下來(lái)重復(fù)步驟三的操作，但是節(jié)點(diǎn)A、B的操作要顛倒過(guò)來(lái)，即在節(jié)點(diǎn)B上創(chuàng)建同步賬號(hào)，查看master信息，在節(jié)點(diǎn)A上設(shè)置節(jié)點(diǎn)B為master。配置完成后，同樣查看節(jié)點(diǎn)A的slave狀態(tài)，Slave_IO_Running和Slave_SQL_Running是否都為YES。

四、測(cè)試

在節(jié)點(diǎn)A添加、刪除schema或表或數(shù)據(jù)，在節(jié)點(diǎn)B中查看是否有正確同步。

測(cè)試自增主鍵，分別在節(jié)點(diǎn)A、B上添加自增主鍵的數(shù)據(jù)，與配置文件是否對(duì)應(yīng)。一個(gè)為奇數(shù)，一個(gè)為偶數(shù)。增加或刪除數(shù)據(jù)，主鍵是否會(huì)出現(xiàn)沖突的情況。

五、控制同步的庫(kù)或表

通過(guò)修改my.ini實(shí)現(xiàn)

（1）在master端控制

binlog-do-db binlog日志記錄的數(shù)據(jù)庫(kù)（多數(shù)據(jù)庫(kù)用，隔開(kāi)）

binlog-ignore-db binlog日志中忽略數(shù)據(jù)庫(kù)（多數(shù)據(jù)庫(kù)用，隔開(kāi)）

只有在binlog中記錄的表，才能同步給從庫(kù)

# binlog中記錄這兩個(gè)表
binlog-do-db=db_1,db_2      
 
# binlog中不記錄這兩個(gè)表 
binlog-ignore-db=db_3,db_4

（2）在slave端控制

replicate-do-db 設(shè)定需要復(fù)制的數(shù)據(jù)庫(kù)
replicate-ignore-db 設(shè)定需要忽略的復(fù)制數(shù)據(jù)庫(kù)
replicate-do-table 設(shè)定需要復(fù)制的表
replicate-ignore-table 設(shè)定需要忽略的復(fù)制表
replicate-wild-do-table 同replication-do-table功能一樣，可以通配符
replicate-wild-ignore-table 同replication-ignore-table功能一樣，可以加通配符

注：忽略的配置里，在從庫(kù)的relaylog中仍有信息，只是沒(méi)有執(zhí)行同步。

# 執(zhí)行同步的庫(kù)
replicate-do-db=db_1
replicate-do-db=db_2
 
# 忽略同步的庫(kù)
replicate-ignore-db=db_3
 
# 執(zhí)行同步的表
replicate-do-table=db_1.table_a
replicate-do-table=db_2.table_b
 
# 忽略同步的表
replicate-ignore-table=db_2.table_c
 
# 只復(fù)制哪個(gè)庫(kù)的哪個(gè)表
replicate-wild-do-table=db_4.% 
 
# 忽略哪個(gè)庫(kù)的哪個(gè)表
replicate-wild-ignore-table=mysql.%

第五部分引用自CSDN博主「Mr. Sun_」的原創(chuàng)文章

原文鏈接：http://chabaoo.cn/article/252656.htm

六、一個(gè)賬號(hào)多個(gè)IP

當(dāng)一臺(tái)mysql作為主節(jié)點(diǎn)，N臺(tái)作為從節(jié)點(diǎn)時(shí)，如果每個(gè)從節(jié)點(diǎn)都添加一個(gè)賬號(hào)會(huì)非常繁瑣。此時(shí)可以在主節(jié)點(diǎn)中，創(chuàng)建一個(gè)賬號(hào)，給此賬號(hào)設(shè)置多個(gè)IP，從而減少添加的賬號(hào)個(gè)數(shù)。

具體做法是，將創(chuàng)建賬號(hào)的語(yǔ)句執(zhí)行多次，每次的IP不同即可。

# 創(chuàng)建一個(gè)賬號(hào)名，為此賬號(hào)設(shè)置多個(gè)ip
grant replication slave on *.* to 'repl_user'@'192.168.5.10' identified by 'slave@100';
grant replication slave on *.* to 'repl_user'@'192.168.5.20' identified by 'slave@100';
FLUSH PRIVILEGES

第六部分引用自CSDN博主「rucypli」的回答

mysql grant 多個(gè)ip-CSDN論壇