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

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

一.主库配置

1.1 确认 binlog 是否开启

SHOW VARIABLES LIKE '%log_bin%';

1.2 创建日志目录并设置权限

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

1.3 修改 my.cnf 配置文件

基于二进制日志（Binlog）文件：

[mysqld]
server-id = 1 #主库的唯一标识
log-bin = /opt/mysql/log_bin/mysql-bin #开启二进制日志，指定文件路径
log_bin_basename = /opt/mysql/log_bin/mysql-bin
log_bin_index = /opt/mysql/log_bin/mysql-bin.index
binlog_format = ROW # 推荐设置为 ROW 格式，确保复制一致性

基于全局事务标识符（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 # 建议设置为ROW模式，确保复制时的一致性
log_bin = mysql-bin # 必须开启二进制日志，确保主库有日志可供从库读取
sync_binlog = 1

1.4 创建用于复制的账户并授予权限

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

1.5 查看主库的二进制日志状态

SHOW MASTER STATUS\G;

• File：表示当前二进制日志文件。

• Position：表示二进制日志的位置。

1.6 全库导出

使用 mysqldump 进行全库导出。

基于二进制日志（Binlog）文件：

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

基于全局事务标识符（GTID）：

5.7版本及以上。

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

二

从库配置

2.1 修改从库的 my.cnf 配置文件

基于二进制日志（Binlog）文件:

[mysqld]
server-id = 2
read-only = 1  # 设置只读模式
relay_log = /opt/mysql/log_bin/relay-bin # 指定中继日志路径

基于全局事务标识符（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  # 确保从库为只读模式，防止误写

2.2 重启 MySQL 服务

2.3 导入主库的全库数据

将从主库导出的 quanbei.sql 文件复制到目标机器。

mysql -u<用户名> -p<密码>

source /quanbei.sql

三

开启同步并验证

3.1 配置主从关系

基于二进制日志（Binlog）文件：

CHANGE REPLICATION SOURCE TOSOURCE_HOST = '主库IP',SOURCE_USER = 'xxxxx',SOURCE_PASSWORD = '123456',SOURCE_LOG_FILE = 'mysql-bin.000269',SOURCE_LOG_POS = 604612268;

基于全局事务标识符（GTID）：

CHANGEMASTERTOMASTER_HOST='123.123.123.123', -- 主库的 IP 地址MASTER_PORT=3306, -- 主库的端口（通常是 3306）MASTER_USER='repl', -- 用于复制的用户名MASTER_PASSWORD='123456', -- 用于复制的用户密码MASTER_CONNECT_RETRY=10, -- 连接主库失败时的重试间隔时间（秒）MASTER_AUTO_POSITION=1; -- 启用基于 GTID 的复制，自动定位日志位置

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

3.2 启动从库同步

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

3.3 验证主从同步状态

在主库上查看二进制日志状态：

SHOW MASTER STATUS\G;

在从库上查看复制状态：

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