?
Solved

MySQL Replication active but not working

Posted on 2009-07-03
5
Medium Priority
?
729 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 79

Expert Comment

by:arnold
ID: 24774486
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 2000 total points
ID: 24775495
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 79

Expert Comment

by:arnold
ID: 24777483
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
ID: 24790777
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
ID: 31599470
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Suggested Courses

741 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