How to Fix Broken MySQL Replication

Posted on by

There are some instances that mysql replication are not synching. If we check the status of the slave we could see something as:

[root@burnzdb2 ~]# mysql -p`cat /etc/.mysqlpw ` -e "show slave status\G;"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.10.217
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000035
          Read_Master_Log_Pos: 3013253
               Relay_Log_File: database-new-relay-bin.000012
                Relay_Log_Pos: 22789
        Relay_Master_Log_File: mysql-binlog.000035
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:

To fix this issue we simply.
1. Copy and restore the database of Master to Slave
2. Fix the point of binlogs for proper replication

Procedure
ON Master DB(burnzdb1)
1. Install hcp module if its not intalled yet:

[root@burnzdb1 ~]# wget http://repo.r1soft.com/binaries/hotcopy/idera-hotcopy.zip
--01:26:38--  http://repo.r1soft.com/binaries/hotcopy/idera-hotcopy.zip
Resolving repo.r1soft.com... 198.64.248.201
Connecting to repo.r1soft.com|198.64.248.201|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7388294 (7.0M) [application/zip]
Saving to: `idera-hotcopy.zip'
100%[======================================================================================================================================================================>] 7,388,294   4.65M/s   in 1.5s
01:26:40 (4.65 MB/s) - `idera-hotcopy.zip' saved [7388294/7388294]

2. Unzip the downloaded package:

[root@burnzdb1 ~]# unzip idera-hotcopy.zip
Archive:  idera-hotcopy.zip
  inflating: idera-hotcopy-i386-5.0.0.tar.gz
  inflating: idera-hotcopy-x86_64-5.0.0.tar.gz
  inflating: idera-hotcopy-5.0.0.i386.rpm
  inflating: idera-hotcopy-5.0.0.x86_64.rpm
  inflating: idera-hotcopy-amd64-5.0.0.deb
  inflating: idera-hotcopy-i386-5.0.0.deb
  inflating: Installing+Hot+Copy.html

We need to install the proper package for our server kernels, we need to identify what server is using:

[root@burnzdb1 ~]# uname -a
Linux burnzdb1.thesysad.com 2.6.18-128.2.1.el5 #1 SMP Tue Jul 14 06:36:37 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

[root@db1 ~]# rpm -iv idera-hotcopy-5.0.0.x86_64.rpm
Preparing packages for installation...
        package idera-hotcopy-5.0.0-18277.x86_64 is already installed
		
[root@burnzdb1 ~]# hcp-setup --get-module
Checking for binary module
Waiting                       |
No binary module found
Gathering kernel information
Gathering kernel information complete.
Creating kernel headers package
Checking '/lib/modules/2.6.18-128.2.1.el5/source/' for kernel headers
Found headers in '/lib/modules/2.6.18-128.2.1.el5/source/'
Compressing...
uploading kernel package                                                                                                                                                      99% 4548KB 276.6KB/s   00:00 ETA
Starting module build...
Complete.
Saving kernel module to '/lib/modules/r1soft/hcpdriver-cki-2.6.18-128.2.1.el5.ko'
Kernel module is now installed.

3. Lets assumed that the mysql password is stored in /etc/.mysqlpw
Login to mysql and start doing a hot copy..

[root@burnzdb1 ~]#  mysql -p`cat /etc/.mysqlpw`
mysql> FLUSH TABLES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> q\

[root@burnzdb1 ~]# hcp /dev/sda3
- You will see the following:
R1Soft Hot Copy    4.2.1 build 17791 (http://www.r1soft.com)
Documentation      http://wiki.r1soft.com
Forums             http://forum.r1soft.com

Thank you for using Hot Copy!
R1Soft makes the only Continuous Data Protection software for Linux.

Starting Hot Copy: /dev/sda3.
Changed blocks stored: /.r1soft_hcp_sda3.cow_hcp1
Snapshot completed: 0.001 seconds
File system frozen: 0.004 seconds
Hot Copy created: Tue Nov 01:44:52 EST 2012
Creating hotcopy snaphost device: /dev/hcp1, Please wait...

Hot Copy created at: /dev/hcp1
Mounting /dev/hcp1 read-write
Hot Copy mounted at: /var/hotcopy/sda3_hcp1

4. If you check your file system you should have the following:

[root@burnzdb1 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             140G   59G   82G  42% /
/dev/sda1              93M   21M   68M  24% /boot
tmpfs                 7.9G     0  7.9G   0% /dev/shm
/dev/hcp1             140G   58G   83G  42% /var/hotcopy/sda3_hcp1

5. Check the current Master Status 

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000149 |  4712841 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

– Take note of the binlog position
6. Unlock tables

mysql> unlock tables;

7. Rsync the data to SLAVE server but before doing it you need to stop, reset the mysql on slave and remove the /var/lib/mysql if you want to backup that you move it somewhere else

[root@burnzdb1 ~]# rsync -avPz /var/hotcopy/sda3_hcp1/var/lib/mysql root@172.20.10.218:/var/lib/

8. After that we need to unmount the hot copy device

[root@burnzdb1 ~]# hcp -r /dev/hcp1
R1Soft Hot Copy    4.2.1 build 17791 (http://www.r1soft.com)
Documentation      http://wiki.r1soft.com
Forums             http://forum.r1soft.com

Thank you for using Hot Copy!
R1Soft makes the only Continuous Data Protection software for Linux.

Hot Copy Session has successfully been stopped.

All active Hot Copy sessions have been stopped. It is now safe to restart the R1Soft Backup Agent.

ON Slave DB(burnzdb2)
Since rsync is done we need to configure the replication
1. Stop, reset, follow the log position of master and start mysql

mysql> stop slave;
mysql> reset slave;
mysql> CHANGE MASTER TO MASTER_HOST='172.20.10.217', MASTER_USER='repl', MASTER_PASSWORD='somepassword',  MASTER_LOG_FILE='mysql-bin.000149', MASTER_LOG_POS=4712841;
mysql> start slave;

2. Check the slave status it should have the following

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.10.217
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000149
          Read_Master_Log_Pos: 97169642
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 92457052
        Relay_Master_Log_File: mysql-bin.000149
             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: 97169642
              Relay_Log_Space: 92457203
              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:

Replication should be good!

Spread the love

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.