The Dummit

  • Tutorial
  • Tips & Tricks
  • About Me
  • Reference

MySQL 5.7 : How to configure Replication (Master/Slave) Database on Centos 7

Posted on 25 June 2019 in Tutorial

 loading


For me, using replication will leverage me from disaster such as server crash or even when database get corrupted.


I use MySQL 5.7 version to setup replication (master/slave) for one of my PowerDNS server because i’m afraid PowerDNS does not work with newest version of MySQL.


Master DB Configuration


  • Open /etc/my.cnf and enable Log and Server-ID in mysql configuration
bind-address = 0.0.0.0
server-id = 1
log-bin = mysql-bin
binlog-ignore-db = "mysql"
  • Login to MySQL and Create user for replication
# mysql -u root -p
mysql> grant replication slave on *.* TO [replication_username]@'[private IP of second database]' identified by '[some password]';
mysql> flush privileges;
mysql> quit
  • Restart MySQL Service
systemctl restart mysqld
  • Please run this command below to make sure there nothing can write master database during a database dump. Also note the filename and position of the binary log because you will need these values to complete the replication configuration on slave database.
# mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

+------------------+--------------------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000010 |        10 |              | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)
  • Now you can perform database dump by executing this command below
mysqldump -u root -p --databases [database-1] [database-2] ...  > /root/db_dump.sql
  • After dump process complete, you can unlock the database by running below command
# mysql -u root -p
mysql> UNLOCK TABLES;
  • copy the database dump file to the slave db server by using your prefered method. Here, i’m just gonna use scp command (or you can use software like WinSCP to copy the file)
scp /root/db_dump.sql [private-IP-of-slave_db_svr]:/root/

Slave DB Configuration

  • Open /etc/my.cnf and enable Log and Server-ID in mysql configuration
bind-address = 0.0.0.0
server-id = 2
master-host =  [private-IP-of-Master_DB_Svr]
master-user = [replication-username]
master-password = [replication-password]
master-connect-retry = 60

You might need to create the database before importing the dump file to the slave database.

  • Now import the database dump from master db to the slave
mysql -u root -p < /root/db_dump.sql
service mysqld restart
  • Complete the replication process
# mysql -u root -p
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='[private-IP-of-Master_DB_Svr]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed-on-master-status];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

Test Replication

To test replication , you can just create new database on the master db or just input new data to it. After that, you can check if the data you inserted to the master database is replicated to the slave database. If the data exist on the slave server, then you replication configuration is successfully configured and ready to do it’s job.

 loading

How to install Openfire and configure server to server on Centos 7

 loading

PowerDNS Authoritative & MySQL: How to Setup for local DNS on Centos 7

Recent Post

  • openldap
    How to compile, install and configure openLDAP in Centos 8
    5 September 2020
  • zimbra desktop
    Zimbra Desktop : How to recover your local folder after crash (Win 10)
    2 September 2020
  • doh
    Mikrotik : How to use DoH with Cloudflare
    7 July 2020
  •  loading
    How to install Openfire and configure server to server on Centos 7
    12 August 2019
  •  loading
    MySQL 5.7 : How to configure Replication (Master/Slave) Database on Centos 7
    25 June 2019