Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We've got a Virtual Server with full ssh and root access running LAMP on Ubuntu Server.

I've got a small computer in the office running ubuntu that isn't being used for anything. I thought I should use it to make and store (a copy of) our MySQL database backups. However, how do I go about backing up our databases?

MySQL Workbench locks tables to avoid errors, amongst a host of other features, but does not have any planned automatic backup bits planned. MySQL Dumper doesn't specify that they can keep the database consistent.
Source for information from this paragraph:

I don't see the point of running a php script on my server and emailing the backups when I have SSH access. Since backups are so important, I figure there must be a standard (or awesome) way of doing it I haven't found yet!

So how do I go about backing up our data? And, preferably, our users, config, etc too!

share|improve this question
up vote 0 down vote accepted

If you have MySQL installed on the Ubuntu machine, you can do the following:

Suppose IP of Virtual is

You can the following on the Ubuntu machine

mysqldump ${MYSQL_CONN} --all-databases > ${BACKUP_FILE}

You could crontab this on the Ubuntu machine as well

If you are looking for consistent mysqldumps, you could

Give it a Try !!!

share|improve this answer
I'll give this a shot! Single Transaction option is a great idea. – Sinagog Sep 19 '12 at 9:21

The script below in a file and make is exe use (chmod u+v file name) then add a crontab entry on the computer @daily file name , and it will dump and keep a weekly backup.

 eightdaysago=$(date --date='7 days ago' +%Y-%m-%d-%A)
 rm -rf /backups/$eightdaysago
 # Backup the mysql Databases
 for database in $(mysql -u b4ckup -pd1psh1t --host -e "show databases" | awk '{print $1}' | grep -v Database| grep -v information_schema | grep -v mysql | grep -v phpmyadmin | grep -v STATS)
    ssh user@ip 'mysqladmin -u (user) -p(password) flush-hosts'
    mysqldump -u (user) -p(password) --lock-tables=false --host ip $database > /backups/`date +\%Y-\%m-\%d`-`date +\%A`/$database.sql
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.