1

Below is the status of my slave. The master log file is at position "mysql-bin-changelog.278337" but the slave log file is at position "mysql-bin-changelog.278008". How to skip the current executing "Relay_Master_Log_File" t point to some other log file?

    mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: kdm-eink-prod.cfewehpmjefz.us-east-1.rds.amazonaws.com
                  Master_User: rdsrepladmin
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.278337
          Read_Master_Log_Pos: 120
               Relay_Log_File: relaylog.377536
                Relay_Log_Pos: 1148
        Relay_Master_Log_File: mysql-bin-changelog.278008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.config_options,innodb_memcache.cache_policies
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 975
              Relay_Log_Space: 354792434
              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: 98230
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1113717901
                  Master_UUID: ec0eef96-a6e9-11e2-bdf0-0015174ecc8e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Sending data
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.24 sec)

ERROR: 
No query specified
  • Is replication still running ? In other words, is Slave_IO_Running = Yes and Slave_SQL_Running = Yes ? – RolandoMySQLDBA Apr 15 '16 at 14:59
1

Replication Lag is usually measured by finding the difference between the following

  • TIMESTAMP#1 at (Relay_Master_Log_File,Exec_Master_Log_Pos)
  • TIMESTAMP#2 at (Master_Log_File,Read_Master_Log_Pos)

From what I am seeing in the SHOW SLAVE STATUS\G

  • you are 98230 seconds (27 hours 17 minutes 10 seconds) behind
  • you have 329 binlogs (278337 - 278008) to process after mysql-bin-changelog.278337
  • The sum of all your binlogs is 354792434 (338M)

If the Seconds_Behind_Master is steadily increasing by one every second, you will have to wait it out and let replication catch up. This is a strange indication that you are running some terrible query that is making the number of binlogs waiting to be processed increase. You could see that query by running show processlist; and look for the SQL thread (where the user is system user) with the same number for Time as the Seconds_Behind_Master.

If you cannot wait for the catch up of replication, you just need to spin up another read-only slave and drop this very latent slave.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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