Skip navigation links

MySQL Forums :: Replication :: InnoDB Slave not updating


Advanced Search

InnoDB Slave not updating
Posted by: Don Pardon ()
Date: October 11, 2012 02:35AM

Hello.
I have a primary MySQL server and am trying set up a replication for InnoDB database.
I perform a Master and Slave configuration, create a dump of Master station DB, restore it on Slave station and run replication. Everything looks fine but no updates are reflected on Slave. The Slave is running, the Read_Master_Log_Pos on Master and Slave are identical. I even look up the mysql-relay-bin.00000X file and there are updates from the Master. But no changes when I run "SELECT * FROM db.table;".
I tried to create an empty database, dump it and restore from it on the Slave, start replication and then create tables on Master and fill them. In this case replication is doing well. But I also need a way to replicate already created and filled database.
I also tried to switch tables engine to MyISAM and the situation is the same.
What should I do?

I have:
my.cnf on Master:
[mysqld]
bind-address=0.0.0.0
port=3306
replicate-do-db=gcm_data
log-bin=/var/lib/mysql/mysql-bin
server-id=8217
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

my.cnf on Slave:
[mysqld]
bind-address=0.0.0.0
port=3306
replicate-do-db=gcm_data
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
relay-log=/var/lib/mysql/mysql-relay-bin
server-id=50890
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

SHOW MASTER STATUS\G
File: mysql-bin.000001
Position: 657
Binlog_Do_DB:
Binlog_Ignore_DB:

SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.13.52
Master_User: slave1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 657
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: gcm_data
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 2
Exec_Master_Log_Pos: 657
Relay_Log_Space: 551
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

SHOW PROCESSLIST on Master:
| 16 | slave1 | 10.0.13.140:54683 | NULL | Binlog Dump | 2062 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |

SHOW PROCESSLIST on Slave:
| 14 | system user | | NULL | Connect | 2203 | Waiting for master to send event | NULL |
| 15 | system user | | NULL | Connect | 2203 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |

I'm running both Master and Slave station from virtual CentOS 6.0, MySQL 5.1.61.

UPDATE:
I implemented a little behavior test: I created am empty database on Master, run a replication script (everything went well - Master and Slave were running). Then I created an InnoDB table (CREATE TABLE tbl (id INT, data VARCHAR(10)) engine=InnoDB;), the table appeared on Slave. Then I filled table with data (INSERT tbl values (1, "1");), the data was updated on Slave. Then I stopped Master (service mysqld stop) and Slave IO also stopped (SHOW SLAVE STATUS - Slave_IO_Running: No, Slave_SQL_Running: Yes). Then I started MySQL on Master and Slave IO also started in some time. But since updates are not available on Slave, hence they are present in mysql-relay-bin.00000X on Slave.



Edited 1 time(s). Last edit at 10/11/2012 04:14AM by Don Pardon.

Options: ReplyQuote


Subject Views Written By Posted
InnoDB Slave not updating 1823 Don Pardon 10/11/2012 02:35AM
Re: InnoDB Slave not updating 629 Rick James 10/11/2012 10:44PM
Re: InnoDB Slave not updating 612 Don Pardon 10/12/2012 06:12AM
Re: InnoDB Slave not updating 620 Don Pardon 10/26/2012 07:58AM
Re: InnoDB Slave not updating 541 Rick James 10/27/2012 06:15AM
Re: InnoDB Slave not updating 488 Don Pardon 10/31/2012 09:06AM
Re: InnoDB Slave not updating 427 Rick James 11/01/2012 10:43PM
Re: InnoDB Slave not updating 444 Don Pardon 11/02/2012 02:07AM
Re: InnoDB Slave not updating 450 Rick James 11/02/2012 11:36PM


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.