How to Setup Master-Slave Replication in MySQL 8 on Ubuntu 20.04

MySQL replication is a process that replicates data from one Mysql DB Server to one or more Mysql DB Servers automatically. Database administrator is responsible for managing the MySQL server and he must have knowledge of the replication concept. This will helps in terms of Database Backup from Slave, Data Security & Fail-over Solution. In this tutorial, you will learn how to setup master-slave replication in mysql 8 on ubuntu 20.04. You can do it on ubuntu 18.04 as well.

MySQL supports replication topology with Master/Slave topology in which one DB Server acts as master, on the other hand one or more servers act as slaves. By default, replication is asynchronous and slaves do not need to be connected permanently to receive updates from the master. It is primarily used to read access on multiple servers for scalability and also used for failover.

Prerequisites

  • Two Servers running Ubuntu 20.04.
  • Static IP address configured on both servers.
  • DB root credentials  configured on both servers.

In this tutorial, we have the following IPs:

Master IP :    192.168.72.128
Slave IP : 192.168.72.129

Read Also : Difference between Soft Links and Hard Links in Linux

Update Ubuntu Official Repo

Before you begin installing MySQL database on both servers, you should update the ubuntu package repository on both machines using below command:

 $  sudo apt update -y

Install MySQL Server on Both Nodes

Initially, you need to install the MySQL server package on both the Master and Slave server. You can install it by running the following command on both machines:

 $  sudo apt install mysql-server -y

After installing MySQL server, start MySQL service and enable it with the following command:

 $  sudo systemctl start mysql

$ sudo systemctl enable mysql

Now, you must secure your MySQL and set root password. With the following command you can set root credentials:

$  mysql_secure_installation 

Next, answer below questions:

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough.

Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
- Dropping test database...
Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.

All done!

Configuring MySQL Master Server

first, we will configure master server. We’ll make few changes in configuration files.

  • Set a unique server ID.
  • Enable the binary logging and putting below entries.

You can open the MySQL configuration file and uncomment or set the following parameters:

$  sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

comment the bind address by putting # (hash) before it.

#bind-address = 127.0.0.1

Put the following entries into your configuration file.

default_authentication_plugin=mysql_native_password
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log

Save and close the config file when you are done. Then, restart the MySQL service to implement the changes.

$  sudo systemctl restart mysql

The next step is to create a new replication user. Log in to the MySQL server as the root user by following command:

$  mysql -u root -p

Next, provide your root MySQL credentials when prompted then execute the following command to create a replication user (Please use mysql_native_password while assigning password )and grant replication slave privileges to the user:

mysql> create user [email protected] identified with mysql_native_password by '[email protected]';

mysql> grant replication slave on *.* to [email protected];

mysql> flush privileges;

mysql> show grants for [email protected];
+--------------------------------------------------------------+
| Grants for [email protected] |
+--------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `replica`@`192.168.72.129` |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

Inside the MySQL prompt, execute the following command that will show the binary filename and position.

mysql> show master status;

You will get the following output:

+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1050 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

Please note down the file name, ‘mysql-bin.000003’ and Position ‘1050’. You will need these values when configuring the slave server. These values will probably be different on your server.

Now you can exit from the MySQL shell with the following command:

mysql> EXIT;

Configuring MySQL Slave Server

Next, go to the Slave Server and make changes in configuration file with  a unique ID and enable the binary logging. You can do it using the following command:

$  sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

comment the bind address by putting # (hash) before it.

#bind-address = 127.0.0.1

Put the following entries into your configuration file.

default_authentication_plugin=mysql_native_password
log_bin = /var/log/mysql/mysql-bin.log
server-id = 2
read_only = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log = 1

Save and exit the config file when you are done. Then, restart the MySQL service to implement the changes:

$  sudo systemctl restart mysql

Connect Slave Server to the Master Server

The next step is to configure the parameters on slave server that will use to connect to the master server. Please use the following commands:

$  mysql -u root -p

You can provide your MySQL root credentials when prompted then stop the Slave threads with the following command:

mysql> STOP SLAVE;

Next, set up the slave server to replicate the master server with the following command that match with the master log file and log position:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.72.128', MASTER_USER='replica', MASTER_PASSWORD='[email protected]', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1050;
  • 192.168.72.128 : Ip address of the Master server.
  • replica : MySQL user of the Master server.
  • password : MySQL replica user password of the Master server.
  • mysql-bin.000003 : Master log file of the Master server.
  • 1050 : Master log position of the Master server.

Next, activate the slave server with the following command:

mysql> START SLAVE;

You can also see the Slave status with the following command:

mysql> SHOW SLAVE STATUS\G

You should see the following screen:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State : Waiting for master to send event
Master_Host : 192.168.72.128
Master_User : replica
Master_Port : 3306
Connect_Retry : 60
Master_Log_File : mysql-bin.000003
Read_Master_Log_Pos : 1050
Relay_Log_File : slave-thecodecloud-relay-bin.000002
Relay_Log_Pos : 324
Relay_Master_Log_File : mysql-bin.000003
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 : 1050
Relay_Log_Space : 546
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_UUID : 83b387cc-f995-11ea-8922-000c29bcff30
Master_Info_File : mysql.slave_master_info
SQL_Delay : 0
SQL_Remaining_Delay : NULL
Slave_SQL_Running_State : Slave has read all relay log; waiting for more updates
Master_Retry_Count : 86400
Master_Bind :
Last_IO_Error_Timestamp :
Last_SQL_Error_Timestamp :
Master_SSL_Crl :
Master_SSL_Crlpath :
Retrieved_Gtid_Set :
Executed_Gtid_Set :
Auto_Position : 0
Replicate_Rewrite_DB :
Channel_Name :
Master_TLS_Version :
Master_public_key_path :
Get_master_public_key : 0
Network_Namespace :
1 row in set (0.00 sec)

Hence, exit from the MySQL shell with the following command:

mysql> EXIT;

Verify Master-Slave Replication

Hence, Master and the Slave server are configured to replicate each other. It’s time to test whether the replication is working or not. On the Master server, login to the MySQL shell with the following command:

$ mysql -u root -p

Provide your root password and create a database with name testdb;

mysql> CREATE  DATABASE testdb;

Next, verify the database with the following command:

mysql> SHOW DATABASES;

You should see the following output:

+————————–+
|        Database                 |
+————————–+
| information_schema  |
| mysql                              |
| performance_schema |
| sys                                   |
| testdb                             |
+————————–+
5 rows in set (0.00 sec)

Now login to the Slave server with the below command:

$  mysql -u root -p

Provide your root credentials then run the following command to list all databases on slave server:

mysql> SHOW DATABASES;

You must see the testdb database which you created on the Master server is replicated on the Slave server:

+————————–+
|        Database                 |
+————————–+
| information_schema  |
| mysql                              |
| performance_schema |
| sys                                    |
| testdb                              |
+————————–+

Conclusion

Therefore, in this tutorial you learned how to Setup Master-Slave Replication in MySQL 8 on Ubuntu 20.04. I hope you get it better. Thanks !!

Read Also :  How to Create or Delete/Remove Users in MySQL Server

Share on:

Ravindra Kumar

Hi, This is Ravindra. I am founder of TheCodeCloud. I am AWS Certified Solutions Architect Associate & Certified in Oracle Cloud as well. I am DevOps and Data Science Enthusiast.

Recommended Reading..

Leave a Comment