We help IT Professionals succeed at work.

MySQL Replication active but not working

Medium Priority
759 Views
Last Modified: 2012-05-07
Hello Experts,

Our environment is this:
Linux (centos 5) machine running MySQL 5.0.45
Windows (2003 server) machine running MySQL 5.0.45

The situation:
Replication has been setup on both machines, the linux machine being the master, and all logs and status' point it to be effective. However, the data simply does not replicate.

The following is the Master config, status and processlist and then the Slave config, status and processlist.

I've explored this being a firewall issue somewhere as the machines are not on the same network, or even on a VPN, I've opened a large range of ports, but to no avail. I'm at a loss.

MASTER CONFIG:
######################################
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
key_buffer_size=1M
tmp_table_size=32M
max_heap_table_size=32M
thread_cache_size=6
table_cache=4
query-cache-type=1
query-cache-size=20M
query_cache_limit=1M
max_connections=500
interactive_timeout=160
wait_timeout=160
connect_timeout=100
log-bin=mysql-bin
server-id=1
binlog-do-db=HafcoLive
binlog-do-db=MxLive
binlog-do-db=NZLive
binlog-do-db=TFI

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
######################################
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000011
Position: 499478
Binlog_Do_DB: HafcoLive,MxLive,NZLive,TFI,HafcoLive,MxLive,NZLive,TFI
Binlog_Ignore_DB:
1 row in set (0.00 sec)
######################################
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 3721
User: ******
Host: ************:28952
db: NULL
Command: Binlog Dump
Time: 4500
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL


######################################
SLAVE CONFIG:
######################################
[client]
port=3307

[wampmysqld]
port=3307
basedir=C:/Website/WAMP/bin/mysql/mysql5.0.45
log-error=C:/Website/WAMP/logs/mysql.log
datadir=C:/Website/WAMP/bin/mysql/mysql5.0.45/data
default-character-set=latin1
default-storage-engine=INNODB
max_connections=500
query_cache_size=20M
table_cache=4
tmp_table_size=32M
thread_cache_size=6

#*** MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=9M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=203K

#*** INNODB Specific options ***

innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=15M
innodb_log_file_size=10M
innodb_thread_concurrency=8

[mysqld]
port=3307
server-id=2
replicate-do-db=HafcoLive
replicate-do-db=MxLive
replicate-do-db=NZLive
replicate-do-db=TFI
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=phpmyadmin
binlog-ignore-db=information_schema
######################################
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: ****
Master_User: *****
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 499478
Relay_Log_File: hfwamp-relay-bin.000012
Relay_Log_Pos: 470547
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: HafcoLive,MxLive,NZLive,TFI
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: 499478
Relay_Log_Space: 470547
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
1 row in set (0.00 sec)
######################################
mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 4465
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 7
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
######################################

I hope this information helps, because its awful wall of text if it doesn't.

Any help is greatly appreciated.
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
What data does not replicate?
Before you setup the replication on the second system, did you backup the databases on the master and restore the four databases on the slave?

Do the databases exist on the slave system?

Based on the show master and show slave, the two systems reflect the same information on the binlog and where it is.
Top Expert 2005
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I do not believe it would matter.
The log-bin, will only record relevant entries.
It is also possible that the mysql engine, records the entry as database.table whether you use the database.table or use the use database and then act on a table.
If the method of entry/update was an issue, it would be part of the replication spec i.e. for replication purposes updates,inserts,deletes must have the following syntax.

Author

Commented:
Sorry for the delay.

The databases exist on both systems and before I started the replication I locked the tables and copied them accurately.

I believe snoyes_jw is onto something. I ran 2 queries on a table with just one record "tbllocks" on database "HafcoLive":

Query 1:
update HafcoLive.tbllocks set lockType = '2' where lockID = '1';

Query 2:
use HafcoLive;update tbllocks set lockType = '0' where lockID = '1';

Query 1 ran and replicated almost instantly. Query 2 ran but did not replicate. So this would mean I have to go through all my code to use database.tablename in all instances. Bummer.

Author

Commented:
The first way replicated successfully.
The second way did not.

From this I was able to correct my application and sort out my replication issues.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.