Helpdesk Support

Official Supportmonk Company Blog!

MYSQL – Replication [ Master – Slave replication]

March 1, 2013 , 8:36 am

You can replicate your mysql server to one or more slave server. Mysql replication is a synchronous process because your master server will not wait for the slave servers to apply the changes and slave server need not be be connected permanently to the master server. Slave server will update all the changes latter and replicate all the changes. All the write operations are performing in the master server and read can takes place in slave box. Asynchronous replication is more faster than synchronous replication.

Here I am going to demonstrate  how to create a Master-Slave mysql replication. For example Master server is and slave server is . Here we have one slave server.

Mysql – Master server configuration :-

In master server we should specify the unique” ID “and ” master bin log” .  From master bin log slave getting the data from the master server.

Step 1. Shut down the mysql server

Step 2. open your my.cnf file and update the settings under  [mysqld] section.






server-id = 1




Here binary logs are enabled using “log-bin” and  the name for the log-bin is admod-bin, you can use any name as per your wish

server-id=1 , it should be a positive integer , by default this value will be “0”

relay-log are executed on slave server. Which contains the events reads from the master bin log.


After this setting we need to restart the mysql server.

Step 3: Create a replication user.

In order to connect our slave server to master and get the update , we need to create a user and grant this user for the privilege to get the mysql updates. So we should create this user in our master server and grant replication privilege for the slave server for the mysql databases.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’’ IDENTIFIED BY ‘password’;

Here user name is replication, password is password.



Flush all the tables and block write statements



File: admod-bin.00006

Position: 550



1 row in set (0.00 sec)


Mysql – Slave server configuration :-

Step 1: open your my.cnf file and update the settings under  [mysqld] section.

We should provide server ID, master server IP, replication user name and its password. So that slave server can connect to the master server.




master-host =

master-user = replication

master-password = password

master-port = 3306


After this edit restart the mysql server in your slave box. Master information will be stored in  /var/lib/mysql/ your slave server.

Step 2: mysql> stop slave;

Step 3: mysql> reset slave;

Step 4: mysql> CHANGE MASTER TO MASTER_HOST=’′, MASTER_USER=’replication’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’admod-bin.00006′;

Here : master server IP , ” replication” replication user we have created in master box, admod-bin.00006 is the bin-log and the value we have get it from “SHOW MASTER STATUS” on Master box.

Step 5: mysql> START SLAVE;

Step 6: mysql> show slave status \G;


Slave_IO_State: Waiting for master to send event


Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: admod-bin.00006

Read_Master_Log_Pos: 550

Relay_Log_File: mysqld-relay-bin.00001

Relay_Log_Pos: 934

Relay_Master_Log_File: admod-bin.00006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0


Slave server read the changes in bin-log and these changes update to the relay-logs  and update to slave SQL Thread. We can verify our replication status is fine by checking Seconds_Behind_Master,Slave_IO_Running and Slave_SQL_Running values.


Envelope Icon

Get Updates Your Email!

Subscribe to Supportmonk and receive blog posts to your email!

Subscribe Via Email

SupportMonk on Facebook

Outsourced Customer Support