Cheap VPS & Xen Server

Residential Proxy Network - Hourly & Monthly Packages

MySQL Backup: Table By Table Backup With Auto Rotation, For Easy Restoration Of Partial/Full Database

Here is a MySQL backup script which can take table by table backups (individual backup files of each table of each database) in a compressed format. It also provides an automatic rotation of old backup files.  The backup script handles innodb and myisam tables separately.

You have to set the following variables prior to running the backup script.


 The database user who has access to all databases and its tables. I used “root” for my deployment.


 Password of the above user, prefixed with “-p”. For example if the password is Secret, then you should write the password as “-pSecret”.


 File to which the backup log will be written. It should be writable by the user who is running the script.


 The backup folder. It should be writable by the user who is running the script.


 Backup rotation period. +30 is 30 days.


The Backup Script

# Database Backup script.
# Created By:    Mohammed Salih
#                 Senior System Administrator
#                Date: 21/06/2007
# Database credentials
#Please append password in the xxxxx section below, note that there is
# no space between -p and xxxxx
# Get list of Databases except the pid file
DBS_LIST=$(echo "show databases;"|mysql -u $DB_USER $DB_PASS -N)
# Log file
# Backup Base directory
# Backup rotation period.
# From here, only edit if you know what you are doing.
# Check if we can connect to the mysql server; otherwise die
if [ ! "$(id -u -n)" = "mysql" ]; then
        echo -e "Error:: $0 : Only user 'mysql' can run this script"
        exit 100
PING=$(mysqladmin ping -u $DB_USER $DB_PASS 2>/dev/null)
if [ "$PING" != "mysqld is alive" ]; then
        echo "Error:: Unable to connected to MySQL Server, exiting !!"
        exit 101
# Backup process starts here.
# Flush logs prior to the backup.
mysql -u $DB_USER $DB_PASS -e "FLUSH LOGS"
# Loop through the DB list and create table level backup,
# applying appropriate option for MyISAM and InnoDB tables.
for DB in $DBS_LIST; do
    DB_BKP_FLDR=$BASE_BAK_FLDR/$(date +%d-%m-%Y)/$DB
    [ ! -d $DB_BKP_FLDR ]  && mkdir -p $DB_BKP_FLDR
    # Get the schema of database with the stored procedures.
    # This will be the first file in the database backup folder
    mysqldump -u $DB_USER $DB_PASS -R -d --single-transaction $DB | \
            gzip -c > $DB_BKP_FLDR/000-DB_SCHEMA.sql.gz
    #Get the tables and its type. Store it in an array.
    table_types=($(mysql -u $DB_USER $DB_PASS -e "show table status from $DB" | \
            awk '{ if ($2 == "MyISAM" || $2 == "InnoDB") print $1,$2}'))
    # Loop through the tables and apply the mysqldump option according to the table type
    # The table specific SQL files will not contain any create info for the table schema.
    # It will be available in SCHEMA file
    while [ "$index" -lt "$table_type_count" ]; do
        START=$(date +%s)
        TYPE=${table_types[$index + 1]}
        echo -en "$(date) : backup $DB : $table : $TYPE "
        if [ "$TYPE" = "MyISAM" ]; then
            DUMP_OPT="-u $DB_USER $DB_PASS $DB --no-create-info --tables "
            DUMP_OPT="-u $DB_USER $DB_PASS $DB --no-create-info --single-transaction --tables"
        mysqldump  $DUMP_OPT $table |gzip -c > $DB_BKP_FLDR/$table.sql.gz
        index=$(($index + 2))
        echo -e " - Total time : $(($(date +%s) - $START))\n"
# Rotating old backup. according to the 'RM_FLDR_DAYS'
if [ ! -z "$RM_FLDR_DAYS" ]; then
    echo -en "$(date) : removing folder : "
    find $BASE_BAK_FLDR/ -maxdepth 1 -mtime $RM_FLDR_DAYS -type d -exec rm -rf {} \;

The Backup Location

For example, if you have taken the backup of “bigdb” on 1st Jan 2007, then the backup will be kept in



The Restore Script

Following command/script is an example for restoring a database called bigdb for which the backup was taken on 1st Jan 2007.

cd /backup/01-01-2007/bigdb;

for table in *; do gunzip -c $table | mysql -u root -pSecret bigdb_new; done.

The above command will iterate through the list of files in the directory and restore all the tables to bigdb_new database. It is assumed that you have created the bigdb_new database prior to running the script.