Solved

MySQL Replication active but not working

Posted on 2009-07-03
5
696 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.
0
Comment
Question by:machineryhouse
  • 2
  • 2
5 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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.
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
Comment Utility
Do you use fully qualified table names in your queries, or do you set the default database and then use relative table names? That is, which of the following?

INSERT INTO HafcoLive.someTable VALUES (...

vs.

USE HafcoLive;
INSERT INTO someTable VALUES(...
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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.
0
 
LVL 1

Author Comment

by:machineryhouse
Comment Utility
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.
0
 
LVL 1

Author Closing Comment

by:machineryhouse
Comment Utility
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now