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
Binlog_Do_DB:
Binlog_Ignore_DB:

On Master:

CHANGE MASTER TO
MASTER_HOST=’db2′,
MASTER_USER=rep_user’,
MASTER_PASSWORD=’rep_pass’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’db2-bin.000004′,
MASTER_LOG_POS=588484588;

Good Luck 🙂

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s