MySQL Replication

Posted on Edited on In

This blog is about how to perform replication for one MySQL primary and one MySQL replica instance, 8.0 Reference

  • Replication is asynchronous by default, it could also be synchronous and semisynchronous.
    • synchronous replication is a characteristic of NDB Cluster.
    • semisynchronous replication is supported in addition to the built-in asynchronous replication.
      • With semisynchronous replication, a commit performed on the source blocks before returning to the session that performed the transaction until at least one replica acknowledges that it has received and logged the events for the transaction.
  • Replicas do not need to be connected permanently(replication can be paused) to receive updates from a source.
  • Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Goal

Here we focus on binary log file position based replication with MySQL version 8.0.42, Reference and MySQL Shell.

Vagrant

The playground is managed by Vagrant, please launch it before move on to the next section.

Briefly, you will have a primary MySQL with bin log enabled and GTID disabled, a replica MySQL the same MySQL version with bin log off and unique server ID set.

Initial Data Seed

On primary, let’s create an animals DB and tables:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE IF NOT EXISTS animals;

CREATE TABLE animals.animals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    species VARCHAR(50),
    age INT
);

INSERT INTO animals.animals (name, species, age) VALUES
('Bella', 'Dog', 3),
('Milo', 'Cat', 2);

SELECT * FROM animals.animals;

Create replication user

Reference

Go to the primary and run below commands to create a dedicated replication user repl with password ReplP2025!! for all hosts:

1
2
3
4
5
6
7
8
9
10
11
12
-- Go to the primary and run:
CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplP2025!!';

-- Check the replication user
SELECT User, Host FROM mysql.user WHERE User='repl';

+------+------+
| User | Host |
+------+------+
| repl | %    |
+------+------+
1 row in set (0.00 sec)

At this point, you are able to connect to primary from replica using user repl:

1
2
# 172.20.21.30 is primary IP
mysql -h 172.20.21.30 -urepl -p

Grant privileges

For replication user, we need below privileges to be granted to perform dump, load and replication actions.

Go to the primary and run below commands by root:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- for replication user to connect to the primary (master) and read the binary
-- log.
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- for allows a user to run SHOW MASTER STATUS, SHOW SLAVE STATUS, root user
-- on replica don't need this.
GRANT REPLICATION CLIENT ON *.* TO 'repl'@'%'; 

-- https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
-- The required set of privileges, you can exclude the unwanted ones.
GRANT RELOAD, SELECT, EVENT, SHOW VIEW, TRIGGER ON *.* TO 'repl'@'%';

-- Check the repl user privileges.
SHOW GRANTS FOR 'repl'@'%';

+-------------------------------------------------------------------------------------------------------------+
| Grants for repl@%                                                                                           |
+-------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO `repl`@`%` |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Data Dump

We use mysqlsh dump utilities instead of mysqldump.

MySQL Shell provides parallel dumping with multiple threads, file compression, and progress information display, etc.

Go to the replica and run below commands:

1
2
3
# Create a folder to host dump
sudo mkdir /etc/mysqlsh-dump
sudo chown vagrant:vagrant /etc/mysqlsh-dump

Run mysqlsh as below in replica:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# The dump log file for debugging.
# --log-file=./dump_mysqlsh.log

# Doing a instance dump, you can choose schema or table dump if needed.
# Please note that the system DBs will be skipped.
# -- util dump-instance /etc/mysqlsh-dump

# When true is set, the utility sets a global read lock using the FLUSH TABLES
# WITH READ LOCK statement (if the user ID used to run the utility has the RELOAD
# privilege)
# Default it is true.
# --consistent=true

# I don't need to dump the events and triggers, so disable them here
# --events=false
# --triggers=false

mysqlsh \
--host='172.20.21.30' \
--port=3306 \
--user='repl' \
--password='ReplP2025!!' \
--log-file=./dump_mysqlsh.log \
-- util dump-instance /etc/mysqlsh-dump \
--consistent=true \
--events=false \
--triggers=false \
--threads=2 \
--dry-run=false

Please note that the --consistent=true uses FTWRL, this lock is held just long enough for all the dump threads to establish their own transactions. Once all threads have started their consistent snapshot transactions, the global read lock is released immediately. From that point on, each thread operates within its own REPEATABLE READ transaction, leveraging InnoDB's MVCC to read a consistent view of the data as it existed when the global read lock was first acquired. This allows concurrent writes to the database to continue without interruption during the majority of the dump process.

The output is like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WARNING: Using a password on the command line interface can be insecure.
NOTE: Backup lock is not available to the account 'repl'@'%' and DDL changes will not be blocked. The dump may fail with an error if schema changes are made while dumping.

Acquiring global read lock
Global read lock acquired

Initializing - done
1 out of 5 schemas will be dumped and within them 1 table, 0 views.
2 out of 5 users will be dumped.

Gathering information - done
All transactions have been started
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 2 threads.

NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done

Starting data dump
100% (2 rows / ~2 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 27 bytes
Compressed data size: 36 bytes
Compression ratio: 0.8
Rows written: 2
Bytes written: 36 bytes
Average uncompressed throughput: 27.00 B/s
Average compressed throughput: 36.00 B/s

Data Load

Use mysqlsh load utilities.

Go to the replica and run below commands:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# I am using Unix socket instead of TCP/IP to connect local MySQL. Since I don't
# configure auth_socket plugin, so password is needed.
# --socket=/var/lib/mysql/mysql.sock

# Ignore the major version cap.
# --ignoreVersion=true

mysqlsh \
--user=root \
--password='easyone' \
--socket=/var/lib/mysql/mysql.sock \
--log-file=./load_mysqlsh.log \
-- util load_dump /etc/mysqlsh-dump \
--threads=2 \
--ignoreVersion=true \
--waitDumpTimeout=300 \
--deferTableIndexes=all \
--dry-run=false

The output is like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WARNING: Using a password on the command line interface can be insecure.
Loading DDL and Data from '/etc/mysqlsh-dump' using 2 threads.

Opening dump...
Target is MySQL 8.0.42. Dump was produced from MySQL 8.0.42

Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
Executing common postamble SQL
100% (27 bytes / 27 bytes), 0.00 B/s, 1 / 1 tables done
Recreating indexes - done
1 chunks (2 rows, 27 bytes) for 1 tables in 1 schemas were loaded in 0 sec (avg throughput 27.00 B/s)
0 warnings were reported during the load.

If the load is good, you should find the animals.animals table presents:

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM animals.animals;

+----+-------+---------+------+
| id | name  | species | age  |
+----+-------+---------+------+
|  1 | Bella | Dog     |    3 |
|  2 | Milo  | Cat     |    2 |
+----+-------+---------+------+
2 rows in set (0.00 sec)

Obtain binlog coordinates

Since we use consistent=true in mysqsh dump, we can find binlog file and position from the @.json file in /etc/mysqlsh-dump folder:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
{
    "dumper": "mysqlsh Ver 8.0.42 for Linux on aarch64 - for MySQL 8.0.42 (MySQL Community Server (GPL))",
    "version": "2.0.1",
    "origin": "dumpInstance",
    "options": {
        "consistent": true,
        "dryRun": false,
        "events": false,
        "threads": 2,
        "triggers": false
    },
    "schemas": [
        "animals"
    ],
    "basenames": {
        "animals": "animals"
    },
    "users": [
        "'repl'@'%'",
        "'root'@'localhost'"
    ],
    "defaultCharacterSet": "utf8mb4",
    "tzUtc": true,
    "bytesPerChunk": 64000000,
    "user": "repl",
    "hostname": "mysql-replica1",
    "server": "mysql-primary",
    "serverVersion": "8.0.42",
    "binlogFile": "binlog.000002",
    "binlogPosition": 2201,
    "gtidExecuted": "",
    "gtidExecutedInconsistent": false,
    "consistent": true,
    "partialRevokes": false,
    "compatibilityOptions": [],
    "capabilities": [],
    "begin": "2025-07-04 00:07:37"
}

Start Replication

We need to kick off the replication within the expiration of the bin log in the primary after the data dump and load:

1
2
mysql> show replica status\G
Empty set (0.01 sec)

Run replication setup in replica:

1
2
3
4
5
6
7
8
9
10
-- You can set SSL if it is enabled, the fill options please see
-- https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '172.20.21.30',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_HEARTBEAT_PERIOD = 5,
  SOURCE_PASSWORD = 'ReplP2025!!',
  SOURCE_LOG_FILE = 'binlog.000002',
  SOURCE_LOG_POS = 2201;

Check replication status, the Replica_IO_Running, Replica_SQL_Running are No since we haven’t started it yet:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
mysql> show replica status\G

*************************** 1. row ***************************
             Replica_IO_State:
                  Source_Host: 172.20.21.30
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 2201
               Relay_Log_File: mysql-replica1-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File: binlog.000002
           Replica_IO_Running: No
          Replica_SQL_Running: No
              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: 2201
              Relay_Log_Space: 157
              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: NULL
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: 0
                  Source_UUID:
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State:
           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.01 sec)

Now start the replication:

1
2
mysql> START REPLICA;
Query OK, 0 rows affected (0.05 sec)

Check the replication status again, the Replica_IO_Running, Replica_SQL_Running should be YES if no issue and Replica_SQL_Running_State contains the current state:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 172.20.21.30
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 2201
               Relay_Log_File: mysql-replica1-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Source_Log_File: binlog.000002
           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: 2201
              Relay_Log_Space: 542
              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: a8f99222-5863-11f0-9d43-08002731abd8
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica 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.01 sec)

Check replication

On Primary, you can show what replicas actively connected to it, if you don’t start the replication on replica, you won’t see it here:

1
2
3
4
5
6
7
8
9
-- 1001 is the replica1 server ID
mysql> show replicas;

+-----------+------+------+-----------+--------------------------------------+
| Server_Id | Host | Port | Source_Id | Replica_UUID                         |
+-----------+------+------+-----------+--------------------------------------+
|      1001 |      | 3306 |         1 | 51eea654-5864-11f0-9394-08002731abd8 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

Show all currently running threads, includes the replication:

1
2
3
4
5
6
7
8
9
10
11
mysql> show processlist\G

*************************** 2. row ***************************
     Id: 46
   User: repl
   Host: 172.20.21.31:52140
     db: NULL
Command: Binlog Dump
   Time: 7738
  State: Source has sent all binlog to replica; waiting for more updates
   Info: NULL

Verification

Now, let’s insert new row on animals.animals table on primary:

1
2
INSERT INTO animals.animals (name, species, age) VALUES
('Maria', 'Fish', 23);

It will be replicated to replica.