Backup MySQL databases with mysqlhotcopy

Posted by sebastian on Mon 19 Dec 2005 at 14:33

If you want to make a hotcopy from your SQL database instead of a dump to a text file, you can use the mysqlhotcopy tool. This tool locks a table, copy it and than unlocks it again.

Use this command if you want to transfer the MySQL data to an other place on the same server.

mysqlhotcopy --user={username} --password={password} {database} {path}

Use this command if you want to transfer the MySQL data to an other server with SSH.

mysqlhotcopy --method=scp --user={username} --password={password} {database} {ssh-username}@{ssh-server}:{path}

You can create a script to automate this action for in a cron-job. Be sure you have SSH key-files for logging in without password.

#!/bin/bash
BACKUP_SERVER={ssh-server}
BACKUP_USER={ssh-user}
BACKUP_USER_PASS={ssh-user-pass}

MYSQL_USER={mysql-user}
MYSQL_USER_PASS={mysql-user-pass}

SERVERNAME=`hostname -s`
DAY=`/bin/date +%A`

MYSQLHOTCOPY=/usr/local/mysql/bin/mysqlhotcopy

# Change to MySQL data directory
cd /var/data/mysql

# Create an array with all directories in the MySQL data directory
# The directory name presents the database name in MySQL.
ARRAY=`find * -type d -prune`

for DATABASE in `echo $ARRAY`
do
    # Create remote directory on backupserver
    ssh `echo $BACKUP_USER`@`echo $BACKUP_SERVER` mkdir -p /backup/$SERVERNAME/$DAY/mysql/$DATABASE

    # Create backup from database
    $MYSQLHOTCOPY --method=scp --user=`echo $MYSQL_USER` --password=`echo $MYSQL_USER_PASS` \
        $DATABASE `echo $BACKUP_USER`@`echo $BACKUP_SERVER`:/backup/$SERVERNAME/$DAY/$DATABASE
done

This is not my production script. That is a bigger script. This is a simplified version. I'd appreciate any suggestions on it.

This article can be found online at the Debian Administration website at the following bookmarkable URL (along with associated comments):

http://www.debian-administration.org/article/Backup_MySQL_databases_with_mysqlhotcopy