How To Set Up Master And Slave MYSQL

In this tutorial I am going to teach you one of the most important MySQL techniques that you must use in your software development. Today huge amount of business use MySQL as their data base. Imagine if their database corrupted what would be happen ?, If your server crashed what word you do ? So we must use database backup technique to overcome those issues.
Now I am going to explain you one of the best solution for above issue is master slave replication for MySQL . That’s mean you have to copies of same database one as master and other as slave. In this scenario you always dealing with master for your needs but you have up to date backup as slave.
I am here going to used ubuntu and MySQL 5.5
How to set the master
If you have brand new server update APT cash and update server

sudo apt-GET UPDATE; 


sudo apt-GET install mysql-server

Then open open up the my.cnf file which contained database configuration

sudo vim /etc/mysql/my.cnf

Then change the following line

bind_address = 127.0.0.1

To

bind_address = 0.0.0.0

Upto now what we have done is set the mysql listning all the IP addresses
Also uncomment following lines

server-id = 1


log_bin = /var/log/mysql/mysql-bin.log

First line we are removing server id
Second line we are removing current backup mechanism. That is LOG
After you done this restart the mysql

sudo service mysql restart

Then log into database as root with password

mysql -u root -p

At thefinal step we need to add user account for the slave. This user account help slave login to master and do operations

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

Setting up slave
At first we need to clear APT cash and installed MySQL

sudo apt-GET UPDATE; sudo apt-GET install mysql-server

During the installation you need to provide password.
After that open my.cnf file

sudo vim /etc/mysql/my.cnf

Uncomment following line

server-id = 2

Save the file and you need to restart mysql server

sudo service mysql restart

Then log in to with your password

mysql -u root -p

Use following query to set up master slave relation

CHANGE MASTER TO MASTER_HOST=’192.168.2.2′, MASTER_USER=’replication’, MASTER_PASSWORD=’password’;

After that start the slave

SLAVE START;

Then you can test the status of slave

SHOW SLAVE STATUS;

Above will prompt “Waiting for master to send event”
Then create database on master and after that give following quarry to slave
SHOW DATABASES;

 Download Link