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
-
Gnome 47.1 Released with a Few Fixes
The latest release of the Gnome desktop is all about fixing a few nagging issues and not about bringing new features into the mix.
-
System76 Unveils an Ampere-Powered Thelio Desktop
If you're looking for a new desktop system for developing autonomous driving and software-defined vehicle solutions. System76 has you covered.
-
VirtualBox 7.1.4 Includes Initial Support for Linux kernel 6.12
The latest version of VirtualBox has arrived and it not only adds initial support for kernel 6.12 but another feature that will make using the virtual machine tool much easier.
-
New Slimbook EVO with Raw AMD Ryzen Power
If you're looking for serious power in a 14" ultrabook that is powered by Linux, Slimbook has just the thing for you.
-
The Gnome Foundation Struggling to Stay Afloat
The foundation behind the Gnome desktop environment is having to go through some serious belt-tightening due to continued financial problems.
-
Thousands of Linux Servers Infected with Stealth Malware Since 2021
Perfctl is capable of remaining undetected, which makes it dangerous and hard to mitigate.
-
Halcyon Creates Anti-Ransomware Protection for Linux
As more Linux systems are targeted by ransomware, Halcyon is stepping up its protection.
-
Valve and Arch Linux Announce Collaboration
Valve and Arch have come together for two projects that will have a serious impact on the Linux distribution.
-
Hacker Successfully Runs Linux on a CPU from the Early ‘70s
From the office of "Look what I can do," Dmitry Grinberg was able to get Linux running on a processor that was created in 1971.
-
OSI and LPI Form Strategic Alliance
With a goal of strengthening Linux and open source communities, this new alliance aims to nurture the growth of more highly skilled professionals.
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.