Replication on mysql doesn't work

MySQL & MariaDB
1

Need help in configuring replication on mysql
I have 2 nodes
1 master
1 slave

mysql>show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 
                  Master_User: 
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: test-db-2-bin.000923
          Read_Master_Log_Pos: 39653484
               Relay_Log_File: aws-test-db-6-relay-bin.000052
                Relay_Log_Pos: 405
        Relay_Master_Log_File: test-db-2-bin.000923
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1146
                   Last_Error: Error executing row event: 'Table 'test_audit' doesn't exist'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 39654557
              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: 1146
               Last_SQL_Error: Error executing row event: 'Table 'test_audit' doesn't exist'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 5fb46b09-6201-11e8-a779-0a3d7cb020ed
             Master_Info_File: /var/lib/mysql/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: 230505 00:17:35
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:

Error Logs:

[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'test-db-2-bin.000923' position 154.

[ERROR] Slave SQL for channel '': Error executing row event: 'Table 'test_audit' doesn't exist', Error_code: 1146

[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'test-db-2-bin.000923' position 154.
2

Hi @Aldrin_John_Lagrima2, welcome to the Percona Forum!

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 ‘test_audit’ 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.

How did was this replica created? Did you load the data into the server before configuring your replication?

It looks to me that this is some testing, so if you can rebuild your replica it may be better than trying to fix this. Here’s a simple tutorial on how to achieve this using Percona XtraBackup, take a look and I hope this helps you.

Best,
Mauricio.