Posted by: Dakusan
« on: July 29, 2013, 10:35:16 am »Original post for My MySQL Replication Ring Configuration can be found at https://www.castledragmire.com/Posts/My_MySQL_Replication_Ring_Configuration.
Originally posted on: 07/29/13
Originally posted on: 07/29/13
As of 2010, if you wanted to set up a MySQL replication configuration with multiple servers which could all update and send the updates to the other servers, a replication ring was the only solution (in which every server has a master and a slave in a ring configuration). While there are new (probably better) solutions as of late including using MariaDB’s multi-source replication, and tungsten-replicator (which I was referred to in late April and have not yet tried), I still find a replication ring to be an easy to use solution in some circumstances. However, there are some major disadvantages including:
- If one node in the ring goes down, the entire ring stops replicating at that point until the node is brought back up
- If a node goes down in the ring and has corrupted or incomplete data, say, from a power outdate, the entire ring may have to be painstakingly synced and rebuilt.
[mysqld]
#---GENERAL REPLICATION VARIABLES--- (These should never change)
log_bin=mysql-bin.log #Turn on the binary log, which is used to hold queries that are propagated to other machines
slave-skip-errors = 1062 #Do not stop replication if a duplicate key is found (which shouldn’t happen anyways). You may want to turn this off to verify integrity, but then your replication ring will stop if a duplicate key is found
#master-connect-retry = 5 #How often to keep trying to reconnect to the master node of the current machine after a connection is lost. This has been removed as of MySQL 5.5, and needs to instead be included in the “CHANGE MASTER” command
sync_binlog = 100 #After how many queries, sync to the binlog
slave_net_timeout = 120 #The number of seconds to wait for more data from a master/slave before restarting the connection
max_binlog_size = 1000M #The maximum size the binlog can get before creating a new binlog
log-slave-updates #Log slave updates to the binary log. If there are only 2 nodes in the ring, this is not required
slave_exec_mode = IDEMPOTENT #Suppression of duplicate-key and no-key-found errors
replicate-same-server-id = 0 #Skip running SQL commands received via replication that were generated by the current server node
#---INDEPENDENT REPLICATION VARIABLES--- (These should change per setup)
binlog-do-db = DATABASE_NAME #Only add statements from this database to the binlog. You can have multiple of these configurations
replicate-do-db = DATABASE_NAME #Only read statements from this database during replication. You can have multiple of these configurations
auto_increment_increment = 2 #After ever auto-increment, add this to its number. Set this to the number of nodes. This helps assures no duplicate IDs
#---SERVER CONFIGURATION--- (These numbers should match)
server-id = 1 #The current node number in the ring. Every node needs to have its own incremental server number starting at 1
auto_increment_offset = 1 #What to start auto-increment numbers at for this server. Set this to the server-id. This helps assures no duplicate IDs
