How to Install MariaDB 10.4.x on Ubuntu Server 18.04 LTS

Post Reply
User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

How to Install MariaDB 10.4.x on Ubuntu Server 18.04 LTS

Post: # 739Post LHammonds
Fri Sep 13, 2019 9:53 am

------------- WORK-IN-PROGRESS -------------

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: >>> INSERT LINK HERE <<<

High-level overview

This thread will cover installation of a dedicated Ubuntu server and MariaDB database. MariaDB is a drop-in replacement for MySQL which is enjoying feature/security/performance updates by the original MySQL team while MySQL languishes under the thumb of Oracle. 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 10 PC. Migration of data from an older database 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 the thread on the Ubuntu forums (see above).

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-database
  • Internet domain: mydomain.com
  • Ubuntu Server IP address: 192.168.107.27
  • Ubuntu Admin ID: administrator
  • Ubuntu Admin Password: myadminpass
  • Email Server (remote): 192.168.107.25
  • Database root Password: mydbrootpass
  • 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.

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Install Ubuntu Server

Post: # 740Post LHammonds
Fri Sep 13, 2019 9:54 am

Install Ubuntu Server

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.

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Install MariaDB

Post: # 741Post LHammonds
Fri Sep 13, 2019 9:55 am

Add MariaDB repositories

Source of information: MariaDB (NOTE: Your flavor of Linux and download location may vary and that page will help)

Perform these steps on each database server.
  1. Connect to the server using PuTTY and login with your administrator credentials.
  2. Type the following to add the MariaDB repositories:

    Code: Select all

    sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
    sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mirror.rackspace.com/mariadb/repo/10.4/ubuntu '$(lsb_release -cs)' main'
    sudo apt update
    
Install MariaDB

Perform these steps on each database server.
  1. Install the database server and required dependencies by typing the following:

    Code: Select all

    sudo apt -y install mariadb-server
  2. When installation is completed, the database service should automatically start. It is also configured to automatically start upon reboot.
Verify database service is running

Verify the service is running by typing any of the following commands:

Code: Select all

systemctl status mariadb
or

Code: Select all

netstat -tap | grep mysql
or

Code: Select all

sudo mysqladmin status
Configuration File

Setup a new configuration file so you can make changes later. Copy the package defaults into a new file:

Code: Select all

sudo cp /etc/mysql/my.cnf.dpkg-new /etc/my.cnf
sudo chown root:root /etc/my.cnf
sudo chmod 644 /etc/my.cnf

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Tighten Security

Post: # 742Post LHammonds
Fri Sep 13, 2019 10:00 am

Secure Installation

MariaDB comes with a script to tighten-down security for a production server.

Perform these steps on the database server.
  1. Connect to the server using PuTTY.
  2. Start the secure install script:

    Code: Select all

    sudo mysql_secure_installation
  3. Enter current password for root: Just press ENTER here since default install does not set a password.
  4. Switch to unix_socket authentication? Select N
  5. Change the root password? Select Y and set your password.
  6. Remove the anonymous users? Select Y
  7. Disallow root login remotely? Select Y
  8. Remove test database and access to it? Select Y
  9. Reload privilege tables now? Select Y
Disable Command History

On production servers, It is a good idea to disable SQL command history which can expose database user accounts, passwords and encryption keys. The following commands will remove the history file if it exists and then link it to a non-existing (null) file...thus never recording anything in the future.

Code: Select all

sudo rm /root/.mysql_history
sudo ln -s /dev/null /root/.mysql_history
Disable Filesystem Access

Once your system is running in production mode, it is a good idea to disable the "local_infile" option until you need it. By default, it is enabled and you can check by running the following command:

Code: Select all

mysql -u root -e "show global variables like 'local_infile';"
Edit the configuration file:

Code: Select all

sudo vi /etc/my.cnf
Add the local-infile line under the "[mysqld]" section. Add the [mysqld] section if it does not already exist.

Code: Select all

[mysqld]
local-infile=0
This will prevent the database (and hackers) from accessing the filesystem.

Error Log File

Specify the location of the error log. By default, it is not set and will write to the datadir location to a filename of its choosing.

Edit the configuration file:

Code: Select all

sudo vi /etc/my.cnf
Add the log_error line under the "[mariadb]" section. Add the [mariadb] section if it does not already exist.

Code: Select all

[mariadb]
log_error = /var/log/mysql/mariadb.err
Restart MariaDB Service to Apply Changes

Code: Select all

sudo systemctl restart mariadb
Check to make sure changes are how you set them:

Code: Select all

mysql -u root -e "show global variables like 'local_infile';show global variables like 'log_error';show global variables like 'datadir';"
Look for Insecure Accounts

Code: Select all

mysql -u root -e "SELECT User,Host,Password FROM mysql.user;"
There will not be anything other than root and mysql at initial install and after running the secure_installation script. But do this once in a while and look for any accounts with blank passwords which are insecure. It is even worse if the associated host setting is % which means that unsecure account can connect from anywhere.

Fail2Ban

If you followed my instructions for setting up the Ubuntu Server, you should already have sshd being protected by Fail2Ban. You can add these few changes to also watch for database login failures.

Code: Select all

sudo vi /etc/fail2ban/jail.local
Add the following to the bottom. If your data path is different, be sure to update "logpath"

Code: Select all

[mysqld-auth]
enabled = true
filter  = mysqld-auth
port    = 3306
logpath = /var/log/mysql/mariadb.err
Restart the service to apply changes:

Code: Select all

sudo systemctl restart fail2ban
Check to see if the rule shows up:

Code: Select all

sudo fail2ban-client status mysqld-auth

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Firewall Rules

Post: # 743Post LHammonds
Fri Sep 13, 2019 10:02 am

Firewall Rules

Edit the firewall script that was created during the initial setup of the server (if you followed my instructions):

Code: Select all

vi /var/scripts/prod/en-firewall.sh
Add the following rule which allows connectivity from from anything on your local subnet:

Code: Select all

echo "Adding Database Server rules"
ufw allow from 192.168.107.0/24 proto tcp to any port 3306 comment 'MariaDB' 1>/dev/null 2>&1
Or you can reduce access even further by limiting to just specific IP addresses such as your web servers like this:

Code: Select all

echo "Adding Database Server rules"
ufw allow from 192.168.107.107.33 proto tcp to any port 3306 comment 'MariaDB' 1>/dev/null 2>&1
ufw allow from 192.168.107.107.34 proto tcp to any port 3306 comment 'MariaDB' 1>/dev/null 2>&1
ufw allow from 192.168.107.107.35 proto tcp to any port 3306 comment 'MariaDB' 1>/dev/null 2>&1
Run the updated rules:

Code: Select all

/var/scripts/prod/en-firewall.sh

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Relocate the Databases

Post: # 744Post LHammonds
Fri Sep 13, 2019 10:51 am

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
  2. At the console, type the following:

    Code: Select all

    sudo mkdir -p /opt/mariadb
    sudo chown mysql:mysql /opt/mariadb
    sudo vi /etc/my.cnf
  3. Change the location where the databases by changing datadir from /var/lib/mysql to:

    Code: Select all

    datadir = /opt/mariadb
  4. Type the following commands to move the database(s)

    Code: Select all

    sudo systemctl stop mariadb
    sudo mv /var/lib/mysql/* /opt/mariadbl/.
    sudo systemctl start mariadb
  5. Verify the service is running by typing either of the following two commands:

    Code: Select all

    systemctl status mariadb
    or

    Code: Select all

    netstat -tap | grep mysql
NOTE: If you did not remove apparmor, it will prevent the database service from starting at this point until you update apparmor settings or remove it.

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Reset lost root password

Post: # 745Post LHammonds
Fri Sep 13, 2019 11:22 am

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 account, 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 mydbrootpass (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.
  3. Shutdown the database service:

    Code: Select all

    sudo systemctl stop mariadb
  4. Start MariaDB in safe mode to bypass user authentication and disable networking since the database is vulnerable in safe mode (The ampersand allows it to run in the background)

    Code: Select all

    sudo mysqld_safe --skip-grant-tables  --skip-networking &
  5. Connect to the database server via the command-line utility (which should not ask for a password):

    Code: Select all

    mysql -u root
  6. Select the primary database and reset the password:
    USE mysql; UPDATE USER SET Password=PASSWORD("mydbrootpass") WHERE User='root'; FLUSH PRIVILEGES; QUIT
  7. Stop the database engine and start in the normal mode:

    Code: Select all

    sudo mysqladmin shutdown
    sudo systemctl start mariadb
  8. Test out the new password by connecting with the command-line utility:

    Code: Select all

    mysql -u root -p
    QUIT

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Usage Examples

Post: # 746Post LHammonds
Fri Sep 13, 2019 11:27 am

Usage Examples
  1. Connect to the server using PuTTY.
  2. At the login prompt, login with your administrator account.
  3. Start a database session and create a database called wordpress and a user called wordpressuser with a password of "mydbpass" and grant the user access to the database:

    Code: Select all

    mysql -u root
    CREATE DATABASE wordpress CHARACTER SET utf8 COLLATE utf8_bin;
    GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'%' IDENTIFIED BY 'mydbpass';
    FLUSH PRIVILEGES;
    QUIT
  4. To remove the changes you just made, you can run the following commands to delete the user and database:

    Code: Select all

    DROP USER wordpressuser;
    DROP DATABASE wordpress;
NOTE #1: The 'wordpressuser'@'%' above means that user can login from anywhere so the application can connect no matter what server it is on. This can be handy if your app server's IP address changes frequently. If you want tighter security, you can specify the IP address of the app server(s) as part of the login permissions. For example, the following would tell the DB server to only allow the database user access from specific app server addresses only:

Code: Select all

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'192.168.107.26' IDENTIFIED BY 'mydbpass';
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'192.168.107.27' IDENTIFIED BY 'mydbpass';
NOTE #2: Another modification to the grant command can limit the user to just the local machine which is perfect if your application and database are on the same machine:

Code: Select all

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost' IDENTIFIED BY 'mydbpass';

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Scripting

Post: # 747Post LHammonds
Fri Sep 13, 2019 11:34 am

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.

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Backup All Databases

Post: # 748Post LHammonds
Fri Sep 13, 2019 11:35 am

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.

You could also modify this so it does not touch the offsite backup and instead, have the offsite backup pull the backup files from the database server to increase security. Increase security in regards to the database server being hacked and not letting it access the offsite location for this reason.

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

Code: Select all

#!/bin/bash
#############################################
## Name          : mysql-backup.sh
## Version       : 1.5
## Date          : 2017-12-27
## Author        : LHammonds
## Purpose       : Complete backup of MySQL database.
## Compatibility : Verified on to work on:
##                  - Ubuntu Server 10.04 LTS - 18.04 LTS
##                  - MySQL 5.1.41 - 5.5.22
##                  - MariaDB 10.1.22 - 10.4.8
## 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
## 2017-04-13 LTH Corrected variable casing.
## 2017-04-24 LTH All databases minus an exception list.
## 2017-09-01 LTH Handle folder creation upon 1st time run.
## 2017-12-27 LTH Added directory/file permission setting.
#############################################

## Import common variables and functions. ##
source /var/scripts/common/standard.conf

LogFile="${LogDir}/mysql-backup.log"
LockFile="${TempDir}/mysql-backup.lock"
TargetDir="${BackupDir}/mysql"
MySQLDir="/opt/mysql"
DatabasesToExclude="JunkDB1 JunkDB1"
ExclusionList="'information_schema','mysql'"
OffsiteBackDir="${OffsiteDir}/${Hostname}/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   
    DataFileSIZE=`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 = ${DataFileSIZE} 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 "[Success] MySQL Backup" "MySQL backup completed with no errors."
  else
    f_sendmail "[Failure] MySQL Backup" "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}


for DB in `echo "${DatabasesToExclude}"`
do
  ExclusionList="${ExclusionList},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${ExclusionList})"
MYSQLDUMP_DATABASES=""
for DB in `mysql -ANe"${SQLSTMT}"`
do
  MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--skip-lock-tables"

## Backup all databases.
${MYSQLDUMP} ${MYSQLDUMP_OPTIONS} --databases ${MYSQLDUMP_DATABASES} > ${TargetDir}/mysql-all.sql

## Loop through every database.
#DBList=$(echo "show databases;"|mysql --skip-column-names)
#for SingleDB in ${DBList}

for SingleDB in ${MYSQLDUMP_DATABASES}
do
  if [ "${SingleDB}" != "information_schema" ] && [ "${SingleDB}" != "performance_schema" ]; then
    ## Backup individual database.
    ${MYSQLDUMP} ${SingleDB} > ${TargetDir}/${SingleDB}.sql
    ## Create database sub-folder.
    mkdir -p ${TargetDir}/${SingleDB}
    ## Export each table in the database individually.
    for SingleTable in `echo "show tables" | $MYSQL ${SingleDB}|grep -v Tables_in_`;
    do
      DataFile=${TargetDir}/${SingleDB}/${SingleTable}.sql
      case "${SingleTable}" in
        general_log)
          ${MYSQLDUMP} ${SingleDB} ${SingleTable} --skip-lock-tables > ${DataFile}
          ;;
        slow_log)
          ${MYSQLDUMP} ${SingleDB} ${SingleTable} --skip-lock-tables > ${DataFile}
          ;;
        *)
          ${MYSQLDUMP} ${SingleDB} ${SingleTable} > ${DataFile}
          ;;
      esac
    done
  fi
done

## Ensure proper directory and file permissions.
find ${TargetDir}/ -type f -print0 | xargs -0 chmod 0600
find ${TargetDir}/ -type d -print0 | xargs -0 chmod 0700

## 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=7 -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
  ## Offline file detected.  Assuming failed mount.
  ErrorFlag=$((${ErrorFlag} + 16))
  echo "`date +%Y-%m-%d_%H:%M:%S` --- ERROR: Offline file detected: ${OffsiteTestFile}" >> ${LogFile}
  f_emergencyexit ${ErrorFlag}
fi

## If destination folder does not exist, create it. Mainly for 1st time use.
if [ ! -d ${OffsiteBackDir} ]; then
  mkdir -p ${OffsiteBackDir}
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

2019-09-13_23:00:01 - MySQL backup started.
2019-09-13_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
2019-09-13_23:00:01 --- Space consumed in  = 104K
2019-09-13_23:00:07 --- Compressing archive: /tmp/2019-09-13-23-00_db-backup.tar.7z
2019-09-13_23:00:18 --- Copying archive file to offsite location.
2019-09-13_23:00:19 --- Total backup time: 0 hour(s) 0 minute(s) 18 second(s)
2019-09-13_23:00:19 - Database backup completed.
2019-09-13_23:00:19 - Database backup exit code: 0
2019-09-14_23:00:02 - Database backup started.
2019-09-14_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
2019-09-14_23:00:02 --- Space consumed in  = 104K
2019-09-14_23:00:07 --- Compressing archive: /tmp/2019-09-14-23-00_db-backup.tar.7z
2019-09-14_23:00:19 --- Copying archive file to offsite location.
2019-09-14_23:00:25 --- Total backup time: 0 hour(s) 0 minute(s) 23 second(s)
2019-09-14_23:00:25 - Database backup completed.
2019-09-14_23:00:25 - Database backup exit code: 0
2019-09-15_23:00:01 - Database backup started.
2019-09-15_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
2019-09-15_23:00:01 --- Space consumed in  = 108K
2019-09-15_23:00:05 --- Compressing archive: /tmp/2019-09-15-23-00_db-backup.tar.7z
2019-09-15_23:00:16 --- Copying archive file to offsite location.
2019-09-15_23:00:17 --- Total backup time: 0 hour(s) 0 minute(s) 16 second(s)
2019-09-15_23:00:17 - Database backup completed.
2019-09-15_23:00:17 - Database backup exit code: 0
2019-09-16_23:00:01 - Database backup started.
2019-09-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:            2 days 8 hours 38 min 25 sec
2019-09-16_23:00:01 --- Space consumed in  = 108K
2019-09-16_23:00:04 --- Compressing archive: /tmp/2019-09-16-23-00_db-backup.tar.7z
2019-09-16_23:00:16 --- Copying archive file to offsite location.
2019-09-16_23:00:17 --- Total backup time: 0 hour(s) 0 minute(s) 16 second(s)
2019-09-16_23:00:17 - Database backup completed.
2019-09-16_23:00:17 - Database backup exit code: 0
Here is a sample of the files stored on the offsite server:

D:\MariaDB\database

Code: Select all

2019-09-06-23-00_db-backup.tar.7z
2019-09-07-23-00_db-backup.tar.7z
2019-09-08-23-00_db-backup.tar.7z
2019-09-09-23-00_db-backup.tar.7z
2019-09-10-23-00_db-backup.tar.7z
2019-09-11-23-00_db-backup.tar.7z
2019-09-12-23-00_db-backup.tar.7z
2019-09-13-23-00_db-backup.tar.7z
2019-09-14-23-00_db-backup.tar.7z
2019-09-15-23-00_db-backup.tar.7z
2019-09-16-23-00_db-backup.tar.7z

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Backup Individual Database

Post: # 749Post LHammonds
Fri Sep 13, 2019 11:47 am

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 the 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/db-ondemand-backup.sh

Code: Select all

#!/bin/bash
#############################################
## Name          : db-ondemand-backup.sh
## Version       : 1.2
## Date          : 2017-09-01
## Author        : LHammonds
## Purpose       : Backup of a single database
## Compatibility : Verified on Ubuntu Server 10.04 - 18.04 LTS, MySQL 5.1.62 - 5.5.22, MariaDB 10.3.8 - 10.4.8
## 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.
## 2017-04-13 LTH Corrected variable casing.
## 2017-09-01 LTH Handle folder creation upon 1st time run.
#############################################

## Import common variables and functions. ##
source /var/scripts/common/standard.conf

LogFile="${LogDir}/db-ondemand-backup.log"
LockFile="${TempDir}/db-ondemand-backup.lock"
TargetDir="${BackupDir}/db-ondemand"
OffsiteBackDir="${OffsiteDir}/${Hostname}/db-ondemand"
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
}

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.
  echo "Lock file detected, aborting script."
  f_sendmail "[Failure] 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
  DatabaseName="mediawiki"
  rm "${ShareDir}/mediawiki"
elif [ -f "${ShareDir}/intranetwp" ]; then
  DatabaseName="intranetwp"
  rm "${ShareDir}/intranetwp"
elif [ -f "${ShareDir}/intranetwpdev" ]; then
  DatabaseName="intranetwpdev"
  rm "${ShareDir}/intranetwpdev"
elif [ -f "${ShareDir}/owncloud" ]; then
  DatabaseName="owncloud"
  rm "${ShareDir}/owncloud"
elif [ -f "${ShareDir}/phpbb" ]; then
  DatabaseName="phpbb"
  rm "${ShareDir}/phpbb"
elif [ -f "${ShareDir}/wordpress" ]; then
  DatabaseName="wordpress"
  rm "${ShareDir}/wordpress"
fi
if [[ "${DatabaseName}" = "" ]]; then
  echo "No database selected. Exiting script."
  f_cleanup 0
  exit 0
fi

ArchiveFile="`date +%Y-%m-%d-%H-%M`_mysql-db-${DatabaseName}.${ArchiveMethod}"

echo "`date +%Y-%m-%d_%H:%M:%S` - Database ${DatabaseName} 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} ${DatabaseName} > ${TargetDir}/${DatabaseName}.sql
## Create database sub-folder.
mkdir -p ${TargetDir}/${DatabaseName}
## Export each table in the database individually.
for SingleTable in `echo "show tables" | $MYSQL ${DatabaseName}|grep -v Tables_in_`;
do
  FileName=${TargetDir}/${DatabaseName}/${SingleTable}.sql
  case "${SingleTable}" in
    general_log)
      ${MYSQLDUMP} ${DatabaseName} ${SingleTable} --skip-lock-tables > ${FileName}
      ;;
    slow_log)
      ${MYSQLDUMP} ${DatabaseName} ${SingleTable} --skip-lock-tables > ${FileName}
      ;;
    *)
      ${MYSQLDUMP} ${DatabaseName} ${SingleTable} > ${FileName}
      ;;
  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=7 -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 "[Failure] 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
  ## Offline file found. Assuming failed mount.
  ErrorFlag=$((${ErrorFlag} + 16))
  echo "`date +%Y-%m-%d_%H:%M:%S` --- ERROR: Offline file detected: ${OffsiteTestFile}" >> ${LogFile}
  f_emergencyexit ${ErrorFlag}
fi

## If destination folder does not exist, create it. Mainly for 1st time use.
if [ ! -d ${OffsiteBackDir} ]; then
  mkdir -p ${OffsiteBackDir}
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 "[Failure] 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` - Database ${DatabaseName} backup completed." >> ${LogFile}

## Perform cleanup routine.
f_cleanup

## Email the result to the administrator.
if [ ${ErrorFlag} -eq 0 ]; then
  f_sendmail "[Success] Database Backup Success" "Database backup completed with no errors."
else
  f_sendmail "[Failure] Database Backup ERROR" "Database backup failed.  ErrorFlag = ${ErrorFlag}"
fi

## Exit with the combined return code value.
exit ${ErrorFlag}

Here is a sample of the log output:

/var/log/db-ondemand-backup.log

Code: Select all

2019-09-10_20:00:01 - Database mediawiki backup started.
2019-09-10_20:00:02 --- Compressing archive: /var/temp/2019-09-10-20-00_db-mediawiki.tar.7z
2019-09-10_20:00:05 --- Copying archive file to offsite location.
2019-09-10_20:00:06 --- Total backup time: 0 hour(s) 0 minute(s) 5 second(s)
2019-09-10_20:00:06 - Database mediawiki backup completed.
2019-09-11_20:00:02 - Database mediawiki backup started.
2019-09-11_20:00:02 --- Compressing archive: /var/temp/2019-09-11-20-00_db-mediawiki.tar.7z
2019-09-11_20:00:07 --- Copying archive file to offsite location.
2019-09-11_20:00:11 --- Total backup time: 0 hour(s) 0 minute(s) 9 second(s)
2019-09-11_20:00:11 - Database mediawiki backup completed.
2019-09-12_20:01:01 - Database mediawiki backup started.
2019-09-12_20:01:02 --- Compressing archive: /var/temp/2019-09-12-20-01_db-mediawiki.tar.7z
2019-09-12_20:01:03 --- Copying archive file to offsite location.
2019-09-12_20:01:04 --- Total backup time: 0 hour(s) 0 minute(s) 3 second(s)
2019-09-12_20:01:04 - Database mediawiki backup completed.
2019-09-13_20:01:01 - Database mediawiki backup started.
2019-09-13_20:01:02 --- Compressing archive: /var/temp/2019-09-13-20-01_db-mediawiki.tar.7z
2019-09-13_20:01:03 --- Copying archive file to offsite location.
2019-09-13_20:01:03 --- Total backup time: 0 hour(s) 0 minute(s) 2 second(s)
2019-09-13_20:01:03 - Database mediawiki backup completed.
Here is a sample of the files stored on the offsite server:

D:\MariaDB\ondemand\

Code: Select all

2019-09-10-20-00_db-mediawiki.tar.7z
2019-09-11-20-00_db-mediawiki.tar.7z
2019-09-12-20-01_db-mediawiki.tar.7z
2019-09-13-20-01_db-mediawiki.tar.7z

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Migration

Post: # 750Post LHammonds
Fri Sep 13, 2019 11:49 am

Migrate Database from Old Server

You can use the full backup automatically made by the "db-backup.sh" script (/bak/mysql/db-all.sql) if you have that already running or you can create a manual backup. The steps below will document how to do it manually but you could just as easily use your existing backup file(s).

Here are the steps to import the databases from the old server into the new server:
  1. On the old server, make a full backup of the current databases by typing: mysqldump --all-databases > /tmp/db-all.sql
  2. Transfer db-all.sql to the new server (via WinSCP or other means)
  3. On the new server, make a full backup of the current databases by typing: mysqldump --all-databases > /tmp/db-all-before.sql
  4. Import the db-all.sql file by typing: mysql < /tmp/db-all.sql
  5. Make a full backup of the current databases by typing: mysqldump --all-databases > /tmp/db-all-after.sql
  6. Verify that your databases, tables, rows and users are now on the new server
  7. Archive the /tmp/*.sql files for safe-keeping until you no longer need them. If you leave them in /tmp, they will be automatically deleted upon next reboot.
  8. Now would be a good time to run your backup script: /var/scripts/prod/db-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.

NOTE: Saving the following lines in case I expand this further by showing how to extract an archive 1st and then restore from that.
  • Make a restore folder by typing: mkdir /tmp/restore
  • Extract the archive by typing: 7za x -so -w/tmp /tmp/*.7z | tar -C /tmp/restore --strip-components=3 -xf -
NOTE: If you see the following error in the syslog, especially after doing an "apt-get upgrade" and noticing the mysqld service stopped, you need to simply run "mysql_upgrade" to fix the problem. Reference Bug

Code: Select all

Jun  1 14:25:48 srv-mysql mysqld_safe[23145]: ERROR: 1136  Column count doesn't match value count at row 1
Jun  1 14:25:48 srv-mysql mysqld_safe[23145]: 2017-06-01 14:25:48 140594627692800 [ERROR] Aborting
Jun  1 14:25:50 srv-mysql mysqld_safe[23145]: Installation of system tables failed!
Migrating Individual Databases from Old Server

If you export one database at a time, it will not include the grants. You will need to export the grants and then import the database, then import the grants on the new server.

Example: Let us assume the database we want to migrate is called "mydb"

On the old server:

Code: Select all

mysqldump --databases mydb > /tmp/mydb.sql
mysql --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g' > /tmp/mydb-grants.sql
Now edit the mydb-grants.sql file and delete everything not related to the DB you want.

Copy both .sql files to the new server and then execute the following commands:

Code: Select all

mysql < /tmp/mydb.sql
mysql < /tmp/mydb-grants.sql
Restoring a database without generating a log

If you are restoring/migrating a huge database and do not want a log generated for the import, you can temporarily disable logging for your session as follows:

Code: Select all

mysql
SET sql_log_bin = 0;
source /tmp/mydb.sql
exit

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Crontab Schedule

Post: # 751Post LHammonds
Fri Sep 13, 2019 11:51 am

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
 
#
# Backup Databases
#
0 23 * * * /var/scripts/prod/db-backup.sh > /dev/null 2>&1
#
# Database On Demand Backup
#
0-59 * * * * /var/scripts/prod/db-ondemand-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

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

Code: Select all

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

Code: Select all

touch /tmp/deleteme
sudo 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

sudo cp /var/scripts/data/crontab.root /var/scripts/data/2019-09-13-crontab.root
sudo vi /var/scripts/data/crontab.root (make your changes)
sudo crontab -u root /var/scripts/data/crontab.root

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Fail2Ban

Post: # 752Post LHammonds
Fri Sep 13, 2019 11:53 am

Fail2Ban

If you have Fail2Ban installed and protecting SSH as part of the base install. You can add these few changes to also watch for database login failures.

Code: Select all

vi /etc/fail2ban/jail.local
Add the following to the bottom. If your data path is different, be sure to update "logpath"

Code: Select all

[DEFAULT]
## 127.0.0.1/8 = ignore login failures on the local machine
## 192.168.107.0/24 = ignore login failures on an entire subnet
## 192.168.107.55 = ignore login failures for this specific IP
ignoreip = 127.0.0.1/8 192.168.107.55

## "bantime" is the number of seconds that a host is banned.
##  300 =  5 minutes
##  600 = 10 minutes
##  900 = 15 minutes
## 1800 = 30 minutes
## 3600 = 60 minutes
bantime = 1800
## "findtime" is the length of time between login attempts before a ban is set.
findtime = 300
## "maxretry" is how many attempts can be made to access the server from a single IP before a ban is imposed.
maxretry = 5
## "destemail" is the email address where you would like to receive the emails.
destemail = lhammonds@mydomain.com
## "sender" is the FROM: address when it arrives in your mailbox.
sender = ubuntu@mydomain.com
## Use the lightweight sendemail instead of sendmail
mta = sendemail
## Email notify with whois report and relevant log lines when a ban occurs
action = %(action_mwl)s

[mysqld-auth]
enabled = true
filter  = mysqld-auth
port    = 3306
logpath = /var/log/mysql/error.log

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Additional Security Measures

Post: # 753Post LHammonds
Fri Sep 13, 2019 11:54 am

Rename root

For increased security, you can rename the root account to something else with the following commands:

Code: Select all

USE mysql;
UPDATE USER SET User='NewSecretRoot' WHERE User='root';
FLUSH PRIVILEGES;
Review User Accounts

Code: Select all

SELECT User,Host,Password FROM mysql.user;
  1. Make sure everyone has a password set.
  2. If there are any hosts as "%" then review to make sure it is absolutely necessary. If possible, use "localhost" first, then use the specific hostname/IP after that. The "%" wildcard should be a last resort for special cases like the app server name/IP changes all the time or there are multiple app servers connecting using the same ID.
You can check the permissions of each login to make sure they have the least permissions necessary to get the job done:

Code: Select all

show grants for 'someuser'@'localhost';
If said user has DELETE privilege when it is not needed, you could remove it using a command like the following:

Code: Select all

REVOKE DELETE ON appdatabase.* FROM 'someuser'@'localhost';

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Graphical User Interfaces

Post: # 754Post LHammonds
Fri Sep 13, 2019 12:15 pm

NOTE: I have not tested any of these since I tend to use the command-line

Web-based interfaces

There are a few web-based tools you can get for a GUI front-end to MariaDB. It is not recommended to install a web server on your database server if it is a dedicated database server....instead, install them onto an existing web server that can connect to your database. For additional security, you might want to limit access to the admin website to just your private network and use VPN from the outside to access the admin site.

Adminer - A single PHP file and free for use even for a commercial business.

WebMySQL - An OpenSource PHP application to replace desktop interfaces for database management.

phpMiniAdmin - A single PHP file to manage MySQL (OpenSource)

SQLBuddy - PHP interface for managing MySQL. May not be compatible with newest versions of PHP and lack support for newer features in MariaDB due to no development since 2011.

Installable Client

DBeaver - A universal database tool. Runs on Windows, MacOSX and Linux and can connect to many different database engines.

User avatar
LHammonds
Site Admin
Site Admin
Posts: 772
Joined: Fri Jul 31, 2009 6:27 pm
Are you a filthy spam bot?: No
Location: Behind You
Contact:

Export User Grants/Revokes

Post: # 777Post LHammonds
Tue Oct 01, 2019 3:42 pm

Export User Grants/Revokes

The Percona Toolkit comes with a handy utility called pt-show-grants which will allow you to export an SQL file of all the grants for all your databases into a single file that you can use to audit permissions or help with a restore or help with database migrations.

This is the command you use to install the toolkit:

Code: Select all

sudo apt install percona-toolkit
The encrypt script below will export all the user grants to an SQL file and then encrypt the file using GPG (OpenGPG Encryption). This allows the file to be backed up to a remote location without worrying about someone opening the file and seeing sensitive plain-text data.

You could schedule the encryption script to run on a normal basis to refresh your backup archive. The decrypt script only needs to be manually run if you need to decrypt the backup file.

Be sure to change line 46 to use a unique passphrase for your environment. This must also match the same passphrase in the decryption script.

/var/scripts/prod/mariadb-user-encrypt.sh

Code: Select all

#!/bin/bash
#############################################################
## Name : mariadb-user-encrypt.sh
## Version : 1.0
## Date : 2018-04-16
## Author : LHammonds
## Purpose : Export REVOKE,DROP,GRANT statements for all users.
##         : Encrypt the files for storage
## How to Decrypt :
##   gpg --cipher-algo aes256 --output decrypted-target.txt /
##       --passphrase-file /etc/gpg.conf --batch --yes /
##       --no-tty --decrypt encrypted-file.enc
## Compatibility : Verified on Ubuntu Server 16.04 thru 18.04 LTS
##               : Verified on MariaDB 10.1.32 thru 10.3.18
## Requirements : percona-toolkit (tested using version 3.0.6)
##              : gpgv (tested using version 2.2.4)
## Run Frequency : Often as desired.
## NOTE: Grant files contain the grant commands only.
##       Revoke files contain the revoke, drop and grant commands together.
## Parameters : None
## Exit Codes :
## 0  = Success
## 1  = ERROR: Lock file detected
## 2  = ERROR: Must be root user
## 4  = ERROR: percona-toolkit not installed
## 8  = ERROR: gpgv not installed
## 16 = ERROR: encryption failure
## 32 = ERROR: checksum mismatch
###################### CHANGE LOG ###########################
## DATE       VER WHO WHAT WAS CHANGED
## ---------- --- --- ---------------------------------------
## 2018-04-16 1.0 LTH Created script.
#############################################################

## Import standard variables and functions. ##
source /var/scripts/common/standard.conf

## Define local variables.
BackupDir=${BackupDir}/mysql
Title="${Company}-mariadb-user-encrypt"
LogFile="${LogDir}/${Title}.log"
LockFile="${TempDir}/${Title}.lock"
GrantFile="${Company}-user-grant.sql"
RevokeFile="${Company}-user-revoke.sql"
## Change this password to anything other than the default! ##
CryptPass="abc123"
CryptPassFile="${TempDir}/${Title}.gpg"
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
  if [ ${ErrorFlag} != 0 ]; then
    f_sendmail "ERROR: Script Failure" "Please review the log file on ${Hostname}${LogFile}"
    echo "`date +%Y-%m-%d_%H:%M:%S` - Encrypt aborted." >> ${LogFile}
  fi
  if [ -f ${CryptPassFile} ]; then
    ## Remove temporary file
    rm ${CryptPassFile}
  fi
  exit ${ErrorFlag}
} ## f_cleanup

function f_encrypt()
{
  filename=$1
  encrypted=$2
  ## Create temporary password file
  touch ${CryptPassFile}
  chmod 0600 ${CryptPassFile}
  echo ${CryptPass} > ${CryptPassFile}
  if ! gpg --cipher-algo aes256 --output ${encrypted} --passphrase-file ${CryptPassFile} --batch --yes --no-tty --symmetric ${filename}; then
    ## Encryption failed, log results, send email, terminate program.
    echo "ERROR: Encryption failed. ${filename}" | tee -a ${LogFile}
    ErrorFlag=16
    f_cleanup
  fi
  if [ -f ${CryptPassFile} ]; then
    ## Remove temporary file
    rm ${CryptPassFile}
  fi
} ## f_encrypt

function f_compare()
{
  filename=$1
  if ! sha512sum --status --check ${filename}; then
    ## Comparison failed, log results, send email, terminate program.
    echo "ERROR: Checksum mismatch: ${filename}" | tee -a ${LogFile}
    ErrorFlag=32
    f_cleanup
  fi
} ## f_compare

#######################################
##       PREREQUISITE CHECKS         ##
#######################################

if [ -f ${LockFile} ]; then
  # Lock file detected.  Abort script.
  echo "Backup partitions 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 backup partitions is not actually running."
  f_sendmail "ERROR: Encrypt 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 script 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
  exit ${ErrorFlag}
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." | tee -a ${LogFile}
  ErrorFlag=2
  f_cleanup
fi

## Requirement Check: Software
command -v pt-show-grants > /dev/null 2>&1 && ReturnCode=0 || ReturnCode=1
if [ ${ReturnCode} = 1 ]; then
  ## Required program not installed.
  echo "ERROR: percona-toolkit not installed." | tee -a ${LogFile}
  ErrorFlag=4
  f_cleanup
fi
command -v gpg > /dev/null 2>&1 && ReturnCode=0 || ReturnCode=1
if [ ${ReturnCode} = 1 ]; then
  ## Required program not installed.
  ## NOTE: gpgv comes standard with Ubuntu Server 16.04 LTS
  echo "ERROR: gpgv not installed." | tee -a ${LogFile}
  ErrorFlag=8
  f_cleanup
fi

#######################################
##           MAIN PROGRAM            ##
#######################################

echo "`date +%Y-%m-%d_%H:%M:%S` - Export/Encrypt started." | tee -a ${LogFile}

## Export grant commands only
pt-show-grants --user root --separate --flush > ${BackupDir}/${GrantFile}
## Export revoke, drop and grant commands
pt-show-grants --user root --separate --flush --revoke --drop > ${BackupDir}/${RevokeFile}

## Encrypt files for transfer and storage
f_encrypt ${BackupDir}/${GrantFile} ${BackupDir}/${GrantFile}.enc
f_encrypt ${BackupDir}/${RevokeFile} ${BackupDir}/${RevokeFile}.enc

## Create checksum files
sha512sum ${BackupDir}/${GrantFile} > ${BackupDir}/${GrantFile}.sha512
sha512sum ${BackupDir}/${RevokeFile} > ${BackupDir}/${RevokeFile}.sha512
sha512sum ${BackupDir}/${GrantFile}.enc > ${BackupDir}/${GrantFile}.enc.sha512
sha512sum ${BackupDir}/${RevokeFile}.enc > ${BackupDir}/${RevokeFile}.enc.sha512

## Verify checksum results
f_compare ${BackupDir}/${GrantFile}.sha512
f_compare ${BackupDir}/${RevokeFile}.sha512
f_compare ${BackupDir}/${GrantFile}.enc.sha512
f_compare ${BackupDir}/${RevokeFile}.enc.sha512

## Remove unencrypted files
rm ${BackupDir}/${GrantFile}
rm ${BackupDir}/${RevokeFile}

## Set file permissions.
chmod 0600 ${BackupDir}/${GrantFile}*
chmod 0600 ${BackupDir}/${RevokeFile}*

/bin/ls -l ${BackupDir}/${GrantFile}.enc | tee -a ${LogFile}
/bin/ls -l ${BackupDir}/${RevokeFile}.enc | tee -a ${LogFile}

echo "`date +%Y-%m-%d_%H:%M:%S` - Export/Encrypt finished." | tee -a ${LogFile}
f_cleanup
Example output:

Code: Select all

2019-10-01_16:32:40 - Export/Encrypt started.
-rw------- 1 root root 1843 Oct  1 16:32 /bak/mysql/abc-user-grant.sql.enc
-rw------- 1 root root 3346 Oct  1 16:32 /bak/mysql/abc-user-revoke.sql.enc
2019-10-01_16:32:42 - Export/Encrypt finished.
The decryption script below will decrypt the encrypted SQL files using GPG.

Be sure to change line 41 to use a unique passphrase for your environment. This must also match the same passphrase in the encryption script.

/var/scripts/prod/mariadb-user-decrypt.sh

Code: Select all

#!/bin/bash
#############################################################
## Name : mariadb-user-decrypt.sh
## Version : 1.0
## Date : 2018-04-16
## Author : LHammonds
## Purpose : Decrypt the SQL files that were encrypted during backup.
## Compatibility : Verified on Ubuntu Server 16.04 thru 18.04 LTS
##               : Verified on MariaDB 10.1.32 thru 10.3.18
## Requirements : percona-toolkit (tested using version 3.0.6)
##              : gpgv (tested using version 2.2.4)
## Run Frequency : Manually.  When backups need to be decrypted.
## NOTE: Grant files contain the grant commands only.
##       Revoke files contain the revoke, drop and grant commands together.
## Parameters : None
## Exit Codes :
## 0  = Success
## 1  = ERROR: Lock file detected
## 2  = ERROR: Must be root user
## 4  = ERROR: percona-toolkit not installed
## 8  = ERROR: gpgv not installed
## 16 = ERROR: checksum mismatch
## 32 = ERROR: decryption failure
###################### CHANGE LOG ###########################
## DATE       VER WHO WHAT WAS CHANGED
## ---------- --- --- ---------------------------------------
## 2018-04-16 1.0 LTH Created script.
#############################################################

## Import standard variables and functions. ##
source /var/scripts/common/standard.conf

## Define local variables.
Title="${Company}-mariadb-user-decrypt"
BackupDir=${BackupDir}/mysql
LogFile="${LogDir}/${Title}.log"
LockFile="${TempDir}/${Title}.lock"
GrantFile="${Company}-user-grant.sql"
RevokeFile="${Company}-user-revoke.sql"
## Change this password to anything other than the default! ##
CryptPass="abc123"
CryptPassFile="${TempDir}/${Title}.gpg"
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
  if [ ${ErrorFlag} != 0 ]; then
    echo "`date +%Y-%m-%d_%H:%M:%S` - Decrypt aborted." | tee -a ${LogFile}
  fi
  if [ -f ${CryptPassFile} ]; then
    ## Remove temporary file
    rm ${CryptPassFile}
  fi
  exit ${ErrorFlag}
} ## f_cleanup

function f_compare()
{
  filename=$1
  if ! sha512sum --status --check ${filename}; then
    ## Comparison failed, log results, terminate program.
    echo "ERROR: Checksum mismatch: ${filename}" | tee -a ${LogFile}
    ErrorFlag=16
    f_cleanup
  fi
} ## f_compare

function f_decrypt()
{
  EncryptedFile=$1
  DecryptedFile=$2
  ## Create temporary password file
  touch ${CryptPassFile}
  chmod 0600 ${CryptPassFile}
  echo ${CryptPass} > ${CryptPassFile}
  if ! gpg --cipher-algo aes256 --output ${DecryptedFile} --passphrase-file ${CryptPassFile} --quiet --batch --yes --no-tty --decrypt ${EncryptedFile}; then
    ## Decryption failed, log results, send email, terminate program.
    echo "ERROR: Decryption failed: ${EncryptedFile}" | tee -a ${LogFile}
    ErrorFlag=32
    f_cleanup
  fi
  if [ -f ${CryptPassFile} ]; then
    ## Remove temporary file
    rm ${CryptPassFile}
  fi
} ## f_decrypt

#######################################
##       PREREQUISITE CHECKS         ##
#######################################

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 not actually running."
  ErrorFlag=1
  exit ${ErrorFlag}
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." | tee -a ${LogFile}
  ErrorFlag=2
  f_cleanup
fi

## Requirement Check: Software
command -v pt-show-grants > /dev/null 2>&1 && ReturnCode=0 || ReturnCode=1
if [ ${ReturnCode} = 1 ]; then
  ## Required program not installed.
  echo "ERROR: percona-toolkit not installed." | tee -a ${LogFile}
  ErrorFlag=4
  f_cleanup
fi
command -v gpg > /dev/null 2>&1 && ReturnCode=0 || ReturnCode=1
if [ ${ReturnCode} = 1 ]; then
  ## Required program not installed.
  ## NOTE: gpgv comes standard with Ubuntu Server 16.04 LTS
  echo "ERROR: gpgv not installed." | tee -a ${LogFile}
  ErrorFlag=8
  f_cleanup
fi

#######################################
##           MAIN PROGRAM            ##
#######################################

echo "`date +%Y-%m-%d_%H:%M:%S` - Decrypt started." | tee -a ${LogFile}

## Verify checksum results on encrypted files
echo "Verifying checksums on encrypted files..."
f_compare ${BackupDir}/${GrantFile}.enc.sha512
f_compare ${BackupDir}/${RevokeFile}.enc.sha512
echo "Encrypted checksums: OK."

## Decrypt files
echo "Decrypting files..."
f_decrypt ${BackupDir}/${GrantFile}.enc ${BackupDir}/${GrantFile}
f_decrypt ${BackupDir}/${RevokeFile}.enc ${BackupDir}/${RevokeFile}

## Verify checksum results on decrypted files
echo "Verifying checksums on decrypted files..."
f_compare ${BackupDir}/${GrantFile}.sha512
f_compare ${BackupDir}/${RevokeFile}.sha512
echo "Decrypted checksums: OK."

## Set file permissions.
chmod 0600 ${BackupDir}/${GrantFile}
chmod 0600 ${BackupDir}/${RevokeFile}

/bin/ls -l ${BackupDir}/${GrantFile} | tee -a ${LogFile}
/bin/ls -l ${BackupDir}/${RevokeFile} | tee -a ${LogFile}

echo "`date +%Y-%m-%d_%H:%M:%S` - Decrypt finished." | tee -a ${LogFile}
f_cleanup
Example output:

Code: Select all

2019-10-01_16:32:50 - Decrypt started.
Verifying checksums on encrypted files...
Encrypted checksums: OK.
Decrypting files...
Verifying checksums on decrypted files...
Decrypted checksums: OK.
-rw------- 1 root root 16418 Oct  1 16:32 /bak/mysql/abc-user-grant.sql
-rw------- 1 root root 35231 Oct  1 16:32 /bak/mysql/abc-user-revoke.sql
2019-10-01_16:32:51 - Decrypt finished.

Post Reply