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
Server | IP address | server-id | database |
---|---|---|---|
db01 | 192.168.1.10 | 1 | pdns |
db02 | 192.168.1.20 | 2 | pdns |
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:
Server | user | password |
---|---|---|
db01 | slave_user | password |
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 'slave_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:
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
---|---|---|---|
mysql-bin.000001 | 107 | pdns |
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 slave_user
and we’re telling MySQL where to start with the replication.
CHANGE MASTER TO MASTER_HOST='192.168.1.10',MASTER_USER='slave_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: slave_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.
Iedere dag een nieuwe hobby? Het lijkt wel alsof A.J. hobby’s een doel op zich heeft gemaakt. Niet te beroerd om een nieuwe hobby of gadget uit te proberen en er vervolgens enthousiast en onderbouwd over te vertellen.
Een aantal van de hobby’s die A.J. ondertussen heeft gehad of nog steeds heeft: fotograferen, filmen, video editen, lezen, schrijven, houtbewerken, (technisch) tekenen, skaten, hardlopen, dansen, barbecuen.