Previous Page

nihilist - 00 / 00 / 00

MySQL Database Master-Slave Replication

In this tutorial we will setup a Master-Slave MySQL database replication between 2 debian hosts ( being the Master, and being the Slave)

Initial Setup:

First let's install mariadb-server:

root@debian-nginx1:~# apt update -y ; apt upgrade -y ; apt install mariadb-server -y
root@debian-nginx2:~# apt update -y ; apt upgrade -y ; apt install mariadb-server -y

Then we run mysql_secure_installation on both of our debian servers:

Configuring the Master and Slave Nodes

First we will configure our MASTER node, so right now we are on 'debian1' at Now since we installed mariadb, we need to edit the following config file:

root@debian-nginx1:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf


bind-address            = #not


server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log


Here we setup the server id as 1 and the bind address as its local ip. Now we save with :wq and restart the mysql service:

Next we create the user 'repl' with its password 'slavepassword', which will be the user the slave node will use:

Now we create a database with a table and an entry:

Now before we start the replication, we will need to first have the same database on the slave node, so we use mysqldump to create a .sql file of the current databases:

root@debian-nginx1:~# mysqldump -uroot --all-databases --master-data > masterdump.sql
root@debian-nginx1:~# cat masterdump.sql | grep Pierre ; cat masterdump.sql | grep bts_sio
INSERT INTO `sisr` VALUES ('Pierre');
-- Current Database: `bts_sio`
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bts_sio` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `bts_sio`;
INSERT INTO `innodb_index_stats` VALUES ('bts_sio','sisr','GEN_CLUST_INDEX','2021-03-20 10:24:28','n_diff_pfx01',0,1,'DB_ROW_ID'),('bts_sio','sisr','GEN_CLUST_INDEX','2021-03-20 10:24:28','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('bts_sio','sisr','GEN_CLUST_INDEX','2021-03-20 10:24:28','size',1,NULL,'Number of pages in the index'),('mysql','gtid_slave_pos','PRIMARY','2021-03-18 15:04:41','n_diff_pfx01',0,1,'domain_id'),('mysql','gtid_slave_pos','PRIMARY','2021-03-18 15:04:41','n_diff_pfx02',0,1,'domain_id,sub_id'),('mysql','gtid_slave_pos','PRIMARY','2021-03-18 15:04:41','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('mysql','gtid_slave_pos','PRIMARY','2021-03-18 15:04:41','size',1,NULL,'Number of pages in the index');
INSERT INTO `innodb_table_stats` VALUES ('bts_sio','sisr','2021-03-20 10:24:28',0,1,0),('mysql','gtid_slave_pos','2021-03-18 15:04:41',0,1,0);

Here we see that our sql file contains what we need to recreate our database and it's contents. So let's move it to the slave node (

root@debian-nginx1:~# sha512sum masterdump.sql
7a762985db9ba8ca620fdc86b3f4628b6cb9b69b919f1024ba4338f261138769414eb6926721c1387bb4ac34f8353e24839d49debf785777670e9e2ec7f18897  masterdump.sql

root@debian-nginx2:~# sha512sum masterdump.sql
7a762985db9ba8ca620fdc86b3f4628b6cb9b69b919f1024ba4338f261138769414eb6926721c1387bb4ac34f8353e24839d49debf785777670e9e2ec7f18897  masterdump.sql

The 2 files have the same hash, so they are the same after the transfer, so we can now configure the slave node properly:

root@debian-nginx2:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf


bind-address            = #not


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


Then we restart mysql:

Now we configure the slave mysql database to communicate to the master database ( with the 'repl' user and the 'slavepassword' password:

Now that's done we will use our masterdump.sql file to reproduce the master database into our slave database:

root@debian-nginx2:~# ls -lash
total 504K
4.0K drwx------  2 root root 4.0K Mar 20 11:08 .
4.0K drwxr-xr-x 22 root root 4.0K Mar 18 14:39 ..
4.0K -rw-------  1 root root  229 Dec  2 10:26 .bash_history
4.0K -rw-r--r--  1 root root  570 Jan 31  2010 .bashrc
4.0K -rw-------  1 root root 1.9K Mar 20 11:08 .mysql_history
4.0K -rw-r--r--  1 root root  148 Aug 17  2015 .profile
8.0K -rw-------  1 root root 4.1K Mar 20 10:38 .viminfo
472K -rw-r--r--  1 root root 469K Mar 20 10:29 masterdump.sql
root@debian-nginx2:~# mysql -uroot < masterdump.sql

Testing the Replication

Now let's start the slave connection and verify if it is working:

root@debian-nginx2:~# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 47
Server version: 10.3.27-MariaDB-0+deb10u1-log Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 1557
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 733
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
                    Last_Errno: 0
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 1557
               Relay_Log_Space: 1043
               Until_Condition: None
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                Last_SQL_Errno: 0
              Master_Server_Id: 1
                    Using_Gtid: No
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 1
1 row in set (0.001 sec)

ERROR: No query specified

And here we see that the Master-Slave connection is being made, now to verify that we simply need to create changes on the master database, to see the changes on the Slave database:

And that's it! When the Master database gets modified, the slave database gets almost immediately changed aswell. which means that our Master-Slave replication is complete.


Until there is Nothing left.

About nihilist

Donate XMR: 8AUYjhQeG3D5aodJDtqG499N5jXXM71gYKD8LgSsFB9BUV1o7muLv3DXHoydRTK4SZaaUBq4EAUqpZHLrX2VZLH71Jrd9k8

Contact: (PGP)