How To Set Up Master And Slave MYSQL

Submitted by: 
Language: 
Visitors have accessed this post 2705 times.

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 = 127.0.0.1

To

  1. 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

  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’@’192.168.2.2 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

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

After that start the slave

  1. SLAVE START;

Then you can test the status of slave

  1. 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;


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 SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

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.

Comments

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

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.