主从同步,从库报错代码: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。 所以就出现问题了。
问题原因已经清楚了,后续就自己处理了。
如对本文有疑问, 点击进行留言回复!!
网友评论