Setting up Master – Slave and Master – Master MySQL Replication

If you are using MySQL as a database, one of the best solution is using MySQL Replication for High Availability.

Here is a short procedure for setting up replication :

  • Set one of server’s “server-id” parameter to any integer value different from the slave one.

For master mysql database :

# vi /etc/my.cnf


server-id     = 5   # This server’s id is 5


For slave mysql database

# vi /etc/my.cnf


server-id     = 6   # This server’s id is 6


  • Restart both MySQL servers for reload my.cnf files.

If you have mysql script under /etc/init.d/ , you can start and stop db servers running “service mysql stop” and “service mysql start” commands.

  • Login slave MySQL database server and run “fake” change master command. Please fill the MASTER_HOST information with the master host’s hostname.

mysql> CHANGE MASTER TO MASTER_HOST=’hostname’, MASTER_USER=’rep_user’,
-> MASTER_PASSWORD=’rep_pass’, MASTER_PORT=3306;

  • Now we will migrate the data from the master host to the slave one. First export full master database to alldb.sql using below command.

# mysqldump -uroot -p –all-databases –single-transaction  –master-data=1  > alldb.sql

  • scp the dump file (alldb.sql) to the slave node from the master node.

# scp alldb.sql slave_node:

  • Import this file to the slave mysql database.

# mysql -uroot -p < alldb.sql

  • When import operation finishes, you can logon the slave database and enable slave mode running :

mysql> start slave;

  • You can check running “show slave status \G” on the slave server. Both Slave_IO_Running and Slave_SQL_Running must be run. (“Yes” indicates that it is running)

mysql> show slave status \G


Slave_IO_Running: Yes
Slave_SQL_Running: Yes


  • Now test time !!

create a table to the master database under test schema and see that it comes to slave database..

If you want to set up Master-Master Replication , you perform a CHANGE MASTER command on the first host taking MASTER_LOG_FILE and MASTER_LOG_POS values from the slave.

On Slave:

mysql> show master status \G
*************************** 1. row ***************************
File: db2-bin.000004
Position: 588484588

On Master:


Good Luck 🙂


One thought on “Setting up Master – Slave and Master – Master MySQL Replication

  1. Pingback: MySQL Error 1236 | My Database Related Blog

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