3 Jump Steps to Configure Master Slave Replication in Mysql

On 02.15.10, In Miscellaneous

Master slave replication in mysql offload some queries from one server to other. The best advantage of master slave is to use master for all inserts and send some select queries to slave. This practice may increase speed of your application without going into optimization of all queries or buying more RAM.

This master slave configuration can be done in 3 follow steps like:

.

First connet to your server via putty or open terminal directly

Step 1: Setup Master configuration file properties

Open my.cnf file

1

	vi /etc/my.cnf

.

And comment lines like below

1
2

	#skip-networking
	#bind-address = 127.0.0.1

.

we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master)

1
2
3

	log-bin = /var/lib/mysql/mysql-bin.log
	binlog-do-db=database_name_to_replication
	server-id=1

.

restart mysql

1

	service mysqld restart

.

Step 2: Setup Master configuration for replication user

logon to mysql server

1

mysql

.

create an user with “replication slave on” privileges

1
2
3
4
5

GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
GRANT RELOAD ON *.* TO 'replication_user'@'%'
GRANT SUPER ON *.* TO 'replication_user'@'%'
FLUSH PRIVILEGES;

.

Flush tables in our database:

1
2
3
4

USE database_name_to_replication;
FLUSH TABLES WITH READ LOCK;
 
SHOW MASTER STATUS;

.

you will see like this:-

1
2
3
4
5
6
7

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 |      106 | db to replicate| mysql            | 
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
==============================================

.

Step 3: Setup Slave configuration file

Open my.cnf file

1

	vi /etc/my.cnf

.

And commands lines like below

1
2
3
4
5
6
7

	server-id=2
	master-host = host_to_master_server
	master-user = replication_user
	master-password = password
	master-port = 3306
	master-connect-retry=60
	replicate-do-db=database_name_to_replication

.

Connect to MySql

1

mysql

.

1

CREATE DATABASE database_name_to_replication;

.

Restart mysql

1

	service mysqld restart

.

Run below commands:-

1
2
3
4

STOP SLAVE;
RESET SLAVE;
START SLAVE;
SHOW SLAVE STATUS;

.

the output will be somthing like this:-

1
2
3
4
5
6

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.4
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
.............

.

now we should load data from our master…

1

LOAD DATA FROM MASTER;

.

or… you can always use below command to load the initial data from master

1

mysql -usome_user_name -psome_password -Ddatabase_name_to_replication < dump_from_master_db.sql

.

after our data is moved to slave server… we can unlock our tables.
Log in to MySQL sever (master) and:

1
2

USE database_name_to_replication;
UNLOCK TABLES;

.

Now just browse your database on master and slave servers. Put some new data on master database, browse the slave database.

Hey you have new data on your slave system.

All Done!
Cheers!!



Steve

Steve is the Founder and Editor-in-chief of Web Developer Juice. He's web developer with 4 years of experience in CSS, HTML, PHP and javascript. If you’d like to connect with him, point to the contact page and follow him on Twitter: @wdjuice.

Visit Website

Comments

started
02.15.10

pretty coolll

John Rockefeller
02.15.10

This is so easy. It almost seems like MySQL replication should be harder than this :)

SM
02.15.10

Helpful post. Thanks!

Fanck
02.15.10

You should always optimyze your queries, no matter what.

jeff
02.15.10

If you are just looking to use the master/slave setup in a failover situation will this approach still work?

Wu
02.15.10

Thanx Bro, its very usefull.

Hi, it would be very useful for me, i am new to SQL and still started learning i have bookmarked this link so i can get the help from here. That is great dear.

Markus
02.15.10

Thank you for the valuable information! Very useful.

I found your blog such a informative and useful post,thanks for sharing the blog.I really like your blog.

Leicester Bank
02.15.10

NIce post ! Thanks for sharing

Good simple and clear instructions!