mysql主从同步

北冥牧之 2024-10-20
42 阅读 0 评论 0 点赞
mysql主从同步

目录

1.创建主从同步用户

2.授予主从同步权限

3.刷新权限

4.修改master配置文件 

5.重启MySQL

6.查看master的状态

7.修改slave配置文件

8.重启mysql

9.构建主从连接信息

10.开始同步

11.查看同步信息

12.同步失败

13.同步报错

13.1 server_id冲突

 13.2 initialize relay log失败

13.3 MySQL server UUIDs相同

说明：

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

同时，MySQL版本是8.0.27。

1.创建主从同步用户

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

  1. CREATE USER ''repl''@''%'' IDENTIFIED BY ''123456'';
  2. 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]条目中，添加:

  1. [mysqld]
  2. # server-id 服务器唯一标识
  3. server_id=1
  4.  
  5. # log_bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
  6. log_bin=master-bin
  7.  
  8. # binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名.
  9. # 如果复制多个数据库，重复设置这个选项即可。默认全部记录.
  10. # 一般不特别指定
  11. # binlog_do_db=test
  12.  
  13. # binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名.
  14. # 如果有多个数据库，重复设置这个选项即可。
  15. binlog-ignore-db=mysql
  16. binlog_ignore_db=sys
  17. binlog_ignore_db=performance_schema
  18. binlog_ignore_db=information_schema
  19.  
  20. # 其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。
  21.  
  22. # 设置二进制日志使用内存大小（事务）
  23. binlog_cache_size=1M  
  24.  
  25. # 设置使用的二进制日志格式（mixed,statement,row）
  26. binlog_format=mixed  
  27.  
  28. # 二进制日志过期清理时间。默认值为0，表示不自动清理。
  29. expire_logs_days=7  
  30.  
  31. # 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
  32. # 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
  33. 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配置文件

  1. # 注意，这里同4
  2. # docker安装mysql挂载目录
  3. vim /user/local/mysql/conf/my.cnf
  4. # 二进制包安装mysql
  5. vim /etc/my.cnf
  1. #在[mysqld]中添加：
  2. server-id=2
  3. relay-log=slave-relay-bin
  4. # relay-log-index=slave-relay-bin.index
  5. # replicate-do-db=test
  6.  
  7. #备注：
  8. # server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，
  9. # 跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
  10.  
  11. # relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，
  12. # 或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。
  13.  
  14. # replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。
  15. # 若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
  16.  
  17. # replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
  18. # 其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。
  19.  
  20. ######## 一下配置不指定也可以 ########
  21.  
  22. # 指定不需要同步的数据库
  23. binlog_ignore_db=mysql
  24. binlog_ignore_db=sys
  25. binlog_ignore_db=performance_schema
  26. # 设置二进制日志使用内存大小（事务）
  27. binlog_cache_size=1M
  28. # 设置使用的二进制日志格式（mixed,statement,row）
  29. binlog_format=mixed
  30. # 二进制日志过期清理时间。默认值为0，表示不自动清理。
  31. expire_logs_days=7
  32. # 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
  33. # 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
  34. slave_skip_errors=1062
  35. # relay_log配置中继日志
  36. relay_log=mall-mysql-relay-bin
  37. # log_slave_updates表示slave将复制事件写进自己的二进制日志
  38. log_slave_updates=1
  39. # slave设置为只读（具有super权限的用户除外）
  40. read_only=1

8.重启mysql

  1. # 二进制安装
  2. systemctl restart mysqld
  3.  
  4. # docker安装
  5. docker restart mysql

9.构建主从连接信息

  1. change master to master_host=''192.168.222.128'',master_port=3306,master_user=''repl'',
  2. master_password=''123456'',master_log_file=''binlog.000001'',master_log_pos=156, master_connect_retry=30;
  3.  
  4. # master_host：对应主服务器的IP地址。
  5. # master_port：对应主服务器的端口。
  6. # master_user：在主数据库创建的用于同步数据的用户账号。
  7. # master_password：在主数据库创建的用于同步数据的用户密码。
  8. # master_log_file：指定从数据库要复制数据的日志文件，对应show master status显示。
  9. # 的File字段的取值，我这里是master-bin.000001。
  10. # master_log_pos：指定从数据库从哪个位置开始复制数据，对应show master status显示的Position字段# 的取值，我这里是154。
  11. # master_connect_retry：连接失败重试的时间间隔，单位为秒。这一项可以不配置。

10.开始同步

  1. #启动slave数据同步
  2. start slave;
  3.  
  4. #停止slave数据同步（若有需要）
  5. 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来跳过事件，跳过这一个错误，然后执行从下一个事件组开始。

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

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

  1. -- 在slave上执行
  2. stop slave;
  3. reset slave all;
  4.  
  5. -- 在master上执行
  6. reset master;
  7.  
  8. -- 在slave上执行，重新执行构建主从连接的SQL即可
  9. change master to master_host=''192.168.222.128'',master_port=3306,master_user=''repl'',
  10. master_password=''123456'',master_log_file=''binlog.000001'',master_log_pos=156;
  11.  
  12. 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：

  1. exit
  2.  
  3. docker restart mysql

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

start slave;

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

show slave status;

New Image

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

标签： 技术社区

相关文章