1. Mysql binlog參數(shù)配置

log-bin=mysql-bin

打開二進制日志功能，默認在datadir下

binlog-ignore-db

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog不記錄上述表的修改

binlog_format

binlog_format=mixed

值有三個，ROW/STATEMENT/MIXED

expire_logs_days

自動刪除binlog的天數(shù)，8.0已棄用，由binlog_expire_logs_seconds代替。MySQL 8.0開始，binlog_expire_logs_seconds選項也存在的話，會忽略expire_logs_days選項

binlog_expire_logs_seconds

二進制日志有效期，單位秒

binlog_expire_logs_seconds=259200  自動刪除3天前的binlog

binlog_cache_size

binlog_cache_size=1M

默認值32KB。在一個事務(wù)中binlog為了記錄SQL狀態(tài)所持有的cache大小，如果經(jīng)常使用大事務(wù)，可以增加此值來獲取更大的性能。

max_binlog_size

max_binlog_size=500M

如果二進制日志寫入的內(nèi)容超出給定值，日志就會發(fā)生滾動

binlog_do_db

binlog-ignore-db=test

binlog只記錄test數(shù)據(jù)庫的修改

log_slave_updates

log_slave_updates=1

從庫如果做為其他從庫的主庫，那么這個參數(shù)必須開啟，設(shè)置為1為開啟

因為直接往mysql寫入的數(shù)據(jù)會寫入binlog，但是后臺IO線程從relaylog日志讀取寫入的數(shù)據(jù)在參數(shù)關(guān)閉的情況下不會寫入binlog，如果該庫還需要向其他slave同步，這個參數(shù)必須開啟

2. Mysql binlog查看詳細內(nèi)容

show binary logs

查看binlog日志列表

show binlog events in 'mysql-bin.000003'

查看指定binlog文件

文件格式如下

3. Mysql雙主搭建

版本8.0.25

my.cnf配置如下

[mysqld]
port=3308
server_id=123
default_authentication_plugin=mysql_native_password
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
symbolic-links=0
explicit_defaults_for_timestamp=true
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
 
binlog_cache_size=1M
binlog_format=mixed
binlog_expire_logs_seconds=259200
max_binlog_size=500M
slave_skip_errors=1062
log_slave_updates=1
lower_case_table_names=1
log-bin=mysql-bin
replicate-ignore-db=mysql,information_schema
sync_binlog=0
bind-address=0.0.0.0
skip-name-resolve
skip_ssl
 
auto_increment_offset=1
auto_increment_increment=1
 
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true
 
max_connections=1000
innodb-force-recovery=0
 
gtid-mode=on   #GTID開啟
enforce-gtid-consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
innodb_flush_log_at_trx_commit=0
relay_log_recovery=1
 
[client]
port=3308
socket=/var/run/mysqld/mysqld.sock
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4

兩臺mysql配置只有server_id不同，如果兩臺mysql部署在同一服務(wù)器，須再設(shè)置端口port不同，配置完成后啟動兩臺mysql

1、登錄兩臺mysql，都創(chuàng)建相應(yīng)用來主從復(fù)制的用戶

CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'ws-123456';
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'ws-123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'repl'@'%';
flush privileges;

2、登錄第一個節(jié)點（3308節(jié)點）

show master status; -- 查看當前節(jié)點binlog日志信息，主要是獲取當前binlog日志名和偏移量

執(zhí)行下邊的命令，將當前mysql設(shè)置為3309的從節(jié)點

change master to
master_host='lxy-mysql-one',
master_port=3309,
master_user='repl',
master_password='ws-123456',
master_log_file='mysql-bin.000004', -- 上邊status命令查出來的日志名
master_log_pos=6534; -- 上邊status命令查出來的日志偏移量
 
 
--如果是GTID模式，不用查偏移量使用下邊進行主從關(guān)系設(shè)置
change master to 
master_host='192.168.149.104',
master_port=3309, 
master_user='repl',
master_password='ws-123456',
MASTER_AUTO_POSITION=1;   

啟動主從復(fù)制

start slave;

檢查主從復(fù)制狀態(tài)，如果紅圈都為YES則正常

show slave status ;

3、同理登錄第二個節(jié)點（3309節(jié)點），同第2步操作，設(shè)置3309為3308的從節(jié)點，show slave status正常后即搭建完成

另：移除同步配置如下命令

stop slave; -- 停止主從復(fù)制

reset slave all; -- 移除主從設(shè)置的所有信息

4. Mysql雙主解決數(shù)據(jù)回環(huán)

如何解決數(shù)據(jù)回環(huán)

主從復(fù)制模式下，是master寫入binlog，slave接收master的binlog，寫入relaylog，slave消費relaylog從而達成數(shù)據(jù)一致，slave只讀不寫，可以關(guān)閉binlog

而雙主模式下，雙主都可寫，也就是都會產(chǎn)生binlog（需要開啟binlog和log_slave_updates參數(shù)），那么master1插入數(shù)據(jù)，產(chǎn)生binlog，master2寫入relaylog消費后又會寫入binlog，推送至master1，如果沒有特殊處理，那么這一條sql豈不是開始無限循環(huán)？

解決這樣無限循環(huán)可以有以下幾個方式

1、master2寫入relaylog消費后不寫binlog，那么循環(huán)即終止。也就是只有從客戶端連接執(zhí)行的sql寫binlog，后臺relaylog執(zhí)行不寫binlog。

使用log_slave_updates參數(shù)配置即可實現(xiàn)relaylog執(zhí)行不寫binlog，但是這樣做如果master1和master2自身有從節(jié)點，即集群是雙主雙從架構(gòu)，這樣會造成master1和對應(yīng)slave數(shù)據(jù)不同步

2、master2寫入relaylog、寫入binlog之后，不推送該條sql到master1。

根據(jù)binlog格式可以看出，binlog中記錄了該條sql從哪個server來（server_id），這也是為什么雙主server_id必須不同的原因，那就只推送server_id是當前節(jié)點id的binlog，但是這樣也不適合雙主雙從架構(gòu)

3、master2寫入relaylog、寫入binlog之后，推送該條sql到master1，master1端根據(jù)某個標識不執(zhí)行重復(fù)sql。

猜測是根據(jù)serverid判斷是否重復(fù)

4.1 雙主同步測試一

1）3308節(jié)點初始狀態(tài)

binlog偏移量為253

binlog日志初始為空

同步狀態(tài)，已讀取3309節(jié)點最新的binlog偏移量為253，當前節(jié)點relaylog偏移量為373

2）3309節(jié)點初始狀態(tài)

binlog偏移量為253

binlog日志初始為空

同步狀態(tài)，已讀取3308節(jié)點最新的binlog偏移量為253，當前節(jié)點relaylog偏移量為373

3）在3308節(jié)點執(zhí)行插入

4）觀察3308節(jié)點變化

插入語句產(chǎn)生了binlog，偏移量更新到了573

產(chǎn)生了binlog

5）觀察3309節(jié)點同步狀態(tài)變化

已讀取到3308節(jié)點binlog日志最新偏移量為573，3309的relaylog偏移量也有了更新

3309節(jié)點相應(yīng)的也產(chǎn)生了binlog，更新了偏移量和日志文件，其中插入語句的server_id是123，是3308節(jié)點的id

6）觀察3308節(jié)點同步狀態(tài)變化

發(fā)現(xiàn)3308節(jié)點讀到了3309節(jié)點最新的binlog偏移量，但是relaylog偏移量沒有變動

4.1.1 測試總結(jié)

3308插入執(zhí)行后產(chǎn)生了binlog，3309節(jié)點的slave狀態(tài)顯示binlog偏移量更新，即讀取到了3308最新的binlog，relay日志偏移量變大，這是正常的主從同步完成，3309寫入之后自身binlog偏移量更改，3308節(jié)點的slave狀態(tài)顯示binlog偏移量更新，即讀取到了3309最新的binlog，但是relay日志偏移量不變。

這表示是在3308節(jié)點中IO線程讀取到了3309節(jié)點這個重復(fù)的binlog，但是沒有寫入relaylog，根據(jù)binlog格式判斷是根據(jù)Server_id進行過濾從而忽略了重復(fù)sql。

但是這個測試表現(xiàn)還不能確定是server_id起了主要作用，因為默認下GTID模式開啟（gtid-mode=on，8.0.25），在binlog中也有g(shù)tid存在，也可能mysql是根據(jù)gtid進行的重復(fù)sql判斷，雖然可能不大，但是還是要嚴謹看測試二

4.2 雙主同步測試二

猜想：3308在接收到來自3309節(jié)點的重復(fù)binlog之后是根據(jù)serverId進行過濾，從而不寫入relaylog，達到一個打破數(shù)據(jù)回環(huán)的效果

測試場景：首先關(guān)閉GTID模式，避免GTID對測試效果產(chǎn)生影響，其次在3308執(zhí)行語句之后，假如修改3308節(jié)點的server_id，那么是不是就不能打破回環(huán)，接著會產(chǎn)生數(shù)據(jù)循環(huán)問題

1）兩個節(jié)點都設(shè)置GTID模式關(guān)閉

SET @@GLOBAL.GTID_MODE=OFF 或者 my.cnf文件中設(shè)置gtid-mode=off
-- 查看參數(shù)確定已經(jīng)關(guān)閉
SELECT @@GTID_MODE

2）將測試一中節(jié)點狀態(tài)重置

stop slave;
reset slave all;
reset master;

3）重新搭建雙主，略

show master status;
 
change master to
master_host='192.168.149.104',
master_port=3309,
master_user='repl',
master_password='ws-123456',
master_log_file='mysql-bin.000001', -- 上邊status命令查出來的日志名
master_log_pos=156; -- 上邊status命令查出來的日志偏移量
 
start slave;
 
-- 檢查同步狀態(tài)，測試插入是否同步等
show slave status;

4）在3308節(jié)點上執(zhí)行如下sql，先停止3308節(jié)點從3309節(jié)點的同步，然后執(zhí)行插入sql

stop slave;
insert into approve.table1 (id, name) values (999, '999');

3308節(jié)點binlog:

show binlog events in 'mysql-bin.000001';

 3309節(jié)點binlog:

可以看到這條sql已經(jīng)寫入3308節(jié)點binlog，同樣也寫入了3309節(jié)點的relaylog和binlog，但是目前3309到3308的同步是關(guān)閉的，3309的binlog還沒推送到3308

5）修改3308節(jié)點的serverId，開啟同步

set global server_id=888;
start slave;
show binlog events in 'mysql-bin.000001';

3308節(jié)點binlog:

3309節(jié)點binlog:

數(shù)據(jù)回環(huán)的效果已經(jīng)出現(xiàn)，binlog在瘋狂的增加，relaylog也來者不拒，不斷地執(zhí)行這條sql，表里都是重復(fù)數(shù)據(jù)

4.2.1 測試總結(jié)

解決數(shù)據(jù)回環(huán)主要就是IO線程在拉取到binlog之后根據(jù)server_id進行過濾，如果該binlog的serverId與自己相同，那么不計入relaylog，從而解決回環(huán)問題。

那GTID在其中又做了什么呢，開啟GTID模式，重新走一遍測試二的流程

4.3 雙主同步測試三

1）啟動GTID模式

SET @@GLOBAL.GTID_MODE=ON 或者 my.cnf配置gtid-mode=on

2）兩個節(jié)點重置測試二狀態(tài)

stop slave;
reset slave all;
delete from approve.table1;
reset master;
 
 
-- 還需要把3308的serverId改回原來的id
set global server_id=123;

3）重新搭建雙主，略，使用MASTER_AUTO_POSITION=1來使用GTID模式搭建主從

change master to
master_host='192.168.149.104',
master_port=3309,
master_user='repl',
master_password='ws-123456',
MASTER_AUTO_POSITION=1;
 
 
start slave;
show slave status;

4）在3308節(jié)點上執(zhí)行如下sql，先停止3308節(jié)點從3309節(jié)點的同步，然后執(zhí)行插入sql

stop slave;
insert into approve.table1 (id, name) values (999, '999');

show binlog events in 'mysql-bin.000001';

 3308節(jié)點狀態(tài)：

3308節(jié)點relaylog日志：

3309節(jié)點binlog狀態(tài)：

5）修改3308節(jié)點的serverId，開啟同步

set global server_id=888;
start slave;

show binlog events in 'mysql-bin.000001';

發(fā)現(xiàn)3308節(jié)點的binlog并沒有任何變化，相應(yīng)的3309節(jié)點更沒有變化

6）再來觀察3308節(jié)點的slave狀態(tài)，relaylog日志進行了滾動，之前是00002，變成了00003文件

滾動是因為停止了同步（每次stop slave;start slave; relaylog都會進行滾動）

3308節(jié)點的relaylog狀態(tài)如下，可以看到relaylog沒有記錄這條sql，但是有一條Rotate記錄，也就是說GTID起了作用，使得IO線程跳過了這條sql不記錄relaylog，再將relaylog的position更新為了最新的binlog位點

4.3.1 測試總結(jié)

在開啟了GTID模式的情況下，在寫入relaylog時除了根據(jù)serverId過濾，還會根據(jù)gtid進行過濾，已經(jīng)執(zhí)行過的gtid不再記錄到relaylog，以此打破回環(huán)

到此這篇關(guān)于Mysql雙主搭建的方法步驟的文章就介紹到這了,更多相關(guān)Mysql雙主搭建內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論