Failed mysql replication

Question

After we realize that our backups (dump) where taking too long, and an incident where the VPS provider shutdown our database vps for 2 hours, we decided to try replication (not going to remove backup, just do the backup in the replication server).

I followed a guide on replication for mysql 8, but did not realize that such guide was made for clean installation without databases already running. Then I tried to import our morning backups wishing that magically the logs would know where to start to replicate, but I was wrong.

Anyone can give me a light in how I should do it? We have some 30 databases that must be 50GB total. I really don't want to stop both servers to take a dump and them import in the other server...

This is my slave status:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: SERVER_IP
                  Master_User: replicador
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 24876902
               Relay_Log_File: pergamum-relay-bin.000004
                Relay_Log_Pos: 714
        Relay_Master_Log_File: mysql-bin.000001
             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: 1032
                   Last_Error: Could not execute Update_rows event on table gc57125800.ate_rondas_virtuais_pontos_registro; Can't find record in 'ate_rondas_virtuais_pontos_registro', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 905
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 546
              Relay_Log_Space: 293925133
              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: 1032
               Last_SQL_Error: Could not execute Update_rows event on table gc57125800.ate_rondas_virtuais_pontos_registro; Can't find record in 'ate_rondas_virtuais_pontos_registro', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 905
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: bef45e1b-99d6-11ea-a355-3e2547e4f083
             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: 200810 19:53:10
               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: 
1 row in set (0.00 sec)

UPDATE: I was doing it wrong. I used the slave log file reference and failed to get the reference at the backup time. If anyone is reading this and scratching his head thinking about your first replication, this guide was very useful to me.

Basically I needed to take a full dump with the log position, so I would be able to tell my slave where to start to replicate.

score 1 · Accepted Answer · answered Aug 11 '20 at 14:09

1

I am assuming that you are taking backups with mysqldump.

To start a replication process, the slave node needs two things:

A copy of recent database from the master
Coordinates in the master binary log corresponding to the state in the copy

You can add --master-data argument to your mysqldump command to include setting of coordinates in the backup.

After you have taken a copy this way, you can restore the copy on the slave, and then use START SLAVE to start replication. The slave will start reading from the master binary log at the position specified in the mysqldump and proceeds with replication.

answered Aug 11 '20 at 14:09

Tero Kilkanen

36,796
3
41
63

I found a similar solution here https://serverfault.com/questions/392222/mysql-replication-automation, but I was not sure if it would work (actualy, how it would work). What would be the correct way to do it? should i drop all databases? what about the configuration that I changed the master to the remote one and said what was my logfile and what was the position that i stooped? Should i just reinstall mysql to make sure it will work? Also, thanks for the help. – Aug 11 '20 at 14:26
You can start setting up the slave from scratch. Create appropriate users, then set up master node information, then restore the dump and start replication. – Tero Kilkanen Aug 11 '20 at 21:00
Thanks mate. I found a guide online and then realize that i was putting the slave log file instead of the master log file ans position. – Aug 12 '20 at 11:53

Failed mysql replication

1 Answers1