Mysql Master Slave Replication

How to Mysql Master Slave Replicate 
 
Step1:- First Install Mysql Application after that we will configure /etc/my.cnf 
Step2:- Chnage on master my.cnf file for slave configuration

binlog-do-db=berkshire_new_cluster

binlog-ignore-db=berkshire_cms_live
server-id= 1

:wq!   Save file  

Step3:- Start mysql services on master server 

# /etc/init.d/mysql.server restart

Step4:- create a user on Master server for replication with Grant Replication 

# mysql -uroot -p

mysql>  create GRANT REPLICATION  SLAVE ON *.* TO 'user1'@'remotehostIP' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;   # for reload update or referesh 
mysql> FLUSH TABLES WITH READ LOCK;      # we have to restrict the write operation  on the master server by running this sql command on the master server. 

Step5:-  Now we will  take dump of  Master databases for  replication. 


# mysqldump -uroot -p berkshire_new_cluster > berkshire_new_cluster.sql
 

Step6:- Now move this dump file on slave server over SCP command like this.


# scp -r berkshire_new_cluster.sql  slave-server-username@slave-server
-ip:/home/

ON SLAVER SERVER CONFIGURATION 


Step-1: First Install Mysql Application after that we will configure
/etc/my.cnf file 

Step-2:   # vim /etc/my.cnf
server-id= 2
#master-connect-retry=60   (this line is already in file)
replicate-do-db=berkshire_new_cluster

:wq!  Save file 

Step3:- Start mysql services on Slave server 
# /etc/init.d/mysql.server restart
Step4:- Now go to Master server and check master status 

mysql -uroot -p
Enter password: **********

mysql> show master status; 
+------------------+----------+-----------------------+--------------------+ 
| File             | Position | Binlog_Do_DB          | Binlog_Ignore_DB   | 
+------------------+----------+-----------------------+--------------------+ 
| mysql-bin.000202 |      107 | berkshire_new_cluster | berkshire_cms_live | 
+------------------+----------+-----------------------+--------------------+ 
1 row in set (0.00 sec) 
Step5:- Now go to SLAVE server and execute below query for connecting master to slave.
mysql>> CHANGE MASTER TO MASTER_HOST='Master Server IP', MASTER_USER='user1', MASTER_PASSWORD='user1 passwd', MASTER_LOG_FILE='file number', MASTER_LOG_POS=position number; 
For Ex- mysql>> CHANGE MASTER TO MASTER_HOST='192.168.28.148', MASTER_USER='bi_replicator', MASTER_PASSWORD='*********', MASTER_LOG_FILE='mysql-bin.000202', MASTER_LOG_POS=107; 
mysql>> START SLAVE;
Now go to Master Server 
On master we could now release the read lock
mysql> UNLOCK TABLES;

Now go to Slave Server
To see the slave running status run the following command  on the slave  server

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.28.96
                  Master_User: user1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000042
          Read_Master_Log_Pos: 107
               Relay_Log_File: hostname-relay-bin.000042
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000042
             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: 107
              Relay_Log_Space: 556
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.06 sec)