A few years back when being engaged in a new position, we were wondering what backup method would be used. We assumed we would be using something like Zmanda to manage backups centrally for all servers. Boy, were we in for a surprise! The enterprise company was using EMC's BCV solution! None of us were quite sure how this would integrate with MySQL!

After doing some research and talking with the "BCV guy" we learned that BCV has to establish a "pair" with a drive of the exact same size. It is a sort of running mirror of the drive for those of you who are not familiar with it. Then once the backup and the data are synced, you "break the mirror." Pretty simple idea, but how do we guarantee consistency? That was the problem!

We talked with MySQL and asked if they had support for BCV and the technician acted kind of surprised as well and informed us they didn't. Now what? We had to create a custom solution!

After a number of email exchanges, MySQL AB suggested we go with a utility called ShellSQL. While this is an awesome utility, it requires the DBA to learn one more thing and maintain it. The boss was not real happy with this solution, so on we went...

Now you may be thinking, why not just pass the "-e" option to the mysql client. While we eventually figured out a way to make that work, our first concern was that using multiple statements in this manner would not work for us.

We needed to lock all of the tables, split the BCV, and then unlock the tables from Bash. The idea was something like this:

mysql -e "FLUSH TABLES WITH READ LOCK"
./split_bcv.ksh
mysql -e "UNLOCK TABLES"

The problem naturally is that when the client disconnects after the lock, MySQL unlocks all of the tables, thus impairing our ability to guarantee a consistent backup.

The solution for us however was in MySQL's "system" command! We just had to string all of the commands together into a sort of "wrapper" script using MySQL to fire the split. Below is that script:

#!/bin/sh
# bcv_backup.sh
#
# Simple Bash script to run BCV Backups
# Valcora: http://www.valcora.com
#

# Configuration
MYSQL="/usr/bin/mysql"
MYSQL_USER="backup"
MYSQL_PASSWORD="mypass"
MYSQL_SOCKET="/tmp/mysql.sock"
BCV_SCRIPT="/opt/emc/BCV_split.ksh"
CMD="FLUSH TABLES WITH READ LOCK; system '$BCV_SCRIPT'; UNLOCK TABLES;"
BACKUP_LOG="/var/logs/cps.log"

# Redirect all output to backup log
exec &>$BACKUP_LOG

echo "Beginning BCV Backups..."
echo "Began: `date`"

# Actual MySQL Client Command That Does It All
$MYSQL -vvv -u$MYSQL_USER -p$MYSQL_PASSWORD -S $MYSQL_SOCKET -e "$CMD"

echo "Ended: `date`"
echo
chown mysql:mysql $BACKUP_LOG

# Reset stdout and stderr back to screen, not logfile
exec >&-

This script would fire the split script while the tables were locked and then upon completion, would unlock the tables.

Naturally, we wanted to keep a log of the activities as well.

We ran this solution by MySQL AB and they agreed there should be no problem doing it that way!

Well, we have been running this for over two years now and not a single problem. We have had on a few occassions needed to restore from the BCV and it has always been perfect!

In case you are interested, below are examples of the BCV scripts for the establish and split:

#/bin/ksh
# BCV_split.ksh
cd /opt/emc
. ./symcli_path
symmir -g SAN_dev split -instant -nop

#!/bin/ksh
# BSV_establish.ksh
## ESTABLISH Pairs  **********************************
cd /opt/emc
. ./symcli_path
symmir -g SAN_dev est -full -nop

#!/bin/sh
. /opt/emc/symcli_path
let COUNT=0
echo Run EMC symmir command and check for return code 0
echo
while true
  do
    symmir verify -g SAN_dev
    RC=`echo $?`
    if [ $RC = 0 ]
      then
        echo
        echo
        echo "       The return code is 0, the BCVs are all established. Exit with return code 0     "
        exit 0
    fi
    sleep 600 # sleep for 10 minutes
    let COUNT=COUNT+1
    echo "  --> Pass $COUNT complete. The BCVs are not established."
    if [ "$COUNT" = "50" ]
      then
        echo
        echo
        echo "       The BCVs are not established after $COUNT passes. Exit with return code 8.     "
        exit 8
    fi
  done

echo
echo "         Unexpected error. Exit with return code 12.      "
exit 12

The configuration file is:

#!/bin/ksh
#symcli_path
PATH=$PATH:/usr/symcli/bin
export PATH
export SYMCLI_CTL_ACCESS=PARALLEL

The BCV_establish.ksh script is called an hour or so before the MySQL script is scheduled so that there is sufficient time for the drives to sync up. Then the MySQL script breaks the mirror and our backup is complete. As you can imagine, all of this is scheduled for off-hours.

Anyway, if you find yourself faced with a similar dilemma, hopefully this may give you some ideas...