How to setup MySQL Master-Slave replication

Hello there!

This time I’m going to discuss one of the projects I got involved recently. The team is expanding the operations to a new operator and we need to deploy the platform in new servers. One instance is to create database replications in MySQL.

Below is the environment I’m working on

1. Master and Slave are CentOS Linux release 7.4 Linux Servers. 
2. Master IP Address is 192.168.100.1. 
3. Slave IP Address is 192.168.100.2. 
4. Master and Slave are on the same LAN network. 

Let’s install MySQL on the master server and configure

1. For CentOS 7 and Red Hat (RHEL) 7
yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

2. CentOS 7.4/6.9 and Red Hat (RHEL) 7.4/6.9
yum install mysql-community-server

3. Start MySQL server and auto start MySQL on boot. Below commands can be used in Fedora 27/26/25 and CentOS 7.4 and Red Hat (RHEL) 7.4
systemctl start mysqld.service
systemctl enable mysqld.service


But I couldn’t log in to the MySQL by using below command.
$mysql -u root -p

It prompts to enter the password which I didn’t enter into the installation process. Then I used below mechanism to get the root password reset.

1. Stop mysql:
systemctl stop mysqld

2. Set the mySQL environment option
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

3. Start mysql using the options you just set
systemctl start mysqld

4. Login as root
mysql -u root

5. Update the root user password with these MySQL commands
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
-> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit

6. Stop mysql
systemctl stop mysqld

7. Unset the MySQL environment option so it starts normally next time
systemctl unset-environment MYSQLD_OPTS

8. Start mysql normally:
systemctl start mysqld

Try to login using your new password:
7. mysql -u root -p


Note
As of MySQL 5.7.6, for MySQL installation using an RPM distribution, server startup and shutdown is managed by systemd on several Linux platforms. On these platforms, mysqld_safe is no longer installed because it is unnecessary.


Now I’ve access to the mysql-server of the master database.


Let’s configure the master server.


# vi /etc/my.cnf

Add the following entries under [mysqld] section and don’t forget to replace testdb with the database name that you would like to replicate on Slave.

# [mysqld] section
# Start Modification
# First line is probably already there
server-id = 1
binlog-do-db=ideabizAdmin
expire-logs-days=7
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql-bin.log


Once configurations are done, you need to restart the mysql. Use below commands.

# /etc/init.d/mysqld restart

Now all the configurations are done in the master database. Let’s create the slave user and grant permission.

Use below command to log in to MySQL

# mysql -u root -p

mysql> STOP SLAVE;
Since MySQL is using row-based logging format, by using above command it will shut down the slave server process.


mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';

Above command will create slave user. Earlier I’ve given the access to the user by below command. Once finished the implementation, I saw there’s a connectivity issue from slave server to Master.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY 'slave_password';

When granting the permission it’s important to mention the IP that you need to access. Otherwise, you can use the wildcard(%) and grant permission.

Note: Using wildcard may cause some security issues.

mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 451228 | lintut | |
+------------------+----------+--------------+------------------+
1 ROW IN SET (0.00 sec)

Now we need to apply the read lock and dump all the databases to the slave server.
mysqldump -u root -p --master-data database > /root/database.sql

Once you dump all the data, let’s release the read lock and exit mysql by using below commands.

mysql> UNLOCK TABLES;
mysql> quit;

Okay, now I’m going to work on the slave server. Let’s copy the mysql dump to the slave server by using below command

scp /root/database.sql root@192.168.100.2:/root/

Let’s configure slave server. 

source: www.toptal.com

As we did in the master server, you need to install MySQL and configure my.cnf file in the slave as below

server-id = 2
replicate-do-db=ideabizAdmin
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

In the earlier MySQL versions, you may allow configuring master-host details in my.cnf file. But when I tried the same, it gave an error saying MySQL cannot be restarted after the modification.

master-host=192.168.100.1
master-connect-retry=60
master-user=slave_user
master-password=slave_password

Instead of setting the slave's my.cnf, I used the MySQL console like below.

1. Let’s stop the slave
mysql> slave stop;

2. Change the host details
mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.1', MASTER_USER='slave_user', MASTER_PASSWORD=’slave_password’, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;

3. Start the slave again
mysql> slave start;

4. See the slave status
mysql> show slave status\G

That’s it!
Now we have successfully implemented the Master-Slave replication in MySQL. Let’s check and confirm.

On the master server, I’m creating the database/tables now

mysql> create database ideabizAdmin;
mysql> use ideabizAdmin;
mysql> CREATE TABLE employee (c int);
mysql> INSERT INTO employee (c) VALUES (1);
mysql> SELECT * FROM employee;

Below is the expected result

+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

Let’s log in to the slave server and verify the data is replicated.

mysql> use ideabizAdmin;
mysql> SELECT * FROM employee;

+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)


Yeyyyyy! We are done :) Do post your comments below