主从同步,从库报错代码:1141 ,错误信息如下:
master_port: 3306
connect_retry: 60
master_log_file: binlog.000086
read_master_log_pos: 596130762
relay_log_file: mysql-relay-bin.000486
relay_log_pos: 212230586
relay_master_log_file: binlog.000086
slave_io_running: yes
slave_sql_running: no
replicate_do_db:
replicate_ignore_db: information_schema,performance_schema,undolog,for_nagios
replicate_do_table:
replicate_ignore_table:
replicate_wild_do_table:
replicate_wild_ignore_table: information_schema.%,performance_schema.%
last_errno: 1141
last_error: coordinator stopped because there were error(s) in the worker(s). the most recent failure being: worker 1 failed executing transaction '510f1fe6-aeba-4334-b18b-3c2043b52ff2:125151353' at master log binlog.000086, end_log_pos 547512983. see error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
skip_counter: 0
exec_master_log_pos: 547458856
relay_log_space: 261211881
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: 1141
last_sql_error: coordinator stopped because there were error(s) in the worker(s). the most recent failure being: worker 1 failed executing transaction '510f1fe6-aeba-4334-b18b-3c2043b52ff2:125151353' at master log binlog.000086, end_log_pos 547512983. see error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
replicate_ignore_server_ids:
master_server_id: 33
master_uuid: 41f96eda-0f5a-11e9-ad75-00163e00d868
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: 190704 19:57:28
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set: 510f1fe6-aeba-4334-b18b-3c2043b52ff2:25836646-125206230
executed_gtid_set: 0b117566-0b1e-11e9-a8b4-00163e001495:1-3087343,
2479bd1b-1271-11e9-8c68-005056bd8639:1-81,
2d748950-c0fd-11e7-81f8-0050569175d7:35404018-35647968,
510f1fe6-aeba-4334-b18b-3c2043b52ff2:1-5363955:5393677-125151352,
db981f90-01f5-11e9-8d52-00163e008b14:1-4218,
e66adda1-ff8d-11e8-9d3d-00163e008b14:1-25261
auto_position: 0
replicate_rewrite_db:
channel_name:
master_tls_version:
解决方法:
根据上述的主从同步信息,
relay_log_file: mysql-relay-bin.000486
relay_log_pos: 212230586
在从库上使用mysqlbinlog将日志文件解析出来,命令如下:
mysqlbinlog --no-defaults -v -v --base64-output=decode-rows mysql-relay-bin.000486 > /tmp/mysql-relay-bin.000486
在解析出来的文件中找到对应的pos点: 212230586
#190704 13:47:32 server id 1 end_log_pos 547458856 xid = 4140969095
commit/*!*/;
# at 212230586
#190704 13:47:32 server id 1 end_log_pos 547458917 gtid last_committed=1230803 sequence_number=1230804 rbr_only=no
set @@session.gtid_next= '510f1fe6-aeba-4334-b18b-3c2043b52ff2:125151281'/*!*/;
# at 212230647
#190704 13:47:32 server id 1 end_log_pos 547459084 query thread_id=469641862 exec_time=0 error_code=0
set timestamp=1562219252/*!*/;
set @@session.sql_mode=1436549152/*!*/;
revoke grant option on `f_common`.* from 'f_common'@'172.20.73.%'
/*!*/;
# at 212230814
发现在执行 revoke语句 删除用户权限。
查看从库上的用户库,发现没有用户 f_common。 所以就出现问题了。
问题原因已经清楚了,后续就自己处理了。
希望与广大网友互动??
点此进行留言吧!