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 !
[…] Master-Slave Replication with MySQL 8.0 in 2 mins […]
Love this guide it worked straight out of the box for me!
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.
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.