MySQL Forums
Forum List  »  Replication

Slave Replication lag in time
Posted by: Dan Berg
Date: February 24, 2014 04:34AM

Hi All,

Having a problem with MySQL 5.5.35 Master / Slave replication.

Using InnoDB engine.

Root problem:

Seconds_behind_the_master - keeps growing up and unable to catch-up the master until it loss last bin-log.

Slave I/O / Slave_SQL - shows status as YES.
Statement update mode: Mixed
Both Master and Slave having the same hardware configuration

Tried many different advices to resolve, among them:

1) Timesync verification
2) Setting new replication.
3) Reseting master.
4) Stoping Slave_SQL_THREAD
5) innodb_flush_log_at_trx_commit = 0
6) Creating a new fresh instance.


Below is myL:

Master status:

mysql> show master status;
+-------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+--------------+------------------+
| mysql2-bin.031682 | 300009048 | | |
+-------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)


Slave status:

Slave_IO_State: Waiting for master to send event
Master_Host: 10.253.1.23
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql2-bin.031682
Read_Master_Log_Pos: 315893523
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 119851712
Relay_Master_Log_File: mysql2-bin.031676
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 362358490
Relay_Log_Space: 3219895040
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: 39865
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: 1
1 row in set (0.00 sec)

process list:

+-----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
| 141 | system user | | NULL | Connect | 345 | Waiting for master to send event | NULL |
| 142 | system user | | NULL | Connect | 39975 | Reading event from the relay log | NULL |
| 144 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
3 rows in set (0.00 sec)


Any other advice, what else can be possible to done?

Options: ReplyQuote


Subject
Views
Written By
Posted
Slave Replication lag in time
1918
February 24, 2014 04:34AM
736
February 25, 2014 10:47PM
804
February 26, 2014 09:09AM
844
February 27, 2014 05:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.