Hourly MySQL Backups with a BASH Script

Here is a script I recently wrote to run hourly backups of our production database server.
It’s important to note here a couple of things;

  • This *could* use a lot of disk space!
  • Backups are processor heavy, I’m running mine on on a dedicated VPS connecting to the SLAVE in a MASTER => SLAVE SLAVE replication setup
  • I’ve only tested this Ubuntu 16.04 and 16.10 connecting to MariaDB 10.1 – BUT nothing should break.

Here is my script. I’ve put this in my users home dir, and created a backups directory.

Create the file, copy from below;
nano ~/backups.sh

Then set executable permissions
chmod +x backup.sh

Then test run it:
./backup.sh

Now: You ARE going to see a password warning:

Warning: Using a password on the command line interface can be insecure.

That’s because I’m storing and sending my password in the script. You can use the my.conf defaults by omitting that from the script;
Here is an example: stackoverflow

Here is the bash script;

# Bash script running hourly to backup MySQL tables and keep the last X hours worth. 

# mySQL connection setting 
MYSQL_HOST='database'
MYSQL_USER='username'
MYSQL_PASS='password'
MYSQL_CONN=" --user=${MYSQL_USER} --password=${MYSQL_PASS} --host=${MYSQL_HOST}"

# databases YOU DONT want on hourly backups...
# in single quotes, comma separated
IGNORE_TABLES="'dev_db_1', 'test_db'"

# Date format and backup filename format;
# I use YYYY-MM-DD-HOUR in 24 hour format so I can find my backups
# final name will be YYYY-MM-DD-HOUR-database_name.sql.gz
DATE=`date '+%Y-%m-%d-%H'`

# Backup dir on the filesystem;
BACKUP_DIR=~/backups/

# HOW MANY DAYS SHOULD WE KEEP
DAYS_TO_KEEP=3


# YOU SHOULD NOT NEED TO EDIT PAST HERE


# DELETE OLD BACKUPS > X DAYS
find ${BACKUP_DIR}* -mtime +${DAYS_TO_KEEP} -exec rm {} \;


#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN  ('mysql','information_schema','performance_schema',${IGNORE_TABLES})"

# GET OUR LIST OF ATABASES, EXCLUDING OUR IGNORED DATABASES
DBLISTFILE="${BACKUP_DIR}databasesToDump.txt"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

# LOOP LIST, AND EXPORT
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; 
	do 

		echo "Preparing ${DB}"
		mysqldump ${MYSQL_CONN} --routines --triggers --single-transaction --databases ${DB} | gzip > ${BACKUP_DIR}${DATE}-${DB}.sql.gz
		echo "Backup Complete: ${BACKUP_DIR}${DATE}-${DB}.sql.gz"

done;

# REMOVE TEMP DATABSE NAMES LIST
rm ${DBLISTFILE}

# AND WE'RE DONE
echo "ALL COMPLETE"

Finding disk usage on Linux/Ubuntu

There are a few ways to see how much free or used disk space in linux;
TheĀ  easiest way is to use:

df -lah

If you want to drill down and find where all your disk space has gone, then ncdu is the best too, all day.

On Ubuntu you can apt install this repo, else there are installation instructions on their site.

sudo apt install ncdu

Once installed run this to see an overview;
sudo ncdu -rx /

Here is an example output from server I’m looking at today;

   30.5 GiB [##########] /var                                                                                                                     
    4.4 GiB [#         ] /home
    1.2 GiB [          ] /usr
  646.7 MiB [          ] /lib
  295.5 MiB [          ] /opt
   14.3 MiB [          ] /bin
   13.8 MiB [          ] /sbin
    6.0 MiB [          ] /etc
   32.0 KiB [          ] /tmp
   20.0 KiB [          ] /root
e  16.0 KiB [          ] /lost+found
    8.0 KiB [          ] /media
    4.0 KiB [          ] /lib64
e   4.0 KiB [          ] /srv
e   4.0 KiB [          ] /snap
e   4.0 KiB [          ] /mnt
>   0.0   B [          ] /sys
>   0.0   B [          ] /run
>   0.0   B [          ] /proc
>   0.0   B [          ] /dev
>   0.0   B [          ] /boot

ctrl + x to exit this screen.

You can see my home directory and the /var directories are the largest.
I found an sql dump file in my home directory that I backed up then deleted.

This is a database/mysql server so to drill down and see in var where all my space was, I ran the same command and added the path on;
sudo ncdu -rx /var
sudo ncdu -rx /var/lib
You can also use your up/down cursor keys to navigate to a directory in the tool and hit enter to inspect that,

I hope the ncdu command saves you hours of time tracking down where your space has gone!