MySQL 主从同步配置

先在docker下创建几个 mysql server,docker-compose.xml 如下:

version: '3.7'
services:
    mysql_1:
        image: "alisql/alisql:latest"
        container_name: mysql_1
        ports:
            - "33060:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
        
​
    mysql_2:
        image: "alisql/alisql:latest"
        container_name: mysql_2
        ports:
            - "33061:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
​
    mysql_3:
        image: "alisql/alisql:latest"
        container_name: mysql_3
        ports:
            - "33062:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
​
    mysql_4:
        image: "alisql/alisql:latest"
        container_name: mysql_4
        ports:
            - "33063:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678

mysql 的配置文件在容器中 /etc/mysql/my.cnf 路径中,如果为了方便可以映射出来,执行docker-compose up 将容器启动起来。

一主一从同步,传统方式指定文件和位置同步

假设有mysql_1、mysql_2 ,1为master,2为slave,配置如下:

master 端的配置文件中加入:

server-id=1
log-bin=mysql-bin
​
sync_binlog=1
binlog_checksum=none
binlog_format=mixed
​
#binlog-do-db=cdev         #需要同步的数据库。如果是多个同步库,就以此格式另写几行即可。如果不指明对某个具体库同步,就去掉此行,表示同步所有库(除了ignore忽略的库)        
binlog-ignore-db = mysql,information_schema,performance_schema

配置一个账号的权限给从库使用:

grant replication slave,replication client on *.* to slave@'mysql_2' identified by "123456";
flush privileges;

重启服务后,查看执行show master status; 查看状态:

mysql> show master status
  -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1219 |             |                 |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Slave 端的配置文件中加入:

server-id=2
log-bin=mysql-bin
slave-skip-errors=all

重启服务,然后执行:

mysql> stop slave;
Query OK, 0 rows affected (0.09 sec)

mysql> change  master to master_host='mysql_1',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1219;
Query OK, 0 rows affected, 2 warnings (0.21 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status \G;

mysql> show slave status \G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                Master_Host: mysql_1
                Master_User: root
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 917
              Relay_Log_File: 7bf91f090e47-relay-bin.000004
              Relay_Log_Pos: 1080
      Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
          Replicate_Do_Table:
      Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                  Last_Errno: 0
                  Last_Error:
                Skip_Counter: 0
        Exec_Master_Log_Pos: 917
            Relay_Log_Space: 1260
            Until_Condition: None
              Until_Log_File:
              Until_Log_Pos: 0
          Master_SSL_Allowed: No
          Master_SSL_CA_File:
          Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
              Master_SSL_Key:
      Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
              Last_IO_Errno: 0
              Last_IO_Error:
              Last_SQL_Errno: 0
              Last_SQL_Error:
Replicate_Ignore_Server_Ids:
            Master_Server_Id: 1
                Master_UUID: 6ee81fed-7a5e-11ea-8623-0242ac120002
            Master_Info_File: /var/lib/mysql/master.info
                  SQL_Delay: 0
        SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
          Master_Retry_Count: 86400
                Master_Bind:
    Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
              Master_SSL_Crl:
          Master_SSL_Crlpath:
          Retrieved_Gtid_Set:
          Executed_Gtid_Set:
              Auto_Position: 0
1 row in set (0.00 sec)

如果你可以看到上面的输出,就表示主从已经生效了。然后可以在主库上创新一个数据库和表,插入几条数据,从库会同步的创建数据库和表还有数据。

Slave_IO_Running: Yes 表示从master获取events存储到 relay_log 的线程正常运行中
Slave_SQL_Running: Yes 表示本地读取 relay_log 和执行的线程正常运行中

在master 端执行 show master status ,在 slave 端执行 show slave status,对比master端的Position和Slave端的Read_Master_Log_PosExec_Master_Log_Pos就可以看出是否同步为一致状态了。

Seconds_Behind_Master 这个值如果大于0的话,表示slave更新的速度晚于master的秒数,是用Exec_Master_Log_Pos对应的记录的原始timestamp值与当前正在执行的 events的时间对比得出的,如果当前slave没有执行同步事件,则这个值为0,如果没有开启执行同步,则这个值在MySQL8中是NULL

MySQL 配置互为主备同步,使用gtid 模式同步

假设 我们目标是 mysql_1 和 mysql_3 互为主备

在mysql_1配置中增加:

[mysqld]
skip-host-cache
skip-name-resolve
datadir = /var/lib/mysql
!includedir /etc/mysql/conf.d/
lower_case_table_names=1
​
server-id=1
log-bin=mysql-bin
​
sync_binlog=1
binlog_checksum=none
binlog_format=mixed
​
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1    #这个配置的意思,将同步过来的更新也写入binlog,这样其他的从库以此为主的话,可以获得更新

配置文件改好后,在sql终端执行命令开启同步:

change master to master_host='mysql_3',master_port=3306,master_user='root',master_password='12345678',master_auto_position =1;
start slave ;

在mysql_3配置中增加:

[mysqld]
skip-host-cache
skip-name-resolve
datadir = /var/lib/mysql
!includedir /etc/mysql/conf.d/
lower_case_table_names=1
​
server-id=3
log-bin=mysql-bin
​
sync_binlog=1
binlog_checksum=none
binlog_format=mixed
​
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1    

配置文件改好后,在sql终端执行命令开启同步:

change master to master_host='mysql_1',master_port=3306,master_user='root',master_password='12345678',master_auto_position =1;
start slave ;

查看状态:

show slave status;

问题:Slave has more GTIDs than master has 异常

我本地突然死机了,强制断电后,出现问题了

Could not find first log file name in binary log index file

我是这么处理的,因为是本地的新数据库,里面基本没数据,所以我就选择了粗暴的方式, reset master 和 reset slave all 重新配置,但是在slave 的status上出现如标题的错误,如果有这种问题的话,也在slave上执行

reset master;

清除 slave上记录的 gtid,我清除后,stat slave 后看状态没有这个错误了,然后我试了一下在主节点更新数据,从节点也能成功的更新。

最近是在看MyCat的分库分表怎么做,里面有配置DataNode节点可以配置WriteHost和ReadHost,用于区分读写分离时候用的Server,所以需要先实现从库同步主库的配置。就研究了下,怎么简单的配置主从设置,其实这块有很多细节。再生产环境中配置的话,需要深入的理解同步的机制,在出现同步问题的时候需要能够快速的解决。

问题:同步时跳过无法执行的GTID对应的事务

如果是修复主从复制中的异常,如果是在确认错误可以跳过的情况下,可以使用如下的方式:

stop slave;
set gtid_next='xxxxxxx:N'; --指定下一个事务执行的版本,即想要跳过的GTID
begin;
commit; --注入一个空事物
set gtid_next='AUTOMATIC' --自动的寻找GTID事务。
start slave; --开始同步

其他可能用得着的命令:

show slave status;
show binlog events;
SHOW BINARY LOGS;

对于有延迟同步需求的,可以防止误操作删除数据后无可恢复,可以在slave端执行,增加一个小时的同步延迟,不过这样的话这个库的就满足实时查数据了:

CHANGE MASTER TO MASTER_DELAY = 3600;#单位为秒。

Leave a Comment

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据