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 [email protected] IDENTIFIED BY '[email protected]';
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 [email protected] ;
Verify the privileges granted by using the following query.
mysql> SHOW GRANTS FOR [email protected];
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='[email protected]', 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.
