#!/usr/bin/env bash

# Author:   Zhang Huangbin (zhb@iredmail.org)
# Date:     16/09/2007
# Purpose:  Backup specified mysql databases with command 'mysqldump'.
# License:  This shell script is part of iRedMail project, released under
#           GPL v2.

###########################
# REQUIREMENTS
###########################
#
#   * Required commands:
#       + mysqldump
#       + du
#       + bzip2 or gzip     # If bzip2 is not available, change 'CMD_COMPRESS'
#                           # to use 'gzip'.
#

###########################
# USAGE
###########################
#
#   * It stores all backup copies in directory '/var/vmail/backup' by default,
#     You can change it in variable $BACKUP_ROOTDIR below.
#
#   * Set correct values for below variables:
#
#       BACKUP_ROOTDIR
#       MYSQL_USER
#       MYSQL_PASSWD
#       DATABASES
#       DB_CHARACTER_SET
#       COMPRESS
#       DELETE_PLAIN_SQL_FILE
#
#   * Add crontab job for root user (or whatever user you want):
#
#       # crontab -e -u root
#       1   4   *   *   *   bash /path/to/backup_mysql.sh
#
#   * Make sure 'crond' service is running, and will start automatically when
#     system startup:
#
#       # ---- On RHEL/CentOS ----
#       # chkconfig --level 345 crond on
#       # /etc/init.d/crond status
#
#       # ---- On Debian/Ubuntu ----
#       # update-rc.d cron defaults
#       # /etc/init.d/cron status
#

#########################################################
# Modify below variables to fit your need ----
#########################################################
# Where to store backup copies.
export BACKUP_ROOTDIR="/var/vmail/backup"
+ export BACKUP_ROOTDIR=/var/vmail/backup
+ BACKUP_ROOTDIR=/var/vmail/backup

# MySQL user and password.
export MYSQL_USER="xxx"
+ export MYSQL_USER=xxx
+ MYSQL_USER=xxx
export MYSQL_PASSWD="xxx"
+ export 'MYSQL_PASSWD=xxx'
+ MYSQL_PASSWD='xxx'

# Databases we should backup.
# Multiple databases MUST be seperated by SPACE.
# Your iRedMail server might have below databases:
# mysql, roundcubemail, policyd (or postfixpolicyd), amavisd, iredadmin
export DATABASES=" mysql mysql postfixpolicyd amavisd roundcubemail iredadmin"
+ export 'DATABASES= mysql mysql postfixpolicyd amavisd roundcubemail iredadmin'
+ DATABASES=' mysql mysql postfixpolicyd amavisd roundcubemail iredadmin'

# Database character set for ALL databases.
# Note: Currently, it doesn't support to specify character set for each databases.
export DB_CHARACTER_SET="utf8"
+ export DB_CHARACTER_SET=utf8
+ DB_CHARACTER_SET=utf8

# Compress plain SQL file: YES, NO.
export COMPRESS="YES"
+ export COMPRESS=YES
+ COMPRESS=YES

# Delete plain SQL files after compressed. Compressed copy will be remained.
export DELETE_PLAIN_SQL_FILE="YES"
+ export DELETE_PLAIN_SQL_FILE=YES
+ DELETE_PLAIN_SQL_FILE=YES

#########################################################
# You do *NOT* need to modify below lines.
#########################################################
export PATH='/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/local/sbin'
+ export PATH=/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/local/sbin
+ PATH=/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/local/sbin

# Commands.
export CMD_DATE='/bin/date'
+ export CMD_DATE=/bin/date
+ CMD_DATE=/bin/date
export CMD_DU='du -sh'
+ export 'CMD_DU=du -sh'
+ CMD_DU='du -sh'
export CMD_COMPRESS='bzip2 -9'
+ export 'CMD_COMPRESS=bzip2 -9'
+ CMD_COMPRESS='bzip2 -9'
export CMD_MYSQLDUMP='mysqldump'
+ export CMD_MYSQLDUMP=mysqldump
+ CMD_MYSQLDUMP=mysqldump
export CMD_MYSQL='mysql'
+ export CMD_MYSQL=mysql
+ CMD_MYSQL=mysql

# Date.
export YEAR="$(${CMD_DATE} +%Y)"
${CMD_DATE} +%Y)"
${CMD_DATE} +%Y)
${CMD_DATE} +%Y
++ /bin/date +%Y
+ export YEAR=2013
+ YEAR=2013
export MONTH="$(${CMD_DATE} +%m)"
${CMD_DATE} +%m)"
${CMD_DATE} +%m)
${CMD_DATE} +%m
++ /bin/date +%m
+ export MONTH=05
+ MONTH=05
export DAY="$(${CMD_DATE} +%d)"
${CMD_DATE} +%d)"
${CMD_DATE} +%d)
${CMD_DATE} +%d
++ /bin/date +%d
+ export DAY=22
+ DAY=22
export TIME="$(${CMD_DATE} +%H.%M.%S)"
${CMD_DATE} +%H.%M.%S)"
${CMD_DATE} +%H.%M.%S)
${CMD_DATE} +%H.%M.%S
++ /bin/date +%H.%M.%S
+ export TIME=12.53.23
+ TIME=12.53.23
export TIMESTAMP="${YEAR}.${MONTH}.${DAY}.${TIME}"
+ export TIMESTAMP=2013.05.22.12.53.23
+ TIMESTAMP=2013.05.22.12.53.23

# Pre-defined backup status
export BACKUP_SUCCESS='YES'
+ export BACKUP_SUCCESS=YES
+ BACKUP_SUCCESS=YES

# Define, check, create directories.
export BACKUP_DIR="${BACKUP_ROOTDIR}/mysql/${YEAR}/${MONTH}/${DAY}"
+ export BACKUP_DIR=/var/vmail/backup/mysql/2013/05/22
+ BACKUP_DIR=/var/vmail/backup/mysql/2013/05/22

# Log file
export LOGFILE="${BACKUP_DIR}/${TIMESTAMP}.log"
+ export LOGFILE=/var/vmail/backup/mysql/2013/05/22/2013.05.22.12.53.23.log
+ LOGFILE=/var/vmail/backup/mysql/2013/05/22/2013.05.22.12.53.23.log

# Check required variables.
if [ X"${MYSQL_USER}" == X"" -o X"${MYSQL_PASSWD}" == X"" -o X"${DATABASES}" == X"" ]; then
    echo "[ERROR] You don't have correct MySQL related configurations in file: ${0}" 1>&2
    echo -e "\t- MYSQL_USER\n\t- MYSQL_PASSWD\n\t- DATABASES" 1>&2
    echo "Please configure them first." 1>&2

    exit 255
fi
+ '[' Xxxx == X -o 'Xxxx' == X -o 'X mysql mysql postfixpolicyd amavisd roundcubemail iredadmin' == X ']'

# Verify MySQL connection.
${CMD_MYSQL} -u"${MYSQL_USER}" -p"${MYSQL_PASSWD}" -e "show databases" &>/dev/null
+ mysql -uxxx '-pxxx' -e 'show databases'
if [ X"$?" != X"0" ]; then
    echo "[ERROR] MySQL username or password is incorrect in file ${0}." 1>&2
    echo "Please fix them first." 1>&2

    exit 255
fi
+ '[' X7 '!=' X0 ']'
+ echo '[ERROR] MySQL username or password is incorrect in file /var/vmail/backup/backup_mysql.sh.'
[ERROR] MySQL username or password is incorrect in file /var/vmail/backup/backup_mysql.sh.
+ echo 'Please fix them first.'
Please fix them first.
+ exit 255

bash -xv /var/vmail/backup/backup_mysql.sh

.
.
.
# Verify MySQL connection.
${CMD_MYSQL} -u"${MYSQL_USER}" -p"${MYSQL_PASSWD}" -e "show databases" &>/dev/null
+ mysql -uroot -p******** -e 'show databases'
if [ X"$?" != X"0" ]; then
    echo "[ERROR] MySQL username or password is incorrect in file ${0}." 1>&2
    echo "Please fix them first." 1>&2

    exit 255
fi
+ '[' X1 '!=' X0 ']'
+ echo '[ERROR] MySQL username or password is incorrect in file /var/vmail/backup/backup_mysql.sh.'
[ERROR] MySQL username or password is incorrect in file /var/vmail/backup/backup_mysql.sh.
+ echo 'Please fix them first.'
Please fix them first.
+ exit 255

root:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| amavisd            |
| cluebringer        |
| iredadmin          |
| mysql              |
| performance_schema |
| roundcubemail      |
| test               |
| vmail              |
+--------------------+
9 rows in set (0.05 sec)

mysql>

mysql -uroot -p'***$$*' -e 'show databases'