Live Database Migration from MMM to MariaDB Galera Cluster

This is a guest post from Igor Shevtsov, a MySQL DBA based in Twickenham, UK. Igor has had the opportunity to deploy and manage different types of high availability MySQL solutions, and we invited him to share his experience. Igor can be contacted by email or LinkedIn.

Migrating from MMM to MariaDB Galera Multi-Master Replication

Agenda:

• MySQL HA with MMM
• Why MMM did not work for us
• Migration requirements
• Migration

MySQL HA with MMM

Firstly I would like to say few words about our original MMM (Multi Master Management) MySQL setup and some reasons why we decided to start looking for another MySQL HA solution.

We had 2 Data Centres with Active master1 in DC1 and passive master2 and Management node in DC2, both masters were running MariaDB 5.5 server. The clients were unevenly distributed across both DC’s. DC’s were connected by a VPN that wasn’t 100% reliable and that occasionally failed.

The simplicity of MMM setup and management as well as speed of failover let us to believe that it was a good choice for MySQL HA. As it usually happens, we did not have much time to test it properly and we quickly realized its limitations after the VPN failed. And of course, it had to fail at night 🙁 

Why MMM Did Not Work for Us

The post-mortem:

• VPN failure caused management node to assign VIP to Master2 and promoted Master2 to active master
• At this point, we have two masters having the same VIP. Each master is serving requests from clients that are in the same DC.
• After the VPN was re-established, the management node brought Master1 back into configuration and set it up into a READ ONLY mode but failed to drop network interface with VIP.
• At this point, a number of clients returned and error “Failed to write to mysql read-only” and we were down at this point with an inconsistent database and a lot to think about.  

Thanks to Robert Hodges for very well explained behaviour of VIP. 

So, we needed a MySQL HA solution with:

• no single point of failure
• ability to read/write to any node
• simple management
• good performance via WAN
• NO data integrity issues
• automatic node provisioning
• no need for manual failover or VIP needed

As you see the requirements are quite bold. After a bit of research we decided to move to a Galera Cluster solution as it looked like Galera ticked all the boxes plus it works with MariaDB which would simplify the migration process.

Migration Requirements

Taking to consideration some Galera limitations, the dataset need to meet some requirements:

• All tables must be InnoDB
• All tables must have Primary Key
• binlog format must be ROW

Thanks to Giuseppe Maxia for a nice check query that helps to identify tables with missing PK as well as tables with FULLTEXT or SPATIAL functionality:

SELECT DISTINCT
       CONCAT(t.table_schema,'.',t.table_name) as tbl,
       t.engine,
       IF(ISNULL(c.constraint_name),'NOPK','') AS nopk,
       IF(s.index_type = 'FULLTEXT','FULLTEXT','') as ftidx,
       IF(s.index_type = 'SPATIAL','SPATIAL','') as gisidx
  FROM information_schema.tables AS t
  LEFT JOIN information_schema.key_column_usage AS c
    ON (t.table_schema = c.constraint_schema AND t.table_name = c.table_name
        AND c.constraint_name = 'PRIMARY')
  LEFT JOIN information_schema.statistics AS s
    ON (t.table_schema = s.table_schema AND t.table_name = s.table_name
        AND s.index_type IN ('FULLTEXT','SPATIAL'))
  WHERE t.table_schema NOT IN ('information_schema','performance_schema','mysql')
    AND t.table_type = 'BASE TABLE'
    AND (t.engine <> 'InnoDB' OR c.constraint_name IS NULL OR s.index_type IN ('FULLTEXT','SPATIAL'))
  ORDER BY t.table_schema,t.table_name

Once identified Primary Key could be added as follows:

ALTER TABLE t ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST, ENGINE = InnoDB

To change binlog format to ROW:

Master2 (Passive Master):

STOP SLAVE

Master1 (Active Master):

FLUSH TABLES WITH READ LOCK
FLUSH LOGS
SET GLOBAL binlog_format = 'ROW'
FLUSH LOGS
UNLOCK TABLES

Master2:

START SLAVE
SET GLOBAL binlog_format = 'ROW'

Edit /etc/my.cnf:

binlog_format = ROW

At this point, we are ready to start the migration process.

Migration

Migration steps:

1. Disable MMM
2. Upgrade Master2 to Galera node1
3. Add Galera node2
4. Failover clients to Galera cluster
5. Upgrade Master1 to Galera node3

Disable MMM

Here, we need to stop MMM management demon and MMM agents.

On the Management node:

$ /etc/init.d/mysql-mmm-monitor stop

On Master1 and Master2:

$ /etc/init.d/mysql-mmm-agent stop

Upgrade Master2 to first Galera node (Galera1)

On Master2:

1. Stop Replication:

mariadb> STOP SLAVE

2. Remove existent MariaDB server with YUM:

$ sudo yum remove MariaDB-server MariaDB-client

3. Install Galera Cluster with YUM:

$ sudo yum install MariaDB-Galera-server MariaDB-client galera

4. Edit /etc/my.cnf

REMOVE Master-Master entries:

auto_increment_offset = 2
auto_increment_increment = 2
sync_binlog = 1
read_only = 1

Add Galera entries:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
# wsrep_cluster_address=gcomm://   # disable this for now!!!
wsrep_node_name=node1
wsrep_provider_options="gcache.size=1G;gcs.fc_limit=256;evs.keepalive_period = PT1S; evs.inactive_check_period = PT3S; evs.suspect_timeout = PT10S; evs.inactive_timeout = PT30S; evs.install_timeout = PT30S"
wsrep_drupal_282555_workaround
wsrep_sst_method=rsync

Make sure rsync is installed and iptables open for ports 4567, 4568, 4444.

For MySQL asynchronous replication to work properly and propagate across all Galera nodes, log_slave_updates, binary logging need to be enabled and server_id must be the same across all Galera nodes:

[mysqld]
default_storage_engine         = InnoDB
innodb_file_per_table
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite             = 0
log_slave_updates              = 1
log_bin                        = bin-log
server_id                      = 1111
binlog_format                  = ROW

5. Restart Galera server:

$ service mysql start --wsrep_cluster_address=gcomm://

6. Start replication on Galera node1:

node1> START SLAVE
node1> SHOW SLAVE STATUSG

At this point, all Clients are connected to Master1 and Master2/Galera1 is using asynchronous MySQL replication to replicate data from Master1.

Add 2nd Galera node (Galera2)

In order to bring Galera2 online, we need:

1. Install Galera Cluster with YUM.
2. Edit /etc/my.cnf

   wsrep_cluster_address=gcomm://192.168.200.2,192.168.200.3