Friday, May 17, 2013

Database Replication Master-Slave

 


Database Replication Master-Slave
10 simple steps for creating Database replicationWe need 2 linux systems with mysql installed. One system we call as the master and other we call the slave. Slave will read data from master, there by keeping a replica of data of master in slave and prevents dataloss. Another advantage is that , if master is down, slave can act as masterA. MASTER SET UP

1. Edit /etc/my.cnf and enter the following details under mysqld
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id = 1
log-bin=db-bin
relay-log=db-relay-bin

2.enter into the mysql database

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.10.2.24' IDENTIFIED BY 'password';

Suppose that your slave server is '10.10.2.24' and that you want to create an account with a user name of 'replication' such that slave servers can use the account to access the master server using a password of 'password'.

3.mysql> FLUSH TABLES WITH READ LOCK;
Flush all the tables and block write statements

4.mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| db-bin.000005 | 792 | | |
+---------------+----------+--------------+------------------+

OR
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
File: db-bin.000005
Position: 792
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

So MASTER Side configuration has over ..
mysql> UNLOCK TABLES;

B SLAVE SERVER CONFIGURATION

1. Edit the file /etc/my.cnf and enter the details as follows
[mysqld]
server-id=2
master-host = 10.10.3.21
master-user = replication
master-password = password
master-port = 3306

10.10.3.21 is the MASTER server IP.

These information will be stored to /var/lib/mysql/master.info

Enter into the Mysql server
2. mysql> stop slave;
3. mysql> reset slave;
4. mysql> CHANGE MASTER TO MASTER_HOST='10.10.3.21', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='db-bin.000005';

Here 10.10.3.21 is your MASTER Server IP 'replication' is the master user 'password' is the password for your master server and 'db-bin.000005' is the Master log file this one you wll get while running the command mysql> SHOW SLAVE STATUS \G; in Master server

5. mysql> START SLAVE;

6. mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.3.21
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db-bin.000005
Read_Master_Log_Pos: 792
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 934
Relay_Master_Log_File: db-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Last_Errno: 0

Exec_Master_Log_Pos: 792
Relay_Log_Space: 1090
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No

Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

The process:- In Master Server(Alter,Delete) all details execute and 'I/O Thread' save to a log file called Binary Log(ie,db-bin.000005) And in slave(only Read) read the changes from Binary log to relay log and update to slave SQL Thread.

You can verify by checking the Seconds_Behind_Master details and Slave_IO_Running and Slave_SQL_Running

No comments:

Post a Comment