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/
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