Deploy an Asynchronous Replication setup with MySQL 8.0 in 2 mins

on

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 !

4 thoughts on “Deploy an Asynchronous Replication setup with MySQL 8.0 in 2 mins

  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

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.

I deeply value the diversity of MySQL developers, users, community and my blog readers. I cannot agree the suffering, oppression, and systemic racism the Black community faces every day. Black lives matter.
recent
categories