MySQL Master -> Slave Replication

Flattr this!

This tutorial is about Master-Slave Replication for MySQL Databases. It should also work for MariaDB as well. Replication is useful if you want to have a LIVE backup system, or to scale out and use the Slave as a Read Only DB. It’s also useful when your database is too big to run mysqldump directly on the master and lock the DB at the same time.

Doing backups is still useful on the Master, if the Master drifts out of sync with the Slave silently. Better to be to have a backup than feeling sorry. The other approach is to run regularly checksums between Master and Slave. But that’s not part of this tutorial.

 

This Tutorials has been made using two Debian Jessie 8.3 Systems. MySQL  Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64)

The Replication is done unencrytped, and should be protected with ssh-tunneling, OpenVPN or MySQL over SSL. In my case i am using routed OpenVPN because it’s already running on the server. Otherwise Setting up MySQL SSL Encryption would be the way to go.
I want to replicate the databases tinytinyrss, baikal and wordpress to my Slave.

OpenVPN IPv4 Master: 10.8.0.1
OpenVPN IPv4 Slave: 10.8.0.17

Master Setup

Each Node participating in Replication needs a different server-id. If the server-id is kept at the default 0, its not possible to use it for replication as Master or Slave.
Binary log must be enabled at the Master. It contains all your transactions in binary format. This log is used for replication to the Slave.
So edit the MySQL config file /etc/mysql/my.cnf

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

Limit the Bin Log to specific DB’s which should be logged or ignored.

These are my settings, as i do not want phpmyadmin or the system databases to be replicated. If you use binlog_do_db you need to restart your mysql master everytime when you want to add/remove a database from your replication pool.

binlog_ignore_db        = information_schema
binlog_ignore_db        = mysql
binlog_ignore_db        = performance_schema
binlog_ignore_db        = phpmyadmin

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use in the master my.cnf file following settings:

innodb_flush_log_at_trx_commit=1
sync_binlog=1

By default MySQL is locked to localhost/127.0.0.1 and should be listening to 0.0.0.0 instead to be reachable via VPN or outside.

bind_address = 127.0.0.1

netstat shows the local address where it is listening and the port.

root@master:~# root@master:/etc/mysql# netstat -tulpn | grep mysqld 
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      739/mysqld

The setting needs to be changed to this:

bind_address = 0.0.0.0

After that, we need to restart mysql.

root@master:~# service mysql restart

And now we can see the change in netstat

root@master:~# netstat -tulpn | grep mysqld 
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      20791/mysqld

Please do a quick check on your database if your users are limited to localhost or if there are users allowed for “%”.

root@master:~# mysql -u root -p
Enter password: 

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | ::1       |
| baikal           | localhost |
| debian-sys-maint | localhost |
| phpmyadmin       | localhost |
| root             | localhost |
| tinytinyrss      | localhost |
| wordpress        | localhost |
+------------------+-----------+
11 rows in set (0.00 sec)

The last step on the Master Setup is to create an user for the Slave, which can be used to do the replication. Use the IPv4 address from your Slave, to limit the logins from that account to just your slave. Choose a secure long password with up to 35 characters.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.8.0.17' IDENTIFIED BY 'trustno1';

Good, now the master Setup is done.

Slave Setup

First Step is to install mysql-server

root@slave:~# apt-get install mysql-server

Then lets edit /etc/mysql/my.cnf
On each slave that you want to connect to the master, you must configure a unique server ID for each node in the replication pool.

server-id               = 2
read_only               = 1

When the read_only system variable is enabled, the server permits no client updates except from users who have the SUPER privilege. This variable is disabled by default.

Even with read_only enabled, the server permits updates performed by slave threads, if the server is a replication slave. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.

System variables that apply to slave replication servers:

;replicate-do-db
;replicate-ignore-db

My settings are as followed:

replicate-do-db = baikal
replicate-do-db = tinytinyrss
replicate-do-db = wordpress

replicate-ignore-db        = information_schema
replicate-ignore-db        = mysql
replicate-ignore-db        = performance_schema
replicate-ignore-db        = phpmyadmin

After that, we need to restart mysql.

root@slave:~# service mysql restart

Check if you can login to your master from your slave with the user you have prepared on your master.

root@slave:~# mysql -u replicator -h 10.8.0.1 -p
Enter password: 
Welcome to the MySQL monitor.

Slave setup is also finished.

Data Migration

The databases which are already available on master need to be transferred to the slave and replication needs to be enabled with the exact Master Log File and Master Log Position.
To achieve this, we make the Master Read-Only and write down the log file and position of the Bin Log while we dump the databases we want to replicate.

Lets lock the Master:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

Get the logfile and bin position from the master while he is locked.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+--------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                       |
+------------------+----------+--------------+--------------------------------------------------------+
| mysql-bin.000001 |    19766 |              | information_schema,mysql,performance_schema,phpmyadmin |
+------------------+----------+--------------+--------------------------------------------------------+
1 row in set (0.00 sec)

The important information is mysql-bin.000001 and 19766 which should be saved in a textpad.

Dump the databases

mysqldump -u root -p --databases baikal tinytinyrss wordpress  > /root/replicated.sql

For safety reasons check again the Master Status. It should be the same like before the dump. Otherwise something went wrong.

Unlock the master

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

If you check the Master Status now, it doesn’t matter anymore if it changes.

Now tar and scp the sql file to your slave

root@master:~# tar -czf replicated.sql.tgz replicated.sql
root@master:~# scp replicated.sql.tgz root@slave:/root/

Log on to your slave:

untar and import the databases to mysql

root@slave:~# tar xzf replicated.sql.tgz  
root@slave:~# cat replicated.sql | mysql -u root -p

Designate the Slave

mysql> CHANGE MASTER TO MASTER_HOST='10.8.0.17', MASTER_USER='replicator', 
MASTER_PASSWORD='trustno1', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=19766

It enslaves the current server to his master, provides login credentials and tells the slave where to start replicating from. The master log file and log position have been written down previously.

Start Replication on the Slave Server

mysql> START SLAVE;

Check for Status of Replication process.

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.8.0.1
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1252259
                          ...: ...

If the state is “Waiting for master to send event” all is fine.

You can now check if the Master_Log_File and Read_Master_Log_Pos are in Sync with the Master Status.

In the next tutorial i will cover how to add new databases to the Slave replication process without stopping and locking the master.

MySQL

Comments are closed.