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:
If you want filter and replicate only certain databases to the Galera Cluster also add:
binlog-do-db=<name of db> binlog-do-db=<name of db2> #... 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
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
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.
$ 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
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