Day 24 Replication Setup

Today we will discuss how setup a replication:

  1. Master – Slave Replication:


  • 2 (Linux) System (MasterServer and SlaveServer)
  • MySQL Installed
  • Connection to both servers are accessible.

On Master Server:

  • Edit my.cnf with following information:


Log-Bin =MySQL_Binlog


  • Create a replication login which has access to REPLICATION SLAVE

mysql> CREATE USER ‘repl’@’%.%’ IDENTIFIED BY ‘mypass’;

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%.%’;


  • Make sure before backup gather data for replication. Like bin-log file name and position.



| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |


| mysql_Binlog.006 | 100       | mysql       |                  |



This information is important from this information only we can setup the replication correctly(making both master and slave in sync).


  • Copy the data from master to slave and restore it to slave

mysqldump –all-databases –master-data > Master_Dump.bkp

It is important to include –master-data option which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication.

We have to restart the mysql to effect my.cnf (bin-log enable) and other settings.

sudo /etc/init.d/mysql restart

On Slave Server:

  • Make system aware that this is slave, by making server-id =2 in my.cnf.




Restore the database

mysql –uroot –p < Master_Dump.bkp

Now we are ready to setup the replication configuration and provide the relation between master an stave. Here we have to provide information which we captured from show master status


   ->     MASTER_HOST=’ MasterServer,

   ->     MASTER_USER=’repl’,

   ->     MASTER_PASSWORD=’mypass’,

   ->     MASTER_LOG_FILE= ‘mysql_Binlog.006’,

   ->     MASTER_LOG_POS=100;


Once we run change master to command replication is setup between master and slave.

Now to start the replication we have to start slave



That it we have replication running.

To see the status of replication

mysql> SHOW SLAVE Status\G;


It will show all the information, some of the important information would be:

Slave_IO_State: Waiting for master to send event

Means replication is running good and no lag

Slave_IO_Running: Yes   Slave_SQL_Running: Yes

Means slave MySQL is running and connections is also good – replication is active.

Replicate_Do_DB: DBReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:

This Variables/options will filter or provide information about database/table specific replication.


Shows error occurred lastly.

Seconds_Behind_Master: 0

Another very important variable to validate replication is up to date

Read_Master_Log_Pos: xxx  Relay_Log_File: xxx.xx  Relay_Log_Pos: xx  Relay_Master_Log_File: xxx.xx


Provide information about Slave logging, how slave is working on the logs received from Master.


For setup master – master replication:

You have to enable bin-log at slave as well (second master).

And server_id=1 for that server.

Follow the same show master status parameters create repl user

And change master to configuration with master status parameters.



This entry was posted in Disaster Recovery, High Avaliability, Internal, MySQL, Others, Replication, What I learned today and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.