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 DisqusIssue 269/2023
Buy this issue as a PDF
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Find SysAdmin Jobs
News
-
Kubuntu Focus Announces XE Gen 2 Linux Laptop
Another Kubuntu-based laptop has arrived to be your next ultra-portable powerhouse with a Linux heart.
-
MNT Seeks Financial Backing for New Seven-Inch Linux Laptop
MNT Pocket Reform is a tiny laptop that is modular, upgradable, recyclable, reusable, and ships with Debian Linux.
-
Ubuntu Flatpak Remix Adds Flatpak Support Preinstalled
If you're looking for a version of Ubuntu that includes Flatpak support out of the box, there's one clear option.
-
Gnome 44 Release Candidate Now Available
The Gnome 44 release candidate has officially arrived and adds a few changes into the mix.
-
Flathub Vying to Become the Standard Linux App Store
If the Flathub team has any say in the matter, their product will become the default tool for installing Linux apps in 2023.
-
Debian 12 to Ship with KDE Plasma 5.27
The Debian development team has shifted to the latest version of KDE for their testing branch.
-
Planet Computers Launches ARM-based Linux Desktop PCs
The firm that originally released a line of mobile keyboards has taken a different direction and has developed a new line of out-of-the-box mini Linux desktop computers.
-
Ubuntu No Longer Shipping with Flatpak
In a move that probably won’t come as a shock to many, Ubuntu and all of its official spins will no longer ship with Flatpak installed.
-
openSUSE Leap 15.5 Beta Now Available
The final version of the Leap 15 series of openSUSE is available for beta testing and offers only new software versions.
-
Linux Kernel 6.2 Released with New Hardware Support
Find out what's new in the most recent release from Linus Torvalds and the Linux kernel team.
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.