Last_SQL_Errno: 1146

1

hi,
I set up the replication. my.cnf is the same(master-slave). but when I say show slave, I see that I get an error.
Last_SQL_Errno: 1146
Last_SQL_Error: Error executing row event: ‘Table ‘table.tablename’ doesn’t exist’

I’m backing up with xtrabackup. I can restore.
Why does it give this error?

slave restore log:

230716 23:06:27 [01] Copying ./xtrabackup_info to /data/mysql/mysql/xtrabackup_info
230716 23:06:27 [01] …done
230716 23:06:27 completed OK!

2

Hi @parttime.

As the error states, your issue is when replica is trying to work on a table that doesn’t exists in your replica:

Last_SQL_Errno: 1146
Last_SQL_Error: Error executing row event: ‘Table ‘table.tablename’ doesn’t exist’

This means your data is not consistent between your primary and your replica, thus something that was successfully executed on the primary, fails when it’s trying to execute on the replica.

If I understood correctly, you’ve restored recently from the XtraBackup? If so, could you share how you restored the data and how you configured the replication?

If you can rebuild your replica it may be better than trying to fix this; since this error basically means the data in between your primary and your replica is not consistent. Here’s a simple tutorial on how to achieve this using Percona XtraBackup, take a look and I hope this helps you.

Best,
Mauricio.

3

yes, ı did. script is as below.

$ innobackupex --copy-back /data/mysql/script
230716 14:22:22 completed OK!

I get backup with xtrabackup, I did restore as in the script. However i am getting the error.

4

Hello @parttime

Please ensure you are using xtrabackup_binlog_info file binlog pos/ GTID details to set up a replica.

Ref:

For GTID replication: How to create a new (or repair a broken) GTID based slave - Percona XtraBackup

PS/MySQL-8.0:

For GTID replication: How to create a new (or repair a broken) GTID-based replica - Percona XtraBackup