Replicating a master database using MariaDB 10 on Debian 8

Replicating databases creates redundancy which can protect against data loss, and permit optimized  performance for applications. This tutorial will cover the basics of replicating an existing MariaDB 10.0 master database to one or more slaves. In the following examples, the host operating system is Debian 8.

These instructions may be applied to other operating systems, but be aware that several commands and default file locations will be different. Specifically, you should substitute the pathnames of /etc/mysql/my.cnf, /var/lib/mysql, the default name and path of your binary logfile, and the commands for starting, stopping, and restarting mysqld according to your system's specifics.

1. Verify connectivity

Before proceeding, make sure the master and slave can reach each other on the network, and that each has a entry for the other in their respective /etc/hosts files. Each host should be able to ping the other, and you should be able to ssh from each to the other as a normal user.

2. Enable mysqld binary log on master

On the master host, check that binary logging is enabled. Invoking mysqld with the switches --verbose --help will display operating values for the MariaDB daemon. As root:

mysqld --verbose --help | grep log-bin

...
log-bin         (No default value)
...

The value of the entry log-bin defines the naming convention of the binary log files. On Debian, these files reside in /var/lib. If the value of log-bin is (No default value), you will need to enable logging by modifying the configuration file my.cnf. On Debian, my.cnf resides in the directory /etc/mysql.

Open /etc/mysql/my.cnf in a text editor and locate the [mysqld] group. If it doesn't exist, create it, and enter a line which simply reads log-bin.

[mysqld]
log-bin

Including this entry will enable binary logging when mysqld is restarted.

You may choose to set a value for log-bin, e.g. log-bin=filename, to define a custom name for the binary logfile. In this tutorial, we will not set a value, and the default log filenames will be used.

Restart mysqld:

service mysql restart

Verify that the change has taken effect:

mysqld --verbose --help | grep log-bin

...
log-bin         mysqld-bin
...

As shown here, the default binary log filename on Debian begins mysqld-bin, e.g. mysqld-bin.nnnnnn.

3. Grant rights to a replication user

It is best practice to have all replication tasks performed by a dedicated replication user. In these examples, we will name the user repluser and set this user's password to the string replpass.

Grant this user the global privileges SUPER, RELOAD, and REPLICATION SLAVE. These will allow the replication user to execute superuser commands, flush database caches, and fetch updates from the master server.

Enter the MariaDB client as database root:

mysql -u root -p

At the MariaDB prompt, enter the command:

GRANT SUPER, RELOAD, REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY 'replpass';

Here, the hostname wildcard '%' allows the replication user to connect from any host.

Verify that the rights have been granted:

SHOW GRANTS FOR 'repluser'\G;

4. Flush database caches and set tables to read-only

In preparation for making a snapshot of the databases, flush all tables and set them to READ LOCK. This should be done quickly, during off-peak hours or a system maintenance period.

On the master:

FLUSH TABLES WITH READ LOCK;

Now that the tables are locked, check the master status:

SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000005 |      995 |              |                  |
+-------------------+----------+--------------+------------------+

Your information will be different, but note down the values of File and Position. You will use this information in step 7.

Exit the MariaDB client:

\q

5. Snapshot databases for the slave host

Create an archive of the database or databases that exist on the master which you which to replicate. Each of these databases has its own directory in /var/lib/mysql. In this example, we will tar up a single database, residing at the path /var/lib/mysql/dbname.

This command archives a single database. If you are archiving additional databases, append their full pathnames to the command, e.g. /var/lib/mysql/dbname1 /var/lib/mysql/dbname2 ...

tar cjvf /home/[username]/mysql-master.tar.bz2 /var/lib/mysql/dbname

Now, as normal user username, transfer this file to a normal user account on the slave host:

rsync -avP mysql-master.tar.bz2 [username]@slavehost:~/.

or, using scp:

scp mysql-master.tar.bz2 [username]@slavehost:~/.

Then, SSH to the slave host:

ssh [username]@slavehost

As root, stop mysqld on the slave:

service mysql stop

...and extract the archive:

tar xjvf /home/[username]/mysql-master.tar.bz2 -C /.

6. Configure server IDs for master and slave

Modify /etc/mysql/my.cnf on the master, adding the entry server-id=n in the [mysqld] group, where n is a unique integer identifying the server. Commonly, n=1 for the master server, but n may be any unique integer in the range [1, 2^32-1]. We will set our master to server-id=1, and our slave to server-id=100.

(If my.cnf doesn't exist on the slave, create it. If it exists, search for an existing server-id entry, and un-comment/edit that line).

In /etc/mysql/my.cnf on the master host:

[mysqld]
server-id=1

In /etc/mysql/my.cnf on the slave host:

[mysqld]
server-id=100

7. Unlock tables and start/restart mysqld on master and slave

On the master server, in the MariaDB client as database root, unlock tables:

mysql -u root -p

UNLOCK TABLES;

\q

Restart mysqld on the master:

service mysql restart

And start it on the slave:

service mysql start

You can verify that the new server-id value has taken effect on each host. As root:

mysqld --verbose --help | grep server-id

8. Configure identity of master on slave

On the slave, configure the identity of the master server. Enter the MariaDB client:

mysql -u root -p

Execute the following command, replacing the values of MASTER_LOG_FILE and MASTER_LOG_POS with the binary log File and Position that you recorded in step 4, and the values of MASTER_HOST, MASTER_USER, and MASTER_PASSWORD with your own values.

CHANGE MASTER TO MASTER_HOST='masterhost', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysqld-bin.000005', MASTER_LOG_POS=995;

9. Activate slave

On the slave, in the MariaDB client as database root:

START SLAVE;

You can now check the status of the slave:

SHOW SLAVE STATUS \G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: masterhost
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000009
          Read_Master_Log_Pos: 1330
               Relay_Log_File: mysqld-relay-bin.000008
                Relay_Log_Pos: 1618
        Relay_Master_Log_File: mysqld-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1330
              Relay_Log_Space: 2204
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos:

If there are any errors in the replication processes, you will see them listed here.

10. Make changes on master, and verify replication on slave

You can verify that replication is occurring by creating a new database on the master, and seeing the changes on the slave.

mysql -u root -p

Create a new database:

CREATE DATABASE repltest;

USE repltest

Create a table and insert a value:

CREATE TABLE test (hello VARCHAR(10));

INSERT INTO test VALUES ('world');

\q

Now enter the MariaDB client on the slave:

mysql -u root -p

USE repltest

SELECT * FROM test;

+-------+
| hello |
+-------+
| world |
+-------+
1 row in set (0.00 sec)

11. Repeat process for additional slaves

You can repeat this process for each additional slave. Specifically, perform these steps:

11(a). On the master, in the MariaDB client as database root, flush and lock tables:

FLUSH TABLES WITH READ LOCK;

After locking, show master status:

SHOW MASTER STATUS;

Note down the File and Position values.

11(b). On the master, as root:

tar cjvf /home/[username]/mysql-master.tar.bz2 /var/lib/mysql/dbname

11(c). On the master, as normal user:

rsync -avP mysql-master.tar.bz2 [username]@slavehost2:~/.

11(d). On the slave, as root:

service mysql stop

tar xjvf /home/[username]/mysql-master.tar.bz2 -C /.

11(e). In /etc/mysql/my.cnf on the slave host, add or edit the server-id= line in the [mysqld] group, where the value of server-id is new and unique:

[mysqld]
server-id=200

11(f). On the master, in the MariaDB client as database root, unlock tables:

UNLOCK TABLES;

11(g). On the master, as root, restart mysqld:

service mysql restart

11(h). On the slave, as root, start mysqld:

service mysql start

11(i). On the slave, in the MariaDB client as database root, configure the master identity, and the binary log filename and position from step 10(a):

CHANGE MASTER TO MASTER_HOST='masterhost', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysqld-bin.nnnnnn’, MASTER_LOG_POS=n;

11(j). On the slave, in the MariaDB client as database root, activate replication:

START SLAVE;

12. Troubleshooting: Slave unable to connect to master

Check /var/mysql/my.cnf on the master for a bind-address entry. If bind-address is set to 127.0.0.1, the server will only accept connections from localhost. Comment out this line, or set the value to * to allow connections from all IPv4 and IPv6 addresses. If you modify my.cnf, don't forget to restart mysqld.

If connections are still not working, make sure that your server is allowing connections on port 3306. On the master, list the kernel firewall tables:

iptables -L

You can create an allowance for connections on port 3306 with the following command, substituting your network interface device name for eth0 as necessary:

iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT