Mysql Master Slave Replication

 Replication is based on the master server keeping track of all changes to its databases (updates, deletes, and so on) in its binary log.The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started. Typically,SELECT  statements are not recorded because they modify neither database structure nor content.

Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave. The slave also executes the events from the binary log that it receives.This has the effect of repeating the original changes just as they were made on the master. Tables are created or their structure modified, and data is inserted, deleted, and updated according to the changes that were originally made on the master.

Because each slave is independent, the replaying of the changes from the master’s binary log occurs independently on each slave that is connected to the master. In addition, because each slave receives a copy of the binary log only by requesting it from the master, the slave is able to read and update the copy of the database at its own pace and can start and stop the replication process at will without affecting the ability to update to the latest database status on either the master or slave side.

  http://dev.mysql.com/doc/refman/5.0/en/replication-implementation-details.html

Masters and slaves report their status in respect of the replication process regularly so that you can monitor them.
http://dev.mysql.com/doc/refman/5.0/en/thread-information.html

The master binary log is written to a local relay log on the slave before it is processed. The slave also records information about the current position with the master’s binary log and the local relay log.

 http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html

Database changes are filtered on the slave according to a set of rules that are applied according to the various configuration options and variables that control event evaluation.
http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html

Steps For Mysql Master-slave Replication

One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen.You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.

let us dive into how to setup master-slave replication under MySQL. There are many configuration changes you can do to optimize your MySQL set up.

Master server ip: 192.168.1.231
Slave server ip : 192.168.1.232Slave username : mysqlslave
Slave password : slavepwd

Your data directory is:  /var/lib/mysql

1. In Master# yum install mysql mysql-server
# service mysqld start
# mysqladmin -uroot password ‘master’
# service mysqld stop

Edit the my.cnf file under [mysqld] section of your mysql master

# vim /etc/my.cnf

[mysqld]
server-id = 1
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
datadir=/var/lib/mysql
log-bin = /var/lib/mysql/mysql-bin

# service mysqld restart

2. In Slave# yum install mysql mysql-server
# service mysqld start
# mysqladmin -uroot password ‘slave’
# service mysqld stop

Add the the following under [mysqld] to the mysql slave by edting my.cnf

# vim /etc/my.cnf

[mysqld]
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
datadir=/var/lib/mysql

# service mysqld restart

3. Then in Mysql Master server create a user with replication privileges

# mysql -uroot -pmaster

mysql>  grant replication slave on *.* to mysqlslave@’192.168.1.232′ identified by ‘slavepwd’;
mysql> flush privileges;You can see the new user on the master db by

mysql> show databases;
mysql> use mysql;
mysql> show tables;
mysql> select * from user;

4. Take a dump of data from Mysql Master to move to slave

# mysqldump -uroot -p –all-databases –single-transaction –master-data=1 > masterdump.sql (master dump is put into slave inorder to make the master and slave data similar before starting the sync)after taking the dump of master import it in slave server,

# scp masterdump.sql root@192.168.1.232:

# ssh root@192.168.1.232 —–> slave machine# mysql -uroot -p < masterdump.sql

After dump is imported go in to mysql client by typing mysql. Let us tell the slave which master to connect to and what login/password to use: ( In slave machine )

# mysql -uroot -p

mysql> change master to master_host=’192.168.1.231′, master_user=’mysqlslave’, master_password=’slavepwd’;

mysql> flush privileges;

5. Let us start the slave

mysql> start slave;You can check the status of the slave by typing
mysql> show slave status;
mysql> show slave status\G

     ****************** 1. row *******************

          Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.231
Master_User: mysqlslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 706
Relay_Log_File: mysql-relay-bin.000008
Relay_Log_Pos: 446
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 706
Relay_Log_Space: 446
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.01 sec)

The last row tells you how many seconds its behind the master. Don’t worry if it doesn’t say 0, the number should be going down over time until it catches up with master (at that time it will show Seconds_Behind_Master: 0) If it shows NULL, it could be that slave is not started (you can start by typing: start slave).

6. For Checking whether replication is working or not create a db in mysql master server

# create database replication;

   and check in slave whether its replicated or not

# show databases;
the db replication will be there in slave as well