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.
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.
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.
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?
Here are the related MySQL configuration files:
[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
[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
[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