欢迎来到尧图网

客户服务 关于我们

您的位置:首页 > 新闻 > 资讯 > mysql8 主从复制一直失败

mysql8 主从复制一直失败

2024/12/22 2:39:22 来源:https://blog.csdn.net/zhangsheXIN_/article/details/144297245  浏览:    关键词:mysql8 主从复制一直失败

问题描述:

开启同步后从服务器一直失败,报错如下:

         Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000004, end_log_pos 47974616. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
 

这个看不出具体,转到日志文件去看:

2024-12-06T09:32:56.038060Z 5 [System] [MY-014001] [Repl] Replica receiver thread for channel '': connected to source 'slave@172.22.107.84:23306' with server_uuid=614993fb-34f0-11ef-ba87-00163e28fbbf, server_id=100. Starting replication from file 'mysql-bin.000004', position '48380251'.
2024-12-06T09:32:56.038099Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.36'  socket: '/var/lib/mysql/mysql.sock'  port: 23306  MySQL Community Server - GPL.
2024-12-06T09:32:56.254437Z 7 [ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000004, end_log_pos 47974616; Could not execute Update_rows event on table xxl_job.xxl_job_log_report; Can't find record in 'xxl_job_log_report', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's source log mysql-bin.000004, end_log_pos 47974616, Error_code: MY-001032
2024-12-06T09:32:56.254598Z 6 [ERROR] [MY-010586] [Repl] Error running query, replica SQL thread aborted. Fix the problem, and restart the replica SQL thread with "START REPLICA". We stopped at log 'mysql-bin.000004' position 47932536
2024-12-06T09:33:43.605596Z 15 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2024-12-06T09:33:43.609579Z 15 [System] [MY-014001] [Repl] Replica receiver thread for channel '': connected to source 'slave@172.22.107.84:23306' with server_uuid=614993fb-34f0-11ef-ba87-00163e28fbbf, server_id=100. Starting replication from file 'mysql-bin.000004', position '48564548'.
2024-12-06T09:33:43.676934Z 17 [ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000004, end_log_pos 47974616; Could not execute Update_rows event on table xxl_job.xxl_job_log_report; Can't find record in 'xxl_job_log_report', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's source log mysql-bin.000004, end_log_pos 47974616, Error_code: MY-001032
2024-12-06T09:33:43.677113Z 16 [ERROR] [MY-010586] [Repl] Error running query, replica SQL thread aborted. Fix the problem, and restart the replica SQL thread with "START REPLICA". We stopped at log 'mysql-bin.000004' position 47974288

注意里面的[ERROR]后面,那是具体的问题

etc/my.cnf配置

[root@PROD-VM-88 log]# vi /etc/my.cnf
[root@PROD-VM-88 log]# systemctl restart mysqld
[root@PROD-VM-88 log]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
port=23306server-id=101user=mysql
character-set-server=utf8mb4
collation-server=utf8mb4_general_cidatadir=/data/www/server/mysql
socket=/var/lib/mysql/mysql.socklog-error=/data/www/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid#关闭完整字段限制
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'lower_case_table_names=1[mysqld]
# 跳过指定的错误码|数据不存在,外健约束
slave-skip-errors=1032,1062,1452

解决办法:

因为master中的一些表是设置了外键约束所以在同步过来时无法执行,还有一些数据在slave上手动删了造成了数据的不一致,所以要把这个都跳过,方法:

[mysqld]
# 跳过指定的错误码|数据不存在,外健约束
slave-skip-errors=1032,1062,1452
 

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com