MySQL backup/Optimisation shell scripts
A couple of useful MySQL maintenance shell scripts to keep your MySQL database safe and running smoothly.
MySQL binary backup shell script
The following shell script does a 'hot' binary backup all databases the specified username has access to, into the specified ('DEST') directory.
Before running the script conifgure the username, password, hostname and destination backup directory, then add the script to cron as a nightly job. It will created a set of daily backups numbered {database-name}.d{0-7} and monthly backups named {database-name}.m{1-12}
#!/bin/bash
# Shell script to binary backup MySql database
# saving a daily backup and a monthly backup
# ---------------------------------------------
MyUSER="USERNAME" # USERNAME
MyPASS="PASSWORD" # PASSWORD
MyHOST="localhost" # Hostname
DB="database" # database name
# Linux bin paths, change this if it can't be autodetected via which command
MYSQLHOTCOPY="/usr/bin/mysqlhotcopy"
MV="/bin/mv"
CP="/bin/cp"
RM="/bin/rm"
# Backup Dest directory, change this if you have someother location
DEST="/var/backups/mysql"
# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"
DAY="$(date +"%w")"
# Get data in dd-mm-yyyy format
MONTH="$(date +"%m")"
# Remove old backups
FILEDAY="$DEST/$DB.d$DAY"
FILEMONTH="$DEST/$DB.m$MONTH"
if [ -e $FILEDAY ];
then
$RM -r $FILEDAY
fi
if [ -e $FILEMONTH ];
then
$RM -r $FILEMONTH
fi
$MYSQLHOTCOPY --allowold --user=$MyUSER --p=$MyPASS $DB $DEST
$MV $DEST/$DB $FILEDAY
$CP -r --preserve $FILEDAY $FILEMONTH
MySQL database table optimisation script
The following script will run a table optimize command on all tables of the specified database. Before running the script configure the username, password, hosts and database names.
#!/bin/bash
# Optimise the tables in the specified database
# ---------------------------------------------
MyUSER="USERNAME" # USERNAME
MyPASS="PASSWORD" # PASSWORD
MyHOST="localhost" # Hostname
MyDB="Database" # Database
# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="/usr/bin/mysql"
DBTABLES="$( $MYSQL -h$MyHOST -u$MyUSER -p$MyPASS $MyDB -Bse 'show tables' )"
for table in $DBTABLES
do
$MYSQL -h $MyHOST -u $MyUSER -p$MyPASS $MyDB -Bse "optimize table $table"
done