Project

General

Profile

Feature #140

mysql_bu.sh: add binary log dump capability

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

Status:
Closed
Priority:
Normal
Start date:
18/01/2014
Due date:
% Done:

0%

Close

Description

The use-case is from ac001.ptdc:

root@ac001.ptdc:/etc/postfix# gev /root/scripts/mysql.backup-hourly
SERVICE='PTDC'
LOGDIR='/var/log/mysql'
BACKUPDIR='/mnt/backup/mysqlbackup'
EXCLUDELIST='/etc/bluelight/dbbackup/mysql-excludelist'
MYUSER='backup'
BACKSERV='backupserver:/home/ptdc/mysql/'
MYPASSWORD='/etc/bluelight/dbbackup/.my.cnf'
MAIL=root
PREFIX=$SERVICE-'mysql-dump.'
DATE=`date +%Y%m%d`
TMPDATE=`date`
DBFN=$PREFIX$DATE'.sql'
MOUNTCHECK=`mount | grep "/mnt/backup"`
TMPFILE=/var/log/mysql/backup-$DATE.log
logger "MYSQL backup started..." 
if [[ ! -f $TMPFILE ]]; then
    touch $TMPFILE
fi
if [[ ${#MOUNTCHECK} == 0 ]];
then
    mount -U 52e3aeff-0216-41af-9e69-499f492ebdae /mnt/backup >> $TMPFILE 2>&1;
    if [[ $? -ne 0 ]]; then
    exit 1
    fi
fi
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
else
    mysqladmin --defaults-extra-file=$MYPASSWORD -u$MYUSER flush-logs >> $TMPFILE 2>&1
fi

History

#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 (http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html), 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
Notes:
  • 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 .my.cnf.org
-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 https://jira.bluelightav.org/browse/BLAVORG-415

#!/bin/bash

# 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
# ~~~~~~~~~~~~~
organisation_name='blavCT102jira'
mysql_log_dir='/var/log/mysql/'    # Must be as set in /etc/mysql/my.cnf
backup_root_dir='/var/backups/mysql/all_with_binary_logs/'
mysql_user='backup_user'
defaults_fn='/etc/opt/bung/mysql.cnf'
report_email=root

# Initialisation
# ~~~~~~~~~~~~~~
script_name=${0##*/}
log_dir=/var/log/$script_name/
today=$(date +%Y-%m-%d)
log_fn=$log_dir$today.log
backup_today_dir=$backup_root_dir$today/

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

# Is there a mysqldump for today?
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysqldump_today_fn=$backup_root_dir$today/all-$today.sql.bz2
if [[ ! -f $mysqldump_today_fn ]]; then

    # Create today's mysqldump
    # ~~~~~~~~~~~~~~~~~~~~~~~~
    mkdir -p "$backup_today_dir" || exit 1    # Create today's directory
    mysqldump_today_uncompressed_fn=${mysqldump_today_fn%.bz2}
    # 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.*' \
        -delete

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

    # 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
else
    # 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" 
fi

exit $?

#6 Updated by Charles Atkinson over 7 years ago

Implemented in mysql_binlog_bu.sh 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 https://jira.bluelightav.org/browse/BLAVORG-415

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: https://jira.bluelightav.org/browse/PTDC-355

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

#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.

Developed mysql_binlog_bu.sh.

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