Home Page
  • March 28, 2024, 02:29:29 pm *
  • Welcome, Guest
Please login or register.

Login with username, password and session length
Advanced search  

News:

Official site launch very soon, hurrah!


Author Topic: My MySQL Replication Ring Configuration  (Read 8606 times)

Dakusan

  • Programmer Person
  • Administrator
  • Hero Member
  • *****
  • Posts: 535
    • View Profile
    • Dakusan's Domain
My MySQL Replication Ring Configuration
« on: July 29, 2013, 10:35:16 am »


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.
Anywho, the following is my basic MySQL configurations for setting up a replication ring, which needs to be put on every server node in the ring: (See MySQL docs for more information on each configuration)

[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
Logged