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
  1. sudo apt-GET UPDATE;
  2. sudo apt-GET install mysql-server
Then open open up the my.cnf file which contained database configuration
  1. sudo vim /etc/mysql/my.cnf
Then change the following line
  1. bind_address =
  1. bind_address =
Upto now what we have done is set the mysql listning all the IP addresses Also uncomment following lines
  1. server-id = 1
  2. 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
  1. sudo service mysql restart
Then log into database as root with password
  1. 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
  1. GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’ IDENTIFIED BY ‘password’;
Setting up slave At first we need to clear APT cash and installed MySQL
  1. sudo apt-GET UPDATE; sudo apt-GET install mysql-server
During the installation you need to provide password. After that open my.cnf file
  1. sudo vim /etc/mysql/my.cnf
Uncomment following line
  1. server-id = 2
Save the file and you need to restart mysql server
  1. sudo service mysql restart
Then log in to with your password
  1. mysql -u root -p
Use following query to set up master slave relation
After that start the slave
Then you can test the status of slave
Above will prompt “Waiting for master to send event” Then create database on master and after that give following quarry to slave SHOW DATABASES;

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.


1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.


I appreciate source codestor for being the best source of knowledge to me.

Add new comment