0

I tried to skip an error on the replica:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

but still getting the error below:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-docker-host-1
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002989
          Read_Master_Log_Pos: 4266303
               Relay_Log_File: 28043b8ba14f-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.002988
             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 'Vysis.ic_results' doesn't exist'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 65966074
              Relay_Log_Space: 43160656
              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 'Vysis.ic_results' doesn't exist'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 4e8fd158-853a-11ec-826f-0242ac120002
             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: 240402 09:55:06
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:

2 Answers 2

0

The existing schema & tables need to be created on the the replica mysql server before replication will work.

2
0

The error indicates that the set of tables on the replica is not the same as on the source it is subscribed to. There are many potential reasons for this.

  • When the replica was initialized, it somehow included an incomplete set of tables
  • The table was dropped on the replica
  • The table was created on the source in a session that suppressed the binary log
  • The replica started replication from the wrong position in the binary log

At this point, I would wonder if there are any other tables missing. Or whether the tables that are present have a complete set of rows.

MySQL doesn't provide good tools for detecting a divergent replica. There are community tools like pt-table-checksum that detect divergent data, but not divergent metadata.

You could mysqldump --no-data from both source and replica, save this output to a pair of files, and then diff the resulting files. This should be a quick way of showing all missing tables. Expect all the auto-increment values to be different, but otherwise we hope that the tables, columns, and indexes are identical.

I have encountered companies where the tables on the replica were missing indexes or columns, when compared to the same tables on the source!

Even if you do enumerate the missing tables and create them on the replica, you don't know what data is missing from those tables.

The most sure way to correct a replica that has extensive data or metadata divergence is to trash that replica, and initialize a new replica using a fresh backup of the source. It is not uncommon to do this regularly.

The advantage of doing a full reinitialization of the replica is that a backup has a complete snapshot of all data from all tables, so you know all references between tables are complete. The backup should also include the precise binary log position from which to start replication, so you know all subsequent changes will continue to be in sync.


Re your comment:

The replica may need to be reinitialized from time to time if it gets out of sync again. At my last job, we rebuilt a replica every week (we had thousands of replica sets).

You can detect and repair minor data discrepancies with tools like pt-table-checksum and pt-table-sync. But these aren't as reliable as a complete reinitialization.

You should make some changes to reduce the chance for the replica to diverge.

  • Start replication from a state that is known to be identical to the source. This is automatic when you rebuild a replica from a backup of the source.
  • Use ROW-based binary log format.
  • Enable read_only and super_read_only on the replica. This discourages anyone from making out-of-band updates directly on the replica.
  • Don't grant SUPER privilege to anyone who you don't trust.
  • Don't use replication filters. They don't work like you probably think they do.
  • Discourage developers or users from suppressing the binary log for any changes (sql_log_bin=off). You can't really enforce this, because setting a session variable doesn't require special privileges. But you must define this policy and communicate it to your team. If they refuse to obey the policy, then make them do the work of rebuilding the replica next time it needs to be done.
1
  • Thanks for sharing the info, if i go do process again rebuild replica and restore lates db dump to read replica probably will work, but in future again down read replica slave how to tackle same senrario currently happened, as my master db server is prod db runing and wrting data continuously, when i do this activity again i need to take down time almost 7 hours as per DB size is 51 gb, please advise how can tackle and smothley can hanldle situation if required in future. Thanks a lot!
    – V S devops
    Apr 2 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.