Back up MySQL Databases with a Simple Bash Script
![Dmitri Popov Dmitri Popov](/var/linux_magazin/storage/images/online/blogs/productivity-sauce/275404-17-eng-US/Productivity-Sauce.png)
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.
![Learn More](https://www.linux-magazine.com/var/linux_magazin/storage/images/media/linux-magazine-eng-us/images/misc/learn-more/834592-1-eng-US/Learn-More_medium.png)
News
-
Canonical Offers 12-Year LTS for Open Source Docker Images
Canonical is expanding its LTS offering to reach beyond the DEB packages with a new distro-less Docker image.
-
Plasma Desktop 6.1 Released with Several Enhancements
If you're a fan of Plasma Desktop, you should be excited about this new point release.
-
SUSE Offers CentOS 7 Support with Liberty Linux Lite
SUSE's Liberty Linux support offering now includes CentOS 7, which means businesses won't be forced to migrate those servers for some time.
-
Ubuntu's App Center Finally Supports Local Installs Again
If you regularly download .deb files and would prefer a GUI method of installing, Ubuntu has your back.
-
AlmaLinux Now Supports Raspberry Pi 5
If you're looking to create with the Raspberry Pi 5 and want to use AlmaLinux as your OS, you're in luck because it's now possible.
-
Kubuntu Focus Releases New Iterations of Ir14 and Ir16 Laptops
If you're a fan of the Kubuntu Focus laptops or have been waiting for the right time to purchase one, that time might be now.
-
NixOS 24.05 Is Ready for Prime Time
The latest release of NixOS (Uakari) has arrived and offers its usual reproducible, declarative, and reliable goodness.
-
Linux Lite 7.0 Officially Released
Based on Ubuntu 24.04 and kernel 6.8, Linux Lite version 7 now offers more options than ever.
-
KaOS Linux 2024.05 Adds Bcachfs Support and More
With updates all around, KaOS Linux now includes support for the bcachefs file system.
-
TUXEDO Computers Unveils New Iteration of the Stellaris Laptop Line
The Stellaris Slim 15 is the 6th generation and includes either an AMD or Intel CPU
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.