Back up MySQL Databases with a Simple Bash Script
Productivity Sauce
If you host your own blog or any Web-based application running on the Apache/MySQL/PHP stack, you should have a backup system in place for keeping data stored in MySQL databases safe. There are several solutions that can help you with that, but nothing beats a simple Bash script I stumbled upon in a blog post comment. Here is the script in all its beauty:
#!/bin/bash NOW=`date +"%Y-%m"`; BACKUPDIR="location/of/your/backup/dir/$NOW"; ### Server Setup ### #* MySQL login user name *# MUSER="user"; #* MySQL login PASSWORD name *# MPASS="pass"; #* MySQL login HOST name *# MHOST="your-mysql-ip"; MPORT="your-mysql-port"; # DO NOT BACKUP these databases IGNOREDB=" information_schema mysql test " #* MySQL binaries *# MYSQL=`which mysql`; MYSQLDUMP=`which mysqldump`; GZIP=`which gzip`; # assuming that /nas is mounted via /etc/fstab if [ ! -d $BACKUPDIR ]; then mkdir -p $BACKUPDIR else : fi # get all database listing DBS="$(mysql -u $MUSER -p$MPASS -h $MHOST -P $MPORT -Bse 'show databases')" # SET DATE AND TIME FOR THE FILE NOW=`date +"d%dh%Hm%Ms%S"`; # day-hour-minute-sec format # start to dump database one by one for db in $DBS do DUMP="yes"; if [ "$IGNOREDB" != "" ]; then for i in $IGNOREDB # Store all value of $IGNOREDB ON i do if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then DUMP="NO"; # SET value of DUMP to "no" #echo "$i database is being ignored!"; fi done fi if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database FILE="$BACKUPDIR/$NOW-$db.gz"; echo "BACKING UP $db"; $MYSQLDUMP --add-drop-database --opt --lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE fi done
The best part is that you only need to specify a handful of parameters to make the script work. This includes BACKUPDIR (the destination for storing backups), MUSER (MySQL user), MPASS (MySQL user password), MHOST (the IP address of the MySQL server, e.g. localhost), and MPORT (the port the MySQL database is running on, default is 3306).
You can run the script manually, or you can set up a cron job which will perform backups on a regular basis. To do this, run the crontab -e command and add the following line (replace the sample path with the actual path and backup script name):
@daily /path/to/mysqlbackupscript.sh
Don't forget to make the script executable using the chmod a+x mysqlbackupscript.sh command.
Comments
comments powered by DisqusSubscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.
News
-
Canonical Bumps LTS Support to 12 years
If you're worried that your Ubuntu LTS release won't be supported long enough to last, Canonical has a surprise for you in the form of 12 years of security coverage.
-
Fedora 40 Beta Released Soon
With the official release of Fedora 40 coming in April, it's almost time to download the beta and see what's new.
-
New Pentesting Distribution to Compete with Kali Linux
SnoopGod is now available for your testing needs
-
Juno Computers Launches Another Linux Laptop
If you're looking for a powerhouse laptop that runs Ubuntu, the Juno Computers Neptune 17 v6 should be on your radar.
-
ZorinOS 17.1 Released, Includes Improved Windows App Support
If you need or desire to run Windows applications on Linux, there's one distribution intent on making that easier for you and its new release further improves that feature.
-
Linux Market Share Surpasses 4% for the First Time
Look out Windows and macOS, Linux is on the rise and has even topped ChromeOS to become the fourth most widely used OS around the globe.
-
KDE’s Plasma 6 Officially Available
KDE’s Plasma 6.0 "Megarelease" has happened, and it's brimming with new features, polish, and performance.
-
Latest Version of Tails Unleashed
Tails 6.0 is based on Debian 12 and includes GNOME 43.
-
KDE Announces New Slimbook V with Plenty of Power and KDE’s Plasma 6
If you're a fan of KDE Plasma, you'll be thrilled to hear they've announced a new Slimbook with an AMD CPU and the latest version of KDE Plasma desktop.
-
Monthly Sponsorship Includes Early Access to elementary OS 8
If you want to get a glimpse of what's in the pipeline for elementary OS 8, just set up a monthly sponsorship to help fund its continued existence.
simple & compact version
NOW=`date +%Y-%m-%d`;
BKPDIR=/path/to/backup/$NOW;
mkdir -p $BKPDIR;
for DB in $(echo 'SHOW DATABASES;' | mysql -uuser -ppassword | grep -v '^Database$'); do
if [[ "${IGNOREDB}" =~ "#${DB}#" ]]; then continue; fi
mysqldump -uuser -ppassword --opt --add-drop-database --lock-all-tables --max_allowed_packet=500M $DB | bzip2 > $BKPDIR/$NOW-$DB.sql.bz2;
done;
Oh the horror
A shorter script using regular expression support
# DO NOT BACKUP these databases
IGNOREDB="#information_schema#mysql#test#"
...
for db in $DBS
do
if [ "${IGNOREDB}" =~ "#${db}#" ]; then continue; fi
FILE="$BACKUPDIR/$NOW-$db.gz";
echo "BACKING UP $db";
$MYSQLDUMP --add-drop-database --opt --lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE
done
Avoiding the second inner loop and the $DUMP variable.
My two cents!
Backing up large databases
You'll want to add something like --max_allowed_packet=500M to the mysqldump command line to work around that.