In this post let me introduce how to setup a basic master-slave replication with MySQL 8 database.

Software environment:

OS: Ubuntu 18

Database: MySQL 8

IP address of master machine: 192.168.1.10

IP address of slave machine: 192.168.1.11

Replication mode: GTID

First step(backup and restore):

If we want to setup a replication with currently running database, we need to backup and restore current database on slave machine. If both master and slave databases are new, we could just skip this step.

1: set database for read only on master machine:

mysql> SET @@GLOBAL.read_only = ON;

need to set it to ‘OFF’ after finished the setup.

2: backup and restore.

Second step(setup):

On master machine:

mysql> SET PERSIST server_id=1;

mysql> SET PERSIST_ONLY gtid_mode = ON;

mysql> SET PERSIST_ONLY enforce_gtid_consistency = true;

mysql> RESTART;

create user used in replication access:

mysql> CREATE USER ‘replica’@’%’ IDENTIFIED BY ‘strong_pass’ REQUIRE SSL;

security option: you could set this user only access from slave machine.

(CREATE USER ‘replica’@’192.168.1.11’ IDENTIFIED BY ‘strong_pass’ REQUIRE SSL;)

mysql> GRANT replication slave on *.* to ‘replica’@’%’;

On slave machine:

mysql> SET PERSIST server_id=2;

mysql> SET PERSIST_ONLY gtid_mode=ON;

mysql> SET PERSIST_ONLY enforce_gtid_consistency = true;

mysql> RESTART;

mysql> CHANGE MASTER TO

MASTER_HOST=’192.168.1.10′,

MASTER_PORT=3306,

MASTER_USER=’replica’,

MASTER_PASSWORD=’strong_pass’,

MASTER_AUTO_POSITION=1,

MASTER_SSL=1;

mysql> start slave;

After those steps your setup should work fine. You can simple test it by create a new database and table on master machine and insert some dummy data into it and check those database , table and data on slave machine.

And of course if you insert any data on slave database it wouldn’t be synced to master database.

Possible way to fix(restart replication process) the error occurred on slave database:

Try to skip GTID:

1: check slave status:

mysql> show slave status\G;

from this command we could know the ‘Executed_Gtid_Set’ and ‘Retrieved_Gtid_Set’;

for example we got:

Executed_Gtid_Set : 51777777-ff7f-aaaa-8989-asdf565fd65:1–54

and

Retrieved_Gtid_Set: 51777777-ff7f-aaaa-8989-asdf565fd65:5–5402

2: try to skip gtid:

mysql>stop slave;

mysql> set @@session.gtid_next=’51777777-ff7f-aaaa-8989-asdf565fd65:54′;

mysql> begin;

mysql> commit;

mysql> set gtid_next=’AUTOMATIC’;

mysql> start slave;

Try to reset:

On master database:

mysql> reset master;

On slave database:

mysql> stop slave;

mysql> reset slave;

mysql> reset master;

mysql> start slave;

Hope this is helpful for you, thanks your reading.

source : https://medium.com/@michael_w_s/basic-setup-of-master-slave-gtid-replication-on-mysql-8-8f39ea29765c

Note :

Add the following line in [mysqld] unit to enable read-only mode Server DB Slave ( Jika Server Database Slave hanya Mode Baca)

read-only = 1

Users with SUPER privilege (like root) can still write to the database, so you should be careful when granting privileges to users. If you don’t want anyone to be able to change/delete the database, you can add the following line in [mysqld] unit.

innodb-read-only = 1

Save and close the file. Then restart MariaDB for the change to take effect.

sudo systemctl restart mysql

Posted on: June 28, 2022, by : Julian's | 102 views