8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Mysql » Here

MySQL : Example Backup Schedule

This article contains an example of how you might go about setting up a backup schedule for MySQL.

Related articles.

Assumptions

Here are some assumptions made in these scripts:

Setup

Create the necessary directories as the "root" user if they are not already present.

# mkdir -p /home/mysql/scripts
# chown mysql:mysql /home/mysql/scripts

# mkdir -p /u02/log_bin
# chown -R mysql:mysql /u02

# mkdir -p /u03/backup/databases
# mkdir -p /u03/backup/binary_logs
# mkdir -p /u03/backup/logs
# chown -R mysql:mysql /u03

Make sure the log_bin parameter is set correctly in the "/etc/my.cnf" file. You may wish to set the expire_logs_days parameter also. The backup schedule will take care of the logs, but this provides a catch-all.

log_bin=/u02/log_bin/myServer

# Remove logs older than 7 days.
expire_logs_days=7

Restart the mysqld service.

# service mysqld restart

From now on, all tasks will be performed as the "mysql" OS user.

# su - mysql
$

Create an option file called "/home/mysql/scripts/extra.my.cnf" containing the MySQL login credentials.

[client]
user=root
password=MyPassword

Change the permissions on the file.

$ chmod 600 /home/mysql/scripts/extra.my.cnf

Create a script to list the current databases.

$ echo "show databases;" > /home/mysql/scripts/get_databases.sql

Create a script called "/home/mysql/scripts/backup_databases.sh" to perform the logical backups of all the databases, except for the "information_schema" and "performance_schema".

#!/bin/bash

BACKUP_DIR=/u03/backup/databases
PASSWD_FILE=/home/mysql/scripts/extra.my.cnf
SCRIPT_DIR=/home/mysql/scripts
DATE_SUFFIX=`date +"%Y"-"%m"-"%d"-"%H"-"%M"-"%S"`

# Set PATH to match that assigned when using the shell.
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

echo "Start database backup... " `date`

# Get a current list of databases
mysql --defaults-extra-file=${PASSWD_FILE} --database=mysql -s < ${SCRIPT_DIR}/get_databases.sql > /tmp/databases.txt

# Flush the binary logs
mysqladmin --defaults-extra-file=${PASSWD_FILE} flush-logs

# Backup databases, excluding "information_schema" and "performance_schema"
while read p; do
  if [ "${p}" != "information_schema" -a "${p}" != "performance_schema" ]; then
    echo "Backup " ${p} `date`

    # Create directory if not present.
    mkdir -p ${BACKUP_DIR}/${p}

    # Backup structure and data.
    time mysqldump --defaults-extra-file=${PASSWD_FILE} ${p} --single-transaction --hex-blob --force \
      > ${BACKUP_DIR}/${p}/${p}-backup-${DATE_SUFFIX}.sql
    gzip ${BACKUP_DIR}/${p}/${p}-backup-${DATE_SUFFIX}.sql


    # If you prefer separation of your backups do the following.
    # Backup structure. Might want to consider "--master-data" also.
    #time mysqldump --defaults-extra-file=${PASSWD_FILE} ${p} --no-data --skip-triggers --force \
    #  > ${BACKUP_DIR}/${p}/${p}-backup-structure-${DATE_SUFFIX}.sql
    #gzip ${BACKUP_DIR}/${p}/${p}-backup-structure-${DATE_SUFFIX}.sql

    # Backup objects. Might want to consider "--master-data" also.
    #time mysqldump --defaults-extra-file=${PASSWD_FILE} ${p} --no-data --no-create-info --triggers --events --routines --force \
    #  > ${BACKUP_DIR}/${p}/${p}-backup-objects-${DATE_SUFFIX}.sql
    #gzip ${BACKUP_DIR}/${p}/${p}-backup-objects-${DATE_SUFFIX}.sql

    # Backup data. Might want to consider "--master-data" also.
    #time mysqldump --defaults-extra-file=${PASSWD_FILE} ${p} --no-create-info --skip-triggers --single-transaction --hex-blob --force \
    #  > ${BACKUP_DIR}/${p}/${p}-backup-data-${DATE_SUFFIX}.sql
    #gzip ${BACKUP_DIR}/${p}/${p}-backup-data-${DATE_SUFFIX}.sql
  fi
done < /tmp/databases.txt

echo "End database backup... " `date`

Create a script called "/home/mysql/scripts/backup_logs.sh" to backup the binary logs. The script uses the index file to identify the binary logs that need to be backed up, then purges them up to, but not including, the most recent binary log.

#!/bin/bash

LOG_DIR="/u02/log_bin/"
LOG_IND="myServer.index"
BACKUP_DIR=/u03/backup/binary_logs
PASSWD_FILE=/home/mysql/scripts/extra.my.cnf
DATE_SUFFIX=`date +"%Y"-"%m"-"%d"-"%H"-"%M"-"%S"`

# Set PATH to match that assigned when using the shell.
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

echo "Start log backup... " `date`

# Flush the binary logs
mysqladmin --defaults-extra-file=${PASSWD_FILE} flush-logs

# Backup logs
while read p; do
  # Store last file processed
  LAST="${p/${LOG_DIR}/}"

  echo "Backup " ${p} `date`
  time zip -g ${BACKUP_DIR}/log_bin-${DATE_SUFFIX}.zip ${p}
done < ${LOG_DIR}${LOG_IND}

# Delete backed up logs
echo "PURGE BINARY LOGS TO '${LAST}';" > /tmp/last_log.txt
mysql --defaults-extra-file=${PASSWD_FILE} < /tmp/last_log.txt

echo "End log backup... " `date`

Create a script called "/home/mysql/scripts/backup_housekeeping.sh" to clear down anything older than 7 days in the backup directory. Feel free to adjust this to meet your requirements.

#!/bin/bash

# Set PATH to match that assigned when using the shell.
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

echo "Start housekeeping... " `date`

# Clean up old backups...
find /u03/backup/databases/ -name *.sql.gz -mtime +7 -exec rm -f {} \;

# Clean up old binary log backups...
find /u03/backup/binary_logs/log_bin-* -mtime +7 -exec rm -f {} \;

# Clean up old backup logs...
find /u03/backup/logs/ -name *.log -mtime +7 -exec rm -f {} \;

echo "End housekeeping... " `date`

Create a file called "/home/mysql/scripts/backup_job.sh" that links the previous scripts together, pushing all the output to a log file.

#!/bin/bash

DATE_SUFFIX=`date +"%Y"-"%m"-"%d"-"%H"-"%M"-"%S"`
LOGFILE=/u03/backup/logs/backup-output-${DATE_SUFFIX}.log

# Set PATH to match that assigned when using the shell.
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

echo "Start backup job... " `date` >> $LOGFILE 2>&1

/home/mysql/scripts/backup_databases.sh >> $LOGFILE 2>&1
/home/mysql/scripts/backup_logs.sh >> $LOGFILE 2>&1
/home/mysql/scripts/backup_housekeeping.sh >> $LOGFILE 2>&1

echo "End backup job... " `date` >> $LOGFILE 2>&1

cat $LOGFILE | mail -s "myServer Backup Complete" "me@mycompany.com"

Make all the previously created shell scripts executable.

$ chmod u+x /home/mysql/scripts/*.sh

Running a Backup

You can now run a backup using the following command.

$ /home/mysql/scripts/backup_job.sh

Alternatively, schedule it with the following type of crontab entry.

0 21 * * * /home/mysql/scripts/backup_job.sh >> /dev/null 2>&1

For more information see:

Hope this helps. Regards Tim...

Back to the Top.