MySQL 5.6 Master-Master replication setup and configuration on CentOS 6

We’ll be using two servers. You can add as many as you like. Low latency is preferable (<50ms) if important changes are being made on both servers at the same time. Please note that the “master-master” expression translates somewhere between the lines like so: each server is a slave for another, accepting and operating changes (slave behaviour) and at the same time instructing the next server to operate the same changes (master behaviour) until full synchronization is achieved.

changes on Server1 –> send them to be executed on –> Server2 –> send them to be executed on –> Server1

If the synchronization is full (same for all servers), the cycle stops. Otherwise, the data will again be passed between the servers.

Onto installation:

  1. Get the official repository and install it, along with the server:
wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm ; rpm -Uvh mysql-community-release-el6-5.noarch.rpm ; yum update ; yum install mysql mysql-client mysql-server
  1. # mysql_secure_installation

  2. On server1:
    mysql > CREATE USER 'username'@'ip-of-server2' IDENTIFIED BY 'password';
    mysql > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username'@'ip-of-server2' IDENTIFIED BY 'password';
    mysql > RESET SLAVE;
    mysql > CHANGE MASTER TO MASTER_HOST='ip-of-server2', MASTER_USER='username_for_server2', MASTER_PASSWORD='password_for_server2', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;

  3. On server2:
    mysql > CREATE USER 'username'@'ip-of-server1' IDENTIFIED BY 'password';
    mysql > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username'@'ip-of-server1' IDENTIFIED BY 'password';
    mysql > RESET SLAVE;
    mysql > CHANGE MASTER TO MASTER_HOST='ip-of-server1', MASTER_USER='username_for_server1', MASTER_PASSWORD='password_for_server1', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;

  4. service mysqld stop

  5. Centralize logging into /var/log/mysql
    # mkdir /var/log/mysql ; cd /var/log/mysql; touch bin.log error.log log-bin.index relay-log.index relay-log.info log-bin.index_crash_safe log-bin.~rec~ ; chown mysql:mysql . ; chown mysql:mysql *.log ; chown mysql:mysql *.index ; chown mysql:mysql *.info ; chown mysql:mysql *.~rec~ ; chown mysql:mysql *.index_crash_safe

  6. Append or overwrite and adjust as needed /etc/my.cnf :

# If you omit server-id the master refuses any connections from slaves.
# CHANGE ID! MUST BE UNIQUE TO EACH SERVER!
server-id=1

# Prevent key collisions
# CHANGE OFFSET! MUST BE UNIQUE TO EACH SERVER!
auto-increment-offset = 1
auto-increment-increment = 4

# Binary logging must be enabled on the master because the binary
# log is the basis for replicating changes from the master to its slaves.
# If binary logging is not enabled using the log-bin option, replication is not possible.
log-bin=mysql-bin
binlog_format=row
binlog_do_db=include_database_name

# For the greatest possible durability and consistency
# in a replication setup using InnoDB with transactions
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# Listen!
bind-address = 0.0.0.0

#######################
### LOG MANAGEMENT ####
#######################
# mkdir /var/log/mysql ; cd /var/log/mysql; 
# touch bin.log error.log log-bin.index relay-log.index relay-log.info log-bin.index_crash_safe log-bin.~rec~
# chown mysql:mysql . ; chown mysql:mysql *.log ; chown mysql:mysql *.index ; chown mysql:mysql *.info ; chown mysql:mysql *.~rec~ ; chown mysql:mysql *.index_crash_safe
#log-bin = /var/log/mysql/bin.log
log-slave-updates
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 10
max_binlog_size = 500M

#######################
##### CREDENTIALS #####
#######################
# Uncomment after running the MASTER SETUP
#master_host = [private IP address of second server]
#master_user = [replication username]
#master_password = [replication password]
#master_connect-retry = 60
#MASTER_SSL = {0|1}
#MASTER_SSL_CA = 'ca_file_name'
#MASTER_SSL_CAPATH = 'ca_directory_name'
#MASTER_SSL_CERT = 'cert_file_name'
#MASTER_SSL_KEY = 'key_file_name'
#MASTER_SSL_CIPHER = 'cipher_list'
#MASTER_SSL_VERIFY_SERVER_CERT = {0|1}

Don’t forget to service mysqld restart.

References:
• http://scale-out-blog.blogspot.com/2012/04/if-you-must-deploy-multi-master.html
• http://www.rackspace.com/knowledge_center/article/mysql-master-master-replication
• https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication
• http://dev.mysql.com/doc/refman/5.6/en/replication-howto.html
• http://www.lefred.be/node/45
• http://myoracleproduct.blogspot.com/2013/09/mysql-standby-creation-master-slave.html
• http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch
Advertisements

Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s