MySQL 8 Master-Slave replication on Ubuntu 20.04

Sometimes we may need to replicate data from one MySQL DB server to one or more MySQL DB servers automatically. Basically, database administrators perform database-related tasks such as replication, clustering, and auto-failover. Managing MySQL database servers and performing replication tasks are the responsibilities of database experts. In this article, we will learn how to set up master-slave MySQL database replication in Ubuntu 20.04. In this article, we will use MySQL 8 for replication.

Prerequisites

• Two ubuntu servers
• Root privileged user account
• Internet connection to download packages

In this example, the following servers are being used.

• Master - 192.168.178.135
• Slave - 192.168.178.137

Install MySQL 8 server on both nodes

Installation of MySQL is a simple and straightforward process. In the Ubuntu system, MySQL can be installed using the APT package repository. Before installation, update the Ubuntu package index using the following command.

$apt update

Install MySQL server package using the command below.

$apt install mysql-server -y

Fresh installation of MySQL server leaves it non-secured. Run the script “mysql_secure_installation” to change some of the less secure default options such as remote root logins, test database, sample users, etc.

$ mysql_secure_installation

Start and enable MySQL service

$ systemctl start mysql

$ systemctl enable mysql

Configure Master DB server

Now we need to make some changes on the master server for replication. Edit the mysqld.conf file under the directory /etc/mysql/mysql.conf.d and add the following lines. Remember to add the configuration variables under the [mysqld] section.

$ vi /etc/mysql/mysql.conf.d/mysqld.conf

[mysqld]
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 = 800M
sync_binlog = 1
expire-logs-days = 5
slow_query_log=1
slow_query_log_file=/var/lib/mysql/mysqld-slow.log

Where:

• server-id - Unique ID of the MySQL server. This ID can not be re-used in any nodes in the cluster.
• log-bin - This is the file in which all the replication information is stored.
• max_binlog_size - Size of the binlog file.

Also, comment the bind-address as:

#bind-address =127.0.0.1

Restart mysql service

$systemctl restart mysql

Create a new user for the Replication service on the master node

Now we need to create a database user in a master node which will be used by slaves when connecting. Log in to the database using the root user and create the user.

$ mysql -u root -p

$ mysql> CREATE USER replication_user@192.168.178.137 IDENTIFIED BY 'StrongP@ssw0rd';

Note: Specifying IP address while creating user will accept MySQL connections only from the specified host. Connections from other hosts will be refused. In this example, I have used my MySQL slave server’s IP address. You may need to change this IP address to your environment accordingly.

The user needs REPLICATION SLAVE privileges to be able to replicate the MySQL data. Use the following query to grant the privileges.

mysql> GRANT REPLICATION SLAVE on *.* to replication_user@192.168.178.137 ;

Verify the privileges granted by using the following query.

mysql> SHOW GRANTS FOR replication_user@192.168.178.137;

Configure slave DB node

We have already installed the MySQL server in the previous step. Now we will change some configuration files to enable the replication process. Edit the mysqld.conf file under /etc/mysql/mysql.conf.d directory and add the following contents.

$vi /etc/mysql/mysql.conf.d/mysqld.conf

Configuration file content:

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server-id = 2
read_only = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 800M
sync_binlog = 1
expire-logs-days = 5
slow_query_log = 2

Where,

• server-id - Unique MySQL server-id.
• read_only - Slave node is set to read-only mode
• log_bin - Enables binary logging in slave node
• slow_query_log - Enables slow query log

Also, comment out the bind-address line.

# bind-address = 127.0.0.1

Now restart MySQL service

$ systemctl restart mysql

Connect slave server to master server

Before configuring the slave server, go to the master server and check the status.

$ mysql -u root -p

mysql> SHOW MASTER STATUS\G

Take a note of the following flags which we will use in the slave server.

• File: mysql-bin.000002
• Position: 156

Now configure the parameters in the slave server which will be used to connect the master server. Follow the below steps.

• Login to the MySQL server using root credentials

$ mysql -u root -p

• Stop Slave threads

mysql> STOP SLAVE;

• Setup the slave server to replicate the master server

mysql> CHANGE MASTER TO MASTER_HOST='192.168.178.137', MASTER_USER='replication_user', MASTER_PASSWORD='StrongP@ssw0rd', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1050;

Where,

• 192.168.178.137 - IP address of the master server
• replication_user - MySQL user of the master server
• password - Replication user password
• mysql-bin.000002 - Logfile of the master server
• 156 - Log position of the master server

Now activate the slave server.

mysql> START SLAVE;

Verify the slave status by using the following query

mysql> SHOW SLAVE STATUS\G

Note the status of the following flag.

• Slave_IO_Running: Connecting
• Slave_IO_Running: Connecting

Verify master-slave replication

Master and slave are configured to replicate. We can verify if master-slave replication is working or not. In the master server, log in to the MySQL shell and create a test database.

$ mysql -u root -p

mysql> CREATE DATABASE example_db;

Verify the created database.

mysql> SHOW DATABASES;

Now login to the slave server

$ mysql -u root -p

Run the following query to list the databases.

mysql> SHOW DATABASES;

You can see the database created in the master server is replicated to the slave server.

Conclusion

In this article, we learned how to set up MySQL master and slave node on Ubuntu 20.04. You can follow this article to set up master-slave MySQL replication on Ubunutu 18.04 too.