Repair MYSQL Master Slave Replication

One of the interesting parts of living on a boat is probably something that most people never think of dealing with. Power Management! Since we only have 30 amps of power available to draw at any one time we have to be quite careful on what is running. On those times that we aren't so careful and the breaker trips my Raspberry Pi running weewx loses power and the MYSQL connection to the web server is lost. This can lead to a corrupted database on the web server that will not work until the errors are cleared. One of the simplest ways that I found was to dump the MYSQL database on the Pi and then use that dump to correct the web server's slave copy. After doing this numerous times I decided there had to be a faster way of doing it instead of typing it out command by command. The following scripts are what I came up with and fix the database problems with running ONE command from the raspberry pi!

Change the -u(user) and "SuperSecretPass" to whatever your MYSQL credentials are.

I also used an identity file (passwordless login) for the scp command using a ssh key pair.

Script that runs on the Raspberry Pi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#!/bin/bash

#Stop weewx so we dont miss anything
service weewx stop

#Lock tables and set the current master log file and position into variables for use later
mysql -u root -p"SuperSecretPass" -e 'FLUSH TABLES WITH READ LOCK' weewx;
FILE=$(mysql -u root -p"SuperSecretPass" -e "SHOW MASTER STATUS;" -AN | awk '{print $1}')
POS=$(mysql -u root -p"SuperSecretPass" -e "SHOW MASTER STATUS;" -AN | awk '{print $2}')
echo $FILE > /tmp/file
echo $POS > /tmp/pos

#Dump the databases to file
mysqldump -u root -p"SuperSecretPass" weewx > /tmp/weewx.sql
mysqldump -u root -p"SuperSecretPass" weewx_raw > /tmp/weewx_raw.sql

#Unlock tables and restart the weewx service
mysql -u root -p"SuperSecretPass" -e 'UNLOCK TABLES' weewx;
service weewx start

#Copy current databases to remote server
scp -C -i /root/.ssh/www_RPi.key /tmp/weewx.sql weewx@andrewhofmans.com:/tmp/
scp -C -i /root/.ssh/www_RPi.key /tmp/weewx_raw.sql weewx@andrewhofmans.com:/tmp/
scp -C -i /root/.ssh/www_RPi.key /tmp/file weewx@andrewhofmans.com:/tmp/
scp -C -i /root/.ssh/www_RPi.key /tmp/pos weewx@andrewhofmans.com:/tmp/

#Execute the script on webserver
ssh -i /root/.ssh/www_RPi.key USER@andrewhofmans.com "sudo /home/USER/reset_database.sh"

#Clean up
rm /tmp/*.sql
rm /tmp/file
rm /tmp/pos

How to run sudo command without password

In the above script the "ssh -i /root/.ssh/www_RPi.key USER@andrewhofmans.com "sudo /home/USER/reset_database.sh" is able to be run as sudo without requiring the user to enter a password. To allow this action append 

weewx   ALL=(ALL) NOPASSWD: /home/weewx/reset_database.sh

to your /etc/sudoers file.

Raspberry Pi runs this script on the webserver

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
#!/bin/bash

FILE=$(cat /tmp/file);  
POS=$(cat /tmp/pos);

mysql -uroot -p"SuperSecretPass" -e 'STOP SLAVE';  
pv /tmp/weewx.sql | mysql -uroot -p"SuperSecretPass" weewx  
pv /tmp/weewx_raw.sql | mysql -uroot -p"SuperSecretPass" weewx_raw  
mysql -uroot -p"SuperSecretPass" -e "CHANGE MASTER TO Master_Log_File='$FILE', master_log_pos=$POS;"  
mysql -uroot -p"SuperSecretPass" -e "START SLAVE;"  
mysql -uroot -p"SuperSecretPass" -e "SHOW SLAVE STATUS\G"  
rm /tmp/*.sql  
rm /tmp/file  
rm /tmp/pos