Mysql Master Slave Replication

Setting the Replication Master Configuration

 On a replication master, you must enable binary logging and establish a unique server ID.

Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves.

If binary logging is not enabled, replication will not be possible.

Each server within a replication group must be configured with a unique server ID.This ID is used to identify individual servers within the group.

To configure the binary log and server ID options, you will need to shut down your MySQL server and edit the my.cnf or my.ini file.  Add the following options to the configuration file within the [mysqld] section.

to enable binary logging using a log file name prefix of mysql-bin, and configure a server ID of 1, use these lines:

[mysqld]
log-bin=mysql-bin
server-id=1

After making the changes, restart the server.

If you omit server-id (or set it explicitly to its default value of 0), a master refuses connections from all slaves.

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file.

Ensure that the skip-networking  option is not enabled on your replication master. If networking has been disabled, your slave will not able to communicate with the master and replication will fail.

if your master is also a slave (DB1 is the master of DB2, DB2 is the master of DB3) in order for DB2 to log updates from his master DB1 to the binlog (so DB3 can read them) you need to put "log-slave-updates" on my.cnf or my.ini.

Replication Implementation

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

Slave 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