How to setup MySQL Master-Slave Replication on Ubuntu 22.04

To Setup Master-Slave Replication On Ubuntu 22.04

Introduction

MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves). It is usually used to spread read access on multiple servers for scalability, although it can also be used for other purposes such as for failover, or analyzing data on the slave in order not to overload the master.

Procedure Steps

Step – 1 : At First check the OS version on master server

root@linuxhelp:~# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 22.04.3 LTS
Release:	22.04
Codename:	jammy

Step – 2 : Check the IP Address on master server

root@linuxhelp:~# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:9d:d7:ce brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.6.134/23 brd 192.168.7.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::ed65:e6a5:ee5d:b483/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

Step – 3 : Now install Mysql server by using the following command

root@linuxhelp:~# apt install mysql-server -y
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libflashrom1 libftdi1-2 libllvm13
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl libfcgi0ldbl
  libhtml-template-perl libmecab2 libprotobuf-lite23 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
  mysql-client-core-8.0 mysql-common mysql-server-8.0 mysql-server-core-8.0
Suggested packages:
  libipc-sharedcache-perl mailx tinyca
The following NEW packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl libfcgi0ldbl
  libhtml-template-perl libmecab2 libprotobuf-lite23 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
emitting double-array: 100% |###########################################| 
reading /usr/share/mecab/dic/ipadic/matrix.def ... 1316x1316
emitting matrix      : 100% |###########################################| 
update-alternatives: using /var/lib/mecab/dic/ipadic-utf8 to provide /var/lib/mecab/dic/debian (mecab-dictionary) in auto mode
Setting up mysql-server-8.0 (8.0.36-0ubuntu0.22.04.1) ...
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done! 

Step – 4 : After the installation done, Edit the mysql configuration file.

root@linuxhelp:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf 
## Find the line 
bind-address = 127.0.0.1
## Replaced it with the following line
bind-address = Your-Master-IP
Next, uncomment the following line:
server-id = 1
Next, add the following lines at the end of the file
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

Step – 5 : Save and close the file then restart the MySQL service to apply the changes.

root@linuxhelp:~# systemctl restart mysql

Step – 6 : Now repeat the same for Slave server, Check the OS version

root@linuxhelp:~# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.3 LTS
Release:        22.04
Codename:       jammy

Step – 7 : Check the IP Address on slave server

root@linuxhelp:~# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:d3:59:a6 brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.6.140/23 brd 192.168.7.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::494:a99c:4da1:b3e/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

Step – 8 : Now install Mysql server by using the following command

root@linuxhelp:~# apt install mysql-server -y
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-template-perl libmecab2
  libprotobuf-lite23 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0 mysql-client-core-8.0 mysql-common mysql-server-8.0 mysql-server-core-8.0
Suggested packages:
done!
Setting up mysql-server (8.0.36-0ubuntu0.22.04.1) ...
Processing triggers for man-db (2.10.2-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3.6) ...

Step – 9 : Set the password for root by using the following command

root@linuxhelp:~# mysql_secure_installation
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: 1
Skipping password set for root as authentication with auth_socket is used by default.
If you would like to use password authentication instead, this can be done with the "ALTER_USER" command.
See https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management for more information.
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!

Step – 10 : After the installation done, Edit the mysql configuration file.

root@linuxhelp:~# nano /etc/mysql/mysql.conf.d/mysqld.cnf
## Find the line 
bind-address = 127.0.0.1
## Replaced it with the following line
bind-address = Your-Master-IP
Next, uncomment the following line:
server-id = 1
Next, add the following lines at the end of the file
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

Step – 11 : Save and close the file then restart the MySQL service to apply the changes.

root@linuxhelp:~# systemctl restart mysql

Step – 12 : Now Create a Replication User on Master Node, First, connect to the MySQL shell with the following command

root@linuxhelp:~# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

Step – 13 : Once login, create a replication user with the following command

mysql> CREATE USER 'replication_user'@'192.168.6.140' IDENTIFIED BY 'Linux@123';
Query OK, 0 rows affected (0.03 sec)

Step – 14 : Next, grant all privileges to replication slave with the following command

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.6.140';
Query OK, 0 rows affected (0.00 sec)

Step – 15 : Next, flush the privileges with the following command.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Step – 16 : Next, verify the Master status with the following command.

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 906
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Step – 17 : log in to MySQL with the following command on Slave server.

root@linuxhelp:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

Step – 18 : Once login, stop the slave server to connect to the master server:

mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

Step – 19 : Run the following command to allow the slave server to replicate the Master server

mysql> CHANGE MASTER TO MASTER_HOST ='192.168.6.134', MASTER_USER ='replication_user', MASTER_PASSWORD ='Linux@123', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 900;
Query OK, 0 rows affected, 8 warnings (0.03 sec)

Step – 20 : Next, start the SLAVE with the following command.

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.02 sec)

Step – 21 : Now check with create a database on the master

mysql> create  database TEST;
Query OK, 1 row affected (0.01 sec)

Step – 22 : Next, log in to the MySQL on the Slave Node, Once you are log in, list all the databases. You should see the following output .

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| TEST               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tt                 |
+--------------------+
6 rows in set (0.00 sec)

Conclusion :

We have reached the end of this article. In this guide, we have walked you through the steps required to setup Master Slave Replication on ubuntu 22.04. Your feedback is much welcome.