Mysql Replication Document

MYSQL REPLICATION &  DISASTER RECOVERY DOCUMENT.

Table of Index

1.MySQL Replication Overview

2.Replication Monitoring

3 Replication Infrastructure

4.MySQL Replication Installation

5.Fail Over Configuration Procedure

6.Restoration Procedure

1.MySQL Replication Overview Replication enables data from one MySQL databases server (called Master) to be replicated to one or more database server (called Slave). Replication in Mysql features is a one-way, asynchronous replication, in which one server act as a master and one or more as a slave. Replication can be used for three reasons. 1. STANDBY DATABASE SERVER - if the primary server fails, the standby can step in, take over, and immediately be current. 2. SCALE-OUT SOLUTIONS - spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. 3. BACKUP DATABASES – without having to bring down or lock out the master server. Backups are done on the slave, rather than on the master. Below Diagram Represent the

Master

Read / Write

Master / Slave MySQL replication.

Slave

Master-Slave Relationship Replication requires at least two servers. The servers are set up so the first server called master, enter into the relationship with the other server, called the slave. Periodically the latest changes to the database on the master server are transfered to the slave server. Updates are transferred from the master to the slave via the master's binary logs. Replication is based on the concept that the master keeps track of the changes to the database through the binary logs and the slave updates its copy of the database by executing the changes recorded on the same logs. Thus binary logging must be enabled on the master for replication to take place. Replication Threads. Three threads are involved in Replication: One on the master and two on the slave. 1. The I/O thread on the slave connects to the master and requests the binary update log. The Binary log dump thread on the master sends the binary update log to th slave on request. 2. Once on the slave, the I/O thread reads the data sent by the master and copies it to the relay log in the data directory. 3. The third thread, also on the slave, is the SQL Thread, which read and executes the queries from the relay log to bring the slave in alignment with the master.

Master

Slave

SQL thread  (slave)

Binary  Update Log

Binlog dump thread (master) I / O thread  (slave)

Relay Log

2.Replication Monitoring Description: The below commands are to be used to monitor the replication status between the master and slave. Open the command prompt and execute as follows: In Slave Server: [root@slavetest ~]# mysql -u root -p Enter password: xxxxx (Please consult MySQL Administrator/IT Manager) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 250 Server version: 5.0.54a-enterprise-gpl-log MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> The show slave status statement command will provides information about the slave mysql> show slave status\G

server status.

The Show processlist statement display information about the threads on the server. It can be obtained status information on both the master and the slave. For each thread, the output is shown in various columns. mysql>show processlist\G [root@mastertest ~]# mysql -u root -p Enter password: xxxxx (Please consult MySQL Administrator/IT Manager) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 250 Server version: 5.0.54a-enterprise-gpl-log MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> The show master status statement display information about the current status of the binary log file name and also log position of the master server. mysql> show master status; mysql>show processlist\G The Show processlist statement display information about the threads on the server. It can be obtained status information on both the master and the slave. For each thread, the output is shown in various columns.

3.Replication Infrastructure MySQL Version  Both master and slave should be the same version. Otherwise replication will be improper. Network Configuration Settings Master Server IP Address : 192.168.1.81  Slave Server Primary IP Address (Ethernet eth0) : 192.168.1.236 Slave Server Secondary IP Address (Ethernet eth1) : 192.168.1.81 (By default it is disabled) 

4.MySQL Replication Installation  Step1:  Install MySQL on master 1 and slave 1. configure network services on both system, like  Master 1/Slave 2 IP: 192.168.1.81 Master 2/Slave 1 IP : 192.168.1.236 Step2: On Master 1, make changes in my.cnf: [mysqld] log­bin=mysql­bin binlog­do­db=  # input the database which should be replicated or ignore this  command             to replicate all the databases. binlog­ignore­db=mysql              # input the database that should be ignored for replication  server­id=1 auto_increment_increment=2 auto_increment_offset=1 Step 3: On master 1, create a replication slave account in mysql. mysql> grant replication slave on *.* to 'replication'@192.168.1.236 identified by 'slave'; Restart the mysql master1.

Step 4: Now edit my.cnf on Slave1 or Master2 : [mysqld] server­id=2 master­host = 192.168.1.81 master­user = replication master­password = slave master­port = 3306

Step 5: Restart th MySQL Slave1. Login to the MySQL command prompt and start the slave replication. [root@Slavetest ~]# mysql -u root -p Enter password: xxxxx (Please consult MySQL Administrator/IT Manager) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 250 Server version: 5.0.54a-enterprise-gpl-log MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

mysql> start slave; mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.81 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000018 Read_Master_Log_Pos: 2953 Relay_Log_File: slavetest-relay-bin.000065 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000018 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: 2953 Relay_Log_Space: 235 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.00 sec) mysql>

Above highlighted rows must be indicate related log files and Slave_IO_Running and  Slave_SQL_Running: must be to YES. Step 6: On Master 1: [root@TESTDB~]# mysql -u root -p Enter password: xxxxx (Please consult MySQL Administrator/IT Manager) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 250 Server version: 5.0.54a-enterprise-gpl-log MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> mysql> show master status; +------------------------------------+-----------+--------------------+------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB| +------------------------------------+-----------+--------------------+------------------------+ |MysqlMYSQL01-bin.000008| 410 | | mysql | +------------------------------------+-----------+--------------------+------------------------+ 1 row in set (0.00 sec) The above scenario is for master-slave,

now we will create a slave master scenario for the same systems and it will work as master master.

Step 7: Edit on Master 2/ Slave 1, edit my.cnf and master entries into it: [mysqld] log­bin=mysql­bin                   #information for becoming master added binlog­ignore­db=mysql   Step 8: Create a replication slave account on master2 for master1: mysql> grant replication slave on *.* to 'slavereplication'@192.168.16.81 identified by 'slave';

Step 9:

Edit my.cnf on master1 for information of its master. [mysqld] auto_increment_increment=2 auto_increment_offset=1 #information for becoming slave. master­host = 192.168.16.236 master­user = slavereplication master­password = slave master­port = 3306  master_connect_retry=60

Step 10: Restart both mysql master1 and master2.

Step 11:Montior Mysql Replication status 

5.Fail Over Configuration Procedure

When mysql master server fails. Follow this document to convert slave server as a new master server.

Eth0 192.168.1.236

Master

Current Master

Slave

Eth1 192.168.1.81

Read / Write

Web Users / Clients

Step1: Login To Slave Server Using Secure Shell in Linux or Putty tool [root@TESTDB ~]# ssh 192.168.1.236 [email protected]'s password: xxxxx Last login: Tue Oct 7 15:30:41 2008 from 192.168.1.81 [root@slavetest ~]# or Use Putty Tool in Windows Enter the slave IP address and user name and password After login into slave machine proceed the below steps.

Step2: Connect the Ethernet Interface cable.

Step3: Enable the secondary Ethernet interface [root@slavetest ~]# ifup eth1

Step4: Login into Mysql [root@slavetest ~]# mysql -u root -p Enter password: xxxxx (Please consult MySQL Administrator/IT Manager) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 250 Server version: 5.0.54a-enterprise-gpl-log MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> You Will get Welcome message and mysql prompt as show above.

Step5: Check the Slave Status mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.81 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000018 Read_Master_Log_Pos: 2953 Relay_Log_File: slavetest-relay-bin.000065 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000018 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: 2953 Relay_Log_Space: 235 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.00 sec) mysql> Above Result will display Slave Replication status.

Make sure that the slave have processed any statements in their relay log. On slave, issue STOP SLAVE IO_THREAD, then check the output of SHOW PROCESSLIST until you see Has read all relay log; waiting for the slave I/O thread to update it. When this is true for all slaves, they can be reconfigured to the new setup.

mysql> Stop Slave io_thread; mysql> show processlist\G *************************** 1. row *************************** Id : 203 User: slavereplication Host: 192.168.1.81:59795 db : NULL Command: Binlog Dump Time: 158086 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** Id: 230 User: system user Host: db: NULL Command: Connect Time: 152220 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL *************************** 3. row *************************** Id: 251 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist 3 rows in set (0.00 sec) mysql>

Step6:Making Slave as Master server mysql> show master status; +----------------------+----------+-------------------+-----------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------------------+----------+-------------------+-----------------------------+ | mysql-bin.000006 | 783 | | mysql,mysql | +----------------------+----------+-------------------+-----------------------------+ 1 row in set (0.00 sec) mysql> (Note : This is very important make a note down of mysql log file and position when the slave becomes master). mysql> stop slave; This command will stop the slave replication. Now it is ready to serve as a master , start the jboss application in the server.

6.Restoration Procedure Note: Bring down the secondary Ethernet interface eth1 down in slave server before your plan to restore. Now once the original master server problem has been fixed and making it as live server. When Master is up again, you must issue the CHANGE MASTER, so that Master becomes a slave of S1 and picks up each Web Client writes that it missed while it was down. Step1: Connect to the Current Master server [root@TESTDB ~]# ssh 192.168.1.236 [email protected]'s password: xxxxxxx Last login: Tue Oct 7 15:30:41 2008 from 192.168.1.81 [root@mastertest ~]# or Use Putty Tool in Windows Enter the slave IP address and user name and password After login into machine proceed the below steps. Step 2: Unplug the secondary Ethernet Interface in current Master. Step3: Disable the secondary Ethernet Interface in current Master. [root@mastertest ~]# ifdown eth1 Now Boot the Original Master Server and if the Ethernet cable is UN-plugged. Plug the Ethernet Interface in the Original Master server. Step4: Login To Original_Mater Server Using Secure Shell in Linux or Putty tool [root@TESTDB ~]# ssh 192.168.1.81 [email protected]'s password: xxxxxxx Last login: Tue Oct 7 15:30:41 2008 from 192.168.1.81 [root@mastertest ~]# or Use Putty Tool in Windows Enter the slave IP address and user name and password After login into machine proceed the below steps.

Step5: Login into Mysql

[root@slavetest ~]# mysql -u root -p Enter password: xxxxx (Please consult MySQL Administrator/IT Manager) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 250 Server version: 5.0.54a-enterprise-gpl-log MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> You Will get Welcome message and mysql prompt as show above. Step6: Configure Old_master as a Slave Server of Current_Master Server mysql>change master to >master_host='192.168.1.236', (slave server ip address) >master_user='slavereplication', (slavereplication-mysql replication user created in the slave server) >master_password='slave', (slave- mysql replication user password created in the slave server) >master_log_file='mysql-bin.0000xx', (xx: position of current_master server log file name as shown in show master status, at the time of making slave as master) > master_log_pos=xx; (xx:position of current_master log position as shown in show master status, at the time of making slave as master) mysql> mysql>start slave; Now it will get update, which are missed during the failure. Once all update has been finished make master a master again. Step7: To make Master a master again (because it is the most powerful machine, for example), use the preceding procedure as if Slave 1 was unavailable and Master was to be the new master. [root@mastertest ~]# service mysql stop [root@mastertest ~]# cd /var/lib/mysql [root@mastertest ~]# mv master.info relay-log.info servername-relay.bin* /root [root@mastertest ~]# service mysql start Step8: Making original Slave as a slave replication server [root@slavetest ~]# mysql -u root -p Enter password: xxxxx (Please consult MySQL Administrator/IT Manager) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 250 Server version: 5.0.54a-enterprise-gpl-log MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> start slave; Step9: Monitor the Replication