Overview
We are trying to implement multi-master replication with MySQL using Federated tables (see explanation below). We have one central Linux server with a LAMP stack. We currently have two manufacturing locations (Plants) that have local MySQL servers to collect the data. We plan to eventually implement this at all 6 of our facilities. We use replication to get data from all the plants combined into one database on the Production MySQL server on the Linux server. We have standard single master multi-slave MySQL replication from the Production server to the plants.
The database stores information for a data collecting system. There are about 30,000 records per plant that are inserted each day.
Multi-Master Replication
MySQL does not have native support for multi-master replication. We’re implementing this as a work around: http://www.fromdual.com/sites/default/files/mm-single-slave-repl.pdf. Our current setup has 5 MySQL servers (more will be added if we add more plants to the system):
- Plant A server, version 5.1.50 (running at the plant on a Windows 2008 server)
- Plant A Federated Table server, version 5.1.34 (running on the Linux server)
- Plant B server, version 5.1.34 (running at the plant on a Windows 2008 server)
- Plant B Federated Table server, version 5.1.34 (running on the Linux server)
- Production server, version 5.1.34 (running on the Linux server)
Plant A local server replicates to the Plant A federated table server. The tables on the Plant A federated table server are federated to the ‘Production’ server. The Production MySQL server replicates to the Plant A local server.
Here is a diagram outlining the different MySQL servers that we have: http://imgur.com/aNrin
The Federated tables use the following format for the connection string: CONNECTION='mysql://username:password@localhost/dbevents/tblEvent'
Most of our tables do not replicate both ways; they either replicate from the Production server to the plant or the other way around. 99% of the data flow is from the Plant to the Production server.
Monitoring Replication
We have a PHP script running on the Linux server every 5 minutes to check the status of the replication between these servers. This script runs a ‘SHOW SLAVE STATUS’ command on each MySQL server slave to check the status of the ‘Slave_IO_Running’ and ‘Slave_SQL_Running’ fields for ‘Yes’ values. We receive emails if one of the values is ‘No’. The script also runs a ‘SHOW MASTER STATUS’ on each master and compares the log position to the slave to ensure that they are somewhat close.
Problem
We started to have replication problems from the beginning where the replication would break between the Plant A’s MySQL server (master) and the associated Federated Table Server (slave). There is no entry under ‘Last_SQL_Error’ when running a SHOW SLAVE STATUS command. The ‘Slave_IO_State’ is ‘Waiting to reconnect after a failed master event read’. We have tried the following to restart the replication:
- ‘stop slave’ command followed by a ‘start slave’ command.
- Restarting the MySQL service on the plant’s server.
- ‘stop slave’, ‘reset slave’, ‘change master to …’ and then ‘start slave’
Restarting the whole Windows server at Plant A seems to be the only way to resolve the issue. The problem happens every 5 days to 3 weeks. Since we started logging replication problems (April 1st, 2012) we’ve seen it happen 7 times at Plant A. We are currently starting to implement this system at Plant B and started to experience the same problem. We’ve only had Plant B live for ~1 week.
Does anyone have any experience with Multi-Master replication, experience with a replication problem like this, or any suggestions for what to troubleshoot next?
Configuration Settings
Here are the related MySQL configuration files:
my-Prod.cnf
[client]
port = 3306
socket = /var/run/mysql/mysql.sock
[mysqld]
datadir = /usr/local/mysql/data
port = 3306
socket = /var/run/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
skip-federated
log-bin=mysql-bin
binlog-do-db=dbevents
binlog-do-db=dbPRT
binlog-do-db=dbPDTS
binlog-do-db=dbSupplierPerformance
binlog-do-db=dbGlbUsr
replicate-wild-do-table=dbevents.tbl_evice_vent
replicate-wild-do-table=dbevents.tbl_vent
replicate-wild-do-table=dbevents.tbl_ycle
replicate-wild-do-table=dbevents.tbl_athfinder6000_ycle
replicate-wild-do-table=dbevents.tbl_eak_ester_ycle
replicate-wild-do-table=dbevents.tbl_evice
replicate-wild-do-table=dbevents.tbl_ress
replicate-wild-do-table=dbevents.tbl_ift_instance
replicate-wild-do-table=dbevents.tbl_vent_ag__
replicate-wild-do-table=dbevents.tbl_hift_nstance
binlog_format=mixed
server-id = 1
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
my-PlantA.ini
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"
datadir="D:/Data/MySQL/Data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=203M
table_cache=256
tmp_table_size=205M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=410M
key_buffer_size=319M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_data_home_dir="D:/Data/MySQL/MySQL InnoDB Datafiles/"
innodb_additional_mem_pool_size=13M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=7M
innodb_buffer_pool_size=618M
innodb_log_file_size=124M
innodb_thread_concurrency=18
log-bin
server-id=4
binlog-do-db=dbevents
slave-skip-errors=1062, 1032
replicate-wild-do-table=dbevents.tbl_ompany_hift
replicate-wild-do-table=dbevents.tbl_onnection
replicate-wild-do-table=dbevents.tbl_evice
replicate-wild-do-table=dbevents.tbl_evice_vent
replicate-wild-do-table=dbevents.tbl_evice_ype
replicate-wild-do-table=dbevents.tbl_vent_rocessor
replicate-wild-do-table=dbevents.tbl_vent_ype
replicate-wild-do-table=dbevents.tbl_essage
replicate-wild-do-table=dbevents.tbl_etwork_onnection
replicate-wild-do-table=dbevents.tbl_ress
replicate-wild-do-table=dbevents.tbl_chedule
replicate-wild-do-table=dbevents.tbl_cheduled_avity
replicate-wild-do-table=dbevents.tbl_erial_onnection
replicate-wild-do-table=dbevents.tbl_hift
replicate-wild-do-table=dbevents.tbl_hift_nstance
replicate-wild-do-table=dbevents.tbl_tandard_ack
replicate-wild-do-table=dbevents.tbl_cp
replicate-wild-do-table=dbevents.tbl_dp
replicate-wild-do-table=dbPRT.tbl_ob
replicate-wild-do-table=dbPRT.tbl_ool
replicate-wild-do-table=dbPDTS.tbl_nventory
replicate-wild-do-table=dbPDTS.tbl_ob_nventory__
replicate-wild-do-table=dbPDTS.tbl_nventory_evision
replicate-wild-do-table=dbPDTS.tbl_nventory_evision_sed_y
replicate-wild-do-table=dbPDTS.tbl_avity
replicate-wild-do-table=dbSupplierPerformance.tbl_iscrepancy_eason
replicate-wild-do-table=dbSupplierPerformance.tbl_iscrepancy_eason_ategory
replicate-wild-do-table=dbevents.tbl_art_aterial__
replicate-wild-do-table=dbevents.tbl_upervisor_ignoff
replicate-wild-do-table=dbevents.tbl_aterial_ot_racking
replicate-wild-do-table=dbGlbUsr.tbl_ser
replicate-wild-do-table=dbGlbUsr.tbl_ser_es
replicate-wild-do-table=dbGlbUsr.tbl_un_es
my-PlantAFederated.cnf
[client]
port = 5307
socket = /var/run/mysql_PlantA/mysql.sock
[mysqld]
datadir = /usr/local/PlantA_mysql/data
port = 5307
socket = /var/run/mysql_PlantA/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
skip-innodb
slave_skip-errors=1159
log-warnings
federated
log-bin=PlantA_mysql-bin
replicate-wild-do-table=dbevents.tbl_ycle
replicate-wild-do-table=dbevents.tbl_evice_vent
replicate-wild-do-table=dbevents.tbl_vent
replicate-wild-do-table=dbevents.tbl_eak_ester_ycle
replicate-wild-do-table=dbevents.tbl_athfinder6000_ycle
replicate-wild-do-table=dbevents.tbl_roduction
replicate-wild-do-table=dbevents.tbl_ress
replicate-wild-do-table=dbevents.tbl_chedule
replicate-wild-do-table=dbevents.tbl_cheduled_avity
replicate-wild-do-table=dbevents.tbl_crap_art
replicate-wild-do-table=dbevents.tbl_____ycle
replicate-wild-do-table=dbevents.tbl_roduction_ours
replicate-wild-do-table=dbevents.tbl_upervisor_ignoff
replicate-wild-do-table=dbevents.tbl_mployee_ignoff
replicate-wild-do-table=dbevents.tbl_aterial_ot_racking
binlog_format=mixed
server-id = 7
innodb_data_home_dir = /usr/local/PlantA_mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/PlantA_mysql/data/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
