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.
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 | Reply to this comment ]
[ Parent | Reply to this comment ]
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 | Reply to this comment ]
[ Send Message | View sebastian's Scratchpad | View Weblogs ]
Cheers, Sebastian
[ Parent | Reply to this comment ]
--
Debian GNU/Linux on an IBM Thinkpad T43p
[ Parent | Reply to this comment ]
You are off the edge of the map, mate. Here there be monsters!
[ Parent | Reply to this comment ]
I suppose it will also work on NDB.
Pau Tallada.
[ Parent | Reply to this comment ]
;-)
[ Parent | Reply to this comment ]
http://www.debianhelp.co.uk/mysqlscript.htmI think this is the easiestway i found to take backup of mysql database
[ Parent | Reply to this comment ]
[ Parent | Reply to this comment ]
Could you please show us how to restore this backup?
My best regards!
P.
[ Parent | Reply to this comment ]
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 | Reply to this comment ]
[ Parent | Reply to this comment ]
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 | Reply to this comment ]
[ Parent | Reply to this comment ]