Feature #140 add binary log dump capability

Added by Charles Atkinson about 8 years ago. Updated over 6 years ago.

Start date:
Due date:
% Done:




The use-case is from ac001.ptdc:

root@ac001.ptdc:/etc/postfix# gev /root/scripts/mysql.backup-hourly
DATE=`date +%Y%m%d`
MOUNTCHECK=`mount | grep "/mnt/backup"`
logger "MYSQL backup started..." 
if [[ ! -f $TMPFILE ]]; then
    touch $TMPFILE
if [[ ${#MOUNTCHECK} == 0 ]];
    mount -U 52e3aeff-0216-41af-9e69-499f492ebdae /mnt/backup >> $TMPFILE 2>&1;
    if [[ $? -ne 0 ]]; then
    exit 1
if [[ $(date +%H) == "08" ]] || [[ "$1" == "dump" ]]; then
    rm -f $LOGDIR/*.bz2 >> $TMPFILE 2>&1
    mysqldump --defaults-extra-file=$MYPASSWORD -u$MYUSER -h localhost --flush-logs --delete-master-logs --skip-events --master-data=2 --add-drop-table --lock-all-tables --all-databases > $LOGDIR/$DBFN
    bzip2 $LOGDIR/$DBFN >> $TMPFILE 2>&1
    rm -f $LOGDIR/*.sql >> $TMPFILE 2>&1
    find $BACKUPDIR/* -type f -ctime +30 -delete >> $TMPFILE 2>&1
sleep 5
    find $BACKUPDIR/* -mindepth 1 -type d -ctime +30 -delete >> $TMPFILE 2>&1
    mysqladmin --defaults-extra-file=$MYPASSWORD -u$MYUSER flush-logs >> $TMPFILE 2>&1


#1 Updated by Charles Atkinson almost 8 years ago

The extra functionality of the script is to run mysqldump with extra options (--flush-logs --delete-master-logs --skip-events --master-data=2 --add-drop-table --lock-all-tables --all-databases) and to run mysqladmin flush-logs.

Which MySQL server versions are in use?
  • Debian 6: 5.1
  • Ubuntu 12.04: 5.5
  • Debian 7: 5.5

OK -- use the 5.1 documentation and hope 5.5 is backwards-compatible with 5.1.

Studied "MySQL 5.1 Reference Manual :: 7 Backup and Recovery :: 7.2 Database Backup Methods (, especially the "Making Incremental Backups by Enabling the Binary Log" and "User Comments" sections.

Do the options (especially --lock-all-tables --all-databases) stop write transactions while the backup is being done? Even if it does, maybe it doesn't matter at 8 AM.

Discussed with Sam:
  • Has a restore ever been done from the backup data created by the script? No.
  • Are there are any current issues with the existing script? Only that it outputs "-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly". Checking the old emails, these messages seem to only be present in the first or only email of each day. Could not connect to PTDC to investigate how that might happen; suspect it is from the mysqldump command.

Decided to start by enhancing bung to do exactly what the existing script does. What is that, in detail?

It is scheduled to run every hour, when it:
  1. Creates /var/log/mysql/backup-$(date +%Y%m%d).log if it does not exist
  2. Ensures /mnt/backup is mounted
  3. Once a day (?):
    1. Removes /var/log/mysql/*.bz2
    2. Runs mysqldump with options ...
      -h localhost --flush-logs --delete-master-logs --skip-events --master-data=2 --add-drop-table --lock-all-tables --all-databases
      ... creating /var/log/mysql/PTDC-mysql-dump.$(date +%Y%m%d).sql.
      Note: -h localhost is default so does not need to be specified.
    3. bzip2 compresses the .sql file just created
    4. Removes the .sql file just created
    5. Removes any files in /mnt/backup/mysqlbackup which were created 30+ days ago
    6. Removes any empty directories under /mnt/backup/mysqlbackup which were created 30+ days ago
  4. The other hours when it runs:
    1. Runs mysqladmin flush-logs
  • Maybe nice to use --log-error=<file> and examine the file afterwards. The mysqldump man page says "_Log warnings and errors by appending them to the named file. The default is to do no logging_".
  • Maybe nice to use --result-file=<file>. In that case any stdout or stderr signifies an unusual condition that may tell of a defective backup (for example the mysql command is not found).
  • Need to find out where mysqladmin flush-logs creates files.

Enough for today.

#2 Updated by Charles Atkinson almost 8 years ago

  • Priority changed from Normal to Low

Investigating ...

root@ac001.ptdc:~# ll -a /etc/bluelight/dbbackup/
total 36
drwxr-xr-x 2 root root 4096 Jan 27 08:20 .
drwxr-xr-x 3 root root 4096 Apr  1  2013 ..
-rw-r--r-- 1 root root  102 Sep 28 09:32 .my-invent.cnf
-rw-r--r-- 1 root root   97 Apr  4  2013 .my-root.cnf
-rw-r--r-- 1 root root  123 Apr  4  2013 .my-root.cnf~
-rw-r--r-- 1 root root  101 Sep 28 09:45 .my.cnf
-rw-r--r-- 1 root root  101 Sep 28 09:45
-rw-r--r-- 1 root root  101 Mar 15  2013 .my.cnf~
-rw-r--r-- 1 root root   40 Dec 18 08:44 mysql-excludelist
root@ac001.ptdc:~# cat /etc/bluelight/dbbackup/mysql-excludelist
+ */
+ *.bz2
+ *.ldif
+ mysql-bin.*
- *

Reviewed /etc/bluelight/dbbackup/.my.*.cnf They have only a password so no configuration.

Next step: further work on the items in the Notes list in the last update. Reduced priority to Low as the existing backup script is believed to be effective; the only issue is that it sends routine mails to .

#3 Updated by Charles Atkinson almost 8 years ago

Started drafting a specification for this feature in "/home/c/d/Projects/bung/BLUE-769 Backup scripts next generation - development log.odt" in section "35 MySQL backup with binary logging"

#4 Updated by Charles Atkinson almost 8 years ago

  • Priority changed from Low to Normal

Changed priority because MySQL binary logging is useful for any database which records valuable transactions. Typically we backup databases daily so a failure could loose up to a day's changes. Candidates for binary log backups include the BL JIRA database and maybe the BL GLPI database when we start updating it heavily.

#5 Updated by Charles Atkinson over 7 years ago

Prototype code from


# MySQL backup with binary logging

# * This script is intended to be run periodically (say every hour).
# * The first time it runs on a day it:
#   - Does a mysqldump of all databases
#   - Emails a report of yesterday's activity
# * Subsequent times it:
#   - Flushes binary logs

# 8 May 2104 Charles for BLAVORG-415 and as a prototype for bung feature 140
#   * Creation, based on Sam's PTDC mysql.backup-hourly script

# Configuration
# ~~~~~~~~~~~~~
mysql_log_dir='/var/log/mysql/'    # Must be as set in /etc/mysql/my.cnf

# Initialisation
# ~~~~~~~~~~~~~~
today=$(date +%Y-%m-%d)

# Set up logging
# ~~~~~~~~~~~~~~
touch "$log_fn" || exit 1
exec >>"$log_fn" 
exec 2>>"$log_fn" 

# Is there a mysqldump for today?
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if [[ ! -f $mysqldump_today_fn ]]; then

    # Create today's mysqldump
    # ~~~~~~~~~~~~~~~~~~~~~~~~
    mkdir -p "$backup_today_dir" || exit 1    # Create today's directory
    # Note: --defaults-file must be the first option
    mysqldump \
        --defaults-file=$defaults_fn \
        --add-drop-table \
        --all-databases \
        --delete-master-logs \
        --events --master-data=2 \
        --flush-logs \
        --lock-all-tables \
        > "$mysqldump_today_uncompressed_fn" 
        bzip2 "$mysqldump_today_uncompressed_fn" #@@@ && rm "$mysqldump_today_uncompressed_fn" 

    # Remove old MySQL binary logs
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    find "$mysql_log_dir" \
        \! -newer "$mysqldump_today_fn" \
        -name 'mysql-bin.*' \

    # Mail yesterday's log
    # ~~~~~~~~~~~~~~~~~~~~
    yesterday=$(date --date=yesterday +%Y-%m-%d)
    if [[ -s $yesterday_log_fn ]]; then
        cat "$yesterday_log_fn" | mailx -s "$organisation_name - $script_name - $yesterday - ERROR" "$report_email" 
        echo 'No error reported' | mailx -s "$organisation_name - $script_name - $today - SUCCESSFUL" $report_email

    # Remove this script's old logs and backups
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    find "$log_dir" -name '*.log' -mtime +28 -delete
    find "$backup_root_dir" -type f -mtime +28 -delete
    find "$backup_root_dir" -type d -empty -delete
    # Flush binary logs and copy to today's backup directory
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    mysqladmin "--defaults-file=$defaults_fn" flush-logs
    rsync -a "$mysql_log_dir/"  "$backup_today_dir" 

exit $?

#6 Updated by Charles Atkinson over 7 years ago

Implemented in and mysql_binlog_bu.sample on 19 May 2014.

No test case (no active MySQL server in the test environment); tested in production; logged in

Later note: it was the proof-of-concept code that was tested in production, not the bung script; the bung script was still partially written at 29 Jun 2014.

Commit: ?

#7 Updated by Charles Atkinson over 7 years ago

  • Status changed from New to Closed

#8 Updated by Charles Atkinson over 7 years ago

  • Status changed from Closed to In Progress

#9 Updated by Charles Atkinson over 7 years ago

  • Status changed from In Progress to Resolved

#10 Updated by Charles Atkinson over 7 years ago

  • Status changed from Resolved to In Progress

Found script still part written More info:

  1. Set up MySQL binary logging in the dev and test environment.
  2. Develop a test case.
  3. Develop and test

#11 Updated by Charles Atkinson over 7 years ago

  • Status changed from In Progress to Resolved

Found MySQL binary logging already set up in the dev and test environment (Slackware default).

Created MySQL database bung_test (turned out not to be necessary) and documented.

Developed test cases 3.6.0 and 3.6.1.

Added test cases 3.6.0 and 3.6.1 to the automated test script.


Test: test cases 3.6.0 and 3.6.1.

Commit: ca81cc67

#12 Updated by Charles Atkinson over 6 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF