How to set up MySQL master - slave replication?

This post is about setting up Master- Slave replication on MySQL database. Here we assume that, primary master server is up and running and you need to add another server as a slave to an existing server. The following step by step guide will help you to set up the replication.

Step 1: Enable binary log on master server:

Binary logs are the source of information for the slave server to replicate the database from master server. The slave server downloads the binary logs and executes it as a SQL statement. In order to enable the binary logs add the following parameters to configuration file and restart the server.

[mysqld]

log-bin = /path/to/log/mysql-bin

server-id = 1

expire_logs_days = 15

max_binlog_size = 1G

binlog_format = MIXED

In order to verify whether the binary log enabled or not, just run of the following command.

SHOW MASTER STATUS \G
SHOW BINARY LOGS;
SHOW VARIABLES LIKE ‘%log-bin%’;

Step 2: Backup the database from master server:

Once the binary log is enabled, backup the database from the master server. Here the important thing to note is that, we need master data to identify the binary log file name and position. Here I am using mysqldump utility for backup. Just run the following command to back up the database with master data.

mysqldump -u user -ppassword –h hostname  - - master-data=2  --all-databases > dump.sql

The parameter  - - master – data =2 will write the binary log file name and position in dump file.

Step 3: Note down the binary log file name and log position:

Now the header of the dump file contains the master log file name and log position, you can read those values from dump by using the following command.

Shell># head dump.sql –n30 | grep ‘MASTER_LOG_POS’

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000959', MASTER_LOG_POS=92604249;

Note down this values, this means that, we have backup dump which contains the data till the log file name ‘mysql-bin.000959’ and position 92604249. These values will be used later to set up replication.

Step 4: Restore the database to slave server:

After note down the master log file name and position of the master server, restore the database from dump file to slave server. The restoration can be done by using the following command.

mysql –u username –h hostname –p < dump.sql

Step 5: Create replication user on master server.

You need a user with replication slave privilege to replicate data to slave server. Create a user on master server with replicate slave on master server. The following command can be used to create replication user on master server.

Create user ‘repl’@’slavehost’ identified by ‘password’;
Grant replication slave on *.* to ‘repl’@’slavehost’;

Step 5: Set the change master settings.

Once the restoration is completed, configure the change master settings on slave server. The change master settings include the master hostname, username and password, master binary log file name and position. The change master settings given below:

CHANGE MASTER TO 

MASTER_HOST = 'hostname',

MASTER_USER = 'repl',

MASTER_PASSWORD = 'replpwd',

MASTER_LOG_FILE = 'bin-log-file-name',

MASTER_LOG_POS = POS;

Example:

CHANGE MASTER TO 

MASTER_HOST = '192.168.1.192',

MASTER_USER = 'repl',

MASTER_PASSWORD = 'replpwd',

MASTER_LOG_FILE = 'mysql-bin.0000001',

MASTER_LOG_POS = 107;

Step 6: Starting slave.

After configuring the change master settings, run the following command to start the slave.

START SLAVE;

You can check the status of the slave by running the following command.

SHOW SLAVE STATUS\G

Some points to consider:

1. Slave status will show you the status of the replication slave. You need to look at status of the following two threads –IO thread and SQL thread.
2. IO thread used to download the binary log from master server to slave server and SQL thread used to execute the binary log (relay log) on slave server as SQL statement.
3. Seconds behind master may not be zero (0) but do not worry about that, as long as both the above mentioned servers are running on the slave.
4. Sometimes you may be get errors while restoring views, in order to avoid those errors, take the backup of mysql database and restore it in slave server before restoring data. (Before doing this, just make sure you understand what is mysql database and its importance).
5. If the show slave status command displays last sql error in its status, you can fix the error and again start the server. If you wish to continue the replication by ignoring the error, you can simply skip the error by using the following command.

Stop slave;

Set global sql_slave_skip_counter = 1;

Start slave;

I hope this post will help you as a starting point for setting up MySQL master – slave replication. Please share your thoughts on the comments section.