Master-Slave Replication 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-Slave 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 Slave

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 !

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.

recent

Last Tweets

Locations of visitors to this page
categories