Introduction
Implementing Master-Slave replication (also known as mirror) is really important to prevent data loss, increase performance, support database backup and as one of the solutions to alleviate system failures. What will you do if your database server crashed?
To give you an idea on what Master-Slave is, imagine two MySQL server instances where the Master server is being copied automatically to a Slave server while the master is still fulfilling its job. You can actually have multiple slave servers. So, what happened is that if the master database server crashed, there will be an exact copy of the data from the master in the slave server.
One of the useful features of MySQL is Replication where MySQL automatically copies data from one MySQL instance to another. You could have an exact copy of your data on another server in case the main server crashes.
Since the master-slave replication is a one-way replication (from master to slave), only the master database is used for the write operations, while read operations may be spread on multiple slave databases.
Prerequisites
For you to setup MySQL Master-Slave Replication, you’ll need two different servers with an installed MySQL, each server responding to a specific IP address. We’ll be calling these two servers as Master Server and Slave Server. We’ll be using CentOS Linux as the operating system for this tutorial.
Tutorial
In this tutorial, we are going to use CentOS Linux as the operating system and the provided commands are for CentOS. If you want to setup a MySQL master-slave replication on other operating system, you’ll have to make modifications for its specific commands. However, the general principles of setting up the MySQL master-slave replication are the same for all operating systems.
- Edit the mysql configuration file (my.cnf) on the Master and Slave servers.
Here’s the path to open up the mysql configuration file:
cd /etc/my.cnf
- Edit the Master Server main config file
Insert the following in the my.cnf file of the Master Server under the [mysqld] and replace the existing ones if present:
server-id = 1
log-bin = mysql-bin
binlog-ignore-db = "mysql"
See the example below:
Notes:
- The ‘
server-id’ is used in replication to enable master and slave servers to identify themselves uniquely and to establish a unique replication ID. - If you start a master server without using server-id to set its ID, the default ID would be
0. In this case, the master refuses connections from slave and the slave refuses to connect to the master. - The
log-bin = mysql-bin means the logs will go to the mysql data directory and will have the mysql-bin prefix. - The
binlog-ignore-db tells the Master not to log changes taking place on the listed database.
- Edit the Slave server main config file
On the Slave Server my.cnf, add this under the [mysqld]:
server-id=2
- Restart MySQL
After saving the changes in my.cnf, make sure to restart mysql both in Master and Slave servers by:
service mysqld restart
- Copy Master Database to Slave
This step is to make sure that the MySQL instances in Master Database starts with the same data with the Slave Database.
If your database is running and there is data that are inserting and updating in your tables in the Master Database, you can use the FLUSH TABLES WITH READ LOCK command to stop any additional inserts into the database and to allow all current transactions to be completed. After fulfilling Step 2, issue the following statement to enable the master to process again: UNLOCK TABLES;
Option 1: If you’re using SQLYog as your MySQL GUI, you can use the ‘Copy Database to Different Host/Database’ function featured in SQLyog as shown below:
Option 2: Or if you’re using MySQL Workbench, use the MySQL Workbench Migration Wizard as shown below:
- Configure Master and Slave Servers
- Go to MySQL Shell of the Master Server:
mysql –u root –p
<enter root password>
GRANT REPLICATION SLAVE ON *.* TO 'root'@'<insert slave server ip address>'
IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
The SHOW MASTER STATUS command is to show the file name and position that the master server is using for logging purposes. Take note of the file and position because it will be used in configuring the slave server.
Alternately, you can also do the SHOW MASTER STATUS command in your MySQL Workbench:
- Go to MySQL Shell of the Slave Server:
mysql –u root -p
<enter root password>
CHANGE MASTER TO MASTER_HOST='<insert master server ip address>',
MASTER_USER='root', MASTER_PASSWORD='<insert password>',
MASTER_LOG_FILE='<insert master_log_file>', MASTER_LOG_POS='<insert master_log_pos>';
Fill up the MASTER_LOG_FILE and MASTER_LOG_POS from the file and position listed on ‘show master status’ command.
Note:
This query informs the slave server about the replication information like, where to start the replication process, the log file of the master database as well as the log position.
- Start replication
In the MySQL Shell of Slave Server, start slave by simply running this query:
START SLAVE;
Now, check if the slave is running by:
SHOW SLAVE STATUS;
You can execute this command either on the MySQL Shell or to your MySQL GUI. To tell if the replication is running, both the Slave_IO_Running and Slave_SQL_Running should have ‘YES’ indicated on it.
Notes:
SHOW SLAVE STATUS shows you the current status of the replication process. If you see the Slave_IO_Running and Slave_SQL_Running as ‘YES’, then database replication is configured properly.- If you want to know how late a slave is compared to the master, check the
Seconds_Behind_Master column from the output of the Show Slave Status.
Tip: If you encounter a scenario where the slave stopped – the Slave_IO_Running and the Slave_SQL_Running have ‘No’ indicated on it, you can find the error in this path: var/LOG/mysqld.log
You can skip the errors by editing the my.cnf file of the Slave server as shown below:
After adding the slave-skip-error command, restart the mysql:
service mysqld restart
