MySQL database replication

For our home I wanted to create a good and stable DNS infrastructure. I could’ve gone with the default router-based DNS service, but I wanted something different.

I had a couple of wishes and demands for my DNS infrastructure:

• It has to be fast and reliable
• It should be something different than BIND
• Editing via web interface and command-line should be possible

The solution I chose in the end was a PowerDNS server on a virtual machine and the same on a physical machine. Data is stored in a MySQL database on both machines and replicated between each other.

This way both machines have the same information, and if one crashes it is not a problem.

#How to configure MySQL database replication
I’m assuming that you already installed MySQL on your servers. If you didn’t, there are plenty of guides on how to install MySQL

For the example I use the following names and IP addresses

Step 1 – Prepare MySQL master server  

The first step is making sure that your server can be reached from the network and is ready to configure for replication. To do this you need to edit the MySQL configuration file. (If you can’t get used to vi, you can also use nano instead of vi). I’m assuming that you don’t do this actions under root, that’s why every command is proceeded by ‘sudo’.

sudo vi /etc/mysql/my.cnf

Server identity  

Edit the address for the corresponding address from:

bind-address = 127.0.0.1

to

bind-address = 192.168.1.10

Also change the server ID:

server-id = 1

If these settings are not present, you can also put them in a separate file:

etcmysqlconf.daddress.conf

Make sure that the settings are under [mysqld]

Logfiles  

For replication you need a binary logfile which contains all changes to the database.

log_bin = /var/log/mysql/mysql-bin.log

And we tell MySQL which database is being replicated
binlog_do_db = pdns

Restart MySQL  

After saving the file (:wq in vi) you need to restart MySQL, with sudo service mysql restart on Debian/Ubuntu for example.

Step 2 – Configuring for replication  

For the configuration I use the following for the walkthrough:

The configuration for replication itself happens in the MySQL command-line tool:

mysql -uroot -p

Granting privileges  

The first part is to grant privileges to the slave MySQL server.

GRANT REPLICATION SLAVE ON *.* TO 'remote_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Note that this replication user has access to all databases. If you don’t want that, just grant on pdns.* instead.

Configuring replication  

Use the database you want to replicate and prevent changes:

USE PDNS;
FLUSH TABLES WITH READ LOCK;

Before you go any further, check if everything is configured correctly for the database:

SHOW MASTER STATUS;

You will see a table that should look something like this:

Remember these settings, you’ll need them later.

Now dump the database to file:

! mysqldump -u root -p --opt pdns > pdns.sql

The ! allows you to execute a shell command. If your MySQL doesn’t supoort it open another window and executed the mysqldump from command line, since exiting the mysql command line utility unlocks the databases.

After you exported the database resume normal operations with

UNLOCK TABLES;
QUIT;

Now the master database is configured for replication.

Step 3 – Configuring the slave database server  

First, copy the pdns.sql file to the slave database server, for example with scp:

scp pdns.sql user@db02:

where user is a user that has access to server db02.

All other actions are performed on the slave server.

Create the database  

For the replication to work we need a database that can contain the replication.
Create the database in the mysql command line utility:

create database pdns

and import the data

mysql -uroot -p pdns < pdns.sql

Configure MySQL  

As with the master MySQL server we need to change the configuration for the slave database server in my.cnf

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog_do_db = pdns

After saving the settings restart the mysql server.

Step 4 – Enabling replication  

Now that both the master and slave are configured it is time to enable the replication.
We’re going to mark the current server as slave of master 192.168.1.10, we’re providing login credentials remote_user and we’re telling MySQL where to start with the replication.

CHANGE MASTER TO MASTER_HOST='192.168.1.10',MASTER_USER='remote_user',
MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=  107; 

Since I configured the database for DNS I didn’t find it fitting to try hostnames.

Next, start the replication with START SLAVE;

Verify if everything is working alright

SHOW SLAVE STATUSG;

This will give you something like:

************************** 1. row ***************************
   	            Slave_IO_State: Waiting for master to send event
   	               Master_Host: 192.168.1.10
   	               Master_User: remote_user
              Master_Port: 3306
   	             Connect_Retry: 60
   	           Master_Log_File: mysql-bin.000001
   	       Read_Master_Log_Pos: 107
   	            Relay_Log_File: mysql-relay-bin.000001
   	             Relay_Log_Pos: 266
   	     Relay_Master_Log_File: mysql-bin.000001
   	          Slave_IO_Running: Yes
   	         Slave_SQL_Running: Yes
...

(only the first part is shown)

Troubleshooting  

The command SHOW SLAVE STATUSG; shows a lot of information about the replication process.

Passwords  

Certain passwords fail when configuring replication. One of my passwords contained a hashtag (‘#’). I couldn’t get the replication up and running until I removed the hashtag from the password.

Firewall  

If SHOW SLAVE STATUSG; shows ‘connecting’ there’s a good chance that a firewall is blocking the connection.

Enable the ports for MySQL (3306 on udp/tcp) in your firewall.
For ufw you can create an application profile in /etc/ufw/applications.d

[MySQL]
title=MySQL
description=MySQL - database server
ports=3306

Load the profile

ufw app update MySQL

and enable the profile

ufw allow MySQL

Verify it with ‘ufw status’. Your firewall should now be showing something like:

To                         Action      From
--                         ------      ----
Nginx Full                 ALLOW       Anywhere
OpenSSH                    ALLOW       Anywhere
PowerDNS                   ALLOW       Anywhere
MySQL                      ALLOW       Anywhere
Nginx Full (v6)            ALLOW       Anywhere (v6)
OpenSSH (v6)               ALLOW       Anywhere (v6)
PowerDNS (v6)              ALLOW       Anywhere (v6)
MySQL (v6)                 ALLOW       Anywhere (v6)

For other firewalls check their respective manuals.

Replication fails  

This statement skips the next 1 events from the master.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
SLAVE START; 

Closing  

As you see setting up replication isn’t that hard, but you have to make sure that you follow all steps. From now on you can have your database replicated to other servers without configuring complex clusters.