Deploy an Asynchronous Replication setup with MySQL 8.0 in 2 mins

There are multiple way to setup replication with MySQL 8.0 and our replication offer as never been so rich: asynchronous, semi-synchronous, group replication, multi-source, … and much more options !

But if you want to setup a very quick Master-Replics environment from scratch for a quick test (you can always use dbdeployer), here are some commands to make it right the first time 😉

Requirements

You need to have MySQL 8.0 installed and running on both servers and with the same initial data (a fresh install for example). Here we use mysql1 and mysql2. We will also use GTID as it’s much more convenient.

Servers Configuration

Let’s setup mysql1 first:

mysql1> SET PERSIST server_id=1; 
mysql1> SET PERSIST_ONLY gtid_mode=ON; 
mysql1> SET PERSIST_ONLY enforce_gtid_consistency=true; 
mysql1> RESTART;

And now mysql2:

mysql2> SET PERSIST server_id=2; 
mysql2> SET PERSIST_ONLY gtid_mode=ON; 
mysql2> SET PERSIST_ONLY enforce_gtid_consistency=true; 
mysql2> RESTART;

Replication User

On mysql1 that will act as master we do:

mysql1> CREATE USER 'repl'@'%' IDENTIFIED BY 'password' REQUIRE SSL; 
mysql1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Starting the Replica

And on mysql2, we just configure and start replication:

mysql2> CHANGE MASTER TO MASTER_HOST='mysql1', 
        MASTER_PORT=3306, MASTER_USER='repl', 
        MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1, MASTER_SSL=1;
mysql2> START SLAVE;

Done !

Very easy, and of course don’t forget to check the manual for much more options !

Update: with MySQL InnoDB ReplicaSet since 8.0.19 it’s even more easy !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

4 Comments

  1. If the mysql server contains databases already, you need to go from gtid_mode ‘OFF’ -> ‘OFF_PERMISSIVE’ -> ‘ON_PERMISSIVE’ -> ‘ON’ in an online state. Otherwise, the server will not start.

    So, the correct sequence of commands is:
    SET PERSIST server_id=1;
    SET PERSIST gtid_mode=OFF_PERMISSIVE;
    SET PERSIST gtid_mode=ON_PERMISSIVE;
    SET PERSIST enforce_gtid_consistency=true;
    SET PERSIST gtid_mode=ON;

    Note the online change of these values instead of PERSIST_ONLY.

  2. If your master data base creates temp tables or uses a create table statement you need to be aware that MySQL 8.0 states that this will not work when you replicate with the GTID mode. If you do not need these dynamically created tables on the replication to the slave I was thinking that to get around this, instead of this:

    mysql1> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;

    Can you just issue the GRANT REPLICATION SLAVE on each specific table you want to replicate:

    mysql1> GRANT REPLICATION SLAVE ON mydatabse.table_1 TO ‘repl’@’%’;
    mysql1> GRANT REPLICATION SLAVE ON mydatabse.table_2 TO ‘repl’@’%’;
    mysql1> GRANT REPLICATION SLAVE ON mydatabse.table_2 TO ‘repl’@’%’;

    Let me know if this is a way to get around the replication constraint.

Leave a Reply to F. DarcyCancel Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.