MySQL database replicationPosted on December 29, 2016 (Last modified on November 13, 2023) • 5 min read • 1,049 words
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:
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
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
Edit the address for the corresponding address from:
bind-address = 127.0.0.1
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:
Make sure that the settings are under
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
After saving the file (:wq in vi) you need to restart MySQL, with
sudo service mysql restart on Debian/Ubuntu for example.
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
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.
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
! 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.
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.
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
As with the master MySQL server we need to change the configuration for the slave database server in
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.
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
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)
SHOW SLAVE STATUSG; shows a lot of information about the replication process.
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.
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.
This statement skips the next 1 events from the master.
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
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.