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:
- MySQL is running under the "mysql" OS user.
- The backups are performed using a combination of logical backups and binary logs.
- The backups are done on a per-database basis, rather than one big file containing all backups.
- The structure and data backups are taken separately.
- Login credentials are supplied using an option file called "/home/mysql/scripts/extra.my.cnf".
- The log_bin parameter in the "my.cnf" file on the server is set to "log_bin=/u02/log_bin/myServer".
- Backups will be located in a directory called "/u03/backup", with three sub-directories ("databases", "binary_logs" and "logs").
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...