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. 
 

3 comments:

  1. I simply wanted to thank you a lot more for your amazing website you have developed here. It can be full of useful tips for those who are actually interested in this specific subject, primarily this very post. Your all so sweet in addition to thoughtful of others and reading the blog posts is a great delight in my opinion. And thats a generous present! Dan and I usually have enjoyment making use of your recommendations in what we need to do in the near future. Our checklist is a distance long and tips will certainly be put to excellent use. Eradicate Error

    ReplyDelete
  2. Just saying thanks will not just be sufficient, for the fantasti c lucidity in your writing. I will instantly grab your rss feed to stay informed of any updates. Cryptocurrency

    ReplyDelete
  3. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing. is can i run it safe

    ReplyDelete