MySQL Slave not Replicating

2
votes

I have set up MySQL master/slave replication for 2 servers before without problems, I usually follow the great documentation on the MySQL website here. However this time I have set it up and everything looks like it should be working, but the slave is not updating with new data from the master. I will show some output from both the master and the server.

Master (IP:10.1.1.21)

show processlist

'107', 'repl', '10.1.1.22:60686', NULL, 'Binlog Dump', '523', 'Has sent all binlog to slave; waiting for binlog to be updated', NULL

show master status

'mysql-bin.000280', '10494', 'joomla', 'mysql'

Slave (IP:10.1.1.22)

show processlist

'9', 'system user', '', NULL, 'Connect', '625', 'Waiting for master to send event', NULL

'10', 'system user', '', NULL, 'Connect', '10', 'Has read all relay log; waiting for the slave I/O thread to update it', NULL

show slave status

'Waiting for master to send event', '10.1.1.21', 'repl', '3306', '60', 'mysql-bin.000280', '14029', 'tim-Dell-DM061-relay-bin.000002', '14174', 'mysql-bin.000280', 'Yes', 'Yes', '', '', '', '', '', '', '0', '', '0', '14029', '14338', 'None', '', '0', 'No', '', '', '', '', '', '0', 'No', '0', '', '0', ''

The way I tested to see if it was working was to create a table on the master. I did this and this new table did not propagate to the slave. I did check the relay binary log, and it did have a lot of updates in it. This is a website database, and I just checked it the # of hits are propagating to the slave, so it seems to be working a little.

Creating a table is not propagating, and also i tried inserting some data into a test table that existing when I did the initial data sync to the slave, this does not propagate to the slave either.

Any ideas of how I can debug this further??

Thanks!

EDIT:

the show global variables like 'replicate%' returns 2 columns: "Variable_name", "Value". There aren't any rows of data.

2
mysqlreplicationmaster-slave
Could you also post the output of the following command: show global variables like 'replicate% (executed on the slave). – Dimitre Radoulov
@DimitreRadoulov I posted the output above, thanks! – jeffery_the_wind
Hi @jeffery_the_wind, see Rolando's comments below - the issue may be related to the settings of binlog-do/ignore-db. – Dimitre Radoulov
Yes they are related to the binlog-do-db settings, thanks! – jeffery_the_wind
This should resolve itself, but it can take many hours – Tum

2 Answers

8
votes

Apparently your bin position on the master is currently 10494 and your slave sees your Read_Master_Log_Pos as 14029.

So your slave thinks it is ahead of the master in log position, and waiting for the master to have new data.

6
votes

In our SHOW MASTER STATUS; I noticed you have this

'mysql-bin.000280', '10494', 'joomla', 'mysql'

I cringe when someone uses binlog-do-db. Here is why.

There is an old bug report for MySQL 4.0 that I do not fully believe was fixed.