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:
Step 1: Configure the
Master Server
The first configuration change to make is setting Server ID for the
master database:
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.
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) |
