(Last Updated On: June 26, 2018)

This guide is to help you Configure MySQL 8.0 Master-Slave Replication on Ubuntu 18.04. MySQL is an open-source relational database management system that has been adopted widely. Some Infrastructure setups require that you have a read-only database server for reading operations like SELECT statements.

The MySQL replication process allows you to maintain multiple copies of MySQL data. All data in the master is synced to Slave servers in an automated process and if you have a disaster, you can easily promote Slave to a Master for commit operations. The main role of replication is to spread read and write workloads across multiple servers for easy scalability.

This setup will use below Server details:

Master MySQL Server: 10.131.74.92
Slave MySQL Server:  10.131.35.167

Setup Prerequisites:

You need to have MySQL Server installed on all servers before you can continue, refer to the following guides for installation of MySQL Server:

How to Install MySQL 8.0 on Ubuntu 18.04 / 16.04

Installing MySQL Server on CentOS 7 / CentOS 6

If you prefer to install version available from the upstream repo, run:

$ sudo apt-get install mysql-server mysql-client

Once the installation of MySQL server is done, proceed to configure replication.

Step 1: Configure the Master Server

The first configuration change to make is setting Server ID for the master database:

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

Add the line below under [mysqld] section. Note that the number set needs to be unique, it can not be re-used on any node in the cluster.

server-id = 1

Set  log_bin location, this is where all replication information is located. All the changes made on the master are written to this file. All slaves will copy data from it.

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

A complete simple configuration looks like below:

[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 = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log

Restart mysql service for changes to take effect:

$ sudo systemctl restart mysql

Step 2: Create Replication user on Master database server

We now need to create a database user to be used by slaves when connecting. Login to MySQL database as root user and create the user:

[email protected]:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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.

mysql> create user [email protected] identified by 'password';
Query OK, 0 rows affected (0.08 sec)

Grant the user REPLICATION SLAVE privileges:

mysql> grant replication slave on *.* to [email protected];
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Confirm grants for created user:

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

Step 3: Install and Configure Slave Server

Install MySQL Server 8.0 on Slave server in a similar process used for the Master server. You can follow steps in the guide How to Install MySQL 8.0 on Ubuntu 18.04 / 16.04

When done with the installation, configure slave by editing the file:

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
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

read_only = 1: This sets the slave to read-only mode. Only users with the SUPER privilege and the replication slave thread will be able to modify data on it. This ensures there are no applications that can accidentally modify data on the slave instead of master.

server-id = 2: This is a Unique server identification number. It will default to if “master-host” is not set.

log_bin = /var/log/mysql/mysql-bin.log:  This enables binary logging. This is required for acting as a MASTER in a replication configuration. You also need the binary log if you need the ability to do point in time recovery from your latest backup.

Restart mysql server after you’ve finished making changes:

$ sudo systemctl restart mysql

Step 4: Initialize Replication process

We should be ready to start Replication process on the slave server. Start by checking Status on the master:

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

Take a note of current Master log file and position. Then configure Slave server with details obtained from the master status command:

[email protected]:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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.

mysql> CHANGE MASTER TO MASTER_HOST='10.131.74.92',
-> MASTER_USER='rpl_user',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=155;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

Then start replication on the slave:

mysql> start slave;
Query OK, 0 rows affected (0.06 sec)

To check slave status, use:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.131.74.92
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 550
               Relay_Log_File: node-02-relay-bin.000002
                Relay_Log_Pos: 717
        Relay_Master_Log_File: mysql-bin.000002
             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: 550
              Relay_Log_Space: 927
              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: d62cd5d2-784a-11e8-9768-eacea5a1be5e
             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
1 row in set (0.00 sec)

Slave IO and SQL should indicate running state:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes