Backing up mysql databases using mysqldump is pretty quick and painless:
mysqldump -u username -ppassword -h hostname db_name > backup_file.sql
or
mysqldump -u username -ppassword -h hostname --all-databases > backup_file

The only problem is that it becomes quite cumbersome recover a particular table.

I wrote a little script to backup each table in each database to its own file:

#!/bin/bash

if [ $# -lt 2 ]
then
        echo "Usage: backup_all_tables username password [host]"
        exit
fi

mysql_bin="/usr/bin/mysql"
mysqldump_bin="/usr/bin/mysqldump"
backup_path="/opt/backups/www/"

if [ $# -eq 3 ]
then
        host=$3
else
        host="localhost"
fi

user=$1
pass=$2

for db in `echo "show databases" | $mysql_bin -u $user -p$pass -h $host | grep -v Database | grep -v information_schema`
do
        for table in `echo "show tables" | $mysql_bin -u $user -p$pass -h $host $db | grep -v "Tables_in"`
        do
                echo "Backing up $host.$db.$table to $backup_path${host}_$db.$table.sql"
                $mysqldump_bin -u $user -p$pass -h $host $db $table > ${backup_path}${host}_$db.$table.sql
        done
done

save the script as “backup_all_tables.sh”, edit the $backup_path variable to suit, do a chmod +x backup_all_tables to make it executable, and run it using:
./backup_all tables username password hostname
If the database is on your local machine you can omit the hostname.

This will backup the files in the format “host_db.table.sql”, ie: “localhost_mysql.user.sql”

1 person likes this post.

Unlike