No Fuss Computing

Using Technology to Make Life Easier

User Tools

Site Tools


public:help:scripts:mysqlbackup

Linux MySQL Backup Script

The easiest way to backup a MySQL database is by by scripting it. Below I have created a Bash script that can be used on a Linux host that will enable you to backup all databases into their own directory only keeping the specified amount of backups. This script can be run from cron or the command line.

Before you use this script you will be required to change some variables so that it works in your environment. the following variables will need to be adjusted to suit:

  1. BACKUPDIR - This is where your data will be backed up to.
  2. MYSQLPATH - This is where the MySQL executable are located on the file system
  3. DB_USER - The user you will be connecting to the database with
  4. DB_PASS - The above Database user's password
  5. DB_HOST - The MySQL host name or IP address, can be 'localhost'
  6. KEEP - The amount of previous backups to keep-For instance if your leave it at 5 and run the script daily then 5 days worth of backups will be kept.

I would recommend that you not use the root user to access MySQL, this is because you will be saving that password the filesystem, instead create a backup user that only has read access.

Bash Script

This file will download with a .txt file extension. Please rename the file so it has the right extension as per the mentioned filename.

MySQLBackup.sh
#!/bin/sh
 
#-----------------------------------------------------------------------------------------------------
# V 0.2
#
#                         MySQL All DataBase Backup
#
#Created by: Jon @ No Fuss Computing 2014
#Feel free to use this script as you see fit, if you do then please leave credit where credit is due.
#
#
# Change log
#  - (0.1) 15 02 09 Created.
#  - (0.2) 15 10 11 Updated backup filename to include .sql
#                   Adjusted date stamp to be formated.
#-----------------------------------------------------------------------------------------------------
DATESTAMP=`date +%y-%m-%d-%H%M%S[%z]`
 
#Adjust to suit your environment
BACKUPDIR=
 
#Adjust to suit your environment
MYSQLPATH=
DB_USER=
DB_PASS=
DB_HOST=
 
 
KEEP=31
 
databases=`$MYSQLPATH/mysql --host=$DB_HOST --user=$DB_USER --password=$DB_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
 
for DB_NAME in $databases; do
 
#Backup each database to it's own directory
DIR=$BACKUPDIR/$DB_NAME/
 
# Create backup directory if it don't exist
mkdir -p ${DIR}
 
FILENAMEPREFIX=$DB_NAME-MySQL-Backup.sql
 
BACKUPS=`find ${DIR} -name "*$FILENAMEPREFIX*.gz" | wc -l | sed 's/\ //g'`
while [ $BACKUPS -ge $KEEP ]
do
  ls -tr1 ${DIR}*-$FILENAMEPREFIX*.gz | head -n 1 | xargs rm -f
  BACKUPS=`expr $BACKUPS - 1`
done
 
# dump databases in a gzip file
 
FILENAME=${DIR}${DATESTAMP}-$FILENAMEPREFIX.gz
 
$MYSQLPATH/mysqldump --host=$DB_HOST --user=$DB_USER --password=$DB_PASS --databases $DB_NAME --opt --flush-logs | gzip > $FILENAME
 
done
public/help/scripts/mysqlbackup.txt · Last modified: 2015/10/14 20:38 by jon

Page Tools