MySql server Master Slave  replication:

Setting  master MySql server for replication:

1) Open config file:

vi /etc/my.cnf

Add the server id. This is a number and must be unique among servers.

server-id = 1

Add the binary log file location, if location is not given, default will be used by MySql.

log_bin = /var/lib/mysql/mysql-bin.log

Save the file and restart MySql.

service mysqld restart

2) Create user for MySql replication:

On master, login into MySql and create user for replication server shell by :

mysql -u root -p
password: <password>
mysql > GRANT REPLICATION SALVE ON *.* TO  'username'@'%'  IDENTIFIED BY 'password';

Then apply changes by

FlUSH PRIVILEGES;

3) Create the backup of  current databases to replicate,

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Copy file name and pid.

Do not close this window, if the window is closed or move away then MySql tables will be unlocked.

This is place from where slave will start replicating, so remmeber the filename and position.

4) Create the database backup:

cd /var/lib/mysql
tar -cvf /tmp/mysql-snapshot.tar *

Once the process is completed ,  go back to your original window and unlock the database by

UNLOCK TABLES;

Setting MySql slave server:

1) Copy backup database to slave

cd /var/lib/mysql
tar -xvf /tmp/mysql-snapshot.tar

2) Open config file and add following lines by:

vi /etc/my.cnf
server-id = 2
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
 relay-log-info-file = /var/lib/mysql/relay-bin.info

Restart the slave server by

$ service mysqld restart

3) Go to slave MySql shell and type:

mysql > CHANGE MASTER TO 
-> MASTER_HOST='IP_OF_MASTER_MYSQL',
-> MASTER_USER='username',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=  106;

Start slave MySql Server

We can check the status of replication by going to MySql shell in slave server and typing:

mysql > SHOW SLAVE STATUS\G;

*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 
 Master_User: replicate
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysqld-bin.000004
 Read_Master_Log_Pos: 144806
 Relay_Log_File: slave-relay.000004
 Relay_Log_Pos: 3430
 Relay_Master_Log_File: mysqld-bin.000004
 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: 144806
 Relay_Log_Space: 145249
 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:
1 row in set (0.00 sec)
ERROR:
No query specified