Mysql 5.7.44 主从复制配置(GTID)

提示

本文为站长原创文章，版权所有，未经允许，禁止转载！

信息

数据库环境信息：
操作系统：Ubuntu 20.04.6 LTS 64bit
Mysql数据库版本：mysql 5.7.44 Community Server 64bit
IP地址对应关系:
192.168.99.131 mysql01
192.168.99.132 mysql02

• GTID是推荐的配置
• slave默认重启备数据库时会自动启动

系统配置

cat /etc/security/limits.conf 
* hard nofile 65535
* soft nofile 65535

cat /etc/sysctl.d/99-sysctl.conf 
# shared memory
#kernel.shmmax = 34359738368
#kernel.shmall = 8388609
#kernel.shmmni = 4096

# semaphore
kernel.sem = 250 32000 100 128

# network
net.core.somaxconn = 4096
net.unix.max_dgram_qlen = 256 
net.core.netdev_max_backlog=8192

# file
fs.file-max = 65535

cat /usr/lib/systemd/system/mysql57.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql57
Group=mysql57
LimitNOFILE=65535
LimitNPROC=65535
Type=forking

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
 
# Execute pre and post scripts as root
PermissionsStartOnly=true

PIDFile=/opt/mysql57/run/mysqld.pid

# Start main service
ExecStart=/opt/mysql57/5.7.44/bin/mysqld --defaults-file=/home/mysql57/my.cnf --daemonize $MYSQLD_OPTS

Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false

my.cnf配置

# 初始化
mysqld --initialize --user=mysql57 --basedir /opt/mysql57 --datadir /opt/mysql57/data

# 重新生成server_uuid
找到 MySQL 数据目录中的 `auto.cnf` 文件。这个文件通常位于 MySQL 数据目录（比如 `/var/lib/mysql`）中。删除这个文件，再启动mysql数据库

# 主库 192.168.99.131
# vi my.cnf
[mysqld]
basedir = /opt/mysql57/5.7.44
datadir = /opt/mysql57/data
port = 3306
socket = /opt/mysql57/run/mysql.sock
default-time_zone = '+8:00'
pid-file = /opt/mysql57/run/mysqld.pid
log-error = /opt/mysql57/log/mysqld-err.log
secure-file-priv = /opt/mysql57/secure-file
log_bin = binlog
binlog_format=row
autocommit=0
server_id = 1

gtid-mode=on 
enforce-gtid-consistency=true 
log-slave-updates=1 

# 从库 192.168.99.132
# vi my.cnf
[mysqld]
basedir = /opt/mysql57/5.7.44
datadir = /opt/mysql57/data
port = 3306
socket = /opt/mysql57/run/mysql.sock
default-time_zone = '+8:00'
pid-file = /opt/mysql57/run/mysqld.pid
log-error = /opt/mysql57/log/mysqld-err.log
secure-file-priv = /opt/mysql57/secure-file
log_bin = binlog
binlog_format=row
autocommit=0
server_id = 2

gtid-mode=on 
enforce-gtid-consistency=true 
log-slave-updates=1 

数据库配置

# repl 用户创建用于复制
mysql> create user repl@'192.168.99.%' identified with mysql_native_password by  'aaronqlm'; 
mysql> grant replication slave on *.* to repl@'192.168.99.%'; 

# 创建一个普通数据库和赋予权限
create database aaronqlm
create user aaronqlm identified by 'aaronqlm';
grant all privileges on aaronqlm.* to aaronqlm;

# 创建表t1
CREATE TABLE t1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10),
    mark VARCHAR(100)
);

# 插入100w条数据
drop PROCEDURE insert_t1_million_records;
DELIMITER //
CREATE PROCEDURE insert_t1_million_records()
BEGIN
    DECLARE counter INT DEFAULT 0;
    START TRANSACTION;
    WHILE counter < 1000000 DO
        INSERT INTO t1 (name, mark) VALUES (CONCAT('name', counter), CONCAT('mark', counter));
        SET counter = counter + 1;
    END WHILE;
    COMMIT;
END//
DELIMITER ;

call insert_t1_million_records()

数据导出导入

# 导出全库数据 数据量在几百G左右。
mysqldump -uroot -p'aaronqlm' -A -h 127.0.0.1 --node-data=2 --single-transaction >/tmp/full.sql

# 导入数据库到指定数据库
mysql -u root -p -h 127.0.0.1 aaronqlm < dbfull.sql 

主从配置

# 查看binlog信息，根据信息内容修改后面同步语句
# 主库执行
show node status;

CHANGE node TO 
node_HOST='192.168.99.131', 
node_USER='repl', 
node_PASSWORD='mysql', 
node_PORT=3306, 
node_LOG_FILE='binlog.000002', 
node_LOG_POS=154, 
node_CONNECT_RETRY=10; 

# 从库启动专用复制线程   
start slave; 

# 检查同步进程： 
# 主库
show node status \G

# 从库
show slave status \G 
看如下内容： 
slave_io_running ： yes 
slave_sql_running ： yes 

# 删除slave配置
# 备库
mysql> reset slave;

只同步指定的库

# 配置文件修改： 
[root@db01 ~]# vim /data/3309/my.cnf  
replicate_do_db=ppt 
replicate_do_db=word 
# 在线修改 
或者使用以下方法：   
mysql> stop slave sql_thread; 
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (word, ppt); 
mysql> start slave sql_thread; 

从库延迟

# SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行 
# 一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间。 
stop slave; 
CHANGE node TO node_DELAY = 300; 
start slave; 

半同步复制

• 半同步复制（Semi-Synchronous Replication）是MySQL中一种复制方式，它介于异步复制（Asynchronous Replication）和全同步复制（Fully Synchronous Replication）之间。在半同步复制中，当主服务器收到事务提交请求后，会等待至少一个从服务器将该事务写入到日志文件并确认收到之后，才会向客户端返回事务提交成功的消息。
• 半同步复制的主要优势在于，它提供了更高的数据一致性和可靠性。相比异步复制，半同步复制可以减少数据丢失的风险，因为在主服务器确认事务提交成功之前，至少一个从服务器已经接收到了相同的事务并写入到了日志中。这意味着即使主服务器出现故障，也可以确保至少有一个从服务器包含了大部分已提交的数据。
• 然而，半同步复制也存在一些缺点，主要是由于性能方面的考虑。由于主服务器需要等待至少一个从服务器的确认，这可能会导致主服务器的性能下降。此外，半同步复制也会增加网络通信开销和从服务器的写入延迟。
• 总的来说，半同步复制在需要更高数据一致性和可靠性的场景下是一个比较合适的选择，但需要权衡好性能和一致性之间的关系。

# 加载插件 
# 主 : 
INSTALL PLUGIN rpl_semi_sync_node SONAME 'semisync_node.so'; 

# 从 : 
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 

# 查看是否加载成功 : 
show plugins; 

# 主 : 
SET GLOBAL rpl_semi_sync_node_enabled = 1; 

vi my.cnf
rpl_semi_sync_node_enabled = 1

#从 : 
SET GLOBAL rpl_semi_sync_slave_enabled = 1; 

vi my.cnf
rpl_semi_sync_slave_enabled = 1
# 重启从库上的 IO 线程 
STOP SLAVE IO_THREAD; 
START SLAVE IO_THREAD; 
查看是否在运行 
主 : 
show status like 'Rpl_semi_sync_node_status'; 
从 : 
show status like 'Rpl_semi_sync_slave_status'; 

# 其他的优化参数：   
rpl_semi_sync_node_enabled                =ON 
rpl_semi_sync_node_timeout                =1000 
rpl_semi_sync_node_trace_level            =32 
rpl_semi_sync_node_wait_for_slave_count   =1 
rpl_semi_sync_node_wait_no_slave          =ON 
rpl_semi_sync_node_wait_point             =AFTER_SYNC 
rpl_semi_sync_slave_enabled                 =ON 
rpl_semi_sync_slave_trace_level             =32 
binlog_group_commit_sync_delay              =1 
binlog_group_commit_sync_no_delay_count     =1000 

多源复制

(6) 构建主从   
# 1. 创建复制用户 ( 主节点 ) 
set sql_log_bin=0;     -- 不产生 sql 操作日志 
create user repl@'192.168.99.%' identified with mysql_native_password by 'aaronqlm'; 
grant replication slave on *.*  to repl@'192.168.99.%' ; 
set sql_log_bin=1; 

### b. 配置多源复制 

CHANGE node TO node_HOST='192.168.99.131',node_USER='repl', node_PASSWORD='aaronqlm', node_AUTO_POSITION=1 FOR CHANNEL 'node_1';                 
CHANGE node TO node_HOST='192.168.99.132',node_USER='repl', node_PASSWORD='aaronqlm', node_AUTO_POSITION=1 FOR CHANNEL 'node_2';            
start slave for CHANNEL  'node_1'; 
start slave for CHANNEL  'node_2'; 

### c. 多源复制监控 

SHOW SLAVE STATUS FOR CHANNEL 'node_1'\G 
SHOW SLAVE STATUS FOR CHANNEL 'node_2'\G 
select * from replication_connection_configuration\G 
SELECT * FROM replication_connection_status WHERE CHANNEL_NAME='node_1'\G 
select * from performance_schema.replication_applier_status_by_worker; 

### d. 多源复制配置过滤 

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%') FOR CHANNEL "node_1"; 
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db2.%') FOR CHANNEL "node_2"; 

命令输出说明

# 主库的复制信息 
node_Host: 192.168.99.131 
node_User: repl 
node_Port: 3307 
Connect_Retry: 10 
node_Log_File: binlog.000004 
Read_node_Log_Pos: 1154 
           
# 从库 SQL 线程已经回放的中继日志信息   
Relay_Log_File: db01-relay-bin.000002 
Relay_Log_Pos: 512 
Relay_node_Log_File: binlog.000004 
Exec_node_Log_Pos: 1154 
说明： 后两条可以帮助我们判断主从延时的日志量 

# 主从复制线程故障 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
Last_IO_Errno: 0 
Last_IO_Error:  
Last_SQL_Errno: 0 
Last_SQL_Error:              

# 过滤复制相关信息              
Replicate_Do_DB:  
Replicate_Ignore_DB:  
Replicate_Do_Table:  
Replicate_Ignore_Table:  
Replicate_Wild_Do_Table:  
Replicate_Wild_Ignore_Table:  

# 主从之间延时的秒数 
Seconds_Behind_node: 0 

# 延时从库状态 
SQL_Delay: 0 
SQL_Remaining_Delay: NULL 

# GTID 复制      
Retrieved_Gtid_Set:  
Executed_Gtid_Set: