Skip to content

Instantly share code, notes, and snippets.

@eddy8
Created November 11, 2021 06:05
Show Gist options
  • Save eddy8/f9a8f02866680d714b2d7cbe52d1376b to your computer and use it in GitHub Desktop.
Save eddy8/f9a8f02866680d714b2d7cbe52d1376b to your computer and use it in GitHub Desktop.
MySQL 5.7 Master-Slave Replication on CentOS 7

MySQL 5.7 Master-Slave Replication on CentOS 7

Install and configure MySQL 5.7 with master-slave replication. The benefits of this include high availability, backups, disaster recovery and reporting.

Install MySQL 5.7

Enable MySQL repository

yum localinstall -y https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

Install MySQL server

yum install -y mysql-community-server

Start MySQL server

systemctl start mysqld

Enable MySQL service

systemctl enable mysqld

Confirm MySQL service is enabled

systemctl is-enabled mysqld

MySQL Configuration

Get temporary password

grep 'temporary password' /var/log/mysqld.log | tail -1

Initialize secure installation

mysql_secure_installation

Master Server

Prerequisite

  • Install MySQL 5.7 server by following instructions above
  • Create a database or import your database mysqldump

Open MySQL config file

vim /etc/my.cnf

Add the following lines (replace database with the name of your database)

server-id = 1
binlog-do-db=database
relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index
log-bin = mysql-bin

Restart MySQL server

systemctl restart mysqld

Login to MySQL server

mysql -u root -p

Create slave user (replace PASSWORD with your password)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'PASSWORD';

Flush privileges

mysql> FLUSH PRIVILEGES;

Lock tables until we finish setting up the slave server

mysql> FLUSH TABLES WITH READ LOCK;

Show master status

mysql> SHOW MASTER STATUS;

You will need the master coordinate's File and Position for the slave server, so write them down

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 | database     |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Exit MySQL server

mysql> exit

If the database you want to replicate is not empty, create a dump from the database

mysqldump -u root -p -B --events --routines --triggers database > /root/database.sql

Transfer the dumped file to the slave server

rsync -Waq -e ssh /root/database.sql 123.456.789.2:/root

Slave Server

Prerequisite

  • Install MySQL 5.7 server by following instructions above

Open MySQL config file

vim /etc/my.cnf

Add the following lines (replace database with the name of database to replicate)

server-id = 2
replicate-do-db=database
relay-log = mysql-relay-bin
log-bin = mysql-bin

Restart MySQL server

systemctl restart mysqld

Import master database dumped file if it was created

mysql -u root -p < /root/database.sql

Login to MySQL server

mysql -u root -p

Stop Slave

mysql> STOP SLAVE;

Configure Slave using data created earlier on the Master (replace PASSWORD with slave password)

mysql> CHANGE MASTER TO MASTER_HOST='123.456.789.1', MASTER_USER='slave', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;

Start Slave

mysql> START SLAVE;

Check Slave status

mysql> SHOW SLAVE STATUS \G;

If Slave_IO_State is Waiting for master to send event, then replication was setup successfully.

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 123.456.789.1
                  Master_User: slaveuser
                  Master_Port: 3306
              Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: database

Exit MySQL server

mysql> exit

Master Server

Login to MySQL server

mysql -u root -p

Unlock tables

mysql> UNLOCK TABLES;

Exit MySQL server

mysql> exit
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment