mysql8 gitd 主从复制

一、master主库

1、配置serviceID、开启二进制日志和打开gitd，可以配置中继日志。然后重启mysql。完整配置如下

[client]
socket=/data/mysql/mysql.sock

[mysqld]
user=root
datadir=/data/mysql/data
basedir=/data/mysql
socket=/data/mysql/mysql.sock
port=3306
#允许最大连接数
max_connections=2000
#日志路径
log-error=/data/mysql/mysqld.log
#开启慢sql日，超过2秒记录
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /data/mysql/mysql-slow.log
#禁用主机名解析
skip-name-resolve
#字符集配置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
server-id=1
## 开启二进制日志
log_bin=/data/mysql/mysql-bin
relay-log=/data/mysql/relay-log
relay-log-index=/data/mysql/relay-log.index
relay_log_purge=OFF
# gtid
gtid-mode=on
enforce-gtid-consistency=ON
#屏蔽系统库同步
binlog_ignore_db=mysql,information_schema,performance_schema

2、创建一个同步账号授权给从数据库使用

mysql> create user 'repl'@'%' identified by 'Sykj@2024';

mysql> grant all on *.* to 'repl'@'%';

mysql> flush privileges;

3、如果主库没数据，不需要全备拷贝到从库，否则需要全备。主从全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致

mysql> FLUSH TABLES WITH READ LOCK;

备份主库并将备份文件传送到从库

mysqldump -uroot -pSykj@2024 --all-databases > /opt/all.sql

scp /opt/all.sql root@192.168.31.31:/opt/

解锁

mysql> unlock tables;

 二、slave从库

1、配置serviceID、开启中继日志和打开gitd，可以开启二进制日志。然后重启mysql。完整配置如下

[client]
socket=/data/mysql/mysql.sock

[mysqld]
user=root
datadir=/data/mysql/data
basedir=/data/mysql
socket=/data/mysql/mysql.sock
port=3306
#允许最大连接数
max_connections=2000
#日志路径
log-error=/data/mysql/mysqld.log
#开启慢sql日，超过2秒记录
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /data/mysql/mysql-slow.log
#禁用主机名解析
skip-name-resolve
#字符集配置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
server-id=2
## 开启二进制日志
log_bin=/data/mysql/mysql-bin
relay-log=/data/mysql/relay-log
#relay-log-index=/data/mysql/relay-log.index
relay_log_purge=OFF
# gtid
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON #从中继日志中获取的 SQL相关操作，同步记录到二进制日志中。
#屏蔽系统库同步
binlog_ignore_db=mysql,information_schema,performance_schema

2、如果全备了主从，就恢复主库的备份

mysql -uroot -pSykj@2024 < /opt/all.sql 

3、认主并启动slave

reset slave;

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.31.30',
    SOURCE_PORT=3306,
    SOURCE_USER='repl',
    SOURCE_PASSWORD='Sykj@2024',
    SOURCE_AUTO_POSITION=1;

start slave;
show slave status \G

 

三、恢复主从

如果因为某些原因如服务器突然重启导致主从不正常，有2种方式恢复

第一种，启动slave后查看主从状态等待Seconds_Behind_Master字段为0

 

第二种，启动slave后查看主从状态，等待一段时间后Seconds_Behind_Master为NULL，并且报错，如下图

 1、查看原因

运行 SELECT * FROM performance_schema.replication_applier_status_by_worker\G

mysql> SELECT * FROM performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
                                           CHANNEL_NAME: 
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 1032
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '7de784fa-912b-11ef-865a-5254cbafea87:14514641' at source log mysql-bin.000017, end_log_pos 9688733; Could not execute Update_rows event on table seata.global_table; Can't find record in 'global_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's source log mysql-bin.000017, end_log_pos 9688733
                                   LAST_ERROR_TIMESTAMP: 2024-12-09 09:42:20.462702
                               LAST_APPLIED_TRANSACTION: 7de784fa-912b-11ef-865a-5254cbafea87:14514637
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-12-09 09:41:19.708559
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-12-09 09:41:19.708559
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2024-12-09 09:42:20.435690
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2024-12-09 09:42:20.459979
                                   APPLYING_TRANSACTION: 7de784fa-912b-11ef-865a-5254cbafea87:14514641
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-12-09 09:41:19.712474
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-12-09 09:41:19.712474
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2024-12-09 09:42:20.460984
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                     APPLYING_TRANSACTION_RETRIES_COUNT: 0
       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
      APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
    APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000

2、解决方法：从master同步seata库到slave，然后slave重新认主

 

 

第三种，简单粗暴直接同步master的所有库到slave，然后开启主从。