MySQL 8 Replication

In this post we´re going to show how to configure replication between two MySQL 8 Servers using Binary Log Position Based replication method (https://dev.mysql.com/doc/refman/8.0/en/binlog-replication-configuration-overview.html). The environment used is:

Ubuntu Server 20.04.1 LTS
MySQL Server 8.0.25

Let´s go to the action. Install MySQL Server on both servers:

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

Connect to MySQL

sudo mysql -u root

Create USER for Replication (Just on source server)

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> exit

Also check if Binary logging is enable at source server. This is required on the source and is enabled by default:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

If no, you need to enable it:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

If you don´t find a line like this:

# log_bin                       = /var/log/mysql/mysql-bin.log

You need to add it and specify the full path to Binary log file. Keep the configuration file open. Now we need to set unique server id to each server (Master and Slave). In this case we set Master ID as 1 and Slave ID as 2. This can be done dynamically (https://dev.mysql.com/doc/refman/8.0/en/replication-howto-masterbaseconfig.html) but in our case we make it fixed. The default server-id for MySQL 8.0 is 1 as you can see at this line:

# server-id = 1

Just in case let´s remove the “#” to be sure that we´ll have the ID that we want. Finally add the following lines at the end of the file to enable SSL Communication using auto-generated MySQL Certificates:

ssl_ca                  = ca.pem
ssl_cert                = server-cert.pem
ssl_key                 = server-key.pem

Save the file and restart MySQL Server:

sudo systemctl restart mysql

Let´s check if the variable is set correctly:

sudo mysql -u root
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.01 sec)

Let´s change the Server ID on second (slave) MySQL Server:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Change the line below:

# server-id = 1

to:

server-id = 2

Save and close the file and restart MySQL Server and check if the id is correct:

sudo systemctl restart mysql
sudo mysql -u root
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.02 sec)

Awesome… we´re almost there. Now it´s time to go one step further. To start the replication at a correct point we need to get the source coordinates. But first, we need to lock the tables to hold writes and get a consistent coordinate. At Master Node:

sudo mysql -u root
mysql> FLUSH TABLES WITH READ LOCK;

Keep this session open. This is very important because the lock is released when the session dies. We need to open another session to get current binary log file name and position:

sudo mysql -u root
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Record the values at File and Position fields. They will be needed later to enable replication at correct point. Now we have two ways that we can follow:

• If we´ve existing data that needs be to synchronized with the replica before start replication, leave the client running (on the first session) so that the lock remains in place. This prevents any further changes being made, so that the data copied to the replica is in synchrony with the source. In this case we need to set a Snapshot strategy to transport the “old” data. This is not what we´ll do here.
• If we´re setting up a new source and replica combination, we can close the lock session and follow next procedures. This is our case in this tutorial.

Now, connect to second MySQL Server (Slave), open a connection to MySQL shell and configure the replica using parameters that we already configured:

sudo mysql -u root
mysql> CHANGE REPLICATION SOURCE TO
    -> SOURCE_HOST='source_host_name',
    -> SOURCE_USER='replication_user_name',
    -> SOURCE_PASSWORD='replication_password',
    -> SOURCE_LOG_FILE='recorded_log_file_name',
    -> SOURCE_LOG_POS=recorded_log_position;

Quick Note: CHANGE REPLICATION SOURCE TO command is just valid starting from MySQL 8.0.23. If you´re using a version before this one the command is CHANGE MASTER TO.

Now we can check replication status by running:

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: 10.0.0.54
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000006
          Read_Source_Log_Pos: 156
               Relay_Log_File: render-mysql-db-02-relay-bin.000008
                Relay_Log_Pos: 365
        Relay_Source_Log_File: binlog.000006
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 156
              Relay_Log_Space: 796
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: d4285ff9-df76-11eb-acbb-fa163e6cd80f
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

And on the source we can check the status of connected replicas by running at MySQL Shell:

mysql> SHOW PROCESSLIST\G
*************************** 2. row ***************************
     Id: 13
   User: repl
   Host: 10.0.0.86:35324
     db: NULL
Command: Binlog Dump
   Time: 593
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

The replica is the server that drives the replication process. This is why on the source just a small piece of information is shown.

Now it´s time to put replication under fire. Let´s see wich databases exists in the replica server:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

At source/master database let´s create a new database called customers:

mysql> CREATE DATABASE customers;
Query OK, 1 row affected (0.14 sec)

Going back to slave let´s see wich databases exists there:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| customers          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

Now we´re going to create a sample table at source server:

mysql> USE customers
Database changed
mysql> CREATE TABLE table1
    -> ( number INT(11) AUTO_INCREMENT,
    -> name VARCHAR(32) NOT NULL,
    -> city VARCHAR(32),
    -> age VARCHAR(7),
    -> CONSTRAINT key1 PRIMARY KEY (number)
    -> );
Query OK, 0 rows affected, 1 warning (1.01 sec)

And check it´s replica at replica server:

mysql> USE customers
Database changed
mysql> SHOW TABLES;
+---------------------+
| Tables_in_customers |
+---------------------+
| table1              |
+---------------------+
1 row in set (0.00 sec)

mysql> SHOW COLUMNS FROM table1;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| number | int         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32) | NO   |     | NULL    |                |
| city   | varchar(32) | YES  |     | NULL    |                |
| age    | varchar(7)  | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

That´s it. I hope that this can help you building replication using MySQL 8 at Ubuntu Server.