How to automatically backup ALL mysql databases into sql statement?

Question

MySQL Administrator> Backup Project. It is a great tool to allow you to select databases, and schedule it.

However my issue is:

Today got a few new websites (new database created) Tomorrow got a few more new websites (new databases created)

In this case, I have to always go into Backup Project> Select those remaining schema not in the backup list...

How to make it auto check for any new databases and include in the backup schedule list?

In other word, how to automate the backup of all the mysql databases (so that we don't need to worry when there's new databases created everyday).

Any way to make it happen?

I'm using Windows 2008 server, which is not sh friendly.

Solution 1

MySQL Administrator

With MySQL Administrator you need to manually add databases to the backup.

mysqldump and --all-databases

If you approach this through a dump-command, you can use the --all-databases option to include all databases into the dump.

Solution 2

The problem with the three answers posted so far is that they do not enable you to selectively restore the databases. This can be a real problem in all but a catastrophe.

Ideally, you should have a daily backup, with some history. It should be bulletproof (--force), it should be logged (>> ...log), it should be compressed (| gzip), it should keep separate copies of each database, and it should automatically pick up any databases that are added.

Consider, rather, a shell script like this:

#!/bin/bash

Host=server.domain.com
BDir=/home/backup/backup/mysql

Dump="/usr/bin/mysqldump --skip-extended-insert --force"
MySQL=/usr/bin/mysql

Today=$(date "+%a")

# Get a list of all databases
Databases=$(echo "SHOW DATABASES" | $MySQL -h $Host)


for db in $Databases; do
        date=`date`
        file="$BDir/$Host-$db-$Today.sql.gz"
        echo "Backing up '$db' from '$Host' on '$date' to: "
        echo "   $file"
        $Dump -h $Host $db | gzip > $file
done

Which is assuming that you have a file ~/.my.cnf (chmod 600), that has:

[client]
user = "BACKUP"
password = "SOMEPASS8342783492"

Make sure that whatever user you are using for BACKUP has this grant statement:

GRANT 
  SELECT, SHOW VIEW ON *.* 
  TO BACKUP@localhost 
  IDENTIFIED BY 'SOMEPASS8342783492';

So simply add this to a nightly cronjob, and you have a daily backup that rotates each 7 days week.

0 3 * * *   backup-mysql >> backup-mysql.log 2>> backup-mysql.log

---

The backup directory then contains:

-rw-r--r-- 1 backup backup 2217482184 Sep  3 13:35 base.appcove.net-VOS4_0-20090903.sql.gz
-rw-rw-r-- 1 backup backup 2505876287 Dec 25 00:48 base.appcove.net-VOS4_0-Fri.sql.gz
-rw-r--r-- 1 backup backup 2500384029 Dec 21 00:48 base.appcove.net-VOS4_0-Mon.sql.gz
-rw-r--r-- 1 backup backup 2506849331 Dec 26 00:48 base.appcove.net-VOS4_0-Sat.sql.gz
-rw-r--r-- 1 backup backup 2499859469 Dec 20 00:48 base.appcove.net-VOS4_0-Sun.sql.gz
-rw-rw-r-- 1 backup backup 2505046147 Dec 24 00:48 base.appcove.net-VOS4_0-Thu.sql.gz
-rw-rw-r-- 1 backup backup 2502277743 Dec 22 00:48 base.appcove.net-VOS4_0-Tue.sql.gz
-rw-r--r-- 1 backup backup 2504169910 Dec 23 00:48 base.appcove.net-VOS4_0-Wed.sql.gz
-rw-r--r-- 1 backup backup   76983829 Dec 25 00:49 base.appcove.net-VOS4_Mail_0-Fri.sql.gz
-rw-r--r-- 1 backup backup   76983829 Dec 21 00:49 base.appcove.net-VOS4_Mail_0-Mon.sql.gz
-rw-r--r-- 1 backup backup   76983829 Dec 26 00:49 base.appcove.net-VOS4_Mail_0-Sat.sql.gz
-rw-r--r-- 1 backup backup   76983829 Dec 20 00:48 base.appcove.net-VOS4_Mail_0-Sun.sql.gz
-rw-rw-r-- 1 backup backup   76983829 Dec 24 00:49 base.appcove.net-VOS4_Mail_0-Thu.sql.gz
-rw-rw-r-- 1 backup backup   76983829 Dec 22 00:49 base.appcove.net-VOS4_Mail_0-Tue.sql.gz
-rw-r--r-- 1 backup backup   76983829 Dec 23 00:49 base.appcove.net-VOS4_Mail_0-Wed.sql.gz
-rw-r--r-- 1 backup backup  304803726 Dec 25 00:49 base.appcove.net-WeSell_0-Fri.sql.gz
-rw-r--r-- 1 backup backup  303480087 Dec 21 00:49 base.appcove.net-WeSell_0-Mon.sql.gz
-rw-r--r-- 1 backup backup  304710121 Dec 26 00:49 base.appcove.net-WeSell_0-Sat.sql.gz
-rw-r--r-- 1 backup backup  303791294 Dec 20 00:49 base.appcove.net-WeSell_0-Sun.sql.gz
-rw-rw-r-- 1 backup backup  305315415 Dec 24 00:49 base.appcove.net-WeSell_0-Thu.sql.gz
-rw-rw-r-- 1 backup backup  302516217 Dec 22 00:49 base.appcove.net-WeSell_0-Tue.sql.gz
-rw-r--r-- 1 backup backup  303314217 Dec 23 00:49 base.appcove.net-WeSell_0-Wed.sql.gz
-rw-r--r-- 1 backup backup     135301 Dec 25 00:30 dc40.appcove.net-mysql-Fri.sql.gz
-rw-r--r-- 1 backup backup     135301 Dec 21 00:30 dc40.appcove.net-mysql-Mon.sql.gz
-rw-r--r-- 1 backup backup     135301 Dec 26 00:30 dc40.appcove.net-mysql-Sat.sql.gz
-rw-r--r-- 1 backup backup     135301 Dec 20 00:30 dc40.appcove.net-mysql-Sun.sql.gz
-rw-rw-r-- 1 backup backup     135301 Dec 24 00:30 dc40.appcove.net-mysql-Thu.sql.gz
-rw-rw-r-- 1 backup backup     135301 Dec 22 00:30 dc40.appcove.net-mysql-Tue.sql.gz
-rw-r--r-- 1 backup backup     135301 Dec 23 00:30 dc40.appcove.net-mysql-Wed.sql.gz

Solution 3

mysqldump -u <username> -p<password> --all-databases > database_backup.sql

Solution 4

Late answer, but as simple as it gets:

1. You need to change lines 3, 4 and 5 to reflect your MySQL user, password and dir where you want to store the dumps.
2. Every time it runs, it’ll delete all the previous backups (If you don’t want this, just comment #rm "$OUTPUT/*gz" > /dev/null 2>&1)

MySqlBackup.sh

#!/bin/bash
 
USER="your_user"
PASSWORD="your_password"
OUTPUT="/path/to/backup/dir"
 
rm "$OUTPUT/*gz" > /dev/null 2>&1
 
databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
 
for db in $databases; do
    if [[ "$db" != "information_schema" ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/`date +%Y%m%d`.$db.sql
        gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

---

---

Make it executable and run it:

chmod 700 MySqlBackup.sh
./MySqlBackup.sh

If needed, add it to the crontab so it’ll run automagically:

crontab -e
00 02 * * * /path/to/MySqlBackup.sh

In this case, it’ll run every day at 2 AM. You can learn more about crontab here.

src

Solution 5

I've been using http://sourceforge.net/projects/automysqlbackup/ to backup my MySQL databases for a couple years now and it's worked very well for me. Here's the description from the sourceforge page:

A script to take daily, weekly and monthly backups of your MySQL databases using mysqldump. Features - Backup mutiple databases - Single backup file or to a seperate file for each DB - Compress backup files - Backup remote servers - E-mail logs - More..

You can set the script to backup all databases so you don't have to change the script when new dbs are added. You can also tell it which dbs to exclude if you have a database or two that you don't want to backup for some reason.

It's well documented and there are lots of options that you can set that will cover most basic db backup needs. Since it's a single bash script, it's also easy to modify/tweak if something's not quite how you want it to be.

View more solutions

Comments

[gahooa]

This is really rough, because you cannot selectively restore the databases.

[Andomar]

This answer is a bit terse but definitely helpful, +1 to compensate for the downvote!

[shylent]

Yes, I am sorry for terseness, however, that's about all, that can be said about it (according to the questioneer's specification). I mean, if you need all the nitty-gritty details you can just read mysqldump's manual, right? At least that's how I work..

[MarkR]

You can selectively restore the databases using the <a href="dev.mysql.com/doc/refman/5.1/en/…"> --one-database</a> option.

[quickshiftin]

This approach is simple, but will lump all the database backups into a single file. Here is a small script which creates a separate file for each database and a bit more.

[quickshiftin]

Nice answer. Take a peak at the script I have on github which is very similar and ready for download :)

[Glenn Plas]

And the flaw in this approach is the fact that when you have a slave you cannot restore it from this backup and replay the binary logs to the point of the backup. check --master-data=2

[ahhmarr]

Will this work for restoring all database as well ?? gunzip < alldb.tar.gz | mysql -u user -p -h localhost --all-databases

[ParisNakitaKejser]

The problem is if you only do this, its will lock every table its export, are you like our company have a database on over 300gb+ you are pretty bad export. but in over all this command working fine if you get a small database :)

[Gaspa79]

@quickshiftin you can specifying the --one-database option: dev.mysql.com/doc/refman/5.7/en/…

[quickshiftin]

Though the question doesn't have a preference, mine is to have a separate backup file for each database. So even if you can restore a single one via --one-database it's not a very useful feature.

[quickshiftin]

Though the question doesn't have a preference, mine is to have a separate backup file for each database. Going the --all-databases/--one-database route puts hard (read: crippling) requirements around how the backups are to be stored. Checkout my script for a clean approach.

[quickshiftin]

That's right Glenn, the script is not designed for an environment with replication.

[falux]

Works great, thanks, I just had to add HOST=my.host.com and --host=$HOST where necessary.