Replicate from a MySQL Server into a Galera Cluster

This article describes how to setup replication between a regular MySQL server and a Galera Cluster. The regular MySQL Server is the MASTER , and one of the Galera nodes will be the SLAVE:

In this example we have the following hosts:

• REGULAR MASTER: 10.0.1.10
• GALERA NODE #1 (SLAVE): 10.0.1.11

Setup the MASTER (10.0.1.10)

Edit the my.cnf file and make sure you have the following fields, in addition to what you already have:

[MYSQLD]
binlog-format=ROW
log-bin=binlog
expire-logs-days=7
server-id=1

If the MASTER is already a slave in an replication setup then also make sure you have:

log-slave-updates=1

If you want filter and replicate only certain databases to the Galera Cluster also add:

binlog-do-db=
binlog-do-db=
#... etc for each database, also see

Restart the MASTER:

$ service mysql restart

GRANT the SLAVE access to the MASTER:

# ON 10.0.1.10 do:

$ mysql -uroot -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.1.11' IDENTIFIED BY 'repl';

Setup the SLAVE (10.0.1.11)

Edit the my.cnf file and make sure you have the following fields:

This can be done from “Manage -> Configuration” in the UI or from the command line.

[MYSQLD]
# log-slaves-updates=1 is necessary for the writes to propagate from the slave to the other galera nodes
log-slave-updates=1
log-bin=binlog
expire-logs-days=7
server-id=2

Stop the MySQL server and let ClusterControl start up the node and wait for it to become Synced.

From the UI, go to Nodes, select the SLAVE (in our case 10.0.1.11) and the Stop Node, and then Start Node.

From the command line do:

$ service mysql stop

and ClusterControl will restart it.

Tell the SLAVE where the MASTER is:

# ON 10.0.1.11 do:

mysql -uroot -p

mysql> CHANGE MASTER TO MASTER_HOST='10.0.1.11', MASTER_USER='repl', MASTER_PASSWORD='repl';

Dump the database on the MASTER

The databases from the master must be dumped out. This will be done in two steps, first dump the schema, and then the data.

Dump schema

$ mysqldump -uroot -p --no-data ---routines --triggers --databases db1 db2 dbN > schema.sql

where dbX above represents each database that you want to replicate.

Load in the schema on the Galera node:

Convert all tables to INNODB tables. If you have tables using FULLTEXT indexes, then those tables must for now (up to including MySQL 5.5) be MyISAM tables. 

If you don’t have any FULLTEXT indexes, then you can simply do this:

$ sed -i.bak 's#MyISAM#innodb#g' schema.sql

Then load in the schema on the Galera cluster:

$ mysql -uroot -p -h10.0.1.11  < schema.sql

Dump the data from the MASTER (create snapshot)

If you have any MYISAM tables on the MASTER you have to do (in order to get a consistent snapshot):

$ mysqldump -uroot -p --no-create-info --master-data=1 --databases db1 db2 dbN > data.sql

If you have only INNODB tables on the MASTER you can do:

$ mysqldump -uroot -p --no-create-info --master-data=1 --single-transaction --skip-add-locks --databases db1 db2 dbN > data.sql

Load in the data on the SLAVE and start the replication

$ mysql -uroot -p -h10.0.1.11 < data.sql

When the data has been loaded in, do:

$ mysql -uroot -p -h10.0.1.11

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS G

If you did all the steps above you will see:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.176.131.76
                  Master_User: repl