mysql主从同步

北冥牧之

已于 2022-12-26 17:57:49 修改

阅读量2.5k

点赞数 2

分类专栏：数据库文章标签： mysql 数据库服务器

于 2022-12-23 13:12:43 首次发布

本文链接：https://blog.csdn.net/qq_42971035/article/details/128417790

版权

数据库专栏收录该内容

20 篇文章 5 订阅

订阅专栏

13.2 initialize relay log失败

13.3 MySQL server UUIDs相同

说明：

本文是基于Docker安装的mysql的主从同步，非这种方式在本文可能个别地方会有区别。

同时，MySQL版本是8.0.27。

1.创建主从同步用户

在master数据库上创建主从同步的用户repl：

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
alter user 'repl'@'%'  IDENTIFIED with mysql_native_password by '123456';

在MySQL8以后默认是不使用密码的，所以我们添加mysql_native_password参数并设置密码。

2.授予主从同步权限

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';

这里的%，可以指定一定网段的，比如192.168.200.%

3.刷新权限

FLUSH PRIVILEGES;

4.修改master配置文件

注意：

如果是rpm包安装的，mysql的配置所在位置一般是：/etc/my.cnf。

如果是docker安装的，一般是在指定挂载的目录下：/usr/local/mysql/conf/my.cnf

在[mysqld]条目中，添加:

[mysqld]
# server-id 服务器唯一标识
server_id=1

# log_bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
log_bin=master-bin

# binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名.
# 如果复制多个数据库，重复设置这个选项即可。默认全部记录.
# 一般不特别指定
# binlog_do_db=test

# binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名.
# 如果有多个数据库，重复设置这个选项即可。
binlog-ignore-db=mysql
binlog_ignore_db=sys
binlog_ignore_db=performance_schema
binlog_ignore_db=information_schema

# 其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。

# 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  

# 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  

# 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  

# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

5.重启MySQL

如果是docker安装的mysql：

docker restart mysql

如果是rpm等二进制包安装的：

systemctl restart mysqld

注意：修改MySQL的配置是一定要重启MySQL的。

6.查看master的状态

show master status;

记住file和position的取值：binlog.000001和392

7.修改slave配置文件

# 注意，这里同4
# docker安装mysql挂载目录
vim /user/local/mysql/conf/my.cnf
# 二进制包安装mysql
vim /etc/my.cnf

#在[mysqld]中添加：
server-id=2
relay-log=slave-relay-bin
# relay-log-index=slave-relay-bin.index
# replicate-do-db=test

#备注：
# server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，
# 跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。

# relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，
# 或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。

# replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。
# 若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。

# replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
# 其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

######## 一下配置不指定也可以 ########

# 指定不需要同步的数据库
binlog_ignore_db=mysql
binlog_ignore_db=sys
binlog_ignore_db=performance_schema
# 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
# 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
# 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
# relay_log配置中继日志
relay_log=mall-mysql-relay-bin
# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
# slave设置为只读（具有super权限的用户除外）
read_only=1

8.重启mysql

# 二进制安装
systemctl restart mysqld

# docker安装
docker restart mysql

9.构建主从连接信息

change master to master_host='192.168.222.128',master_port=3306,master_user='repl',
master_password='123456',master_log_file='binlog.000001',master_log_pos=156, master_connect_retry=30;

# master_host：对应主服务器的IP地址。
# master_port：对应主服务器的端口。
# master_user：在主数据库创建的用于同步数据的用户账号。
# master_password：在主数据库创建的用于同步数据的用户密码。
# master_log_file：指定从数据库要复制数据的日志文件，对应show master status显示。
# 的File字段的取值，我这里是master-bin.000001。
# master_log_pos：指定从数据库从哪个位置开始复制数据，对应show master status显示的Position字段# 的取值，我这里是154。
# master_connect_retry：连接失败重试的时间间隔，单位为秒。这一项可以不配置。

10.开始同步

#启动slave数据同步
start slave;
 
#停止slave数据同步（若有需要）
stop slave;

11.查看同步信息

show slave status;

也可以在mysql命令行中使用如下命令：

show slave status \G

Slave_IO_Running和Slave_SQL_Running都为yes，则表示同步成功。

Slave_IO_Running是slave和master进行二进制日志同步的线程。

Slave_SQL_Running是slave执行写入relay中继日志的SQL的线程。

12.同步失败

执行完同步命令后查看同步信息，会有一个字段用来显示报错信息：

Last_SQL_Error。

若在主从同步的过程中，出现其中一条语句同步失败报错了，则后面的语句也肯定不能同步成功了。例如，主库有一条数据，而从库并没有这一条数据，然而，在主库执行了删除这一条数据的操作，那么从库没有这么一条数据就肯定删除不了，从而报错了。在此时的从数据库的数据同步就失败了，因此后面的同步语句就无法继续执行。

这里提供的解决方法有两种：
（1）在从数据库中，使用SET全局sql_slave_skip_counter来跳过事件，跳过这一个错误，然后执行从下一个事件组开始。

-- 在从数据库上操作
stop slave;
set global sql_slave_skip_counter=1;
tart slave;

（2）在从数据库中，重新连上主数据库。这种操作会直接跳过中间的那些同步语句，可能会导致一些数据未同步过去的问题。最好就是等到从数据库与主数据库的数据结构和数据都一致了之后，再来恢复主从同步的操作。

-- 在slave上执行
stop slave;
reset slave all;

-- 在master上执行
reset master;

-- 在slave上执行，重新执行构建主从连接的SQL即可
change master to master_host='192.168.222.128',master_port=3306,master_user='repl',
master_password='123456',master_log_file='binlog.000001',master_log_pos=156;

start slave;

13.同步报错

这个过程中可能遇到的错误大体上有一下几种。

13.1 server_id冲突

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

说的是主从库之间的server_id相同。

修改my.cnf配置文件的server_id即可。

注意：所有的数据库的server_id不能相同，可以自行修改。

修改之后记得重启mysql数据库。

13.2 initialize relay log失败

Fatal error: Slave failed to initialize relay log info structure from the repository

这个一般是之前有过数据同步，修改了之后重新进行同步报错的。

解决方案就是：

在进行同步之前先重置同步信息，具体就是：在start slave之前，先启用reset slave。

13.3 MySQL server UUIDs相同

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

这个发生的概率较大，一般是因为虚拟机是copy的，而不是单独生成的。

不管是二进制包安装还是docker方式安装，这个都可能会发生。

解决方案都是一样的，只是docker的多了一步而已。

为了确定主从库的uuids是一样的，我们在数据库查看UUIDs：

show variables like '%server_uuid%';

分别在主从库中执行这个命令，我们得到的都是：

4b729139-6320-11ed-80c4-0242ac110002

这说明确实是冲突了。

解决方案：

如果是二进制包等方式安装的直接执行如下命令：

find / -name auto.cnf

一般是这个目录：/var/lib/mysql/auto.cnf

如果是docker安装的，一般在挂载的mysql的数据目录下：/usr/local/mysql/data/auto.cnf

我们打开这个文件，发现这里面的UUIDs和我们使用show variables like '%server_uuid%'这个命令查看的是一致的，这说明我们找到的文件是正确的。

二进制包安装的方式直接删除主从库中其中一个的这个文件即可，

rm -f /var/lib/mysql/auto.cnf

然后重启数据库：

systemctl restart mysqld

重启之后，auto.cnf这个文件会重新生成，我们发现这个UUIDs已经变了：

bc502531-843d-11ed-a1d6-0242ac110002

网上有一些文章说这个id是随便生成的，我们随便修改就行。实际上不是的，随便修改的话会导致mysql启动失败的。

其实我们看看这两个UUIDs会发现，只有前2段变了，后面的3段其实没有变化的。如果要修改前2段是否可行，这个我不确定。但是修改后面的肯定会导致mysql启动失败的，这个是血的教训。

如果是docker安装方式，如果我们直接修改/usr/local/mysql/data/auto.cnf的话是不行的，我们要进入容器中进行操作：

docker exex -it mysql bash

同样执行命令：

find / -name auto.cnf

同样在这个目录中：

/var/lib/mysql/auto.cnf

我们删除这个文件即可。

rm -f /var/lib/mysql/auto.cnf

然后重启退出容器，重启mysql：

exit

docker restart mysql

最后重新执行同步命令即可：

start slave;

再查看同步信息就正常了：

show slave status;

一般地看到：Slave_IO_Running和Slave_SQL_Running的取值都是Yes就代表主从同步成功了。此时就可以在主库中创建数据库、修改表结构、新增数据等操作，然后在从库中去查看是否有同步了。如果正常同步说明主从同步已经成功了。

北冥牧之

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql主从同步

若在主从同步的过程中，出现其中一条语句同步失败报错了，则后面的语句也肯定不能同步成功了。例如，主库有一条数据，而从库并没有这一条数据，然而，在主库执行了删除这一条数据的操作，那么从库没有这么一条数据就肯定删除不了，从而报错了。在此时的从数据库的数据同步就失败了，因此后面的同步语句就无法继续执行。这种操作会直接跳过中间的那些同步语句，可能会导致一些数据未同步过去的问题。（1）在从数据库中，使用SET全局sql_slave_skip_counter来跳过事件，跳过这一个错误，然后执行从下一个事件组开始。
复制链接

扫一扫