Hamsa K
Editor
11 min read | 1 year ago

How to Configure Mysql 8.0 Master Slave on Centos 7

Configuring Mysql Master and slave on centos 7 

MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves). It is usually used to spread read access on multiple servers for scalability, although it can also be used for other purposes such as for failover, or analyzing data on the slave in order not to overload the master.

In this tutorial we will configure mysql master and slave on centos 7 servers. 

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 and you can refer the url  Install Mysql on centos 7

otherwise follow below steps to install mysql server.

rpm -Uvh  https://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm
yum install mysql-server
systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld

Also Read -> How to Setup Syslog server in Centos 7

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

 Create replication user on Master 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)

Also Read -> Installation of Apache MySQL and PHP in centos 7

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)

Also Read -> How to install Elasticsearch on centos 7

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.

Also Read -> How to Configure RAID1 on Centos 7



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