MySQL dump is a nice tool but will allow you only to backup a single database in one file or multiple databases in one single file. Furthermore will it not allow you to compress the output unless you are piping it to e.g. gzip. Of course there are many scripts out there and complete software packages, but people like me prefer a more simple solution, which you can easily modify and embed where ever it would be needed. For those who are looking for a more complex script than mine, have a look at AutoMySQLBackup (http://sourceforge.net/projects/automysqlbackup/).
My script below will backup all databases on a given server, split them into separate files and compress those files one-by-one. As an addition to this, the script will create a separate backup folder each time when the script gets executed (date labelled).
**UPDATE 2016-03-02: Fixed problems with folders and exclude more databases
Create a new file in your user space:
touch backupMySQLDBsinSingleFiles.shMake the file executable:
chmod u+x backupMySQLDBsinSingleFiles.shOpen the file:
nano backupMySQLDBsinSingleFiles.sh
Copy the content below, change the username, password and outpud dir!
#!/bin/bash
################################################
#
# Backup all MySQL databases in separate files and compress those.
# The script will also create a folder with the current timestamp for each run
# @author: Per Lasse Baasch
# @Version: 2016-03-02
# NOTES:
# - MySQL and gzip must be installed on the system
# - Requires write permission in the destination folder
# - Excludes MySQL admin tables ('mysql',information_schema','performance_schema')
#
################################################
# MySQL User
USER='root'
# MySQL Password
PASSWORD='password'
# Backup Directory - WITH TAILING SLASH IF PATH OTHER THEN '.'!
OUTPUT="/home/user/backups/"
##### Execute the backup
TIMESTAMP=`date +%Y%m%d_%H%M%S`;
mkdir $OUTPUT/$TIMESTAMP;
OUTPUTDEST=$OUTPUT/$TIMESTAMP;
echo "Starting MySQL Backup";
echo `date`;
databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] && [[ "$db" != "mysql" ]] && [[ "$db" != "performance_schema" ]] ; then
echo "Dumping database: $db"
mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUTDEST/dbbackup-$TIMESTAMP-$db.sql
gzip $OUTPUTDEST/dbbackup-$TIMESTAMP-$db.sql
fi
done
echo "Finished MySQL Backup";
echo `date`;If you have any suggestions, or comments, please let me know.