Script Hooks: How to Protect MySQL with Retrospect

Hooks

Mysql logo

MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more.

With Retrospect, you can protect your live, running MySQL instance with no downtime. We’ll walk through how to do it.

By default, MySQL stores data at /var/lib/mysql on Linux and Mac systems and at C:\ProgramData\MySQL\MySQL Server 5.6\data on Windows systems. Alternatively, you can specify the location in my.ini.

To protect the underlying data while your MySQL instance is running, we will use Retrospect’s Script Hooks to lock the database from writes before the backup using the StartSource trigger and unlock the database after the backup completes using the EndSource trigger. The database is still readable during this time, but all writes are prevented. You can selectively protect only the MySQL data store on the system by using a rule/selector to only include the data store path.

Backup Steps

To protect MySQL, Retrospect or Retrospect Client needs to be running on the same system.

  1. Download Retrospect_for_Windows_External_Scripts.zip and place the MySQL script hook at the appropriate location:

    • Retrospect for Windows: C:\ProgramData\Retrospect

    • Retrospect for Mac: /Library/Application Support/Retrospect

    • Retrospect Client for Windows: C:\Program Files\Retrospect\Retrospect Client

    • Retrospect Client for Mac: /Library/Application Support/Retrospect Client/retroeventhandler

  2. In the RetroEventHandler file, replace the hostname, username, and password with the appropriate values for your environment. Below is the relevant section:

    function StartSource {
    /bin/rm -f /tmp/retrospectmysqlpipe
    /usr/bin/mkfifo /tmp/retrospectmysqlpipe
    # Note that we use 3598 to be a unique number to search for when we kill the sleep during cleanup.
    /bin/sleep 3598 > /tmp/retrospectmysqlpipe &
    /usr/local/bin/mysql -B -h HOSTNAME -u USERNAME -pPASSWORD &>/dev/null < /tmp/retrospectmysqlpipe &
    echo "FLUSH TABLES WITH READ LOCK;" > /tmp/retrospectmysqlpipe

    echo
}

function EndSource {
    /bin/ps -ef | /usr/bin/awk '/[s]leep 3598/{print $2}' | /usr/bin/xargs kill
    /bin/rm -f /tmp/retrospectmysqlpipe

    echo
}

  3. Start a backup of the system where MySQL is running. You do not need to restart Retrospect for the updated file to be used.

Restore Steps

  1. Stop your MySQL instance.

  2. Restore the Retrospect backup containing the MySQL data store.

  3. Start your MySQL instance. The instance will utilize the restored data.

Last Update: 05 Set 2017