1

Is there a way to restart replication without going over MySQL :: MySQL 5.1 Reference Manual :: 16 Replication.

I'm NOT seeing ANY errors on master and I'm not entirely sure why slave is experiencing issues such as following:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: Master_Host-FQDN.TLD
                  Master_User: Master_User-REPL
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000057
          Read_Master_Log_Pos: 247673142
               Relay_Log_File: mysql:PR_DB-relay-bin.000041
                Relay_Log_Pos: 5537565
        Relay_Master_Log_File: mysql-bin.000044
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: RDD1,RDD2,RDDX
          Replicate_Ignore_DB: information_schema,mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 25338679
              Relay_Log_Space: 2190669763
              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: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

--log-error=/var/log/mysqld.log (slave):

140630  9:10:53 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 255, event_type: 2
140630  9:10:53 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
140630  9:10:53 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
140630  9:10:53 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000044' position 25338679

Can I use CHANGE MASTER TO (MySQL :: MySQL 5.1 Reference Manual :: 16.1.1.10 Setting the Master Configuration on the Slave) and point to respective values:

  • MASTER_LOG_FILE = Relay_Master_Log_File,
  • MASTER_LOG_POS Exec_Master_Log_Pos.
4

The safest position you should replicate from is

  • Log File mysql-bin.000044
  • Log Position : 25338679

These represent the Latest Binary Log Event that Executed on the Slave. That is where you pick up replication from. Running CHANGE MASTER TO will erase the relay logs and start with brand new relay logs.

  • you're missing LOG_POS (which is 25338679). – alexus Jun 30 '14 at 21:06
1

i have the same error I do this:

  1. Stop slave (stop slave ;)
  2. reset slave (rest slave ;)
  3. change master_log_file and master_log_pos (CHANGE MASTER TO MASTER_LOG_FILE='mysql-Iin.000006', MASTER_LOG_POS= 11054817;) (use Relay_Master_Log_File for MASTER_LOG_FILE and Exec_Master_Log_Pos for MASTER_LOG_POS )
  4. start slave (start slave ;)

after do this i see : DUPLICATE entry error (1062 error) for this error i do this steps:

  1. open my.cnf file (/etc/my.cnf or /etc/mysql/my.cnf)
  2. add thi line : slave-skip-errors = 1062
  3. restart mysql server

and slave replicated normally without any error

0

In my case, it seems I had the same kind of problem:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.4.0.4
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: gtc-master-bin.000022
          Read_Master_Log_Pos: 163183335
               Relay_Log_File: gtc-relay-bin.000195
                Relay_Log_Pos: 37830249
        Relay_Master_Log_File: gtc-master-bin.000022
             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: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 74792622
              Relay_Log_Space: 163184965
              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: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 793997ad-6398-11e6-a062-000d3aa1879f
             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: 170506 19:50:05
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Looking at the error log from the slave I could see that the root cause was a problem about low disk space:

2017-05-06T19:02:52.678039Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 15056ms. The settings might not be optimal. (flushed=2 and evicted=0, during the time.)
2017-05-06T19:10:27.232249Z 2 [ERROR] Disk is full writing './gtc-relay-bin.000195' (Errcode: 15180656 - No space left on device). Waiting for someone to free space...
2017-05-06T19:10:27.232689Z 2 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
2017-05-06T19:10:27.362769Z 18007 [Note] Aborted connection 18007 to db: 'unconnected' user: 'backup' host: 'localhost' (Got an error reading communication packets)
2017-05-06T19:20:27.244649Z 2 [ERROR] Disk is full writing './gtc-relay-bin.000195' (Errcode: 15180656 - No space left on device). Waiting for someone to free space...
2017-05-06T19:20:27.264895Z 2 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
2017-05-06T19:30:27.417753Z 2 [ERROR] Disk is full writing './gtc-relay-bin.000195' (Errcode: 15180656 - No space left on device). Waiting for someone to free space...
2017-05-06T19:30:27.419691Z 2 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
2017-05-06T19:40:27.864058Z 2 [ERROR] Disk is full writing './gtc-relay-bin.000195' (Errcode: 15180656 - No space left on device). Waiting for someone to free space...
2017-05-06T19:40:27.864800Z 2 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
2017-05-06T19:49:40.947589Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-05-06T19:49:40.948741Z 0 [Warning] Changed limits: ta2017-05-06T19:49:49.121921Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-05-06T19:49:49.121958Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-05-06T19:49:49.122016Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-05-06T19:49:53.758747Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-05-06T19:49:53.759413Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-05-06T19:49:53.759422Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-05-06T19:49:53.775719Z 0 [Note] InnoDB: Waiting for purge to start
2017-05-06T19:49:53.828518Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6720ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-05-06T19:49:53.829030Z 0 [Note] InnoDB: 5.7.13 started; log sequence number 15543585458
2017-05-06T19:49:53.830064Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-05-06T19:49:53.830640Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-05-06T19:49:53.912376Z 0 [Note] Recovering after a crash using gtc-slave-bin
2017-05-06T19:49:53.912476Z 0 [Note] Starting crash recovery...
2017-05-06T19:49:53.912579Z 0 [Note] Crash recovery finished.
2017-05-06T19:49:55.163266Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170506 19:49:55
2017-05-06T19:49:58.665294Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-05-06T19:49:58.665321Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2017-05-06T19:49:58.665330Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2017-05-06T19:49:58.665368Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2017-05-06T19:50:05.480733Z 0 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 367, event_type: 2
2017-05-06T19:50:05.480765Z 0 [Warning] Error reading GTIDs from relaylog: -1
2017-05-06T19:50:05.620202Z 2 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-05-06T19:50:05.621524Z 1 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-05-06T19:50:05.621572Z 1 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'gtc-master-bin.000022' at position 74792622, relay log './gtc-relay-bin.000195' position: 37830249
2017-05-06T19:50:05.630830Z 2 [Note] Slave I/O thread for channel '': connected to master 'replicator@10.4.0.4:3306',replication started in log 'gtc-master-bin.000022' at position 74792800
2017-05-06T19:50:05.651022Z 1 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 367, event_type: 2
2017-05-06T19:50:05.651066Z 1 [ERROR] Error reading relay log event for channel '': slave SQL thread aborted because of I/O error
2017-05-06T19:50:05.651117Z 1 [ERROR] Slave SQL for channel '': Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
2017-05-06T19:50:05.651138Z 1 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'gtc-master-bin.000022' position 74792622
2017-05-06T19:50:05.797632Z 0 [Note] Event Scheduler: Loaded 0 events
2017-05-06T19:50:05.797742Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.13-0ubuntu0.16.04.2-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2017-05-06T19:50:05.836873Z 4 [Note] Access denied for user 'root'@'localhost' (using password: NO)

Once I solved that problem, the way to resume replication is:

  1. Check Relay_Master_Log_File and Exec_Master_Log_Pos values. In my case, gtc-master-bin.000022 and 74792622.
  2. Issue the following statements using the above values.

This way:

mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.52 sec)
mysql> change master to master_log_file='gtc-master-bin.000022',master_log_pos=74792622;
Query OK, 0 rows affected (0.49 sec)
mysql> start slave;

Then it started to work normally again.

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.