YouTube Icon

Code Playground.

How to Configure MySQL (MariaDB) Master-Slave Replication on Debian 10

CFG

How to Configure MySQL (MariaDB) Master-Slave Replication on Debian 10

MySQL replication is a cycle of duplicating information from one data set worker (ace) to at least one workers (slaves). 

MySQL underpins a few replication geographies with Master/Slave geography being one of the most notable geographies in which one information base worker goes about as the expert, while at least one workers go about as slaves. Naturally, the replication is offbeat where the expert sends occasions that depict information base changes to its twofold log and slaves demand the occasions when they are prepared. 

In this guide, we will tell you the best way to set up MariaDB Master/Slave replication with one expert and one slave worker on Debian 10. MariaDB is the default execution of MySQL in Debian. Similar advances apply for Oracle MySQL. 

This sort of replication geography is most appropriate for conveying of read imitations for read scaling, live information bases reinforcement for calamity recuperation, and for investigation occupations. 

Prerequisites

We're accepting that you have two workers running Debian 10, speaking with one another over a private organization. On the off chance that your facilitating supplier doesn't uphold private IP addresses, you can utilize the public IP addresses and arrange your firewall to permit traffic on port 3306 just from confided in sources. 

The workers utilized in this model have the accompanying IP addresses: 

Master IP: 10.10.8.12
Slave IP:  10.10.8.164

Installing MariaDB

The default The Debian 10 stores incorporates MariaDB form 10.3. It is ideal to introduce a similar MariaDB adaptation on the two workers to dodge any possible issues. 

Introduce MariaDB on both the expert and the slave by giving the accompanying orders: 

sudo apt-get update
sudo apt-get install mariadb-server

Configuring the Master Server

The initial step is to set up the expert worker. We'll roll out the accompanying improvements: 

Set the MariaDB worker to tune in on the private IP . 

Set a one of a kind worker ID. 

Empower twofold logging. 

Open the MariaDB design record and uncomment or set the accompanying lines: 

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

master:/etc/mysql/mariadb.conf.d/50-server.cnf

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

When done, spare the document and restart the MySQL administration for changes to produce results: 

sudo systemctl restart mariadb

The subsequent stage is to make another replication client. Sign in to the MariaDB worker as the root client: 

sudo mysql

Run the accompanying SQL questions to make a client named reproduction and award the REPLICATION SLAVE advantage to the client: 

CREATE USER 'replica'@'10.10.8.164' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'10.10.8.164';

Ensure you change the IP with your slave IP address. You can name the client as you need. 

While still inside the MySQL brief, execute the accompanying order that will print the double filename and position. 

SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 328
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.001 sec)

Observe record name, 'mysql-bin.000001' and Position '328'. These qualities are important while arranging the slave worker and will presumably be distinctive on your worker. 

Configuring the Slave Server

We'll roll out similar improvements on the slave worker as those on the expert: 

Set the MySQL worker to tune in on the private IP. 

Set an extraordinary worker ID. 

Empower parallel logging. 

Open the MariaDB design record and alter the accompanying lines: 

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

slave:/etc/mysql/mariadb.conf.d/50-server.cnf

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

Restart the MariaDB administration: 

sudo systemctl restart mariadb

The following stage is to design the boundaries that the slave worker will use to interface with the expert worker. Login to the MariaDB shell: 

sudo mysql

Start by halting the slave strings: 

STOP SLAVE;

Run the accompanying inquiry to arrange the Master/Slave replication: 

CHANGE MASTER TO
MASTER_HOST='10.10.8.12',
MASTER_USER='replica',
MASTER_PASSWORD='replica_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=328;

Ensure you are utilizing the right IP address, client name, and secret phrase. The log record name and position must be equivalent to the qualities you acquired from the expert worker. 

When done, start the slave strings. 

START SLAVE;

Test the Configuration

Now, you ought to have a working Master/Slave replication arrangement. 

To check that everything is arrangement up effectively, make another information base on the expert worker: 

sudo mysql
CREATE DATABASE replicatest;

Login to the slave MySQL shell: 

sudo mysql

Run the accompanying order to list all information bases : 

SHOW DATABASES;

You will see that the information base you made on the expert worker is imitated on the slave: 

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replicatest        |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

5 columns in set (0.00 sec) 

Conclusion

In this instructional exercise, we have demonstrated you make a MariaDB Master/Slave replication on Debian 10. 

Don't hesitate to leave a remark on the off chance that you have any inquiries.




CFG