The Last MySQL Backup Script You'll Ever Need

TL;DR

• Login to the MySQL database server you want to backup
• Copy/paste MySQL GRANT commands
• Login to a CentOS 7 server with plenty of disk space (backup server)
• Copy/paste The Whole Script here to a shell script on that server
• Set the three obvious variables at the top
• Execute
• Walk away. This script will continue to backup every single database on the server you specify, every hour and every day, until the end of time.
• Post if you have problems and @blervin will help

Backup Your Databases!

If you haven't accidentally typed DELETE FROM mytable WHERE... a bit too broad, nearly truncating your entire table, then you haven't been around databases long enough. After learning this painful lesson far too many times, I'm now comfortably settled into a routine of comprehensive backup plans for all databases on all servers at all times. Not only do I want to spare you the pain of lost data, I want to make everything about your backup strategy dead simple.

Our Environment

This script is written for MySQL databases running on CentOS 7 Linux, though much of it is universal and can be tweaked for other flavors of Linux pretty easily. In fact, comment with questions if you're trying to adapt this in another environment and I'll help you get up and running.

This is expected to be deployed on a backup server separate from the MySQL database server we're connecting to but you could easily specify localhost and it will run the same directly on the database server.

Everything here is written in Bash and I normally implement the entire script as part of my initial deployment of cloud servers. While I have this all integrated into my own automated deployments, you can literally create this script and manually run it once and it will work the same.

I've got the entire script together at the end but I'll walk you through the components individually.

Source Database Server

Since we're connecting to another server to make backups we need permissions for that connection. Connect to MySQL on the database server, change the host/user/password in this snippet, and run the following:

CREATE USER 'backup'@'backup_server_hostname_or_ip_address' IDENTIFIED BY 'mypassword';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'backup_server_hostname_or_ip_address';
GRANT LOCK TABLES ON *.* TO 'backup'@'backup_server_hostname_or_ip_address';
FLUSH PRIVILEGES;

That is a restrictive GRANT that may not fit your precise needs, so here's a broad grant that may also make sense if you have troubles with the above.

GRANT ALL ON *.* TO backup@'backup_server_hostname_or_ip_address' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;

I'm assuming you want to restrict this user to the specific backup server you're connecting from so include the IP/hostname of that server where backup_server_hostname_or_ip_address is specified or just use % to allow connections for this user from any host.

Backup Server

Once we have our permissions in place on the database server we can implement our script on the backup server. I mention that this can be run directly on the database server itself, but I strongly advise against this. Lost data isn't just accidental deletes, but sometimes hardware or other failures at the local level beyond the database itself. If the whole server goes down and your backups are stored there, that doesn't help with recovery at all. And since we've all got our heads--uh, servers--in the clouds today, spinning up another instance is simple so we have no reason not to.

Obviously, the backup server should have a decent amount of storage and another layer of backups and redundancy in that infrastructure as well.

Variables

To start, we define a few variables that will be used throughout the rest of the script.

For mysql_host you can use any hostname, or IP including RFC 1918 space (i.e. 192.168.0.1/24) or even just localhost explicitly itself if you're running this directly on the database server and not running a separate backup server.

##############
## VARIABLES ##
##############
mysql_host='my_hostname_or_ip_address'
mysql_user='backup'
mysql_password='mypassword'

Install MySQL Client

Today the base repos all use MariaDB but I prefer to stay true to MySQL and so we pull in another repository to get the MySQL client utilities, specifically mysqldump

##############################
## INSTALL MySQL Client Utilities ##
##############################
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install -y mysql-community-client

Hourly Backup

All of our scripts are written to /root/bin with the backups themselves created at /var/backups Obviously, change both as necessary.

This script loops through every database on the server, but explicitly excludes mysql, information_schema, performance_schema, and underscore prefix _tables so feel free update the exclusion to include other database you don't want to backup. Just add the following to the end of the exclusions:

&& [[ $db != 'another_db_name' ]]

A cron job is created to run this script each night.

#########################
## HOURLY MySQL BACKUP ##
#########################
echo "Create hourly backup location"
mkdir -p /var/backups/mysql/hourly/
echo "Create hourly backup script"
mkdir /root/bin
cd /root/bin
touch /root/bin/dbbackup_hourly.sh
echo "#!/bin/bash" >> dbbackup_hourly.sh
## We use single quotes to preseve this text explicitly
echo 'databases=`mysql -h' "$mysql_host -u $mysql_user -p$mysql_password -e " '"SHOW DATABASES;" | tr -d "| " | grep -v Database`' >> dbbackup_hourly.sh
## We use double quotes to evaulate our variables write the host/user/pass explicitly to the local shell script
echo 'for db in $databases; do' >> dbbackup_hourly.sh
echo '    if [[ $db != 'information_schema' ]] && [[ $db != 'performance_schema' ]] && [[ $db != 'mysql' ]] && [[ $db != _* ]] ; then' >> dbbackup_hourly.sh
echo "        echo 'Dumping database: $db'" >> dbbackup_hourly.sh
echo "        mysqldump -h $mysql_host -u $mysql_user -p$mysql_password "'$db'" | /bin/gzip > /var/backups/mysql/hourly/"'$db'".\`/bin/date +\"%Y-%m-%d-%H\"\`.gz" >> dbbackup_hourly.sh
echo "    fi" >> dbbackup_hourly.sh
echo "done" >> dbbackup_hourly.sh
chmod 755 /root/bin/dbbackup_hourly.sh
echo "Create hourly cron"
echo '5 * * * *       root    /root/bin/dbbackup_hourly.sh' > /etc/cron.d/dbbackup_hourly

Daily Backup

The daily backup script resembles the hourly script almost identically, with only differing destination paths and cron settings.

#######################
## DAILY MySQL BACKUP ##
#######################
echo "Create daily backup location"
mkdir -p /var/backups/mysql/daily/
echo "Setup daily script"
mkdir /root/bin
cd /root/bin
touch /root/bin/dbbackup_daily.sh
echo "#!/bin/bash" >> dbbackup_daily.sh
echo 'databases=`mysql -h' "$mysql_host -u $mysql_user -p$mysql_password -e " '"SHOW DATABASES;" | tr -d "| " | grep -v Database`' >> dbbackup_daily.sh
echo 'for db in $databases; do' >> dbbackup_daily.sh
echo '    if [[ $db != 'information_schema' ]] && [[ $db != 'performance_schema' ]] && [[ $db != 'mysql' ]] && [[ $db != _* ]] ; then' >> dbbackup_daily.sh
echo "        echo 'Dumping database: $db'" >> dbbackup_daily.sh
echo "        mysqldump -h $mysql_host -u $mysql_user -p$mysql_password "'$db'" | /bin/gzip > /var/backups/mysql/daily/"'$db'".\`/bin/date +\"%Y-%m-%d\"\`.gz" >> dbbackup_daily.sh
echo "    fi" >> dbbackup_daily.sh
echo "done" >> dbbackup_daily.sh
chmod 755 /root/bin/dbbackup_daily.sh
echo "Setup daily cron"
echo '15 0 * * *       root    /root/bin/dbbackup_daily.sh' > /etc/cron.d/dbbackup_daily

Rotation

If we keep writing backups indefinitely we'll soon run out of space. This script continually deletes our old scripts to ensure we have sufficient space available for new backups. The key to this rotation is the naming convention used in the backup scripts above, which includes the date as a string in the filename.

The defaults here keep 7 days of daily backups and the last 24 hours of hourly backups.

#############################
## CLEANUP ROTATION SCRIPT ##
#############################
touch /root/bin/dbcleanup.sh
chmod 755 /root/bin/dbcleanup.sh
echo '#!/bin/bash' >> /root/bin/dbcleanup.sh
echo 'rm -f  /var/backups/mysql/daily/*.`/bin/date +"%Y-%m-%d" -d "7 days ago"`.gz' >> /root/bin/dbcleanup.sh
echo 'rm -f  /var/backups/mysql/hourly/*.`/bin/date +"%Y-%m-%d-%H" -d "1 days ago"`*.gz' >> /root/bin/dbcleanup.sh
echo '35 * * * *       root    /root/bin/dbcleanup.sh' >> /etc/cron.d/dbbackup_hourly

Adjustments

Everything here is written to my requirements, backups every night saved for 7 days and every hour saved for 24 hours. This generic rule likely applies to most cases but you can tweak this to fit any scencario.

For example, you may want a longer retention of 30 days of daily backups. The CLEANUP ROTATION SCRIPT makes it pretty obvious that you can change 7 days ago to 30 days ago for this scenario.

Or instead, you may want backups every 5 minutes. That change is a little more significant because we'll need to add the minute to the filename we create for the backup to keep it unique. Here we'll need to change a couple of things.

First, we'll want to start writing the current minute to the filename used for the backup. In the HOURLY MySQL BACKUP we'll need to simply add -%M to look like this:

echo "        mysqldump -h $mysql_host -u $mysql_user -p$mysql_password "'$db'" | /bin/gzip > /var/backups/mysql/hourly/"'$db'".\`/bin/date +\"%Y-%m-%d-%H-%M\"\`.gz" >> dbbackup_hourly.sh

Then, in the CLEANUP ROTATION SCRIPT we'll make a similar change, as well as setting the date to 5 minutes ago:

echo 'rm -f  /var/backups/mysql/hourly/*.`/bin/date +"%Y-%m-%d-%H-%M" -d "5 minutes ago""`*.gz' >> /root/bin/dbcleanup.sh

Finally, we need the cron job to run more frequently so we set it like this:

echo '*/5 * * * *       root    /root/bin/dbcleanup.sh' >> /etc/cron.d/dbbackup_hourly

The */5 means the cron job will run every 5 minutes.

The general idea here is that this can be adapted to nearly any scenario. Again, comment with your specific scenario if you'd like some help.

Summary

After you run this entire script, the new scripts will be written to your local file system at /root/bin and cron jobs initialized to keep this running indefinitely. An important consideration here is disk space so you'll definitely want to setup a monitor of some sort to notify you if you run low.

Steem Power

Since I'm a little minnow barely a few days in, I'm powering up for 100% steem power on this post to show I'm committed for the long haul as I try to build my reputation here so I appreciate any support!

The Whole Script

First, permissions on the database server:

CREATE USER 'backup'@'backup_server_hostname_or_ip_address' IDENTIFIED BY 'mypassword';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'backup_server_hostname_or_ip_address';
GRANT LOCK TABLES ON *.* TO 'backup'@'backup_server_hostname_or_ip_address';
FLUSH PRIVILEGES;

Then the backup server script:

#!/bin/bash
##############
## VARIABLES ##
##############  
mysql_host='my_hostname_or_ip_address'
mysql_user='backup'
mysql_password='mypassword'
##############################
## INSTALL MySQL Client Utilities ##
##############################
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install -y mysql-community-client
#########################
## HOURLY MySQL BACKUP ##
#########################
echo "Create hourly backup location"
mkdir -p /var/backups/mysql/hourly/
echo "Create hourly backup script"
mkdir /root/bin
cd /root/bin
touch /root/bin/dbbackup_hourly.sh
echo "#!/bin/bash" >> dbbackup_hourly.sh
## We use single quotes to preseve this text explicitly
echo 'databases=`mysql -h' "$mysql_host -u $mysql_user -p$mysql_password -e " '"SHOW DATABASES;" | tr -d "| " | grep -v Database`' >> dbbackup_hourly.sh
## We use double quotes to evaulate our variables write the host/user/pass explicitly to the local shell script
echo 'for db in $databases; do' >> dbbackup_hourly.sh
echo '    if [[ $db != 'information_schema' ]] && [[ $db != 'performance_schema' ]] && [[ $db != 'mysql' ]] && [[ $db != _* ]] ; then' >> dbbackup_hourly.sh
echo "        echo 'Dumping database: $db'" >> dbbackup_hourly.sh
echo "        mysqldump -h $mysql_host -u $mysql_user -p$mysql_password "'$db'" | /bin/gzip > /var/backups/mysql/hourly/"'$db'".\`/bin/date +\"%Y-%m-%d-%H\"\`.gz" >> dbbackup_hourly.sh
echo "    fi" >> dbbackup_hourly.sh
echo "done" >> dbbackup_hourly.sh
chmod 755 /root/bin/dbbackup_hourly.sh
echo "Create hourly cron"
echo '5 * * * *       root    /root/bin/dbbackup_hourly.sh' > /etc/cron.d/dbbackup_hourly
########################
## DAILY MySQL BACKUP ##
########################
echo "Create daily backup location"
mkdir -p /var/backups/mysql/daily/
echo "Setup daily script"
mkdir /root/bin
cd /root/bin
touch /root/bin/dbbackup_daily.sh
echo "#!/bin/bash" >> dbbackup_daily.sh
echo 'databases=`mysql -h' "$mysql_host -u $mysql_user -p$mysql_password -e " '"SHOW DATABASES;" | tr -d "| " | grep -v Database`' >> dbbackup_daily.sh
echo 'for db in $databases; do' >> dbbackup_daily.sh
echo '    if [[ $db != 'information_schema' ]] && [[ $db != 'performance_schema' ]] && [[ $db != 'mysql' ]] && [[ $db != _* ]] ; then' >> dbbackup_daily.sh
echo "        echo 'Dumping database: $db'" >> dbbackup_daily.sh
echo "        mysqldump -h $mysql_host -u $mysql_user -p$mysql_password "'$db'" | /bin/gzip > /var/backups/mysql/daily/"'$db'".\`/bin/date +\"%Y-%m-%d\"\`.gz" >> dbbackup_daily.sh
echo "    fi" >> dbbackup_daily.sh
echo "done" >> dbbackup_daily.sh
chmod 755 /root/bin/dbbackup_daily.sh
echo "Setup daily cron"
echo '15 0 * * *       root    /root/bin/dbbackup_daily.sh' > /etc/cron.d/dbbackup_daily
#############################
## CLEANUP ROTATION SCRIPT ##
#############################
touch /root/bin/dbcleanup.sh
chmod 755 /root/bin/dbcleanup.sh
echo '#!/bin/bash' >> /root/bin/dbcleanup.sh
echo 'rm -f  /var/backups/mysql/daily/*.`/bin/date +"%Y-%m-%d" -d "7 days ago"`.gz' >> /root/bin/dbcleanup.sh
echo 'rm -f  /var/backups/mysql/hourly/*.`/bin/date +"%Y-%m-%d-%H" -d "1 days ago"`*.gz' >> /root/bin/dbcleanup.sh
echo '35* * * *       root    /root/bin/dbcleanup.sh' >> /etc/cron.d/dbbackup_hourly

---

Posted on Utopian.io - Rewarding Open Source Contributors

---