Backup MySQL Periodically. DB Restore. MySQL Table Restore

vicnumb Jan 28, 2013

Jan 28 2013 Published by under Linux&Administration, Tricks

Using many MyISAM tables into MySQL DB I have faced the following problem:
the DB frequently crash while phpmyadmin and mysql table repair tools do not help on fixing tables.

The easy backup way is mysql dumping

mysqldump -u [username] -p[password] -f --all-databases > /mysql-backup-folder/backup-file.sql

This command will save all MySQL databases available for your user [username].

Restoring MySQL dump data

mysql -u [username] -p[password] < /mysql-backup-folder/backup-file.sql

Restoring a single DB

mysql -u [username] -p[password] --one-database [db_to_restore] < /mysql-backup-folder/backup-file.sql

Restoring a single table from DB, you have to extract the table from the total dump (for big dumps it usually takes several minutes):

awk '/Table structure for table .[table_name]./,/Table structure for table .[next_table_name]./{print}' /mysql-backup-folder/backup-file.sql > /mysql-backup-folder/single-table-backup-file.sql

then use mysql import

mysql -u [username] -p[password] [db_name] < /mysql-backup-folder/single-table-backup-file.sql

All this worked until a next crash happened:

  • 3rd crash during 6 months
  • DB size: 6Gb (extracting of a single table takes 4 minutes)
  • Number of DBs: 15

Extracting data from the dump and importing them lots of data were missing.
After some researches we found multiple tables with the same name in MySQL DB dump, it imported them all for a certain DB overwriting one over another. Last DB dump was loaded and used as active.

Besides of this problem we periodically got requests to restore some members accounts that were removed 5, 10 and even 20 days before.

As a effective solution had to write a bash file saving DB dumps into multiple folders (so we could have several versions available), removing old DB dumps (to save disk space).

MySQL backup bash file requirements:

  • writing DB dumps daily (keep DB dumps for last 7 days), weekly (keep DB dumps for the last 4 weeks), monthly (keep DB dumps for the last 4 months). – this should be enough to cover all possible MySQL DB backup needs.
  • writing each new backup into another folder like:
    /mysql-backup-folder/mysql-dump-YYYY-MM-DD/
  • writing each DB into a separate file like:
    /path-to-backup-folders/[backup-period]/mysql-dump-YYYY-MM-DD/db_name.sql
  • removing older DB dumps (see the rule above)

The basic idea is – running a file via cron with arguments daily, weekly, monthly:

10 0 * * * /path-to-backup-file/backup-db.sh daily 7
10 0 * * 1 /path-to-backup-file/backup-db.sh weekly 28
10 0 1 * * /path-to-backup-file/backup-db.sh monthly 120

backup-db.sh file contents

#!/bin/bash
# backup each MySQL db to a different file
 
#IMPORTANT: 
#argument 1 stands for values: daily / weekly / monthly
#argument 2 stands for the number of days to keep backup copies (e.g. 7, 10, 15, 30 etc). Older copies are removed.
 
USER="db_user"
PASSWORD="db_password"
HOST="host"
OUTPUTDIR="/[backup-folder-path]/$1/`date '+%Y-%m-%d'`" #use a YYYY-MM-DD - subfolder
BACKUPDIR="/[backup-folder-path]/$1" #Use first argument as folder name
REMOVEAFTER=$2
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
mkdir $OUTPUTDIR
 
# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
 
# dump each database in turn
for db in $databases; do
    echo $db
    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD  --databases $db | gzip > "$OUTPUTDIR/$db.sql.gz"
done
 
#remove files older than argument 2 (argument 2 represents number of days)
find $BACKUPDIR -name "*" -mtime +$REMOVEAFTER -exec rm -rf {} \;

Last steps

1. Put the cron lines above to cron job table.
2. backup-db.sh file settings to 666:

chmod 666 /[path-to-backup-shell-file]/backup-db.sh

Feel free to use the info above for your administrative needs.

2 responses so far

  • Constantin says:

    WOW! I didn’t know about command switch “–one-database” is supported by mysql command, like “mysql -u [username] -p –one-database”

    that’s really helping in small databases situation!

  • […] I had to do is to follow the steps: 1. Make a copy of damaged database 2. Replace it with backup database 3. Unite tables of 2 databases to add into restored database the last 10 days missing […]

Leave a Reply