How to Setup Mysql Master Slave Replication

Introduction- Master slave configuration is made to reduce database single point of failure and improve the database query performance.In Master DB Server we can write & read both, where as from Slave DB Server we can only read.

Configuration-

A) Master DB server

1.Install mysql-server

 # apt-get install mysql-server

2. edit mysql's my.cnf file with following changes-

 # vim /etc/mysql/my.cnf

 Bind-address- 192.168.1.2 (give master server's ip)

 Server-id = 1

 uncomment- # log_bin

3. restart mysql service

 # service mysql restart

4. Go to mysql server and create a user and allowe it from Slave DB Servers.

 # mysql -uroot -p

 mysql> CREATE USER 'user1'@'%' IDENTIFIED BY 'slavepassword';

 mysql> GRANT REPLICATION SLAVE ON *.* TO 'user1'@'%';

 ## Now create some test data to cross check easily

 mysql> CREATE DATABASE company;

 mysql> CREATE TABLE company.employee (name varchar(25));

 mysql> INSERT INTO company.employee value("prakhar");

 mysql> SELECT * FROM company.employee;

 (To see master's Replication Status Variables)

 mysql>show master status\g

 mysql> exit;

5.Now creating snapshot of the master db server

 # mysqldump -uroot --all-databases --master-data > masterdump.sql

 (It causes binary log position information in a dump file, Backup have a change master statement in it,  

 # grep CHANGE *sql | head -1

 (It gives binary log file and its position, Copy that file to slave db server)

 # scp masterdump.sql [email protected]:/tmp

B) Slave DB Server

1. install mysql-server

 # apt-get install mysql-server

2. Edit mysql's my.cnf file and make following changes-

  # vim /etc/mysql/my.cnf

 Bind-address- 192.168.1.4 (give slave server's ip)

 Server-id = 2 (anything greater than 1,increment in this by 1 in each next slave)

3. Restart mysql service

 # service mysql restart

4. Go to mysql server and create a give Master DB's information as follows-

 # mysql -uroot -p

 mysql> CHANGE MASTER TO

 mysql> MASTER_HOST='192.168.1.2',

 mysql> MASTER_USER='user1',

 mysql> MASTER_PASSWORD='slavepassword';

 mysql> exit;

5. Import that masterdump.sql file here now,

 # mysql -uroot < masterdump.sql

6. Start mysql Slave-

 # mysql -uroot -p

 mysql> start slave;

 (See Master log files, its position, Slave running, Replication status variables)

 mysql> show slave status\G;

7. See data in tables-

mysql> select * from company.employee;

Note- You can add another slaves to master server and check that on Master server.

 mysql> show slave hosts; (run it on master server)

 But Vice-Versa is not true.

Testing-

Now add some more entries to the table company.employee on Master DB Server and then check that on Slave DB Server.