How to Configure MySQL Master-Slave Replication on Ubuntu 18.04
MySQL replication is a cycle that permits information from one data set worker to be naturally duplicated to at least one workers.
MySQL underpins various 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. Of course, the replication is offbeat where the expert sends occasions that depict information base adjustments to its paired log and slaves demand the occasions when they are prepared.
This instructional exercise covers a fundamental illustration of MySQL Master/Slave replication with one expert and one slave worker on Ubuntu 18.04. Similar advances apply for MariaDB.
This kind of replication geography is most appropriate for sending of read reproductions for read scaling, live information bases reinforcement for catastrophe recuperation and for investigation occupations.
Prerequisites
This model expects you have two workers running Ubuntu 18.04, which can speak with one another over a private organization. In the event that your facilitating supplier doesn't offer private IP addresses, you can utilize the public IP addresses and design your firewall to permit traffic on port 3306 just from confided in sources.
The workers in this model have the accompanying IPs:
Master IP: 192.168.121.190
Slave IP: 192.168.121.236
Install MySQL
The default The Ubuntu 18.04 stores incorporates MySQL variant 5.7. To evade any issues, it is ideal to introduce a similar MySQL adaptation on the two workers.
Introduce MySQL on the Master worker:
sudo apt-get update
sudo apt-get install mysql-server
Introduce MySQL on the Slave worker utilizing similar orders:
sudo apt-get update
sudo apt-get install mysql-server
Configure the Master Server
The initial step is to arrange the expert MySQL worker. We'll roll out the accompanying improvements:
- Set the MySQL worker to tune in on the private IP .
- Set a remarkable worker ID..
- Empower the paired logging
To do so open the MySQL setup record and uncomment or set the accompanying:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
master:/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 192.168.121.190
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
When done, restart the MySQL administration for changes to produce results:
sudo systemctl restart mysql
The subsequent stage is to make another replication client. Sign in to the MySQL worker as the root client by composing:
sudo mysql
From inside the MySQL brief, run the accompanying SQL questions that will make the copy client and award the REPLICATION SLAVE advantage to the client:
CREATE USER 'replica'@'192.168.121.236' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.121.236';
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: 629
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Observe document name, 'mysql-bin.000001' and Position '629'. You'll require these qualities while arranging the slave worker. These qualities will presumably be diverse on your worker.
Configure the Slave Server
Like for the expert worker above, we'll roll out the accompanying improvements to the slave worker:
Set the MySQL worker to tune in on the private IP
Set an exceptional worker ID
Empower the parallel logging
Open the MySQL design record and alter the accompanying lines:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
slave:/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 192.168.121.236
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
Restart the MySQL administration:
sudo systemctl restart mysql
The following stage is to design the boundaries that the slave worker will use to associate with the expert worker. Login to the MySQL shell:
sudo mysql
To begin with, stop the slave strings:
STOP SLAVE;
Run the accompanying inquiry that will set up the captive to repeat the expert:
CHANGE MASTER TO
MASTER_HOST='192.168.121.190',
MASTER_USER='replica',
MASTER_PASSWORD='replica_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=629;
Ensure you are utilizing the right IP address, client name, and secret word. The log document 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 fills in true to form, we'll 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)
Conclusion
In this instructional exercise, we have indicated you make a MySQL Master/Slave replication.
Don't hesitate to leave a remark in the event that you have any inquiries.
