Setting up MySQL master-slave replication in Linux server 

What is MySQL replication

MySQL replication is the process of copying data from one MySQL database server (master) to one or more MySQL database servers (slaves). This allows you to split the load of read queries across different servers, improve reliability by keeping multiple copies of your data, and simplify backup and reporting its activities.

Setting up MySQL master to slave replication involves several steps. Here’s a detailed guide on how to do it:

For example, I have 2 mysql installed servers, one is master and the other is slave.

Master ip : 100.101.101.90
Slave ip  : 100.201.202.80

Lets look, how to setup a MySQL replication from the master server to slave:

First, Configure the Master Server by making the necessary changes in the mysql configuration file inorder to enable binary logging: 

On the master server to enable binary logging.

# vim /etc/my.cnf

Add or modify the following lines under the [mysqld] section:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

Save and exit, then restart mysql make the changes take effect

# systemctl restart mysqld

After that, Log in to MySQL on the master server and create a user with replication privileges. Replace master_user and password with the desired username and password:

> CREATE USER 'master_user'@'%' IDENTIFIED BY 'password';
> GRANT REPLICATION SLAVE ON *.* TO 'master_user'@'%';
> FLUSH PRIVILEGES;
> SHOW MASTER STATUS;

The SHOW MASTER STATUS statement provides us status information about the binary log files of the source server. note the current binary log file and position.

If you want to copy and move data that is already in the databases, you can copy all the databases from the master server to the slave server. To do that

Take the DB dump using:

# mysqldump --all-databases --user=root --password > masterdatabase.sql

Transfer it to the slave server using the transfer tools. Here we are using scp:

# scp masterdatabase.sql [email protected]:/root

Then, go to the slave server and find the location where the dump is kept. In this case, it’s in /root (#cd /root).

Now, upload the dump file into the slave server’s MySQL:

# mysql -u root -p < /root/masterdatabase.sql

After this, configure the slave server:

In order to make the slave server act as a replica of the master server, modify the following line under the [mysqld] section of the mysql configuration file /etc/my.cnf

# vim /etc/my.cnf

[mysqld]
server-id = 2

After that, restart MySQL:

# systemctl restart mysqld

Then, login to mysql:

# mysql -u root -p

And run these MySQL commands to make the slave node ready to replicate databases from the master node: 

> STOP SLAVE;
> CHANGE MASTER TO MASTER_HOST='100.101.101.90', 
MASTER_USER='master_user',
MASTER_PASSWORD='password', MASTER_LOG_FILE='master_log_file', MASTER_LOG_POS=’master_log_pos’;
> SLAVE START;

Replace the values for MASTER_LOG_FILE and MASTER_LOG_POS, with the values we got when running SHOW MASTER STATUS command in master node.

After the setup is completed, check the slave status to verify the replication is working correctly.

> SHOW SLAVE STATUS\G;

That’s it! Your master to slave replication should now be set up and working using MySQL. Make sure to monitor replication regularly to ensure it continues functioning as expected.

Our Technical Team will be available to assist you with “Setting up MySQL master-slave replication in linux server” that can make your job a lot easier. Get in Touch with Skynats if you have any queries.