MySQL基于binlog和gtid主從搭建方案

一.主庫(kù)配置

1.1 確認(rèn) binlog 是否開啟

SHOW?VARIABLES?LIKE?'%log_bin%';

1.2 創(chuàng)建日志目錄并設(shè)置權(quán)限

mkdir?-p?/opt/mysql/log_bin
chown -R mysql:mysql?/usr/local/mysql
chmod -R?755?/usr/local/mysql

1.3 修改 my.cnf 配置文件

基于二進(jìn)制日志（Binlog）文件：

[mysqld]
server-id?=?1?#主庫(kù)的唯一標(biāo)識(shí)
log-bin?= /opt/mysql/log_bin/mysql-bin #開啟二進(jìn)制日志，指定文件路徑
log_bin_basename?= /opt/mysql/log_bin/mysql-bin
log_bin_index?= /opt/mysql/log_bin/mysql-bin.index
binlog_format?= ROW # 推薦設(shè)置為 ROW 格式，確保復(fù)制一致性

基于全局事務(wù)標(biāo)識(shí)符（GTID）：

[mysqld]
server_id =?1
gtid_mode =?on
enforce_gtid_consistency =?1
# binlog?group?commit
binlog_group_commit_sync_delay =?100
binlog_group_commit_sync_no_delay_count =?20
binlog_format = ROW?# 建議設(shè)置為ROW模式，確保復(fù)制時(shí)的一致性
log_bin = mysql-bin?# 必須開啟二進(jìn)制日志，確保主庫(kù)有日志可供從庫(kù)讀取
sync_binlog =?1

1.4 創(chuàng)建用于復(fù)制的賬戶并授予權(quán)限

CREATE?USER?'repl'@'%'?IDENTIFIED?WITH?mysql_native_password?BY?'123456';
GRANT?REPLICATION?SLAVE?ON?*.*?TO?'repl'@'%';
flush?privileges;

1.5 查看主庫(kù)的二進(jìn)制日志狀態(tài)

SHOW?MASTER?STATUS\G;

• File：表示當(dāng)前二進(jìn)制日志文件。

• Position：表示二進(jìn)制日志的位置。

1.6 全庫(kù)導(dǎo)出

使用 mysqldump 進(jìn)行全庫(kù)導(dǎo)出。

基于二進(jìn)制日志（Binlog）文件：

mysqldump -uroot -p?--set-gtid-purged=OFF --single-transaction --master-data=2?--all-databases > /home/mysql/quanbei.sql

基于全局事務(wù)標(biāo)識(shí)符（GTID）：

5.7版本及以上。

mysqldump -uroot -p?--set-gtid-purged=OFF --single-transaction --all-databases > /home/mysql/quanbei.sql

二

從庫(kù)配置

2.1 修改從庫(kù)的 my.cnf 配置文件

基于二進(jìn)制日志（Binlog）文件:

[mysqld]
server-id?=?2
read-only?=?1? # 設(shè)置只讀模式
relay_log?= /opt/mysql/log_bin/relay-bin # 指定中繼日志路徑

基于全局事務(wù)標(biāo)識(shí)符（GTID）：

[mysqld]
server_id?=?561013307
gtid_mode?=?on
enforce_gtid_consistency?=?1
report_host?=?'slave1'?#別名
log_slave_updates?=?1
relay_log?= /opt/mysql/log_bin/relay-bin # 建議顯式指定中繼日志
read_only?=?1? # 確保從庫(kù)為只讀模式，防止誤寫

2.2 重啟 MySQL 服務(wù)

2.3 導(dǎo)入主庫(kù)的全庫(kù)數(shù)據(jù)

將從主庫(kù)導(dǎo)出的 quanbei.sql 文件復(fù)制到目標(biāo)機(jī)器。

mysql -u<用戶名> -p<密碼>

source?/quanbei.sql

三

開啟同步并驗(yàn)證

3.1 配置主從關(guān)系

基于二進(jìn)制日志（Binlog）文件：

CHANGE?REPLICATION?SOURCE?TOSOURCE_HOST =?'主庫(kù)IP',SOURCE_USER =?'xxxxx',SOURCE_PASSWORD =?'123456',SOURCE_LOG_FILE =?'mysql-bin.000269',SOURCE_LOG_POS =?604612268;

基于全局事務(wù)標(biāo)識(shí)符（GTID）：

CHANGEMASTERTOMASTER_HOST='123.123.123.123',?-- 主庫(kù)的 IP 地址MASTER_PORT=3306,?-- 主庫(kù)的端口（通常是 3306）MASTER_USER='repl',?-- 用于復(fù)制的用戶名MASTER_PASSWORD='123456',?-- 用于復(fù)制的用戶密碼MASTER_CONNECT_RETRY=10,?-- 連接主庫(kù)失敗時(shí)的重試間隔時(shí)間（秒）MASTER_AUTO_POSITION=1;?-- 啟用基于 GTID 的復(fù)制，自動(dòng)定位日志位置

-- MySQL 5.7 的命令為 CHANGE MASTER TO，而 MySQL 8.0 則使用 CHANGE REPLICATION SOURCE TO。

3.2 啟動(dòng)從庫(kù)同步

START?SLAVE; ?--?
MySQL 5.7 使用START?REPLICA; ?--?
MySQL 8.0 使用

3.3 驗(yàn)證主從同步狀態(tài)

在主庫(kù)上查看二進(jìn)制日志狀態(tài)：

SHOW?MASTER?STATUS\G;

在從庫(kù)上查看復(fù)制狀態(tài)：

SHOW?SLAVE?STATUS\G; ?--
MySQL 5.7SHOW?REPLICA?STATUS\G; ?--?
MySQL 8.0