Docker初识：mysql8主从复制（单向）

最新推荐文章于 2024-05-24 12:00:26 发布

滴水可藏海

最新推荐文章于 2024-05-24 12:00:26 发布

阅读量271

点赞数

分类专栏： # mysql 数据库

本文链接：https://blog.csdn.net/wngpenghao/article/details/103710262

版权

mysql 同时被 2 个专栏收录

25 篇文章 1 订阅

订阅专栏

数据库

11 篇文章 0 订阅

订阅专栏

Linux系统：CentOS Linux release 7.4.1708 (Core)
Docker版本： 17.03.0-ce, build 3a232c8

mysql ：8

一、准备

1、创建主从服务器配置文件以及文件

在opt下创建mysql文件夹，以及其相关的目录结构。

1.1 主服务器目录结构以及配置文件

[root@VM_0_6_centos mysql]# pwd
/opt/mysql
[root@VM_0_6_centos mysql]# ls
master  slave
[root@VM_0_6_centos mysql]# cd master/
[root@VM_0_6_centos master]# ls
cnf  data
[root@VM_0_6_centos master]# cd cnf/
[root@VM_0_6_centos cnf]# ls
my.cnf
[root@VM_0_6_centos cnf]# cd ../data/
[root@VM_0_6_centos data]# ls
mysql

主服务器配置文件my.cnf

[mysqld]
basedir = /opt/mysql/master/
datadir = /opt/mysql/master/data/mysql

log-bin = mysql-bin
server-id = 1

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

注意：这两项必选有

#[必须]启用二进制日志，打开Binlog日志记录，并指定文件名
log-bin = mysql-bin
#/[必须]服务器唯一ID，需要给集群中的每个服务分配一个单独的ID，默认是1，从库设置为2
server-id = 1
log_bin-index：Binlog日志文件

其他基本配置说明，如果需要可以直接在配置文件中添加。

[mysqld]
server-id=1
#开启binlog
log_bin=master-bin
log_bin-index=master-bin.index
skip-name-resolve
# 设置连接端口
port=3306
# 设置mysql的安装目录
basedir = /opt/mysql/master/
# 设置mysql数据库的数据的存放目录
datadir = /opt/mysql/master/data/mysql
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password

1.2 主服务器目录结构以及配置文件

[root@VM_0_6_centos mysql]# pwd
/opt/mysql
[root@VM_0_6_centos mysql]# ls
master  slave
[root@VM_0_6_centos mysql]# cd slave/
[root@VM_0_6_centos slave]# ls
cnf  data
[root@VM_0_6_centos slave]# cd cnf/
[root@VM_0_6_centos cnf]# ls
my.cnf
[root@VM_0_6_centos cnf]# cd ../data/
[root@VM_0_6_centos data]# ls
mysql

从服务器配置文件my.cnf

[mysqld]
basedir = /opt/mysql/slave/
datadir = /opt/mysql/slave/data/mysql


log-bin = mysql-bin
server-id = 2


sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

注意：这两项必选有

#[必须]启用二进制日志，打开从服务的bin-log日志记录
log-bin = mysql-bin
#/[必须]服务器唯一ID，默认是1，从库设置为2
server-id = 2
#打开从服务的relay-log日志。
relay-log：

其他基本配置说明，如果需要可以直接在配置文件中添加。

[mysqld]
#主库和从库需要不一致
server-id=2
#打开MySQL中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
#打开从服务二进制日志
log-bin=mysql-bin
#使得更新的数据写进二进制日志中
log-slave-updates=1
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir = /opt/mysql/slave/
# 设置mysql数据库的数据的存放目录
datadir = /opt/mysql/slave/data/mysql
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password

二、主从服务器配置

1、启动主从服务器，并运行测试。

1.1 主服务器运行以及客户端连接测试

[root@VM_0_6_centos mysql]# docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               5.7                 1e4405fe1ea9        4 weeks ago         437MB
mysql               latest              d435eee2caa5        4 weeks ago         456MB
[root@VM_0_6_centos mysql]# docker run -itd -p 3307:3306 --name master -v /opt/mysql/master/cnf:/etc/mysql/conf.d -v /opt/mysql/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=master123 d435eee2caa5
404047f180831b29cbf2f6360c835b4ec73b0cb1e729a37342ad1e0d0cc9b1f6
[root@VM_0_6_centos mysql]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
404047f18083        d435eee2caa5        "docker-entrypoint.s…"   3 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:3307->3306/tcp   master
[root@VM_0_6_centos mysql]# docker exec -it 404047f18083 /bin/bash
root@404047f18083:/# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@404047f18083:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | root             | caching_sha2_password | $A$005$Npo^P0T      6KKkrgPa8dCXbzn1plB.EhBPSX3RwD/OqatHQuXsUWVhMhttEaB |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | caching_sha2_password | $A$005${m    \ "q!
                                                                             FG:d!
                                                                                  b0ISxbKLKe6bk2zHnWl4wNWkYt/3RmzFkwn/8KffCVI5 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'master123';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | root             | mysql_native_password | *E20FA3FC93A99D845ADE17D2B27F1C360ECD3B48                              |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | caching_sha2_password | $A$005${m    \ "q!
                                                                             FG:d!
                                                                                  b0ISxbKLKe6bk2zHnWl4wNWkYt/3RmzFkwn/8KffCVI5 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> 
mysql> exit;
Bye
root@404047f18083:/# exit
exit
[root@VM_0_6_centos mysql]#

客户端连接测试：

1.2 从服务器运行以及客户端连接测试

[root@VM_0_6_centos mysql]# docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               5.7                 1e4405fe1ea9        4 weeks ago         437MB
mysql               latest              d435eee2caa5        4 weeks ago         456MB
[root@VM_0_6_centos mysql]# docker run -itd -p 3308:3306 --name slave -v /opt/mysql/slave/cnf:/etc/mysql/conf.d -v /opt/mysql/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=slave123 d435eee2caa5
cbd90a269ffbfa9949c34dcaafc46247d705da8739104cf995803f239906aa01
[root@VM_0_6_centos mysql]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
cbd90a269ffb        d435eee2caa5        "docker-entrypoint.s…"   3 seconds ago       Up 2 seconds        33060/tcp, 0.0.0.0:3308->3306/tcp   slave
404047f18083        d435eee2caa5        "docker-entrypoint.s…"   6 minutes ago       Up 6 minutes        33060/tcp, 0.0.0.0:3307->3306/tcp   master
[root@VM_0_6_centos mysql]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
cbd90a269ffb        d435eee2caa5        "docker-entrypoint.s…"   9 seconds ago       Up 8 seconds        33060/tcp, 0.0.0.0:3308->3306/tcp   slave
404047f18083        d435eee2caa5        "docker-entrypoint.s…"   6 minutes ago       Up 6 minutes        33060/tcp, 0.0.0.0:3307->3306/tcp   master
[root@VM_0_6_centos mysql]# docker exec -it cbd90a269ffb /bin/bash
root@cbd90a269ffb:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'slave123';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
root@cbd90a269ffb:/# exit
exit
[root@VM_0_6_centos mysql]#

客户端连接测试：

2、进入主服务器控制台，添加复制master数据的用户replicate，供从服务器使用。

[root@VM_0_6_centos ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
cbd90a269ffb        d435eee2caa5        "docker-entrypoint.s…"   21 minutes ago      Up 21 minutes       33060/tcp, 0.0.0.0:3308->3306/tcp   slave
404047f18083        d435eee2caa5        "docker-entrypoint.s…"   27 minutes ago      Up 27 minutes       33060/tcp, 0.0.0.0:3307->3306/tcp   master
[root@VM_0_6_centos ~]# docker exec -it 404047f18083 /bin/bash
root@404047f18083:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE USER 'replicate'@'%' IDENTIFIED BY 'replicate123';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* to 'replicate'@'%' identified by 'replicate123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'reader123'' at line 1

mysql> GRANT REPLICATION SLAVE ON *.* to 'replicate'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

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

上述show master status这个指令结果中的File和Position记录的是当前日志的binlog文件以及文件中的索引。而后面的Binlog_Do_DB和Binlog_Ignore_DB这两个字段是表示需要记录binlog文件的库以及不需要记录binlog文件的库。目前我们没有进行配置，就表示是针对全库记录日志。

开启binlog后，数据库中的所有操作都会被记录到datadir当中，以一组轮询文件的方式循环记录。而指令查到的File和Position就是当前日志的文件和位置。而在后面配置从服务时，就需要通过这个File和Position通知从服务从哪个地方开始记录binLog。

注意：在实际生产环境中，通常不会直接使用root用户，而会创建一个拥有全部权限的用户来负责主从同步。

3、在从服务器上配置连接主服务器的信息

[root@VM_0_6_centos ~]# docker exec -it cbd90a269ffb /bin/bash
root@cbd90a269ffb:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to master_host='172.17.0.6',master_port=3307,master_user='replicate',master_password='replicate123',master_log_file='mysql-bin.000003',master_log_pos=3684;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.17.0.6
                  Master_User: replicate
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 3684
               Relay_Log_File: cbd90a269ffb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 3684
              Relay_Log_Space: 155
              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: NULL
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: 3d0dd786-278c-11ea-948f-0242ac120002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.6
                  Master_User: replicate
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 3684
               Relay_Log_File: cbd90a269ffb-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000003
             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: 3684
              Relay_Log_Space: 537
              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: 3d0dd786-278c-11ea-948f-0242ac120002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

说明：

Slave_IO_Running: Yes，Slave_SQL_Running: Yes即表示启动成功。
change master to master_host='主服务器Ip',master_port=主服务器端口（不要带引号）,master_user='复制用户',master_password='复制用户密码',master_log_file='master状态查看中File',master_log_pos=master状态查看中的Position（不要带引号）;
如果需要停止slave

stop slave;#如果之前启动过，需要先关闭
reset master;#重置

注意：change master指令中需要指定的Master_Log_File和Read_Master_Log_Pos必须与主服务中查到的保持一致。并且后续如果要检查主从架构是否成功，也可以通过检查主服务与从服
务之间的File和Position这两个属性是否一致来确定。

从这个指令的结果能够看到，有很多Replicate_开头的属性，这些属性指定了两个服务之间要同步哪些数据库、哪些表的配置。只是在我们这个示例中全都没有进行配置，就标识是全库进行同步。

4、通过客户端或服务器上创建测试数据库：tr。

可以看到，tr已成功复制到mysql从服务器(单向复制，即只能从master复制到slaver，而不能逆向进行)。

注意注意注意，重要的事情说三遍：

在这种MySQL主从架构中，是需要严格限制从服务的数据写入的，一旦从服务有数据写入，就会造成数据不一致。并且从服务在执行事务期间还很容易造成数据同步失败。

三、问题

1、mysql 8 中使用 grant ... identified by 时 error 1064 near 'identified by '密码'' at line 1

mysql> GRANT REPLICATION SLAVE ON *.* to 'replicate'@'%' identified by 'replicate123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'replicate123'' at line 1

解答：新版的的mysql版本已经将创建账户和赋予权限的方式分开了。

创建账户:create user '用户名'@'访问主机' identified by '密码';
赋予权限:grant 权限列表 on 数据库 to '用户名'@'访问主机' ;

2、start slave时候启动后，无法连接主服务器。

Last_IO_Error: error connecting to master 'replicate@172.17.0.6:3307' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解决：

ALTER USER 'replicate'@'%' IDENTIFIED WITH mysql_native_password BY 'replicate123';

3、执行show slave status\G时，出现：No query specified

出现此错误的原因是因为执行命令：show slave status\G;的时候，多加了一个“;”分号。

4、特别注意

主从架构是有可能失败的，如果在slave从服务上查看slave状态，发现Slave_SQL_Running=no，就表示主从同步失败了。这有可能是因为在从数据库上进行了写操作，与同步过来的SQL操作冲突了，也有可能是slave从服务重启后有事务回滚了。

解决：

1）如果是因为slave从服务事务回滚的原因，可以按照以下方式重启主从同步：

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

2）另一种解决方式就是重新记录主节点的binlog文件消息。

mysql> stop slave ;
mysql> change master to .....
mysql> start slave ;

但是这种方式要注意binlog的文件和位置，如果修改后和之前的同步接不上，那就会丢失部分数据。所以不推荐使用。

滴水可藏海

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Docker初识：mysql8主从复制（单向）

Linux系统：CentOS Linux release 7.4.1708 (Core)Docker版本： 17.03.0-ce, build 3a232c8mysql ：8一、准备1、创建主从服务器配置文件以及文件在opt下创建mysql文件夹，以及其相关的目录结构。1.1 主服务器目录结构以及配置文件[root@VM_0_6_centos mysql]# pwd...
复制链接

扫一扫

专栏目录