This site is now 100% read-only, and retired.

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.

 

 


Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (213.164.xx.xx) on Mon 19 Dec 2005 at 16:10
> Be sure you have SSH key-files for logging in without password.

Yikes! That would mean your server was compromised if your certificate was stolen.
Better to use ssh-agent and enter a passphrase at boot.

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (67.104.xx.xx) on Mon 19 Dec 2005 at 19:05
If you used the Passphrase at boot and your machine was compromised I don't see how the intruder would not have the same access to the remote machine anyways.

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (213.164.xx.xx) on Tue 20 Dec 2005 at 13:23
"bob" has an account which has been compromised.

Scenario 1 - passwordless keys
The thief steals his keys and has access to the remote server.

Scenario 2 - keys with passwords, but passwords help by ssh-agent
The thief must steal the keys AND find the password in memory.

It's another layer.

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by jeld (64.90.xx.xx) on Mon 19 Dec 2005 at 19:50
I have just tested mysqlhotcopy. There are a few minor disapointments, but there is at least one major one. Basically, what this program attempts to do is physically copy the storage files for the database. This works fine, for the MyISAM storage engine, which stores each table in a separate file, but for InnoDB (a more effective storage engine storing data separately from the structure) this doesn't work and neither it works for the ndb cluster storage which stores all the DB info in memory. More then that, when I tried mysqlhotcopy on my DB server, it ran for a few seconds and proudly reported that it backed up my entire 2GB database in under 30 seconds. That was hard to believe, so I double checked, and found that only the contents of the DB directory in the MySQL data dir was copied over. But there was no indication from the program itself that something went wrong. At this point I stopped testing and figured that this is by no means a solution for backing up anything remotely resembling a production database.
You are off the edge of the map, mate. Here there be monsters!

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (80.58.xx.xx) on Mon 19 Dec 2005 at 22:50
For dumping InnoDB databases, try mysqldump --single-transaction

I suppose it will also work on NDB.

Pau Tallada.

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (212.202.xx.xx) on Tue 20 Dec 2005 at 05:40
try the wrapper for mysqldump i wrote at: http://pol.spurious.biz/projects/scripting/mysqlblasy.php

;-)

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (195.14.xx.xx) on Tue 20 Dec 2005 at 08:30
you can check this automatic backup script for mysql
http://www.debianhelp.co.uk/mysqlscript.htm
I think this is the easiestway i found to take backup of mysql database

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (86.135.xx.xx) on Wed 21 Dec 2005 at 22:47
good website link cheers

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (66.225.xx.xx) on Tue 14 Apr 2009 at 17:20
How about RTM before commenting:

"mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare."

Once more:

"mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables."

[ Parent ]

How to restore mysqlhotcopy backup?
Posted by ptecza (193.0.xx.xx) on Tue 20 Dec 2005 at 09:52
Hi!

Could you please show us how to restore this backup?

My best regards!

P.

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by trakic (85.81.xx.xx) on Mon 9 Jan 2006 at 21:08
[ View Weblogs ]
@My_run:


admir@debian:~$ ls /var/data
ls: /var/data: No such file or directory


Quickfix:

MYSQLHOTCOPY=/usr/local/mysql/bin/mysqlhotcopy
if ! [ -d /var/data/mysql ]; then mkdir -p /var/data/mysql; fi
# Change to MySQL data directory
cd /var/data/mysql
...


/Admir

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by trakic (85.81.xx.xx) on Mon 9 Jan 2006 at 21:19
[ View Weblogs ]
Ooops - My last correction relates to hosts with mysql-client package only, but can fetch mysql data through ssh ;-)

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (146.103.xx.xx) on Fri 3 Mar 2006 at 11:22
Why would you like to use SSH backup in this script, isn't this just a way to slow down the backup process? I may be wrong but isn't it better to mysqlhotbackup your database to the local filesystem and then use another script to upload this backup to the other server?

Does this make any difference on the time the database table is locked or is the backup written to the memory, table unlocked and then transferred over SSH?

Thanks!

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (116.226.xx.xx) on Wed 11 Jan 2012 at 07:54
Use SSH backup is more faster.

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (88.140.xx.xx) on Mon 28 Jan 2008 at 16:01
You'd better use .my.cnf in your admin home directory to avoid clear text mysql passwords in those scripts.

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Ulti (78.148.xx.xx) on Sun 10 Jul 2011 at 21:59
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


is there a good reason why this doesn't just read as the following?

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

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

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (108.16.xx.xx) on Tue 24 Apr 2012 at 21:52
wow - that has to be the worst bash code I've ever seen.

[ Parent ]

Re: Backup MySQL databases with mysqlhotcopy
Posted by Anonymous (188.91.xx.xx) on Tue 24 Apr 2012 at 22:04
Maybe you can rewrite it?

[ Parent ]