Topic: Getting MariaDB replication working with an existing database
Date:  2015 DEC 23
MariaDB, the open-source, non-Oracle drop in replacement for MySQL, supports master->slave database replication. What this means is that a MariaDB server can have one or more slave servers that connect to it and receive live updates as queries are executed on the master. This is useful for live backups as well as running a high speed, low latency read-only reporting database on a local network. It seems like I need to look up some step in the replication set-up process every time I configure a new slave, and none of the existing documents out there match what I typically need to do.
There are a few requirements and limitations for MariaDB replication:
- Connection between master and slave requires the master listen on an address accessible to the slave
- A slave can only replicate one master
- Unique server IDs need to be assigned to the master and slave(s)
The master configuration should be secured with respect to allowing incoming connections. I currently use an existing OpenVPN link to replicate masters to local reporting servers. The MariaDB traffic can be encrypted with SSL/TLS if it must pass through the public Internet. If the master listens on a public-facing interface, it’s probably a good idea to restrict source IPs in your server’s firewall.
Add a unique server ID to the master’s configuration, usually through
my.cnf. If set to 0 (default if omitted from the configuration), replication will not work. This will require a restart of MariaDB.
Once you’ve configured a server ID, you’ll need to add a GRANT for replication:
Replace the address
22.214.171.124 with the address that the slave will be connecting from. The identifying password will be encrypted in MariaDB and recent versions of MySQL. You must grant replication on all master databases; however, you can restrict what’s actually replicated through the server’s configuration, as shown here.
While logged in to the master’s host, you can go ahead and prepare a database dump using
mysqldump. The following command will dump
masterdb with master position data, which will make configuring the slave simpler:
You’ll be prompted for the root MariaDB password (you weren’t going to type it on the command line and leave it in the shell history, right?!). GZIPping the data isn’t necessary if it’s very small or you’re transferring across a fast, unmetered link. Copy the database dump to the slave machine and proceed with slave configuration.
As with the master, configure the slave with a unique server ID and restart. The server IDs need to be unique among all servers involved in replication – if you’re replicating to multiple slaves, they each should have a unique ID. Connect as root to the MariaDB console and configure the slave:
Replace the address
126.96.36.199 with the address that the MariaDB master is listening on, and of course use the
MASTER_PASSWORD set when the master was configured. The slave now knows which master it will receive updates from, but the master is not yet running. The database dump from earlier can now be loaded:
You’ll again be prompted for the root MariaDB password. This snippet uses the
pv command, which is the pipe viewer. You can replace it with
cat if you don’t have it and don’t want to install it; however,
pv is basically
cat with a status bar and provides a general idea of how far along you are in the DB load. Generally a very useful utility to have.
If you’re on a non-GNU userland (Mac OS X, *BSD) you will probably need to use
gzcat instead of
The slave can now be started from the MariaDB console with
START SLAVE, and its status viewed with
SHOW SLAVE STATUS:
Notes on Users
It appears MariaDB replication requires that any users who execute some SQL commands to which the user is associated also exist on the slave server. This issue popped up when configuring replication for a Ruby on Rails – it appears that Rails migrations will cause an error if the Rails application connects as a user other than root if that user does not exist on the slave and have adequate GRANT permissions on the replicated database.