Questions And Answers

More Tutorials

MYSQL Replication

Master - Slave Replication Setup

Consider 2 MySQL Servers for replication setup, one is a Master and the other is a Slave.

We are going to configure the Master that it should keep a log of every action performed on it. We are going to configure the Slave server that it should look at the log on the Master and whenever changes happens in log on the Master, it should do the same thing.

Master Configuration

First of all, we need to create a user on the Master. This user is going to be used by Slave to create a connection with the Master.

CREATE USER 'user_name'@'%' IDENTIFIED BY 'user_password';
GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'%';

Change user_name and user_password according to your Username and Password.

Now my.inf (my.cnf in Linux) file should be edited. Include the following lines in [mysqld] section.
server-id = 1
log-bin = mysql-bin.log
binlog-do-db = your_database
The first line is used to assign an ID to this MySQL server.

The second line tells MySQL to start writing a log in the specified log file. In Linux this can be configured like log-bin = /home/mysql/logs/mysql-bin.log. If you are starting replication in a MySQL server in which replication has already been used, make sure this directory is empty of all replication logs.

The third line is used to configure the database for which we are going to write log. You should replace your_database with your database name.

Make sure skip-networking has not been enabled and restart the MySQL server(Master)

Slave Configuration

my.inf file should be edited in Slave also. Include the following lines in [mysqld] section.

server-id = 2
master-host = master_ip_address
master-connect-retry = 60
master-user = user_name
master-password = user_password
replicate-do-db = your_database
relay-log = slave-relay.log
relay-log-index = slave-relay-log.index

The first line is used to assign an ID to this MySQL server. This ID should be unique.

The second line is the I.P address of the Master server. Change this according to your Master system I.P.

The third line is used to set a retry limit in seconds.

The next two lines tell the username and password to the Slave, by using which it connect the Master.

Next line set the database it needs to replicate.

The last two lines used to assign relay-log and relay-log-index file names.

Make sure skip-networking has not been enabled and restart the MySQL server(Slave)


In this page (written and validated by ) you learned about MYSQL Replication . What's Next? If you are interested in completing MYSQL tutorial, your next topic will be learning about: MYSQL Backup using mysqldump.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue:

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using, you agree to have read and accepted our terms of use, cookies and privacy policy.