MySQL Database Master-Slave Replication
In this tutorial we will setup a Master-Slave MySQL database replication between 2 debian hosts (192.168.0.150 being the Master, and 192.168.0.151 being the Slave)
In this tutorial we will setup a Master-Slave MySQL database replication between 2 debian hosts (192.168.0.150 being the Master, and 192.168.0.151 being the Slave)
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:
First we will configure our MASTER node, so right now we are on 'debian1' at 192.168.0.150. 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 = 192.168.0.150 #not 127.0.0.1
[...]
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 (192.168.0.151):
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 = 192.168.0.151 #not 127.0.0.1
[...]
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 (192.168.0.150) 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
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_Host: 192.168.0.150
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
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: 1557
Relay_Log_Space: 1043
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:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
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.
Some Address 67120,
Duttlenheim, France.
This cute theme was created to showcase your work in a simple way. Use it wisely.