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!