Development, Servers and Stuff

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"