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

How to create a Master-Master MariaDB Galera 10.0 (stable) cluster on CentOS 6

We’ll be using CentOS 6 updated with epel and remi repositories. As for MySQL server, we’ll be using MariaDB 10.0 (stable), which is a fine tuned drop-in replacement for Oracle’s MySQL 5.6.

  1. Install MySQL server
    [add MariaDB repo; yum install MariaDB-Galera-server MariaDB-client galera holland-xtrabackup]

  2. mysql_secure_installation
    and PLEASE choose a password different from your VPS credentials

  3. Since you open your server to the world, it is understandable if paranoia kicks in. You can furthermore try to secure it by changing the root user to something else and iptable/fail2ban/csf the crap of that 3306 & 4567 port, allowing only few IPs to actually knock and enter.

  4. find / -name libgalera_smm.so

  5. Edit server.cnf: vim /etc/my.cnf.d/server.cnf:

 [mysqld]
# Mandatory settings: these settings are REQUIRED for proper cluster operation
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
#innodb_doublewrite=1 - this is the default and it should stay this way
 
# Optional mysqld settings: your regular InnoDB tuning
#datadir=/mnt/mysql/data
#innodb_buffer_pool_size=28G
#innodb_log_file_size=100M
#innodb_file_per_table
innodb_flush_log_at_trx_commit=2
 
# Provider configuration
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"
wsrep_cluster_address=gcomm://first_ip,second_ip,third_ip
wsrep_cluster_name='root_cluster'
# THIS NODE INFO
wsrep_node_address='this_node_ip'
wsrep_node_name='hostname'
# xtrabackup or rsync
wsrep_sst_method=xtrabackup
wsrep_sst_auth=root:password
 
# OTHER NODE(S) INFO
wsrep_node_incoming_address='ip_address'
wsrep_sst_donor='hostname'
wsrep_slave_threads=16
## HINT. Where to check for errors: /var/lib/mysql/_declared_hostname_.err
## IMPORTANT. Start the first server with: service mysql start --wsrep-new-cluster
# For others, just do it normally: service mysql start
## HELP! SERVER DOWN. Use the last standing server to restart the cluster to preserve
# proper data replication: mysqld --wsrep_cluster_address=gcomm:// --user=mysql
  1. Check /var/lib/mysql/<em>_declared_hostname_</em>.err for any errors. Execute a random CREATE, INSERT, UPDATE and monitor for changes.
References:
• http://matthewcasperson.blogspot.com/2013/07/setting-up-galera-cluster-in-centos-6.html
• http://jmoses.co/2014/03/18/setting-up-a-mysql-cluster-with-mariadb-galera.html
• http://corp.icoa.com/mysql-master-to-master-percona-galera-cluster-in-centos-6-2014/

MySQL 5.6 Master-Master replication on CentOS 6

In our pursue of 100% uptime and trying to chase away the “single point of failure” demon, we’re going to propose a master-master replication configuration of two MySQL servers located on two VPS on different datacenters.

We’ll be using CentOS 6 updated with epel and remi repositories. As for MySQL server, we’ll be using MariaDB 10.0 (stable), which is a fine tuned drop-in replacement for Oracle’s MySQL 5.6.

  1. Install MySQL server
    [add MariaDB repo; yum install MariaDB-Galera-server MariaDB-client galera holland-xtrabackup]

  2. mysql_secure_installation
    and PLEASE choose a password different from your VPS credentials

  3. Since you open your server to the world, it is understandable if paranoia kicks in. You can furthermore try to secure it by changing the root user to something else and iptable/fail2ban/csf the crap of that 3306 & 4567 port, allowing only few IPs to actually knock and enter.

  4. find / -name libgalera_smm.so

  5. Edit server.cnf: vim /etc/my.cnf.d/server.cnf:

 [mysqld]
# Mandatory settings: these settings are REQUIRED for proper cluster operation
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
#innodb_doublewrite=1 - this is the default and it should stay this way
 
# Optional mysqld settings: your regular InnoDB tuning
#datadir=/mnt/mysql/data
#innodb_buffer_pool_size=28G
#innodb_log_file_size=100M
#innodb_file_per_table
innodb_flush_log_at_trx_commit=2
 
# Provider configuration
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"
wsrep_cluster_address=gcomm://first_ip,second_ip,third_ip
wsrep_cluster_name='root_cluster'
# THIS NODE INFO
wsrep_node_address='this_node_ip'
wsrep_node_name='hostname'
# xtrabackup or rsync
wsrep_sst_method=xtrabackup
wsrep_sst_auth=root:password
 
# OTHER NODE(S) INFO
wsrep_node_incoming_address='ip_address'
wsrep_sst_donor='hostname'
wsrep_slave_threads=16
## HINT. Where to check for errors: /var/lib/mysql/_declared_hostname_.err
## IMPORTANT. Start the first server with: service mysql start --wsrep-new-cluster
# For others, just do it normally: service mysql start
## HELP! SERVER DOWN. Use the last standing server to restart the cluster to preserve
# proper data replication: mysqld --wsrep_cluster_address=gcomm:// --user=mysql
  1. Check /var/lib/mysql/<em>_declared_hostname_</em>.err for any errors. Execute a random CREATE, INSERT, UPDATE and monitor for changes.
References:
• http://matthewcasperson.blogspot.com/2013/07/setting-up-galera-cluster-in-centos-6.html
• http://jmoses.co/2014/03/18/setting-up-a-mysql-cluster-with-mariadb-galera.html
• http://corp.icoa.com/mysql-master-to-master-percona-galera-cluster-in-centos-6-2014/