Database Replication Master Slave

This post is about how to perform to Database Replication Slave to Master. I have used the Green Font to represent Master Server and Red Font to represent Slave Server.

Step 1:CHANGING MySQL CONFIGURATION 
We need to add some extra configuration properties in the my.ini/my.cnf file. This step is must to perform replication.The following sub steps will describe in detail.

• Open my.ini file for windows or  my.cnf file for Linux
• Search for [mysqld]
• Add the following properties under [mysqld] section

server-id=1
log-bin=mysql-bin

• save the my.ini file and Restart the mysql services
• To check the result execute the following command in mysql console 

  show binary logs;

• If it shows logs, Binary logging has been initiated and we can configure mirroring  
• Perform the same steps on slave database.

Step 2: CRATE USER AND GRANT RELICATION AND PREVILEGES

• Create user

create user 'replication'@'%' identified by 'PASSWORD';

• Grant replication

grant replication slave on *.* to 'replication'@'%';

• Grant privileges

grant all privileges on *.* to 'replication'@'%' with grant option;

• Perform the same steps on slave database with different password

Step 3: CHECKING MASTER STATUS

show master status;

Step 4:SETTING UP THE SLAVE

CHANGE MASTER TO
-> MASTER_HOST=’192.168.2.2′,
-> MASTER_PORT=3306,
-> MASTER_USER=’replication’,
-> MASTER_PASSWORD='PASSWORD',
-> MASTER_LOG_FILE=’mysql-bin.000019′,
-> MASTER_LOG_POS= 2186;

• where
• MASTER_HOST is IP address of Master
• MASTER_LOG_FILE is credentials of master status (from Step 3)
• MASTER_LOG_POS is credentials of master status (from Step 3)
• Restart mysql from services on both master and slave

Step 5: CHECK MIRRORING

• Go to slave database execute the following command

show slave status \G;

Output:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.195.2.60
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 642
Relay_Log_File: India-Server-relay-bin.000003
Relay_Log_Pos: 409
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: 642
Relay_Log_Space: 571
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:

• Check Last_IO_Error,If it has error, do the steps provided in ERROR section
• Else replication has been performed successfully and you can check executing commands from master to check its validity.

ERROR

• ERROR#1 -> Can occur if operation is performed directly on slave instead of master.
• ERROR#2 -> Can occur if there is a problem in network.
• For ERROR#1 drop the replication user, flush the binary logs and restart the mysql server on both master and slave servers.

drop user 'replication'@'%';
flush binary logs;

• Now proceed the same procedure again from Step 1

Note:  If two servers are not of same architecture(i.e One server is of 64 bit and another is of 32 bit machine). We should select only 64 bit machine for Master and 32 bit machine for Slave.