Hamsa K
Editor
8 min read | 3 months ago

How to Configure Mysql 8.0 Master Slave on Centos 7

Configuring Mysql Master and slave on centos 7 

I am using server details as below.

Master MySQL Server: 192.168.0.10 (mysql Ver 8.0.18)
Slave MySQL Server:  192.168.0.192 (mysql Ver 8.0.18)

Prerequisites

You need to have MySQL Server installed on all servers before you can continue

Please refer for below link 

Configure Master

open mysql configuration file like below 

[root@lampblogs ~]# vi /etc/my.cnf

Add below lines under mysqld section.

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

Restart mysql service for changes to take effect.

[root@lampblogs ~]# systemctl restart mysqld

step2: Create replication user on Mater db server

mysql> create user 'replica'@'192.168.0.192' identified by 'Ak6$Ft4nIp';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on . to 'replica'@'192.168.0.192'; Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)

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

create database and table and grant privileges

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

Lock db tables and backup database 

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 937 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

Backup database and copy to slave server

[root@lampblogs ~]# mysqldump -u root -p replicadb > replicadb.sql
Enter password: 
[root@lampblogs ~]# rsync replicadb.sql 192.168.0.192:/root/.
root@192.168.0.192's password: 

 unlock tables in mysql shell

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit

Configure Slave server

configure slave server by modifying my.conf file

[mysqld]
server-id=2
relay-log=mysql-relay-bin.log
log_bin=mysql-bin.log
binlog_do_db=replicadb
user=mysql
symbolic-links=0
read_only = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500
expire-logs-days = 7
slow_query_log

read_only = 1 ---This sets the slave to read-only mode. Only users with super privilege and the replication slave thread will be able to modify data on it. This means 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.

Now restart mysql server as below in slave

systemctl restart mysqld

Restore database in slave

you might need to create the database before importing the data

mysql> create database replicadb;
Query OK, 1 row affected (0.03 sec)

import db with below command

cat replicadb.sql | mysql -u root -p replicadb

Initialize Replication process

we are ready to start replication process in slave server

Login into mysql server with root and stop slave

mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)

Then configure Slave server with details obtained from the master status command.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.10', MASTER_USER='replica', MASTER_PASSWORD='Ak6$Ft4nIp', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=937;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

Now start replication on the slave 

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

Then check slave status as below

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.10
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 937
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000005
             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: 937
              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: 82588126-f96f-11e9-b214-0800278de12a
             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
            Network_Namespace: 
1 row in set (0.00 sec)

In above file you can see Slave_IO_Running and Slave_SQL_Running are 'Yes'

Test Replication

In Master db

mysql> use replicadb;
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> INSERT INTO replicatb ( id, name ) VALUES ( null, 'aa' );
Query OK, 1 row affected (0.05 sec)
mysql> select * from replicatb;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.00 sec)

Login into slave and check

mysql> use replicadb;
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 replicatb;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.00 sec)

That's it. Now we have successfully configured master and slave replication using mysql on centos 7 server.

 



Warning! This site uses cookies
By continuing to browse the site, you are agreeing to our use of cookies. Read our terms and privacy policy