It’s important to back up your MySQL database(s) regularly in the event of a disaster/mishap.
Setting up a simple mysqldump backup is quick and easy. A small sample script is shown below.
#!/bin/bash
# NAME
# mysqldump_by_schema.sh
#
# Rev 1.0
#
# DESCRIPTION
# This creates separate database exports based on schemas found.
#
# —————————————————————
# VARIABLES
#
TODAY=`date +’%Y-%m-%d’`
HOSTNAME=$(hostname -s)
DESTINATION=/data/mysql-backup/
>/var/log/mysql/exports/$HOSTNAME.log
#
# —————————————————————-
# MySQL Dumps
# —————————————————————-
#
# Make sure script is run as root
if [ “$UID” -ne 0 ]; then
echo “$0 must be run as root!”
exit 2
fi
# Ensure destination directory exists
if [ ! -d /data/mysql-backup ]; then
mkdir -p /data/mysql-backup
fi
echo “show databases;” | /usr/bin/mysql -uroot | grep -v “^Database” | while read SID
do
/usr/bin/mysqldump –opt -d -R -uroot -h localhost –database $SID > $DESTINATION/$HOSTNAME-$TODAY.$SID-shell.dmp
/usr/bin/mysqldump –opt -vvv -n -t -q -R -uroot -h localhost –database $SID | gzip -c -v > $DESTINATION/$HOSTNAME-$TODAY.$SID.dmp.gz
done
