How to Configure MySQL 8.0 Master Slave Replication on Centos 7.x

This guide to help you Configure MySQL 8.0 Master-Slave Replication on Centos 7.x

This setup will use below Server details:

Master MySQL Server: 10.0.0.98 Slave MySQL Server:  10.0.0.99

Setup Prerequisites:

You need to have MySQL Server installed on all servers before you can continue, refer to the following guides for installation of MySQL Server:

How to Install MySQL 8 Community on CentOS 7

Step 1: Configure the Master Server

The first configuration change to make is setting Server ID for the master database:

# vim /etc/my.cnf

Add the line below under [mysqld] section. Note that the number set needs to be unique, it can not be re-used on any node in the cluster.

server-id = 1

Set  log_bin location, this is where all replication information is located. All the changes made on the master are written to this file. All slaves will copy data from it.

log-bin=mysql-bin.log binlog_do_db=exampledb server-id=1 sync_binlog=1 user=mysql symbolic-links=0 tmpdir = /tmp binlog_format = ROW max_binlog_size = 500 expire-logs-days = 7 slow_query_log

A complete simple configuration looks like below:

[mysqld] log-bin=mysql-bin.log binlog_do_db=exampledb server-id=1 sync_binlog=1 user=mysql symbolic-links=0 tmpdir = /tmp binlog_format = ROW max_binlog_size = 500 expire-logs-days = 7 slow_query_log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [innodb] innodb_flush_log_at_trx_commit=1

binlog_do_db=exampledb: this is the database that will be replicated

Restart mysql service for changes to take effect:

# systemctl restart mysql

Step 2: Create Replication user on Master database server

We now need to create a database user to be used by slaves when connecting. Login to MySQL database as root user and create the user:

[root@master etc]# mysql -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.15 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create user 'sync'@'10.0.0.99' identified by 'sync1234'; Query OK, 0 rows affected (0.01 sec)

Grant the user REPLICATION SLAVE privileges:

mysql> grant replication slave on *.* to 'sync'@'10.0.0.99'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)

Confirm grants for created user:

mysql> show grants for 'sync'@'10.0.0.99'; +------------------------------------------------------+ | Grants for sync@10.0.0.99                            | +------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO `sync`@`10.0.0.99` | +------------------------------------------------------+ 1 row in set (0.00 sec)

Step 3: Create Database and table on Master database server

We now need to create a database and table for example Replication:

mysql> create database exampledb; Query OK, 1 row affected (0.04 sec) mysql> use exampledb; Database changed mysql> CREATE TABLE exampletb ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary     key (id) ); Query OK, 0 rows affected (0.02 sec) mysql> GRANT ALL PRIVILEGES ON exampledb.* to 'sync'@'10.0.0.99'; Query OK, 0 rows affected (0.01 sec)

Step 4: Backup database

From shell terminal :

[root@master ~]# mysqldump -uroot -p --opt exampledb > exampledb.sql Enter password:

Copy to slave  database server

[root@master ~]# scp exampledb.sql root@10.0.0.98:/root/ root@10.0.0.98's password: exampledb.sql                                 100% 1875     2.1MB/s   00:00

Step 5: Install and Configure Slave Server

Install MySQL Server 8.0 on Slave server in a similar process used for the Master server. You can follow steps in the guide How to Install MySQL 8.0 on CentOS 7

When done with the installation, configure slave by editing the file:

[mysqld] server-id=2 relay-log=mysql-relay-bin.log log_bin=mysql-bin.log binlog_do_db=exampledb user=mysql symbolic-links=0 read_only = 1 tmpdir = /tmp binlog_format = ROW max_binlog_size = 500 expire-logs-days = 7 slow_query_log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [innodb] innodb_flush_log_at_trx_commit=1

read_only = 1: This sets the slave to read-only mode. Only users with the SUPER privilege and the replication slave thread will be able to modify data on it. This ensures there are no applications that can accidentally modify data on the slave instead of master.

log_bin = mysql-bin.log:  This enables binary logging. This is required for acting as a MASTER in a replication configuration. You also need the binary log if you need the ability to do point in time recovery from your latest backup.

Restart mysql server after you’ve finished making changes:

# systemctl restart mysqld

Step 6: Restore database

First login to mysql with root privilege create database exampledb:

[root@slave ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 20 Server version: 8.0.15 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database exampledb; Query OK, 1 row affected (0.01 sec)

from shell terminal

[root@slave ~]# mysql -uroot -p exampledb < exampledb.sql Enter password:

Step 7: Initialize Replication process

We should be ready to start Replication process on the slave server. Start by checking Status on the master databse server:

mysql> show master status\G *************************** 1. row ***************************              File: mysql-bin.000030          Position: 155      Binlog_Do_DB: exampledb  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

Take a note of current Master log file and position. Then configure Slave server with details obtained from the master status command:

[root@slave etc]# mysql -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.15 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.98',     -> MASTER_USER='sync',     -> MASTER_PASSWORD='sync1234',     -> MASTER_LOG_FILE='mysql-bin.000030',     -> MASTER_LOG_POS=155; Query OK, 0 rows affected, 2 warnings (0.02 sec)

Then start replication on the slave:

mysql> start slave; Query OK, 0 rows affected (0.01 sec)

To check slave status, use:

mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.0.0.98                   Master_User: sync                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000030           Read_Master_Log_Pos: 155                Relay_Log_File: mysql-relay-bin.000002                 Relay_Log_Pos: 322         Relay_Master_Log_File: mysql-bin.000030              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                    Last_Error:                  Skip_Counter: 0           Exec_Master_Log_Pos: 155               Relay_Log_Space: 530               Until_Condition: None                Until_Log_File:                 Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1                   Master_UUID: d144000b-3b07-11e9-a246-0800274a8d27              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 0          Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version:        Master_public_key_path:         Get_master_public_key: 0 1 row in set (0.00 sec)

Slave IO and SQL should indicate running state:

Slave_IO_Running: Yes             Slave_SQL_Running: Yes

Test transaction replication

on master database server:

[root@master ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.0.15 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use exampledb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------+ | Tables_in_exampledb | +---------------------+ | exampletb           | +---------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO exampletb ( id, name ) VALUES ( null, 'uu' ); Query OK, 1 row affected (0.03 sec) mysql> select * from exampletb; +----+------+ | id | name | +----+------+ |  1 | uu   | +----+------+ 1 row in set (0.00 sec)

on slave database server:

[root@slave ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 25 Server version: 8.0.15 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use exampledb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from exampletb; +----+------+ | id | name | +----+------+ |  1 | uu   | +----+------+ 1 row in set (0.00 sec)