LHammonds Tutorials

Tutorials
https://hammondslegacy.com/forum/

My Notes for Installing MySQL on Ubuntu Server 12.04.3 LTS

https://hammondslegacy.com/forum/viewtopic.php?f=40&t=193

Page 1 of 1

My Notes for Installing MySQL on Ubuntu Server 12.04.3 LTS

Posted: Tue Jul 17, 2012 8:40 am
by LHammonds
Greetings and salutations,

I hope this thread will be helpful to those who follow in my foot steps as well as getting any advice based on what I have done / documented.

To discuss this thread, please participate here: Ubuntu Forums

High-level overview

This thread will cover installation of a dedicated Ubuntu server and MySQL database. The server will be installed inside a virtual machine in vSphere running on ESXi servers. Notes will also be supplied for doing the same thing for VirtualBox on a Windows 7 PC. Migration of data from an older MySQL server to the new one will be covered. Although there are some VMware-specific and VirtualBox-specific steps, they are very few and the majority of this documentation will work for other Virtual Machines or even directly installed onto a physical machine (e.g. bare-metal install). If you have any advice on doing things better, please let me know by replying to this thread on the Ubuntu forums.

This thread will also cover some custom scripts to help automate tasks such as backing up, automatically growing the file system when free space is low, etc.

Tools utilized in this process

Helpful links

The list below are sources of information that was helpful in the creation of this document.
Assumptions

This documentation will need to make use of some very-specific information that will most-likely be different for each person / location. And as such, this information will be noted in this section. They will be highlighted in red throughout the document as a reminder that you should plug-in your own value rather than actually using these "place-holder" values.

Under no circumstance should you use the actual values listed below. They are place-holders for the real thing. This is just a checklist template you need to have answered before you start the install process.

Wherever you see RED in this document, you need to substitute it for you will use in your environment.

  • Ubuntu Server name: srv-mysql
  • Internet domain: mydomain.com
  • Ubuntu Server IP address: 192.168.107.27
  • Ubuntu Server IP subnet mask: 255.255.255.0
  • Ubuntu Server IP gateway: 192.168.107.1
  • Internal DNS Server 1: 192.168.107.212
  • Internal DNS Server 2: 192.168.107.213
  • External DNS Server 1: 8.8.8.4
  • External DNS Server 2: 8.8.8.5
  • Ubuntu Admin ID: administrator
  • Ubuntu Admin Password: myadminpass
  • Email Server (remote): 192.168.107.25
  • MySQL root Password: mysqlrootpass
  • Windows Share ID: mysqlshare
  • Windows Share Password: mysqlsharepass
It is also assumed the reader knows how to use the VI editor. If not, you will need to beef up your skill set or use a different editor in place of it.

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 7:57 am
by LHammonds
Analysis and Design

The Ubuntu Server Long-Term Support (LTS) is free but we have the option of buy support and that is the main reason this server was selected.

The steps for setting up the base server are covered in this article: How to install and configure Ubuntu Server

It is assumed that the server was configured according to that article with the exceptions that the assumptions in red (variables above) are used instead of the assumptions in that document since we are building a database server.

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 7:59 am
by LHammonds
** DELETED **

OS install steps covered in the other thread.

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 8:00 am
by LHammonds
** DELETED **

OS install steps covered in the other thread.

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 8:02 am
by LHammonds
** DELETED **

OS install steps covered in the other thread.

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 8:11 am
by LHammonds
Install MySQL
  1. Start the Ubuntu server and connect using PuTTY.
  2. At the login prompt, login with your administrator account (administrator / myadminpass) and then temporarily grant yourself super user privilages by typing sudo su
  3. Install MySQL by typing aptitude -y install mysql-server
  4. Type your password for the MySQL "root" user account mysqlrootpass {ENTER}
  5. Re-type the password again mysqlrootpass {ENTER}
  6. When installation is completed, the MySQL service should automatically start
  7. Verify the service is running by typing either of the following two commands:
    service mysql status
    or
    netstat -tap | grep mysql
Configure MySQL
  1. Make a personal MySQL config file for the root user so the password is not required when running MySQL commands
    touch ~/my.cnf chmod 0600 ~/my.cnf vi ~/my.cnf
  2. Add the following lines to ~/my.cnf
    [client] password=mysqlrootpass
  3. Edit the global mysql config file by typing vi /etc/mysql/my.cnf
  4. Comment-out the bind-address by adding a pound sign (#) at the beginning of the line:
    #bind-address = 127.0.0.1
  5. Add the following line to the very bottom:
    !include ~/my.cnf
  6. Restart the mysql service by typing service mysql restart
  7. See if the following command will work without prompting for a password: mysqladmin status
  8. If this did not work, it is possible you did not uninstall apparmor in an earlier step

Tighten Security

MySQL comes with a script to tighten-down security for a production server.
  1. Connect to the server using PuTTY and at the login prompt, login with your administrator account (administrator / myadminpass) and then temporarily grant yourself super user privilages by typing sudo su
  2. Type mysql_secure_installation
  3. Type the MySQL "root" password: mysqlrootpass
  4. Type N to skip changing the password
  5. Type Y to remove the anonymous user account
  6. Type Y to disallow remote root login
  7. Type Y to remove the test database
  8. Type Y to reload privilege tables
Relocate the Databases

If you do not want to leave your databases in the default location (/var/lib/mysql), then you can follow these steps to move them somewhere else. In this example, we will move them to /opt/mysql

  1. At the login prompt, login with your administrator account (administrator / myadminpass) and then temporarily grant yourself super user privilages by typing sudo su
  2. At the console, type the following:
    mkdir -p /opt/mysql chown mysql:mysql /opt/mysql vi /etc/mysql/my.cnf
  3. Change the location where the databases by changing datadir from /var/lib/mysql to:
    datadir = /opt/mysql
  4. Type the following commands to move the database(s)
    service mysql stop mv /var/lib/mysql/* /opt/mysql/. service mysql start
  5. Verify the service is running by typing either of the following two commands:
    service mysql status
    or
    netstat -tap | grep mysql
NOTE: If you did not remove apparmor, it will prevent MySQL from starting at this point until you update apparmor or remove it.

Reset lost root password

If you ever find yourself in the position of not being able to login (locally) to your database with your root password, you will need to reset the password. For example, phpmyadmin fails to change the password correctly and neither the old nor the new password works.

This procedure will require taking the database offline for a short time...so schedule the downtime appropriately.

In this example, we are going to set the password to mysqlrootpass (but please DO NOT actually use this exact password for your server...doing so will earn you derp points).
  1. Connect to the server using PuTTY.
  2. Login with your administrator account. At the $ prompt, temporarily grant yourself super user privileges by typing sudo su
  3. Shutdown the database service:
    service mysql stop
  4. Start MySQL in safe mode so that it does not try to authenticate users:
    mysqld_safe --skip-grant-tables &
  5. Connect to the MySQL server via the command-line utility (which should not ask for a password):
    mysql -u root
  6. Select the primary database and reset the password:
    use mysql; update user set password=PASSWORD("mysqlrootpass") where User='root'; flush privileges; quit
  7. Stop the MySQL service and restart it in the normal mode:
    service mysql stop service mysql start
  8. Test out the new password by connecting with the command-line utility:
    mysql -u root -p quit
  9. Update your ~/my.cnf file with the current password.
Quick Usage Examples
  1. Connect to the server using PuTTY.
  2. Login with your administrator account. At the $ prompt, temporarily grant yourself super user privileges by typing sudo su
  3. Start a mysql session and create a database called minecraft and a user called minecraftuser with a password of "mysqlpass" and grant the user access to the database:
    mysql CREATE DATABASE minecraft; GRANT ALL PRIVILEGES ON minecraft.* TO 'minecraftuser'@'%' IDENTIFIED BY 'mysqlpass'; FLUSH PRIVILEGES; quit
  4. To remove the changes you just made, you can run the following commands to delete the user and database:
    DELETE USER minecraftuser; DROP DATABASE minecraft;

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 8:15 am
by LHammonds
Scripting

Much of the solutions beyond this point involve scripts (programming snippets / automated commands).

In particular, they are Bash Scripts. I chose this due to its popularity and the fact it comes with Ubuntu. I try to make use of what comes with the system without requiring additional software / services unless they really add to the bottom line such as decreasing the time it takes for a process to run or to conserve storage and bandwidth usage.

When setting up a server and testing things out, there is typically very little concern for procedures / process since much of the activity is exploration and experimentation as well as not having an impact on production. However, once a server goes into production, processes and procedures need to be in place to ensure the availability of the services being provided.

In regards to these scripts, they will be treated like any other program and will require being tested, documented and go through a promotion process.

The ideal situation would involve 3 servers (for a single server setup). A test / development server, a quality assurance staging server and the production server itself. If 3 servers cannot be utilized, then it can still work well with 2 servers. Testing scripts / programs / restore on the production server is not advisable and many times impractical...how can you test your restore process / data periodically if you only have a production server?

The QA Staging server would resemble the production server as close as possible. The server should be setup in such a way that production backups are restored to this server which also tests and validates the backup / restore process as well as maintains a close representation of the production server to mitigate variable risk involved when testing new or modified programs and upgrades.

The test / development server can serve as the QA server if absolutely necessary.

The directory structure and how scripts can import other scripts will be configured to facilitate this process.

Example:

Directory path for scripts to import common variables, functions and server settings: /var/scripts/common/

Directory path for production scripts: /var/scripts/prod/

Directory path for QA staging area scripts: /var/scripts/qa/

Directory path for test / development scripts: /var/scripts/test/

Directory path for data for use by scripts: /var/scripts/data/

With a production and test servers on physically different machines, the "common" scripts folder can be custom-tailored for that environment and allow for minimal changes to a script when running on the test, QA or production server. This is similar to "normalizing" a database. If you have a variable, path or function that is duplicated in multiple scripts, consider pulling it out and placing it in the common folder. If ever you need to change who receives the email reports, you only need to update a single script and all programs will use the new reference from that point on.

Most of my scripts will import a file called "standard.conf" from the common script folder.

/var/scripts/common/standard.conf (contents of the file on the production server)
## Global Variables ## TEMPDIR="/tmp" SHAREDIR="/srv/samba/share" MYDOMAIN="mydomain.com" ADMINEMAIL="admin@${MYDOMAIN}" REPORTEMAIL="lhammonds@${MYDOMAIN}" BACKUPDIR="/backup" OFFSITEDIR="/mnt/backup" OFFSITETESTFILE="${OFFSITEDIR}/online.txt" ARCHIVEMETHOD="tar.7z" ## Choices are tar.7z or tgz HOSTNAME="$(hostname -s)" SCRIPTNAME="$0" SCRIPTDIR="/var/scripts" MAILFILE="${TEMPDIR}/mailfile.$$" ## Global Functions ## function f_sendmail() { ## Purpose: Send administrative email message. ## Parameter #1 = Subject ## Parameter #2 = Body sendemail -f "${ADMINEMAIL}" -t "${REPORTEMAIL}" -u "${1}" -m "${2}\n\nServer: ${HOSTNAME}\nProgram: ${SCRIPTNAME}\nLog: ${LOGFILE}" -s srv-mail:25 1>/dev/null 2>&1 } function f_sendusermail() { ## Purpose: Send end-user email message. ## Parameter #1 = To ## Parameter #2 = Subject ## Parameter #3 = Body sendemail -f "${ADMINEMAIL}" -t "${1}" -u "${2}" -m "${3}" -s srv-mail:25 1>/dev/null 2>&1 } function f_mount() { ## Mount the pre-configured Windows share folder. ## NOTE: The Windows share should have a file called "online.txt" mount -t cifs //srv-backup/mysql ${OFFSITEDIR} --options nouser,rw,nofail,noatime,noexec,credentials=/etc/cifspw } function f_umount() { ## Dismount the Windows share folder. ## NOTE: The unmounted folder should have a file called "offline.txt" umount ${OFFSITEDIR} }
/var/scripts/common/standard.conf (contents of the file on the test server)
## Global Variables ## TEMPDIR="/tmp" SHAREDIR="/srv/samba/share" MYDOMAIN="mytestdomain.com" ADMINEMAIL="test1@${MYDOMAIN}" REPORTEMAIL="test2@${MYDOMAIN}" BACKUPDIR="/backup" OFFSITEDIR="/mnt/fakedir" OFFSITETESTFILE="${OFFSITEDIR}/online.txt" ARCHIVEMETHOD="tar.7z" ## Choices are tar.7z or tgz HOSTNAME="$(hostname -s)" SCRIPTNAME="$0" SCRIPTDIR="/var/scripts" MAILFILE="${TEMPDIR}/mailfile.$$" ## Global Functions ## function f_sendmail() { ## Purpose: Send administrative email message. ## Parameter #1 = Subject ## Parameter #2 = Body sendemail -f "${ADMINEMAIL}" -t "${REPORTEMAIL}" -u "${1}" -m "${2}\n\nServer: ${HOSTNAME}\nProgram: ${SCRIPTNAME}\nLog: ${LOGFILE}" -s srv-mail:25 1>/dev/null 2>&1 } function f_sendusermail() { ## Purpose: Send end-user email message. ## Parameter #1 = To ## Parameter #2 = Subject ## Parameter #3 = Body sendemail -f "${ADMINEMAIL}" -t "${1}" -u "${2}" -m "${3}" -s srv-mail:25 1>/dev/null 2>&1 } function f_mount() { ## Mount the pre-configured Windows share folder. ## NOTE: The Windows share should have a file called "online.txt" mount -t cifs //mypc/share ${OFFSITEDIR} --options nouser,rw,nofail,noatime,noexec,credentials=/etc/cifspw } function f_umount() { ## Dismount the Windows share folder. ## NOTE: The unmounted folder should have a file called "offline.txt" umount ${OFFSITEDIR} }
When receiving administrative email notifications, the server name, script name and path will be included at the bottom of the email every time. It will be readily apparent if the email was generated from the test, qa or production server simply because of the location (even if test, qa and production are all on the same server).

Here are the scripts to help automate the creation of this structure on the various servers (would run all of them if all are the same box)

setup-script-prod.sh
#!/bin/bash if [ ! -d /var/scripts/prod ]; then mkdir -p /var/scripts/prod fi if [ ! -d /var/scripts/common ]; then mkdir -p /var/scripts/common fi if [ ! -d /var/scripts/data ]; then mkdir -p /var/scripts/data fi chown root:root -R /var/scripts chmod 0755 -R /var/scripts
setup-script-qa.sh
#!/bin/bash if [ ! -d /var/scripts/qa ]; then mkdir -p /var/scripts/qa fi if [ ! -d /var/scripts/common ]; then mkdir -p /var/scripts/common fi if [ ! -d /var/scripts/data ]; then mkdir -p /var/scripts/data fi chown root:root -R /var/scripts chmod 0777 -R /var/scripts
setup-script-test.sh
#!/bin/bash if [ ! -d /var/scripts/test ]; then mkdir -p /var/scripts/test fi if [ ! -d /var/scripts/common ]; then mkdir -p /var/scripts/common fi if [ ! -d /var/scripts/data ]; then mkdir -p /var/scripts/data fi chown root:root -R /var/scripts chmod 0777 -R /var/scripts

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 8:17 am
by LHammonds
Toggle Mount Script

Sometimes it is helpful to toggle the mount on or off so here is a script you might find helpful.

/var/scripts/prod/togglemount.sh

Code: Select all

#!/bin/bash
#############################################
## Name          : togglemount.sh
## Version       : 1.0
## Date          : 2012-05-11
## Author        : LHammonds
## Compatibility : Ubuntu Server 12.04 LTS
## Purpose       : Toggle the mount status of a pre-configured backup mount.
## Run Frequency : Manual as needed.
## Exit Codes    :
##   0 = success
##   1 = failure
################ CHANGE LOG #################
## DATE       WHO WHAT WAS CHANGED
## ---------- --- ----------------------------
## 2012-05-11 LTH Created script.
#############################################
 
## Import common variables and functions. ##
source /var/scripts/common/standard.conf
ERRORFLAG=0
 
if [ -f ${OFFSITEDIR}/offline.txt ]; then
  echo "Windows share is not mounted.  Mounting share now..."
  f_mount
  sleep 2
  if [ -f ${OFFSITEDIR}/online.txt ]; then
    echo "Mount successful.  Listing contents:"
  else
    echo "Mount failed.  Listing contents:"
    ERRORFLAG=1
  fi
else
  echo "Windows share is mounted.  Dismounting share now..."
  f_umount
  sleep 2
  if [ -f ${OFFSITEDIR}/offline.txt ]; then
    echo "Dismount successful.  Listing contents:"
  else
    echo "Dismount failed.  Listing contents:"
    ERRORFLAG=1
  fi
fi
ls -l ${OFFSITEDIR}
exit ${ERRORFLAG}
MySQL Backup Script

This script is designed to perform a full backup of all databases while the server is online. It actually does three kinds of backups. First, it pulls all the databases into a single file which is good for restoring everything on new servers. It then pulls by database to create database files and then it pulls each individual table into its own file which is easier to isolate individual items to restore if necessary.

My needs are not that great at the moment so I backup one time per day...which is handled by a crontab schedule covered later in this thread.

/var/scripts/prod/mysql-backup.sh

Code: Select all

#!/bin/bash
#############################################
## Name          : mysql-backup.sh
## Version       : 1.3
## Date          : 2012-10-02
## Author        : LHammonds
## Purpose       : Complete backup of MySQL database.
## Compatibility : Verified on to work on:
##                  - Ubuntu Server 10.04 LTS - 12.04.1 LTS
##                  - MySQL 5.1.41 - 5.5.24
## Requirements  : p7zip-full (if ARCHIVEMETHOD=tar.7z), sendemail
## Run Frequency : Once per day after hours or as needed (will not shutdown service)
## Exit Codes    : (if multiple errors, value is the addition of codes)
##    0 = success
##    1 = 7zip not installed
##    2 = archive failure
##    4 = archive purge failure
##    8 = configuration error
##   16 = mount warning
################ CHANGE LOG #################
## DATE       WHO WHAT WAS CHANGED
## ---------- --- ----------------------------
## 2011-12-19 LTH Created script.
## 2012-01-09 LTH Bugfix - f_PurgeOldestArchive
## 2012-08-07 LTH Added --routines to mysqldump
## 2012-10-02 LTH Fixed if condition, changed () to []
#############################################
 
## Import common variables and functions. ##
source /var/scripts/common/standard.conf
 
LOGFILE="${LOGDIR}/mysql-backup.log"
LOCKFILE="${TEMPDIR}/mysql-backup.lock"
TARGETDIR="${BACKUPDIR}/mysql"
OFFSITEBACKDIR="${OFFSITEDIR}/mysql"
ARCHIVEFILE="`date +%Y-%m-%d-%H-%M`_mysql-backup.${ARCHIVEMETHOD}"
ERRORFLAG=0
 
#######################################
##            FUNCTIONS              ##
#######################################
function f_PurgeOldestArchive()
{
  ## Purpose: Delete the oldest archive on the remote site.
  ## Return values:
  ##    0 = Success
  ##    1 = Cannot delete file
  ##    9 = Configuration error, path empty
 
  ## Variable Error Check. *
  if [ ${OFFSITEBACKDIR} = "" ]; then
    ## Make darn sure the path is not empty since we do NOT
    ## want to start purging files from a random location.
    echo "`date +%Y-%m-%d_%H:%M:%S` --- Purge error: OFFSITEBACKDIR site variable is empty!" >> ${LOGFILE}
    return 9
  fi
  ## Get the name of the oldest file.
  OLDESTFILE=`ls -1t ${OFFSITEBACKDIR} | tail -1`
  if [ "${OLDESTFILE}" = "" ]; then
    ## Error. Filename variable empty.
    echo "`date +%Y-%m-%d_%H:%M:%S` --- Purge error: OLDESTFILE variable is empty." >> ${LOGFILE}
    return 9
  else   
    FILESIZE=`ls -lak "${OFFSITEBACKDIR}/${OLDESTFILE}" | awk '{ print $5 }' | sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'`
    echo "`date +%Y-%m-%d_%H:%M:%S` --- Purging old file: ${OFFSITEBACKDIR}/${OLDESTFILE}, Size = ${FILESIZE} kb" >> ${LOGFILE}
    rm "${OFFSITEBACKDIR}/${OLDESTFILE}"
    if [ -f "${OFFSITEBACKDIR}/${OLDESTFILE}" ]; then
      ## File still exists.  Return error.
      return 1
    else
      return 0
    fi
  fi
}
 
function f_cleanup()
{
  echo "`date +%Y-%m-%d_%H:%M:%S` - MySQL backup exit code: ${ERRORFLAG}" >> ${LOGFILE}
 
  if [ -f ${LOCKFILE} ];then
    ## Remove lock file so other backup jobs can run.
    rm ${LOCKFILE} 1>/dev/null 2>&1
  fi
  ## Email the result to the administrator.
  if [ ${ERRORFLAG} -eq 0 ]; then
    f_sendmail "MySQL Backup Success" "MySQL backup completed with no errors."
  else
    f_sendmail "MySQL Backup ERROR" "MySQL backup failed.  ERRORFLAG = ${ERRORFLAG}"
  fi
}
 
function f_emergencyexit()
{
  ## Purpose: Exit script as cleanly as possible.
  ## Parameter #1 = Error Code
  f_cleanup
  exit $1
}
 
#######################################
##           MAIN PROGRAM            ##
#######################################
 
## Binaries ##
TAR="$(which tar)"
MY7ZIP="$(which 7za)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
 
if [ -f ${LOCKFILE} ]; then
  ## Program lock file detected.  Abort script.
  f_sendmail "MySQL Backup aborted - Lock File" "This script tried to run but detected the lock file: ${LOCKFILE}\n\nPlease check to make sure the file does not remain when this script is not actually running."
  exit 1
else
  ## Create the lock file to ensure only one script is running at a time.
  echo "`date +%Y-%m-%d_%H:%M:%S` ${SCRIPTNAME}" > ${LOCKFILE}
fi
 
echo "`date +%Y-%m-%d_%H:%M:%S` - MySQL Backup started." >> ${LOGFILE}
 
## If the 7-Zip archive method is specified, make sure the package is installed.
if [ "${ARCHIVEMETHOD}" = "tar.7z" ]; then
  if [ ! -f "/usr/bin/7za" ]; then
    ## Required package (7-Zip) not installed.
    echo "`date +%Y-%m-%d_%H:%M:%S` - CRITICAL ERROR: 7-Zip package not installed.  Please install by typing 'aptitude -y install p7zip-full'" >> ${LOGFILE}
    ERRORFLAG=1
    f_emergencyexit ${ERRORFLAG}
  fi
fi
 
echo "`date +%Y-%m-%d_%H:%M:%S` --- Partition status:" >> ${LOGFILE}
df -h >> ${LOGFILE}
 
## Document the current uptime.
${MYSQL} -e status | grep -i uptime >> ${LOGFILE}
 
StartTime="$(date +%s)"
 
echo "`date +%Y-%m-%d_%H:%M:%S` --- Space consumed in ${MYSQLDIR} = `du -sh ${MYSQLDIR} | awk '{ print $1 }'`" >> ${LOGFILE}
 
## Backup all databases.
${MYSQLDUMP} --skip-lock-tables --all-databases --routines > ${TARGETDIR}/mysql-all.sql
 
## Loop through every database.
DATABASES=$(echo "show databases;"|mysql --skip-column-names)
for DATABASE in ${DATABASES}
do
  if [ "${DATABASE}" != "information_schema" ] && [ "${DATABASE}" != "performance_schema" ]; then
    ## Backup individual database.
    ${MYSQLDUMP} ${DATABASE} > ${TARGETDIR}/${DATABASE}.sql
    ## Create database sub-folder.
    mkdir -p ${TARGETDIR}/${DATABASE}
    ## Export each table in the database individually.
    for TABLE in `echo "show tables" | $MYSQL ${DATABASE}|grep -v Tables_in_`;
    do
      FILE=${TARGETDIR}/${DATABASE}/${TABLE}.sql
      case "${TABLE}" in
        general_log)
          ${MYSQLDUMP} ${DATABASE} ${TABLE} --skip-lock-tables > ${FILE}
          ;;
        slow_log)
          ${MYSQLDUMP} ${DATABASE} ${TABLE} --skip-lock-tables > ${FILE}
          ;;
        *)
          ${MYSQLDUMP} ${DATABASE} ${TABLE} > ${FILE}
          ;;
      esac
    done
  fi
done
 
## Compress the backup into a single file based on archive method specified.
echo "`date +%Y-%m-%d_%H:%M:%S` --- Compressing archive: ${TEMPDIR}/${ARCHIVEFILE}" >> ${LOGFILE}
case "${ARCHIVEMETHOD}" in
tar.7z)
  ${TAR} -cpf - ${TARGETDIR} | ${MY7ZIP} a -si -mx=9 -w${TEMPDIR} ${TEMPDIR}/${ARCHIVEFILE} 1>/dev/null 2>&1
  RETURNVALUE=$?
  ## Restore using one of the following commands (do not uncomment, only for notation):
  ## 7za x -so -w/tmp ${TEMPDIR}/${ARCHIVEFILE} | tar -C / -xf -
  ## 7za x -so -w/tmp ${TEMPDIR}/${ARCHIVEFILE} | tar -C ${TEMPDIR}/restore --strip-components=1 -xf -
  ;;
tgz)
  ${TAR} -cpzf ${TEMPDIR}/${ARCHIVEFILE} ${TARGETDIR} 1>/dev/null 2>&1
  RETURNVALUE=$?
  ## Restore using one of the following commands (do not uncomment, only for notation):
  ## tar -C / -xzf ${TEMPDIR}/${ARCHIVEFILE}
  ## tar -C ${TEMPDIR}/restore --strip-components=1 -xzf ${TEMPDIR}/${ARCHIVEFILE}
  ;;
*)
  ${TAR} -cpzf ${TEMPDIR}/${ARCHIVEFILE} ${TARGETDIR} 1>/dev/null 2>&1
  RETURNVALUE=$?
  ;;
esac
 
if [ ${RETURNVALUE} -ne 0 ]; then
  ## tar command failed.  Send warning email.
  f_sendmail "MySQL Backup Failure - tar" "tar failed with return value of ${RETURNVALUE}"
  ERRORFLAG=$((${ERRORFLAG} + 2))
fi
 
## Mount the remote folder. ##
f_mount
 
if [ ! -f ${OFFSITETESTFILE} ]; then
  ## Could not find expected file on remote site.  Assuming failed mount.
  ERRORFLAG=$((${ERRORFLAG} + 16))
  echo "`date +%Y-%m-%d_%H:%M:%S` --- ERROR: Cannot detect remote location: ${OFFSITETESTFILE}" >> ${LOGFILE}
  f_emergencyexit ${ERRORFLAG}
fi
 
FREESPACE=`df -k ${OFFSITEDIR} | grep ${OFFSITEDIR} | awk '{ print $3 }'`
BACKUPSIZE=`ls -lak "${TEMPDIR}/${ARCHIVEFILE}" | awk '{ print $5 }'`
 
## Make sure space is available on the remote server to copy the file.
if [ ${FREESPACE} -lt ${BACKUPSIZE} ]; then
  ## Not enough free space available.  Purge existing backups until there is room.
  ENOUGHSPACE=0
  while [ ${ENOUGHSPACE} -eq 0 ]
  do
    f_PurgeOldestArchive
    RETURNVALUE=$?
    case ${RETURNVALUE} in
    1)
      ## Cannot purge archives to free up space.  End program gracefully.
      echo "`date +%Y-%m-%d_%H:%M:%S` - ERROR: Not enough free space on ${OFFSITEBACKDIR} and cannot purge old archives.  Script aborted." >> ${LOGFILE}
      ## Stop and exit the script with an error code.
      ERRORFLAG=$((${ERRORFLAG} + 4))
      f_emergencyexit ${ERRORFLAG}
      ;;
    9)
      ## Configuration error, end program gracefully.
      echo "`date +%Y-%m-%d_%H:%M:%S` - ERROR: Configuration problem. Script aborted." >> ${LOGFILE}
      ## Stop and exit the script with an error code.
      ERRORFLAG=$((${ERRORFLAG} + 8))
      f_emergencyexit ${ERRORFLAG}
      ;;
    esac
    FREESPACE=`df -k ${OFFSITEDIR} | grep ${OFFSITEDIR} | awk '{ print $3 }'`
    if [ ${FREESPACE} -gt ${BACKUPSIZE} ]; then
      ## Enough space is now available.
      ENOUGHSPACE=1
    else
      ## Not enough space is available yet.
      ENOUGHSPACE=0
    fi
  done
fi
 
## Copy the backup to an offsite storage location.
echo "`date +%Y-%m-%d_%H:%M:%S` --- Copying archive file to offsite location." >> ${LOGFILE}
cp ${TEMPDIR}/${ARCHIVEFILE} ${OFFSITEBACKDIR}/${ARCHIVEFILE} 1>/dev/null 2>&1
if [ ! -f ${OFFSITEBACKDIR}/${ARCHIVEFILE} ]; then
  ## NON-FATAL ERROR: Copy command did not work.  Send email notification.
  echo "`date +%Y-%m-%d_%H:%M:%S` --- WARNING: Remote copy failed. ${OFFSITEBACKDIR}/${ARCHIVEFILE} does not exist!" >> ${LOGFILE}
  f_sendmail "MySQL Backup Failure - Remote Copy" "Remote copy failed. ${OFFSITEBACKDIR}/${ARCHIVEFILE} does not exist\n\nBackup file still remains in this location: ${HOSTNAME}:${TEMPDIR}/${ARCHIVEFILE}"
else
  ## Remove local copy of the compressed backup file
  rm ${TEMPDIR}/${ARCHIVEFILE}
fi
 
## Unmount the Windows shared folder.
f_umount
 
## Calculate total time for backup.
FinishTime="$(date +%s)"
ElapsedTime="$(expr ${FinishTime} - ${StartTime})"
Hours=$((${ElapsedTime} / 3600))
ElapsedTime=$((${ElapsedTime} - ${Hours} * 3600))
Minutes=$((${ElapsedTime} / 60))
Seconds=$((${ElapsedTime} - ${Minutes} * 60))
 
echo "`date +%Y-%m-%d_%H:%M:%S` --- Total backup time: ${Hours} hour(s) ${Minutes} minute(s) ${Seconds} second(s)" >> ${LOGFILE}
 
echo "`date +%Y-%m-%d_%H:%M:%S` - MySQL backup completed." >> ${LOGFILE}
 
## Perform cleanup routine.
f_cleanup
## Exit with the combined return code value.
exit ${ERRORFLAG}
Here is a sample of the log output:

/var/log/mysql-backup.log

Code: Select all

2012-05-16_23:00:01 - MySQL backup started.
2012-05-16_23:00:01 --- Partition status:
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/LVG-root  3.8G  1.1G  2.6G  29% /
udev                  237M  4.0K  237M   1% /dev
tmpfs                  99M  540K   98M   1% /run
none                  5.0M     0  5.0M   0% /run/lock
none                  246M     0  246M   0% /run/shm
/dev/sda1             179M   25M  145M  15% /boot
/dev/mapper/LVG-bak   4.0G   65M  3.8G   2% /backup
/dev/mapper/LVG-var   2.0G  347M  1.6G  18% /var
/dev/mapper/LVG-tmp   2.0G   31M  1.9G   2% /tmp
Uptime:            13 days 8 hours 50 min 14 sec
2012-05-16_23:00:01 --- Space consumed in  = 104K
2012-05-16_23:00:07 --- Compressing archive: /tmp/2012-05-16-23-00_mysql-backup.tar.7z
2012-05-16_23:00:18 --- Copying archive file to offsite location.
2012-05-16_23:00:19 --- Total backup time: 0 hour(s) 0 minute(s) 18 second(s)
2012-05-16_23:00:19 - MySQL backup completed.
2012-05-16_23:00:19 - MySQL backup exit code: 0
2012-05-17_23:00:02 - MySQL backup started.
2012-05-17_23:00:02 --- Partition status:
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/LVG-root  3.8G  1.1G  2.6G  29% /
udev                  237M  4.0K  237M   1% /dev
tmpfs                  99M  540K   98M   1% /run
none                  5.0M     0  5.0M   0% /run/lock
none                  246M     0  246M   0% /run/shm
/dev/sda1             179M   25M  145M  15% /boot
/dev/mapper/LVG-bak   4.0G   65M  3.8G   2% /backup
/dev/mapper/LVG-var   2.0G  347M  1.6G  18% /var
/dev/mapper/LVG-tmp   2.0G   31M  1.9G   2% /tmp
Uptime:            8 hours 38 min 26 sec
2012-05-17_23:00:02 --- Space consumed in  = 104K
2012-05-17_23:00:07 --- Compressing archive: /tmp/2012-05-17-23-00_mysql-backup.tar.7z
2012-05-17_23:00:19 --- Copying archive file to offsite location.
2012-05-17_23:00:25 --- Total backup time: 0 hour(s) 0 minute(s) 23 second(s)
2012-05-17_23:00:25 - MySQL backup completed.
2012-05-17_23:00:25 - MySQL backup exit code: 0
2012-05-18_23:00:01 - MySQL backup started.
2012-05-18_23:00:01 --- Partition status:
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/LVG-root  3.8G  1.1G  2.6G  29% /
udev                  237M  4.0K  237M   1% /dev
tmpfs                  99M  540K   98M   1% /run
none                  5.0M     0  5.0M   0% /run/lock
none                  246M     0  246M   0% /run/shm
/dev/sda1             179M   25M  145M  15% /boot
/dev/mapper/LVG-bak   4.0G   65M  3.8G   2% /backup
/dev/mapper/LVG-var   2.0G  347M  1.6G  18% /var
/dev/mapper/LVG-tmp   2.0G   31M  1.9G   2% /tmp
Uptime:            1 day 8 hours 38 min 25 sec
2012-05-18_23:00:01 --- Space consumed in  = 108K
2012-05-18_23:00:05 --- Compressing archive: /tmp/2012-05-18-23-00_mysql-backup.tar.7z
2012-05-18_23:00:16 --- Copying archive file to offsite location.
2012-05-18_23:00:17 --- Total backup time: 0 hour(s) 0 minute(s) 16 second(s)
2012-05-18_23:00:17 - MySQL backup completed.
2012-05-18_23:00:17 - MySQL backup exit code: 0
2012-05-19_23:00:01 - MySQL backup started.
2012-05-19_23:00:01 --- Partition status:
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/LVG-root  3.8G  1.1G  2.6G  29% /
udev                  237M  4.0K  237M   1% /dev
tmpfs                  99M  540K   98M   1% /run
none                  5.0M     0  5.0M   0% /run/lock
none                  246M     0  246M   0% /run/shm
/dev/sda1             179M   25M  145M  15% /boot
/dev/mapper/LVG-bak   4.0G   65M  3.8G   2% /backup
/dev/mapper/LVG-var   2.0G  347M  1.6G  18% /var
/dev/mapper/LVG-tmp   2.0G   31M  1.9G   2% /tmp
Uptime:            2 days 8 hours 38 min 25 sec
2012-05-19_23:00:01 --- Space consumed in  = 108K
2012-05-19_23:00:04 --- Compressing archive: /tmp/2012-05-19-23-00_mysql-backup.tar.7z
2012-05-19_23:00:16 --- Copying archive file to offsite location.
2012-05-19_23:00:17 --- Total backup time: 0 hour(s) 0 minute(s) 16 second(s)
2012-05-19_23:00:17 - MySQL backup completed.
2012-05-19_23:00:17 - MySQL backup exit code: 0
Here is a sample of the files stored on the offsite server:

D:\MySQL\MySQL

Code: Select all

2012-05-09-23-00_mysql-backup.tar.7z
2012-05-10-23-00_mysql-backup.tar.7z
2012-05-11-23-00_mysql-backup.tar.7z
2012-05-12-23-00_mysql-backup.tar.7z
2012-05-13-23-00_mysql-backup.tar.7z
2012-05-14-23-00_mysql-backup.tar.7z
2012-05-15-23-00_mysql-backup.tar.7z
2012-05-16-23-00_mysql-backup.tar.7z
2012-05-17-23-00_mysql-backup.tar.7z
2012-05-18-23-00_mysql-backup.tar.7z
2012-05-19-23-00_mysql-backup.tar.7z

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 8:18 am
by LHammonds
Database Backup On Demand

This script is designed to run every minute looking for key files. If a specific file shows up on the samba share, it will trigger an immediate backup of the specified database. This is helpful when scheduling the backup of an app server and coordinating the backup of the database at the same time. The remote app backup script can trigger the database backup anytime it runs no matter if it is schedule via crontab or manually run.

For an example of a app server configured to make use of the script, take a look at my MediaWiki thread.

If a file such as /srv/samba/share/mediawiki shows up, this script will delete that file and perform a backup of the mediawiki database.

To configure this script for your own needs, modify the IF statements between lines 116 and 130.

/var/scripts/prod/mysql-db-backup.sh

Code: Select all

#!/bin/bash
#############################################
## Name          : mysql-db-backup.sh
## Version       : 1.0
## Date          : 2012-05-14
## Author        : LHammonds
## Purpose       : Backup of a single database
## Compatibility : Verified on Ubuntu Server 10.04 - 12.04 LTS, MySQL 5.1.62 - 5.5.22
## Requirements  : p7zip-full (if ARCHIVEMETHOD=tar.7z), sendemail
## Run Frequency : As needed
## Exit Codes    : (if multiple errors, value is the addition of codes)
##    0 = success
##    1 = 7zip not installed
##    2 = archive failure
##    4 = archive purge failure
##    8 = configuration error
##   16 = mount warning
################ CHANGE LOG #################
## DATE       WHO WHAT WAS CHANGED
## ---------- --- ----------------------------
## 2012-05-14 LTH Created script.
#############################################
 
## Import common variables and functions. ##
source /var/scripts/common/standard.conf
 
LOGFILE="${LOGDIR}/mysql-db-backup.log"
LOCKFILE="${TEMPDIR}/mysql-db-backup.lock"
TARGETDIR="${BACKUPDIR}/mysql-db"
OFFSITEBACKDIR="${OFFSITEDIR}/mysql-db"
ERRORFLAG=0
 
#######################################
##            FUNCTIONS              ##
#######################################
function f_PurgeOldestArchive()
{
  ## Purpose: Delete the oldest archive on the remote site.
  ## Return values:
  ##    0 = Success
  ##    1 = Cannot delete file
  ##    9 = Configuration error, path empty
 
  ## Variable Error Check. *
  if [ ${OFFSITEBACKDIR} = "" ]; then
    ## Make darn sure the path is not empty since we do NOT
    ## want to start purging files from a random location.
    echo "`date +%Y-%m-%d_%H:%M:%S` --- Purge error: OFFSITEBACKDIR site variable is empty!" >> ${LOGFILE}
    return 9
  fi
  ## Get the name of the oldest file.
  OLDESTFILE=`ls -1t ${OFFSITEBACKDIR} | tail -1`
  if [ "${OLDESTFILE}" = "" ]; then
    ## Error. Filename variable empty.
    echo "`date +%Y-%m-%d_%H:%M:%S` --- Purge error: OLDESTFILE variable is empty." >> ${LOGFILE}
    return 9
  else   
    FILESIZE=`ls -lak "${OFFSITEBACKDIR}/${OLDESTFILE}" | awk '{ print $5 }' | sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'`
    echo "`date +%Y-%m-%d_%H:%M:%S` --- Purging old file: ${OFFSITEBACKDIR}/${OLDESTFILE}, Size = ${FILESIZE} kb" >> ${LOGFILE}
    rm "${OFFSITEBACKDIR}/${OLDESTFILE}"
    if [ -f "${OFFSITEBACKDIR}/${OLDESTFILE}" ]; then
      ## File still exists.  Return error.
      return 1
    else
      return 0
    fi
  fi
}
 
function f_cleanup()
{
  if [ -f ${LOCKFILE} ];then
    ## Remove lock file so other rsync jobs can run.
    rm ${LOCKFILE} 1>/dev/null 2>&1
  fi
  if [[ "${TARGETDIR}" != "" && "{TARGETDIR}" != "/" ]]; then
    ## Remove local backup files.
    rm -rf ${TARGETDIR}/*
  fi
  ## Email the result to the administrator.
  if [ ${ERRORFLAG} -eq 0 ]; then
    f_sendmail "MySQL DB Backup Success" "MySQL backup completed with no errors."
  else
    f_sendmail "MySQL DB Backup ERROR" "MySQL backup failed.  ERRORFLAG = ${ERRORFLAG}"
  fi
}
 
function f_emergencyexit()
{
  ## Purpose: Exit script as cleanly as possible.
  ## Parameter #1 = Error Code
  f_cleanup
  echo "`date +%Y-%m-%d_%H:%M:%S` - MySQL backup exit code: ${ERRORFLAG}" >> ${LOGFILE}
  exit $1
}
 
#######################################
##           MAIN PROGRAM            ##
#######################################
 
## Binaries ##
TAR="$(which tar)"
MY7ZIP="$(which 7za)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
 
if [ -f ${LOCKFILE} ]; then
  ## Program lock file detected.  Abort script.
  f_sendmail "MySQL DB Backup Aborted - Lock File" "This script tried to run but detected the lock file: ${LOCKFILE}\n\nPlease check to make sure the file does not remain when this script is not actually running."
  exit 1
else
  ## Create the lock file to ensure only one script is running at a time.
  echo "`date +%Y-%m-%d_%H:%M:%S` ${SCRIPTNAME}" > ${LOCKFILE}
fi
 
## Figure out which database will be backed up. Only one per run.
if [ -f "${SHAREDIR}/mediawiki" ]; then
  DATABASE="mediawiki"
  rm "${SHAREDIR}/mediawiki"
elif [ -f "${SHAREDIR}/phpbb" ]; then
  DATABASE="phpbb"
  rm "${SHAREDIR}/phpbb"
elif [ -f "${SHAREDIR}/wordpress" ]; then
  DATABASE="wordpress"
  rm "${SHAREDIR}/wordpress"
fi
if [[ "${DATABASE}" = "" ]]; then
  f_cleanup 0
  exit 0
fi
 
ARCHIVEFILE="`date +%Y-%m-%d-%H-%M`_mysql-db-${DATABASE}.${ARCHIVEMETHOD}"
 
echo "`date +%Y-%m-%d_%H:%M:%S` - MySQL ${DATABASE} backup started." >> ${LOGFILE}
 
## If the 7-Zip archive method is specified, make sure the package is installed.
if [ "${ARCHIVEMETHOD}" = "tar.7z" ]; then
  if [ ! -f "/usr/bin/7za" ]; then
    ## Required package (7-Zip) not installed.
    echo "`date +%Y-%m-%d_%H:%M:%S` - CRITICAL ERROR: 7-Zip package not installed.  Please install by typing 'aptitude -y install p7zip-full'" >> ${LOGFILE}
    ERRORFLAG=1
    f_emergencyexit ${ERRORFLAG}
  fi
fi
 
StartTime="$(date +%s)"
 
## Backup individual database.
${MYSQLDUMP} ${DATABASE} > ${TARGETDIR}/${DATABASE}.sql
## Create database sub-folder.
mkdir -p ${TARGETDIR}/${DATABASE}
## Export each table in the database individually.
for TABLE in `echo "show tables" | $MYSQL ${DATABASE}|grep -v Tables_in_`;
do
  FILE=${TARGETDIR}/${DATABASE}/${TABLE}.sql
  case "${TABLE}" in
    general_log)
      ${MYSQLDUMP} ${DATABASE} ${TABLE} --skip-lock-tables > ${FILE}
      ;;
    slow_log)
      ${MYSQLDUMP} ${DATABASE} ${TABLE} --skip-lock-tables > ${FILE}
      ;;
    *)
      ${MYSQLDUMP} ${DATABASE} ${TABLE} > ${FILE}
      ;;
  esac
done
 
## Compress the backup into a single file based on archive method specified.
echo "`date +%Y-%m-%d_%H:%M:%S` --- Compressing archive: ${TEMPDIR}/${ARCHIVEFILE}" >> ${LOGFILE}
case "${ARCHIVEMETHOD}" in
tar.7z)
  ${TAR} -cpf - ${TARGETDIR} | ${MY7ZIP} a -si -mx=9 -w${TEMPDIR} ${TEMPDIR}/${ARCHIVEFILE} 1>/dev/null 2>&1
  RETURNVALUE=$?
  ## Restore using one of the following commands (do not uncomment, only for notation):
  ## 7za x -so -w/tmp ${TEMPDIR}/${ARCHIVEFILE} | tar -C / -xf -
  ## 7za x -so -w/tmp ${TEMPDIR}/${ARCHIVEFILE} | tar -C ${TEMPDIR}/restore --strip-components=1 -xf -
  ;;
tgz)
  ${TAR} -cpzf ${TEMPDIR}/${ARCHIVEFILE} ${TARGETDIR} 1>/dev/null 2>&1
  RETURNVALUE=$?
  ## Restore using one of the following commands (do not uncomment, only for notation):
  ## tar -C / -xzf ${TEMPDIR}/${ARCHIVEFILE}
  ## tar -C ${TEMPDIR}/restore --strip-components=1 -xzf ${TEMPDIR}/${ARCHIVEFILE}
  ;;
*)
  ${TAR} -cpzf ${TEMPDIR}/${ARCHIVEFILE} ${TARGETDIR} 1>/dev/null 2>&1
  RETURNVALUE=$?
  ;;
esac
 
if [ ${RETURNVALUE} -ne 0 ]; then
  ## tar command failed.  Send warning email.
  f_sendmail "MySQL Backup Failure - tar" "tar failed with return value of ${RETURNVALUE}"
  ERRORFLAG=$((${ERRORFLAG} + 2))
fi
 
## Mount the remote folder. ##
f_mount
 
if [ ! -f ${OFFSITETESTFILE} ]; then
  ## Could not find expected file on remote site.  Assuming failed mount.
  ERRORFLAG=$((${ERRORFLAG} + 16))
  echo "`date +%Y-%m-%d_%H:%M:%S` --- ERROR: Cannot detect remote location: ${OFFSITETESTFILE}" >> ${LOGFILE}
  f_emergencyexit ${ERRORFLAG}
fi
 
FREESPACE=`df -k ${OFFSITEDIR} | grep ${OFFSITEDIR} | awk '{ print $3 }'`
BACKUPSIZE=`ls -lak "${TEMPDIR}/${ARCHIVEFILE}" | awk '{ print $5 }'`
 
## Make sure space is available on the remote server to copy the file.
if [ ${FREESPACE} -lt ${BACKUPSIZE} ]; then
  ## Not enough free space available.  Purge existing backups until there is room.
  ENOUGHSPACE=0
  while [ ${ENOUGHSPACE} -eq 0 ]
  do
    f_PurgeOldestArchive
    RETURNVALUE=$?
    case ${RETURNVALUE} in
    1)
      ## Cannot purge archives to free up space.  End program gracefully.
      echo "`date +%Y-%m-%d_%H:%M:%S` - ERROR: Not enough free space on ${OFFSITEBACKDIR} and cannot purge old archives.  Script aborted." >> ${LOGFILE}
      ## Stop and exit the script with an error code.
      ERRORFLAG=$((${ERRORFLAG} + 4))
      f_emergencyexit ${ERRORFLAG}
      ;;
    9)
      ## Configuration error, end program gracefully.
      echo "`date +%Y-%m-%d_%H:%M:%S` - ERROR: Configuration problem. Script aborted." >> ${LOGFILE}
      ## Stop and exit the script with an error code.
      ERRORFLAG=$((${ERRORFLAG} + 8))
      f_emergencyexit ${ERRORFLAG}
      ;;
    esac
    FREESPACE=`df -k ${OFFSITEDIR} | grep ${OFFSITEDIR} | awk '{ print $3 }'`
    if [ ${FREESPACE} -gt ${BACKUPSIZE} ]; then
      ## Enough space is now available.
      ENOUGHSPACE=1
    else
      ## Not enough space is available yet.
      ENOUGHSPACE=0
    fi
  done
fi
 
## Copy the backup to an offsite storage location.
echo "`date +%Y-%m-%d_%H:%M:%S` --- Copying archive file to offsite location." >> ${LOGFILE}
cp ${TEMPDIR}/${ARCHIVEFILE} ${OFFSITEBACKDIR}/${ARCHIVEFILE} 1>/dev/null 2>&1
if [ ! -f ${OFFSITEBACKDIR}/${ARCHIVEFILE} ]; then
  ## NON-FATAL ERROR: Copy command did not work.  Send email notification.
  echo "`date +%Y-%m-%d_%H:%M:%S` --- WARNING: Remote copy failed. ${OFFSITEBACKDIR}/${ARCHIVEFILE} does not exist!" >> ${LOGFILE}
  f_sendmail "MySQL Backup Failure - Remote Copy" "Remote copy failed. ${OFFSITEBACKDIR}/${ARCHIVEFILE} does not exist\n\nBackup file still remains in this location: ${HOSTNAME}:${TEMPDIR}/${ARCHIVEFILE}"
else
  ## Remove local copy of the compressed backup file
  rm ${TEMPDIR}/${ARCHIVEFILE}
fi
 
## Unmount the Windows shared folder.
f_umount
 
## Calculate total time for backup.
FinishTime="$(date +%s)"
ElapsedTime="$(expr ${FinishTime} - ${StartTime})"
Hours=$((${ElapsedTime} / 3600))
ElapsedTime=$((${ElapsedTime} - ${Hours} * 3600))
Minutes=$((${ElapsedTime} / 60))
Seconds=$((${ElapsedTime} - ${Minutes} * 60))
 
echo "`date +%Y-%m-%d_%H:%M:%S` --- Total backup time: ${Hours} hour(s) ${Minutes} minute(s) ${Seconds} second(s)" >> ${LOGFILE}
 
echo "`date +%Y-%m-%d_%H:%M:%S` - MySQL ${DATABASE} backup completed." >> ${LOGFILE}
 
## Perform cleanup routine.
f_cleanup
## Exit with the combined return code value.
exit ${ERRORFLAG}
Here is a sample of the log output:

/var/log/mysql-db-backup.log

Code: Select all

2012-05-16_20:00:01 - MySQL mediawiki backup started.
2012-05-16_20:00:02 --- Compressing archive: /var/temp/2012-05-16-20-00_mysql-db-mediawiki.tar.7z
2012-05-16_20:00:05 --- Copying archive file to offsite location.
2012-05-16_20:00:06 --- Total backup time: 0 hour(s) 0 minute(s) 5 second(s)
2012-05-16_20:00:06 - MySQL mediawiki backup completed.
2012-05-17_20:00:02 - MySQL mediawiki backup started.
2012-05-17_20:00:02 --- Compressing archive: /var/temp/2012-05-17-20-00_mysql-db-mediawiki.tar.7z
2012-05-17_20:00:07 --- Copying archive file to offsite location.
2012-05-17_20:00:11 --- Total backup time: 0 hour(s) 0 minute(s) 9 second(s)
2012-05-17_20:00:11 - MySQL mediawiki backup completed.
2012-05-18_20:01:01 - MySQL mediawiki backup started.
2012-05-18_20:01:02 --- Compressing archive: /var/temp/2012-05-18-20-01_mysql-db-mediawiki.tar.7z
2012-05-18_20:01:03 --- Copying archive file to offsite location.
2012-05-18_20:01:04 --- Total backup time: 0 hour(s) 0 minute(s) 3 second(s)
2012-05-18_20:01:04 - MySQL mediawiki backup completed.
2012-05-19_20:01:01 - MySQL mediawiki backup started.
2012-05-19_20:01:02 --- Compressing archive: /var/temp/2012-05-19-20-01_mysql-db-mediawiki.tar.7z
2012-05-19_20:01:03 --- Copying archive file to offsite location.
2012-05-19_20:01:03 --- Total backup time: 0 hour(s) 0 minute(s) 2 second(s)
2012-05-19_20:01:03 - MySQL mediawiki backup completed.
Here is a sample of the files stored on the offsite server:

D:\MySQL\MySQL-DB

Code: Select all

2012-05-16-20-00_mysql-db-mediawiki.tar.7z
2012-05-17-20-00_mysql-db-mediawiki.tar.7z
2012-05-18-20-01_mysql-db-mediawiki.tar.7z
2012-05-19-20-01_mysql-db-mediawiki.tar.7z

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 8:20 am
by LHammonds
Check Storage Space

In favor of manage by exception, I wrote a script that can be scheduled to run daily to check the file systems to see if they are getting close to filling up and will automatically expand them a little bit and give you an email notice. Everything is done at the megabyte level. If you do not want the script to perform the increase, simply add a pound sign in front of the resize2fs command on line 64 to comment it out. Might also want to modify the log and email messages so it does not look like it actually "performed" the resize but instead is telling YOU how to perform the resize.

Here are the lines I added to my crontab schedule which will check each file system I expect will grow on a daily basis @ 1am, 2am and 3am. I have them checking to see if we have less than 50 MB available and if so, it will try to increase by 50 MB.

crontab

Code: Select all

0 1 * * * /var/scripts/prod/check-storage.sh var 50 50 > /dev/null 2>&1
0 2 * * * /var/scripts/prod/check-storage.sh bak 50 50 > /dev/null 2>&1
0 3 * * * /var/scripts/prod/check-storage.sh tmp 50 50 > /dev/null 2>&1
/var/scripts/prod/check-storage.sh

Code: Select all

#!/bin/bash
#############################################
## Name          : check-storage.sh
## Version       : 1.0
## Date          : 2012-05-11
## Author        : LHammonds
## Purpose       : Check available space for a file system and expand if necessary.
## Compatibility : Verified on Ubuntu Server 12.04 LTS
## Requirements  : None
## Run Frequency : Recommend once per day for each FS to monitor.
## Parameters    :
##    1 = (Required) File System name (e.g. var)
##    2 = (Required) File System Threshold in MB (e.g. 50)
##    3 = (Required) Amount to increase File System in MB (e.g. 50)
## Exit Codes    :
##    0 = Success (either nothing was done or FS expanded without error)
##    1 = ERROR: Missing or incorrect parameter(s)
##    2 = ERROR: Invalid parameter value(s)
##    4 = ERROR: Lock file detected
##    8 = ERROR: Resize2fs error
##   16 = SEVERE: No room to expand
##   32 = ERROR: Script not run by root user
################ CHANGE LOG #################
## DATE       WHO WHAT WAS CHANGED
## ---------- --- ----------------------------
## 2012-05-11 LTH Created script.
#############################################
 
## Import standard variables and functions. ##
source /var/scripts/common/standard.conf
 
## Define local variables.
LOGFILE="${LOGDIR}/check-storage.log"
LOCKFILE="${TEMPDIR}/check-storage.lock"
ErrorFlag=0
ReturnCode=0
 
#######################################
##            FUNCTIONS              ##
#######################################
 
function f_cleanup()
{
  if [ -f ${LOCKFILE} ];then
    ## Remove lock file so other check space jobs can run.
    rm ${LOCKFILE} 1>/dev/null 2>&1
  fi
  exit ${ErrorFlag}
}
 
function f_showhelp()
{
  echo -e "\nUsage : ${SCRIPTNAME} FileSystemName ThresholdSizeInMB AmountToIncreaseByInMB\n"
  echo -e "\nExample: ${SCRIPTNAME} var 50 50\n"
}
 
function f_auto-increment()
{
  let RoomInLV=${LVSize}-${FSSize}
  if [[ ${RoomInLV} -gt ${FSIncreaseBy} ]]; then
    ## There is room in the LV to increase space to the FS.
    resize2fs ${FSVol} ${NewFSSize}M
    ReturnCode=$?
    echo "`date +%Y-%m-%d_%H:%M:%S` --- resize2fs ${FSVol} ${NewFSSize}M, ReturnCode=${ReturnCode}" | tee -a ${LOGFILE}
    if [[ ${ReturnCode} -ne 0 ]]; then
      ## There was an error in resize2fs.
      return ${ReturnCode}
    fi
  else
    ## There is not enough room in the LV to increase space in the FS.
    return 50
  fi
  return 0
}
 
#######################################
##           MAIN PROGRAM            ##
#######################################
 
if [ -f ${LOCKFILE} ]; then
  # Lock file detected.  Abort script.
  echo "Check space script aborted"
  echo "This script tried to run but detected the lock file: ${LOCKFILE}"
  echo "Please check to make sure the file does not remain when check space is not actually running."
  f_sendmail "ERROR: check storage script aborted" "This script tried to run but detected the lock file: ${LOCKFILE}\n\nPlease check to make sure the file does not remain when check space is not actually running.\n\nIf you find that the script is not running/hung, you can remove it by typing 'rm ${LOCKFILE}'"
  ErrorFlag=4
  f_cleanup
else
  echo "`date +%Y-%m-%d_%H:%M:%S` ${SCRIPTNAME}" > ${LOCKFILE}
fi
 
## Requirement Check: Script must run as root user.
if [ "$(id -u)" != "0" ]; then
  ## FATAL ERROR DETECTED: Document problem and terminate script.
  echo "ERROR: Root user required to run this script."
  echo ""
  ErrorFlag=32
  f_cleanup
fi
 
## Check existance of required command-line parameters.
case "$1" in
  "")
    f_showhelp
    ErrorFlag=1
    f_cleanup
    ;;
  --help|-h|-?)
    f_showhelp
    ErrorFlag=1
    f_cleanup
    ;;
  *)
    FSName=$1
    ;;
esac
case "$2" in
  "")
    f_showhelp
    ErrorFlag=1
    f_cleanup
    ;;
  --help|-h|-?)
    f_showhelp
    ErrorFlag=1
    f_cleanup
    ;;
  *)
    FSThreshold=$2
    ;;
esac
case "$3" in
  "")
    f_showhelp
    ErrorFlag=1
    f_cleanup
    ;;
  --help|-h|-?)
    f_showhelp
    ErrorFlag=1
    f_cleanup
    ;;
  *)
    FSIncreaseBy=$3
    ;;
esac
 
## Check validity of File System name.
case "${FSName}" in
  "var")
    FSVol="/dev/LVG/var"
    FSMap="/dev/mapper/LVG-var"
    ;;
  "bak")
    FSVol="/dev/LVG/bak"
    FSMap="/dev/mapper/LVG-bak"
    ;;
  "tmp")
    FSVol="/dev/LVG/tmp"
    FSMap="/dev/mapper/LVG-tmp"
    ;;
  *)
    echo "ERROR: ${FSName} does not match a known file system defined in this script."
    f_showhelp
    ErrorFlag=2
    f_cleanup
    ;;
esac
 
## Check validity of threshold value.
test ${FSThreshold} -eq 0 1>/dev/null 2>&1
if [[ $? -eq 2 ]]; then
  ## Threshold parameter is not an integer.
  echo "ERROR: ${FSThreshold} is not an integer."
  f_showhelp
  ErrorFlag=2
  f_cleanup
fi
 
## Check validity of increment value.
test ${FSIncreaseBy} -eq 0 1>/dev/null 2>&1
if [[ $? -eq 2 ]]; then
  ## FSIncreaseBy parameter is not an integer.
  echo "ERROR: ${FSIncreaseBy} is not an integer."
  f_showhelp
  ErrorFlag=2
  f_cleanup
fi
 
## Get available space for the file system.
FSAvailable="`df --block-size=m ${FSMap} | awk '{ print $4 }' | tail -n 1 | sed 's/M//'`"
 
## Get the current size of the File System.
FSSize="`df --block-size=m ${FSMap} | awk '{ print $2 }' | tail -n 1 | sed 's/M//'`"
 
## Get the current size of the Logical Volume for the File System
LVSize="`lvs --noheadings --nosuffix --units=m ${FSMap} | awk '{ print $4}' | sed 's/[.].*//'`"
 
## Calculate the new size of the FS in case we need it.
let NewFSSize=${FSSize}+${FSIncreaseBy}
 
if [[ ${FSAvailable} -lt ${FSThreshold} ]]; then
  echo "`date +%Y-%m-%d_%H:%M:%S` - Starting expansion of ${FSVol}" | tee -a ${LOGFILE}
  echo "`date +%Y-%m-%d_%H:%M:%S` --- LVSize=${LVSize}MB, FSSize=${FSSize}MB, FSAvail=${FSAvailable}MB, FSThreshold=${FSThreshold}MB, FSIncreaseBy=${FSIncreaseBy}MB" | tee -a ${LOGFILE}
  ## Run the auto-expansion function.
  f_auto-increment
  ReturnCode=$?
  case ${ReturnCode} in
  0)
    f_sendmail "NOTICE: File System Expanded" "${FSVol} was expanded because it was nearing max capacity.  Please review disk space usage and plan appropriately. LVSize=${LVSize}MB, FSSize=${FSSize}MB, FSAvailable=${FSAvailable}MB, FSThreshold=${FSThreshold}MB, FSIncreaseBy=${FSIncreaseBy}MB"
    ;;
  50)
    echo "`date +%Y-%m-%d_%H:%M:%S` - SEVERE: No room to expand ${FSVol}" | tee -a ${LOGFILE}
    ErrorFlag=16
    f_sendmail "SEVERE: No room to expand ${FSVol}" "There is not enough room in the Logical Volume to expand the ${FSVol} File System.  Immediate action is required.  Make sure there is free space in the Volume Group 'LVG' and then expand the Logical Volume...then expand the File System.\n\nLVSize=${LVSize}MB, FSSize=${FSSize}MB, FSAvailable=${FSAvailable}MB, FSThreshold=${FSThreshold}MB, FSIncreaseBy=${FSIncreaseBy}MB.\n\nType 'vgs' to see if there is any free space in the Volume Group which can be given to the Logical Volume.\n\nType 'lvs' to see the current sizes of the LVs.\n\nType 'lvdisplay' to see a list of Logical Volumes so you can get the LV Name which is used in the lvextend and resize2fs commands.\n\nType 'lvextend -L+50M /dev/LVG/var' if you want to extend the var Logical Volume by 50 megabytes (assuming there is 50MB available in the Volume Group).\n\nType 'df --block-size=m' to see a list of file systems and their associated size and available space.\n\nType 'resize2fs /dev/LVG/var ${NewFSSize}M' to set the size of var to ${NewFSSize} megabytes. Make sure you set the size to the desired end-result which should be LARGER than the current FS size so you do not lose data."
    ;;
  *)
    echo "`date +%Y-%m-%d_%H:%M:%S` - ERROR: Expansion failure for ${FSVol}" | tee -a ${LOGFILE}
    ErrorFlag=8
    f_sendmail "ERROR: File System Expansion Failed" "${FSVol} Expansion failed with return code of ${ReturnCode}.  LVSize=${LVSize}MB, FSSize=${FSSize}MB, FSAvailable=${FSAvailable}MB, FSThreshold=${FSThreshold}MB, FSIncreaseBy=${FSIncreaseBy}MB"
    ;;
  esac
  echo "`date +%Y-%m-%d_%H:%M:%S` - Finished expansion of ${FSVol}" | tee -a ${LOGFILE}
else
  echo "`date +%Y-%m-%d_%H:%M:%S` - ${FSVol} ${FSAvailable}M>${FSThreshold}M No action required." | tee -a ${LOGFILE}
fi
 
## Perform cleanup routine.
f_cleanup
Here is the typical output when it does not have to increase the FS:

/var/log/check-storage.log

Code: Select all

2012-05-01_01:00:00 - /dev/LVG/var 44M>5M No action required.
2012-05-01_02:00:00 - /dev/LVG/bak 91M>5M No action required.
2012-05-01_03:00:00 - /dev/LVG/tmp 93M>5M No action required.
2012-05-02_01:00:00 - /dev/LVG/var 44M>5M No action required.
2012-05-02_02:00:00 - /dev/LVG/bak 91M>5M No action required.
2012-05-02_03:00:00 - /dev/LVG/tmp 93M>5M No action required.
2012-05-03_01:00:00 - /dev/LVG/var 44M>5M No action required.
2012-05-03_02:00:00 - /dev/LVG/bak 91M>5M No action required.
2012-05-03_03:00:00 - /dev/LVG/tmp 93M>5M No action required.
Here is a sample of what the log will look like when it perform increases...notice how I had to increase the threshold in order for it to fire off the increase (but it still just issued a single megabyte increase):

/var/log/check-storage.log

Code: Select all

2012-05-02_01:00:00 - Starting expansion of /dev/LVG/var
2012-05-02_01:00:00 --- LVSize=75MB, FSSize=50MB, FSAvail=44MB, FSThreshold=55MB, IncreaseBy=1MB
2012-05-02_01:00:00 --- resize2fs /dev/LVG/var 51, ReturnCode=0
2012-05-02_01:00:00 - Finished expansion of /dev/LVG/var
2012-05-02_02:00:00 - Starting expansion of /dev/LVG/bak
2012-05-02_02:00:00 --- LVSize=125MB, FSSize=99MB, FSAvail=91MB, FSThreshold=100MB, IncreaseBy=1MB
2012-05-02_02:00:00 --- resize2fs /dev/LVG/bak 100, ReturnCode=0
2012-05-02_02:00:00 - Finished expansion of /dev/LVG/bak
2012-05-02_03:00:00 - Starting expansion of /dev/LVG/tmp
2012-05-02_03:00:00 --- LVSize=125MB, FSSize=99MB, FSAvail=93MB, FSThreshold=100MB, IncreaseBy=1MB
2012-05-02_03:00:00 --- resize2fs /dev/LVG/temp 100, ReturnCode=0
2012-05-02_03:00:00 - Finished expansion of /dev/LVG/tmp

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 8:23 am
by LHammonds
Migrate Database from Old Server

Since I am upgrading my database server by installing an entirely new server, I will be using the backup archives created by the same "mysql-backup.sh" script that the new server is using.

Here are the steps to import the databases from the old server into the new server from the latest backup archive on the remote server to /tmp
  1. Make a full backup of the current database by typing: mysqldump --all-databases > /tmp/all-db-before.sql
  2. Make a restore folder by typing: mkdir /tmp/restore
  3. Extract the archive by typing: 7za x -so -w/tmp /tmp/*.7z | tar -C /tmp/restore --strip-components=3 -xf -
  4. Import the all-database file by typing: mysql < /tmp/restore/mysql-all.sql
  5. Make a full backup of the current database by typing: mysqldump --all-databases > /tmp/all-db-after.sql
  6. Verify that your databases, tables, rows and users are now on the new server
  7. Cleanup by removing the restore folder: rm -rf /tmp/restore
  8. Now would be a good time to run your backup script: /var/scripts/prod/mysql-backup.sh
Now you can point your apps to the new database server or shutdown the old server and then change the IP of this server to match the old server.

Crontab Schedule

I would not advise anyone to ever "edit" a live crontab schedule by typing "crontab -e" but rather edit a saved schedule file and then load the schedule file. This will allow you to make backups of the schedule so you can always go back to a known-good schedule or at least back to the way it was before you made a change...assuming you always work with a copy of the schedule 1st.

Here is my root crontab scheduling file:

/var/scripts/data/crontab.root

Code: Select all

########################################
# Name: Crontab Schedule for root user
# Author: LHammonds
############# Update Log ###############
# 2012-05-20 - LTH - Created schedule
########################################
 
SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
 
# m h dom mon dow command
 
#
# Adjust the time clock
#
0 1-23 * * * /usr/sbin/ntpdate ntp.ubuntu.com > /dev/null 2>&1
#
# Backup MySQL Server
#
0 23 * * * /var/scripts/prod/mysql-backup.sh > /dev/null 2>&1
#
# Backup MySQL Database On Demand
#
0-59 * * * * /var/scripts/prod/mysql-db-backup.sh > /dev/null 2>&1
#
# Daily check for available space on /var
#
0 1 * * * /var/scripts/prod/check-storage.sh opt 50 50 > /dev/null 2>&1
#
# Daily check for available space on /backup
#
0 2 * * * /var/scripts/prod/check-storage.sh bak 50 50 > /dev/null 2>&1
#
# Daily check for available space on /temp
#
0 3 * * * /var/scripts/prod/check-storage.sh tmp 50 50 > /dev/null 2>&1
Once you have created the file, make sure appropriate permissions are set by typing the following:

Code: Select all

chown root:root /var/scripts/data/crontab.root
chmod 0600 /var/scripts/data/crontab.root
To enable the root schedule using this file, type the following:

Code: Select all

crontab -u root /var/scripts/data/crontab.root
To disable the root schedule, type the following:

Code: Select all

touch /tmp/deleteme
crontab -u root /tmp/deleteme
rm /tmp/deleteme
If you need to modify the schedule, make a backup copy 1st. For example:

Code: Select all

cp /var/scripts/data/crontab.root /var/scripts/data/2011-11-28-crontab.root
vi /var/scripts/data/crontab.root (make your changes)
crontab -u root /var/scripts/data/crontab.root
Web Front-end

Someone asked if there was a graphical way to manage the server. After a long sigh, I mentioned that phpmyadmin is a popular front-end.

Here is how you install it:

Code: Select all

aptitude install phpmyadmin
I would recommend uninstalling it after your initial setup since having an Apache web service tends to eat away at your CPU/RAM. My corporate DB server runs happily on 512 MB of RAM.

Re: My Notes for Installing MySQL on Ubuntu Server 12.04 LTS

Posted: Thu Jul 19, 2012 8:26 am
by LHammonds
APT Upgrade

Here is a script that can be scheduled to run daily to check for updates and then install them if available.

This is the line you put in crontab to have the script once per day @ 3am.

/var/scripts/data/crontab.root

Code: Select all

0 3 * * * /var/scripts/prod/apt-upgrade.sh > /dev/null 2>&1

/var/scripts/prod/apt-upgrade.sh

Code: Select all

#!/bin/bash
#############################################
## Name          : apt-upgrade.sh
## Version       : 1.0
## Date          : 2012-06-01
## Author        : LHammonds
## Purpose       : Keep system updated (rather than use unattended-upgrades)
## Compatibility : Verified on Ubuntu Server 12.04 LTS
## Requirements  : Sendemail, run as root
## Run Frequency : Recommend once per day.
## Parameters    : None
## Exit Codes    :
##    0 = Success
##    1 = ERROR: Lock file detected.
##    2 = ERROR: Not run as root user.
##    4 = ERROR: Aptitude update Error.
##    8 = ERROR: Aptitude safe-upgrade Error.
##   16 = ERROR: Aptitude autoclean Error.
################ CHANGE LOG #################
## DATE       WHO WHAT WAS CHANGED
## ---------- --- ----------------------------
## 2012-06-01 LTH Created script.
#############################################
 
## Import standard variables and functions. ##
source /var/scripts/common/standard.conf
 
## Define local variables.
LOGFILE="${LOGDIR}/apt-upgrade.log"
LOCKFILE="${TEMPDIR}/apt-upgrade.lock"
ErrorFlag=0
ReturnCode=0
APTCMD="$(which aptitude)"
 
#######################################
##            FUNCTIONS              ##
#######################################
 
function f_cleanup()
{
  if [ -f ${LOCKFILE} ];then
    ## Remove lock file so subsequent jobs can run.
    rm ${LOCKFILE} 1>/dev/null 2>&1
  fi
  exit ${ErrorFlag}
}
 
#######################################
##           MAIN PROGRAM            ##
#######################################
 
if [ -f ${LOCKFILE} ]; then
  # Lock file detected.  Abort script.
  echo "** Script aborted **"
  echo "This script tried to run but detected the lock file: ${LOCKFILE}"
  echo "Please check to make sure the file does not remain when check space is not actually running."
  f_sendmail "ERROR: Script aborted" "This script tried to run but detected the lock file: ${LOCKFILE}\n\nPlease check to make sure the file does not remain when check space is not actually running.\n\nIf you find that the script is not running/hung, you can remove it by typing 'rm ${LOCKFILE}'"
  ErrorFlag=1
  f_cleanup
else
  echo "`date +%Y-%m-%d_%H:%M:%S` ${SCRIPTNAME}" > ${LOCKFILE}
fi
 
## Requirement Check: Script must run as root user.
if [ "$(id -u)" != "0" ]; then
  ## FATAL ERROR DETECTED: Document problem and terminate script.
  echo -e "ERROR: Root user required to run this script.\n"
  ErrorFlag=2
  f_cleanup
fi
 
echo "`date +%Y-%m-%d_%H:%M:%S` - Begin script." >> ${LOGFILE}
echo "`date +%Y-%m-%d_%H:%M:%S` --- Aptitude Update" >> ${LOGFILE}
${APTCMD} update > /dev/null 2>&1
ReturnCode=$?
if [[ "${ReturnCode}" -gt 0 ]]; then
  ErrorFlag=4
  f_cleanup
fi
echo "`date +%Y-%m-%d_%H:%M:%S` --- Aptitude Safe-Upgrade" >> ${LOGFILE}
echo "--------------------------------------------------" >> ${LOGFILE}
${APTCMD} safe-upgrade --assume-yes --target-release `lsb_release -cs`-security >> ${LOGFILE} 2>&1
ReturnCode=$?
if [[ "${ReturnCode}" -gt 0 ]]; then
  ErrorFlag=8
  f_cleanup
fi
echo "--------------------------------------------------" >> ${LOGFILE}
echo "`date +%Y-%m-%d_%H:%M:%S` --- Aptitude Autoclean" >> ${LOGFILE}
echo "--------------------------------------------------" >> ${LOGFILE}
${APTCMD} autoclean >> ${LOGFILE} 2>&1
ReturnCode=$?
if [[ "${ReturnCode}" -gt 0 ]]; then
  ErrorFlag=16
  f_cleanup
fi
echo "--------------------------------------------------" >> ${LOGFILE}
echo "`date +%Y-%m-%d_%H:%M:%S` - End script." >> ${LOGFILE}
 
## Perform cleanup routine.
f_cleanup

Here is the typical output:

/var/log/apt-upgrade.log

Code: Select all

2012-06-01_09:31:19 - Begin script.
2012-06-01_09:31:19 --- Aptitude Update
2012-06-01_09:32:01 --- Aptitude Safe-Upgrade
--------------------------------------------------
Reading package lists...
Building dependency tree...
Reading state information...
Reading extended state information...
Initializing package states...
No packages will be installed, upgraded, or removed.
0 packages upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
Need to get 0 B of archives. After unpacking 0 B will be used.
Reading package lists...
Building dependency tree...
Reading state information...
Reading extended state information...
Initializing package states...
--------------------------------------------------
2012-06-01_09:32:03 --- Aptitude Autoclean
--------------------------------------------------
Reading package lists...
Building dependency tree...
Reading state information...
Reading extended state information...
Initializing package states...
Freed 0 B of disk space
--------------------------------------------------
2012-06-01_09:32:04 - End script.
2012-06-02_03:00:01 - Begin script.
2012-06-02_03:00:01 --- Aptitude Update
2012-06-02_03:00:26 --- Aptitude Safe-Upgrade
--------------------------------------------------
Reading package lists...
Building dependency tree...
Reading state information...
Reading extended state information...
Initializing package states...
The following packages will be upgraded:
  grub-common grub-pc grub-pc-bin grub2-common libcups2 libgcrypt11
6 packages upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
Need to get 3,611 kB of archives. After unpacking 59.4 kB will be freed.
Writing extended state information...
Get: 1 http://us.archive.ubuntu.com/ubuntu/ precise-updates/main libgcrypt11 amd64 1.5.0-3ubuntu0.1 [280 kB]
Get: 2 http://us.archive.ubuntu.com/ubuntu/ precise-updates/main libcups2 amd64 1.5.3-0ubuntu1 [171 kB]
Get: 3 http://us.archive.ubuntu.com/ubuntu/ precise-updates/main grub-pc amd64 1.99-21ubuntu3.1 [140 kB]
Get: 4 http://us.archive.ubuntu.com/ubuntu/ precise-updates/main grub-pc-bin amd64 1.99-21ubuntu3.1 [861 kB]
Get: 5 http://us.archive.ubuntu.com/ubuntu/ precise-updates/main grub2-common amd64 1.99-21ubuntu3.1 [94.3 kB]
Get: 6 http://us.archive.ubuntu.com/ubuntu/ precise-updates/main grub-common amd64 1.99-21ubuntu3.1 [2,066 kB]
Fetched 3,611 kB in 3s (941 kB/s)
debconf: unable to initialize frontend: Dialog
debconf: (TERM is not set, so the dialog frontend is not usable.)
debconf: falling back to frontend: Readline
debconf: unable to initialize frontend: Readline
debconf: (This frontend requires a controlling tty.)
debconf: falling back to frontend: Teletype
dpkg-preconfigure: unable to re-open stdin:
(Reading database ... 61584 files and directories currently installed.)
Preparing to replace libgcrypt11 1.5.0-3 (using .../libgcrypt11_1.5.0-3ubuntu0.1_amd64.deb) ...
Unpacking replacement libgcrypt11 ...
Preparing to replace libcups2 1.5.2-9ubuntu1 (using .../libcups2_1.5.3-0ubuntu1_amd64.deb) ...
Unpacking replacement libcups2 ...
Preparing to replace grub-pc 1.99-21ubuntu3 (using .../grub-pc_1.99-21ubuntu3.1_amd64.deb) ...
Unpacking replacement grub-pc ...
Preparing to replace grub-pc-bin 1.99-21ubuntu3 (using .../grub-pc-bin_1.99-21ubuntu3.1_amd64.deb) ...
Unpacking replacement grub-pc-bin ...
Preparing to replace grub2-common 1.99-21ubuntu3 (using .../grub2-common_1.99-21ubuntu3.1_amd64.deb) ...
Unpacking replacement grub2-common ...
Preparing to replace grub-common 1.99-21ubuntu3 (using .../grub-common_1.99-21ubuntu3.1_amd64.deb) ...
Unpacking replacement grub-common ...
Processing triggers for man-db ...
debconf: unable to initialize frontend: Dialog
debconf: (TERM is not set, so the dialog frontend is not usable.)
debconf: falling back to frontend: Readline
debconf: unable to initialize frontend: Readline
debconf: (This frontend requires a controlling tty.)
debconf: falling back to frontend: Teletype
Processing triggers for install-info ...
Processing triggers for ureadahead ...
Setting up libgcrypt11 (1.5.0-3ubuntu0.1) ...
Setting up libcups2 (1.5.3-0ubuntu1) ...
Setting up grub-common (1.99-21ubuntu3.1) ...
Installing new version of config file /etc/grub.d/10_linux ...
Setting up grub2-common (1.99-21ubuntu3.1) ...
Setting up grub-pc-bin (1.99-21ubuntu3.1) ...
Setting up grub-pc (1.99-21ubuntu3.1) ...
debconf: unable to initialize frontend: Dialog
debconf: (TERM is not set, so the dialog frontend is not usable.)
debconf: falling back to frontend: Readline
debconf: unable to initialize frontend: Readline
debconf: (This frontend requires a controlling tty.)
debconf: falling back to frontend: Teletype
Installation finished. No error reported.
Generating grub.cfg ...
Found linux image: /boot/vmlinuz-3.2.0-24-generic
Found initrd image: /boot/initrd.img-3.2.0-24-generic
Found linux image: /boot/vmlinuz-3.2.0-23-generic
Found initrd image: /boot/initrd.img-3.2.0-23-generic
Found memtest86+ image: /memtest86+.bin
done
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place
ldconfig deferred processing now taking place
Reading package lists...
Building dependency tree...
Reading state information...
Reading extended state information...
Initializing package states...
--------------------------------------------------
2012-06-02_03:00:43 --- Aptitude Autoclean
--------------------------------------------------
Reading package lists...
Building dependency tree...
Reading state information...
Reading extended state information...
Initializing package states...
Freed 0 B of disk space
--------------------------------------------------
2012-06-02_03:00:44 - End script.
All times are UTC-06:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited