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

[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 MariaDB database. MariaDB is a 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

• Ubuntu Server 20.04 LTS, 64-bit
• MariaDB 10.4.13
• Portable PuTTY 0.73
• VMware vSphere 6.0.0
• VirtualBox 6.0.20

Helpful links

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

• MariaDB Package Repository Setup and Usage
• Ubuntu Documentation
• Ubuntu Firewall Basics

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

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.

[LHammonds]

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.

[LHammonds]

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 --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
   sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://atl.mirrors.knownhost.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

sudo mysqladmin status

or (if you have net-tools installed)

Code: Select all

netstat -tap | grep mysql

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 /etc/my.cnf
sudo mv /etc/mysql/my.cnf /etc/mysql/my-cnf.original
sudo chown root:root /etc/my.cnf
sudo chmod 644 /etc/my.cnf

[LHammonds]

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

[LHammonds]

Firewall Rules

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

Code: Select all

sudo 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.33 proto tcp to any port 3306 comment 'MariaDB' 1>/dev/null 2>&1
ufw allow from 192.168.107.34 proto tcp to any port 3306 comment 'MariaDB' 1>/dev/null 2>&1
ufw allow from 192.168.107.35 proto tcp to any port 3306 comment 'MariaDB' 1>/dev/null 2>&1

Run the updated rules:

Code: Select all

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

[LHammonds]

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/mariadb/.
   sudo systemctl start mariadb

5. Verify the service is running by typing the following:

   Code: Select all

   systemctl status mariadb

NOTE: If you did not disable apparmor, it will prevent the database service from starting at this point until you update apparmor settings or disable it.

[LHammonds]

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

[LHammonds]

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 u_wordpress 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 'u_wordpress'@'%' 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 u_wordpress;
   DROP DATABASE wordpress;

NOTE #1: The 'u_wordpress'@'%' 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 'u_wordpress'@'192.168.107.26' IDENTIFIED BY 'mydbpass';
GRANT ALL PRIVILEGES ON wordpress.* TO 'u_wordpress'@'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 'u_wordpress'@'localhost' IDENTIFIED BY 'mydbpass';

Managing Users with Roles

When managing more than 1 user on a database, it would be better to manage permissions on roles and just assign roles to users to ensure all users have consistency in permissions.

Create some users:

Code: Select all

CREATE USER 'u_dba'@'localhost' IDENTIFIED BY 'dbapassword';
CREATE USER 'u_readonly'@'localhost' IDENTIFIED BY 'readonlypassword';
CREATE USER 'u_db1'@'192.168.1.22' IDENTIFIED BY 'db1UserPassword';
CREATE USER 'u_db2'@'192.168.1.23' IDENTIFIED BY 'db2UserPassword';

Create some roles:

Code: Select all

CREATE ROLE r_dba;
CREATE ROLE r_readonly;
CREATE ROLE r_db1;
CREATE ROLE r_db2;

Grant specific privileges to specific roles:

Code: Select all

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON *.* TO r_dba WITH GRANT OPTION;
GRANT SELECT ON *.* TO r_readonly;
GRANT SELECT,INSERT,UPDATE,DELETE ON db1.* TO r_db1;
GRANT SELECT,INSERT,UPDATE,DELETE ON db2.* TO r_db2;

Grant specific roles to specific users and set the role as their default:

Code: Select all

GRANT r_dba TO 'u_dba'@'localhost';
SET DEFAULT ROLE r_dba FOR 'u_dba'@'localhost';
GRANT r_readonly TO 'u_readonly'@'localhost';
SET DEFAULT ROLE r_readonly FOR 'u_readonly'@'localhost';
GRANT r_db1 TO 'u_db1'@'192.168.1.22';
SET DEFAULT ROLE r_db1 FOR 'u_db1'@'192.168.1.22';
GRANT r_db2 TO 'u_db2'@'192.168.1.23';
SET DEFAULT ROLE r_db2 FOR 'u_db2'@'192.168.1.23';

List all roles:

Code: Select all

SELECT user FROM mysql.user WHERE is_role='Y';

List all users including roles:

Code: Select all

SELECT user, host, is_role, default_role FROM mysql.user;

Find all users for a specific database:

Code: Select all

SELECT User FROM mysql.db WHERE db = 'DatabaseName';

Show grants assigned to the roles:

Code: Select all

SHOW GRANTS FOR r_dba;
SHOW GRANTS FOR r_readonly;
SHOW GRANTS FOR r_db1;
SHOW GRANTS FOR r_db2;

Show grants assigned to the users (should not be anything beyond the default USAGE):

Code: Select all

SHOW GRANTS FOR 'u_dba'@'localhost';
SHOW GRANTS FOR 'u_readonly'@'localhost';
SHOW GRANTS FOR 'u_db1'@'192.168.1.22';
SHOW GRANTS FOR 'u_db2'@'192.168.1.23';

Remove specific roles from specific users:

Code: Select all

REVOKE r_dba FROM 'u_dba'@'localhost';
REVOKE r_readonly FROM 'u_readonly'@'localhost';
REVOKE r_db1 FROM 'u_db1'@'192.168.1.22';
REVOKE r_db2 FROM 'u_db2'@'192.168.1.23';

Remove all privileges from specific roles:

Code: Select all

REVOKE ALL PRIVILEGES ON *.* FROM r_dba;
REVOKE ALL PRIVILEGES ON *.* FROM r_readonly;
REVOKE ALL PRIVILEGES ON db1.* FROM r_db1;
REVOKE ALL PRIVILEGES ON db2.* FROM r_db2;

Delete specific roles:

Code: Select all

DROP ROLE r_dba;
DROP ROLE r_readonly;
DROP ROLE r_db1;
DROP ROLE r_db2;

Delete specific users:

Code: Select all

DROP USER 'u_dba'@'localhost';
DROP USER 'u_readonly'@'localhost';
DROP USER 'u_db1'@'192.168.1.22';
DROP USER 'u_db2'@'192.168.1.23';

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

[LHammonds]

Database Backup Script

This script is designed to perform a full backup of all databases while the server is online. It actually does several tasks which you can tailor to your needs:

1. Extracts the system database, compresses it into a single archive and encrypts it.
2. Extracts all user databases into a single file, compresses it into a single archive and encrypts it.
3. Extracts all user databases into individual files, compresses it into separate archives and encrypts it.
4. Extracts all tables per database into a database-specific file, compresses it into a single archive and encrypts it.
5. Extracts all user grants, compresses it into a single archive and encrypts it.
6. Encrypted backups stored locally and a copy placed in a separate folder to be pulled by a remote/offsite backup server. This "pull" design prevents the offsite storage from being exposed if the server is ever compromised.

Backups are scheduled via the root crontab covered later in this article.

Create the password file that will hold the encryption phrase (be sure to set this to your own secret word):

Code: Select all

touch /etc/passenc
chown root:root /etc/passenc
chmod 600 /etc/passenc
echo 'CryptPass="abc123"' >> /etc/passenc

Create the backup script:

Code: Select all

touch /var/scripts/prod/db-backup.sh
chown root:root /var/scripts/prod/db-backup.sh
chmod 700 /var/scripts/prod/db-backup.sh
vi /var/scripts/prod/db-backup.sh

/var/scripts/prod/db-backup.sh (GitHub Download)

Code: Select all

#!/bin/bash
#############################################################
## Name          : db-backup.sh
## Version       : 1.7
## Date          : 2020-10-09
## Author        : LHammonds
## Purpose       : Complete encrypted backup of MariaDB databases.
## Compatibility : Verified on to work on:
##                  - Ubuntu Server 20.04 LTS
##                  - MariaDB 10.4.13 -> MariaDB 10.5.6
## 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 = lock file detected
##    2 = root access
##    4 = 7zip not installed
##    8 = sql export failure
##   16 = archive failure
##   32 = archive purge failure
##   64 = configuration error
######################## CHANGE LOG #########################
## DATE       VER WHO WHAT WAS CHANGED
## ---------- --- --- ---------------------------------------
## 2011-12-19 1.0 LTH Created script.
## 2012-01-09 1.1 LTH Bugfix - f_PurgeOldestArchive
## 2017-04-13 1.2 LTH Corrected variable casing.
## 2017-04-24 1.3 LTH All databases minus an exception list.
## 2017-09-01 1.4 LTH Handle folder creation upon 1st time run.
## 2017-12-27 1.5 LTH Added directory/file permission setting.
## 2020-06-25 1.6 LTH Added encryption, removed remote push.
## 2020-10-09 1.7 LTH Added support for MariaDB roles.
##                    This scripts is no longer compatible with MySQL.
#############################################################

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

## Change the password in this file to anything other than the default! ##
source /etc/passenc

Title="db-backup"
DBDir="/opt/mariadb"
TargetDir="${BackupDir}/db"
RemoteDir="${BackupDir}/remote"
WorkingDir="${TempDir}/db"
CryptPassFile="${TempDir}/${Title}.gpg"
Timestamp="`date +%Y-%m-%d_%H%M`"
LogFile="${LogDir}/${Company}-${Title}.log"
LockFile="${TempDir}/${Company}-${Title}.lock"
DatabasesToExclude="'information_schema','mysql','performance_schema','JunkUserDB1','JunkUserDB2'"
ErrorFlag=0

## Binaries, you can let the script find them or you can set the full path manually here. ##
TarCmd="$(which tar)"
ZipCmd="$(which 7za)"
MysqlCmd="$(which mysql)"
MysqldumpCmd="$(which mysqldump)"
SedCmd="$(which sed)"
AwkCmd="$(which awk)"

#######################################
##            FUNCTIONS              ##
#######################################

function f_cleanup()
{
  echo "`date +%Y-%m-%d_%H:%M:%S` - ${Title} 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] ${Title}" "${Title} completed with no errors."
  else
    f_sendmail "[Failure] ${Title}" "${Title} failed.  ErrorFlag = ${ErrorFlag}"
  fi
  if [ -f ${CryptPassFile} ]; then
    ## Remove temporary file
    rm ${CryptPassFile}
  fi
  if [ ! "$(ls -A ${WorkingDir})" ]; then
    rmdir ${WorkingDir}
  fi
} ## f_cleanup() ##

function f_emergencyexit()
{
  ## Purpose: Exit script as cleanly as possible.
  f_cleanup
  exit ${ErrorFlag}
} ## f_emergencyexit() ##

function f_encrypt()
{
  ArcFile=$1
  EncFile=$2
  ## Create temporary password file
  touch ${CryptPassFile}
  chmod 0600 ${CryptPassFile}
  echo ${CryptPass} > ${CryptPassFile}
  if ! gpg --cipher-algo aes256 --output /${EncFile} --passphrase-file ${CryptPassFile} --batch --yes --no-tty --symmetric ${ArcFile}; then
    ## Encryption failed, log results, send email, terminate program.
    rm ${CryptPassFile}
    echo "ERROR: Encryption failed. ${ArcFile}" | tee -a ${LogFile}
    ErrorFlag=16
    f_cleanup
  else
    ## Encryption succeeded, create checksum files and delete archive.
    sha512sum ${EncFile} > ${EncFile}.sha512
    ## Delete archive file and its checksum.
    rm ${ArcFile}
    rm ${CryptPassFile}
  fi
  ## Set expected permissions and move to destination.
  chmod 0600 ${EncFile}.sha512
  chmod 0600 ${EncFile}
  chown root:root ${EncFile}.sha512
  chown root:root ${EncFile}*
  echo "`date +%Y-%m-%d_%H:%M:%S` -- Created ${EncFile}" >> ${LogFile}
} ## f_encrypt() ##

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=99
    f_cleanup
  fi
  if [ -f ${CryptPassFile} ]; then
    ## Remove temporary file
    rm ${CryptPassFile}
  fi
} ## f_decrypt() ##

function f_archive_file()
{
  OrgFile=$1
  ArcFile=$2
  ## Set expected permissions.
  chmod 0600 ${OrgFile}
  chown root:root ${OrgFile}
  ## Create archive
  case "${ArchiveMethod}" in
  tar.7z)
    ${TarCmd} -cpf - ${OrgFile} | ${ZipCmd} a -si -mx=9 -w${TempDir} ${ArcFile} 1>/dev/null 2>&1
    ReturnValue=$?
    ## Restore using one of the following commands (do not uncomment, only for notation):
    ## 7za x -so -w/tmp /tmp/archive.tar.7z | tar -C / -xf -
    ## 7za x -so -w/tmp /tmp/archive.tar.7z | tar -C /tmp/restore --strip-components=1 -xf -
    ;;
  tgz)
    ${TarCmd} -cpzf ${ArcFile} ${OrgFile} 1>/dev/null 2>&1
    ReturnValue=$?
    ## Restore using one of the following commands (do not uncomment, only for notation):
    ## tar -C / -xzf /tmp/archive.tar.gz
    ## tar -C /tmp/restore --strip-components=1 -xzf /tmp/archive.tar.gz
    ;;
  *)
    ${TarCmd} -cpzf ${ArcFile} ${OrgFile} 1>/dev/null 2>&1
    ReturnValue=$?
    ;;
  esac
  if [ ${ReturnValue} -ne 0 ]; then
    ## tar command failed.  Send warning email.
    f_sendmail "${Title} Archive Creation Failure" "Archive failed with return value of ${ReturnValue}"
    ErrorFlag=8
  else
    ## tar succeeded.  Remove original file.
    if [ -f ${OrgFile} ]; then
      rm ${OrgFile}
    else
      echo "Missing expected file: ${OrgFile}" | tee -a ${LogFile}
    fi
    ## Set expected permissions.
    chmod 0600 ${ArcFile}
    chown root:root ${ArcFile}
  fi
} ## f_archive_file() ##

function f_archive_folder()
{
  FolderName=$1
  ArcFile=$2
  ## Set expected permissions.
  chmod 0600 ${FolderName}/*
  chown root:root ${FolderName}/*
  ## Create archive
  case "${ArchiveMethod}" in
  tar.7z)
    ${TarCmd} -cpf - ${FolderName}/* | ${ZipCmd} a -si -mx=9 -w${TempDir} ${ArcFile} 1>/dev/null 2>&1
    ReturnValue=$?
    ## Restore using one of the following commands (do not uncomment, only for notation):
    ## 7za x -so -w/tmp /tmp/archive.tar.7z | tar -C / -xf -
    ## 7za x -so -w/tmp /tmp/archive.tar.7z | tar -C /tmp/restore --strip-components=1 -xf -
    ;;
  tgz)
    ${TarCmd} -cpzf ${ArcFile} ${FolderName}/* 1>/dev/null 2>&1
    ReturnValue=$?
    ## Restore using one of the following commands (do not uncomment, only for notation):
    ## tar -C / -xzf /tmp/archive.tar.gz
    ## tar -C /tmp/restore --strip-components=1 -xzf /tmp/archive.tar.gz
    ;;
  *)
    ${TarCmd} -cpzf ${ArcFile} ${FolderName}/* 1>/dev/null 2>&1
    ReturnValue=$?
    ;;
  esac
  if [ ${ReturnValue} -ne 0 ]; then
    ## Archive command failed.  Send warning email.
    f_sendmail "${Title} Archive Creation Failure" "Archive failed with return value of ${ReturnValue}"
    ErrorFlag=16
  else
    ## Archive command succeeded.  Remove original folder.
    if [ -d ${FolderName} ]; then
      rm -rf ${FolderName}
    else
      echo "Missing expected folder: ${FolderName}" | tee -a ${LogFile}
    fi
    ## Set expected permissions.
    chmod 0600 ${ArcFile}
    chown root:root ${ArcFile}
  fi
} ## f_archive_folder() ##

function f_export_systemdb()
{
  ## Dump system database to single file, archive it, encrypt it, set permission, delete temp files
  ${MysqldumpCmd} --skip-lock-tables --databases mysql > ${WorkingDir}/db-system.sql
  f_archive_file ${WorkingDir}/db-system.sql ${WorkingDir}/${Timestamp}-db-system.${ArchiveMethod}
  f_encrypt ${WorkingDir}/${Timestamp}-db-system.${ArchiveMethod} ${WorkingDir}/${Timestamp}-db-system.${ArchiveMethod}.enc
} ## f_export_systemdb() ##

function f_export_users()
{
  ## Dump user accounts/grants to single file, archive it, encrypt it, set permission, delete temp files
  ## Create roles ##
  ${MysqlCmd} --skip-column-names --no-auto-rehash --silent --execute="SELECT User FROM mysql.user WHERE is_role = 'Y';" | ${SedCmd} 's/^/CREATE ROLE /;s/$/;/g;1s/^/## Create Roles ##\n/' > ${WorkingDir}/db-grants.sql
  ## Grant roles ##
  ${MysqlCmd} --skip-column-names --no-auto-rehash --silent --execute="SELECT CONCAT('SHOW GRANTS FOR ''',User,''';') FROM mysql.user WHERE is_role = 'Y'" | ${MysqlCmd} --skip-column-names --no-auto-rehash | ${SedCmd} 's/$/;/g;1s/^/## Grants for Roles ##\n/' >> ${WorkingDir}/db-grants.sql
  ## Create user ##
  ${MysqlCmd} --skip-column-names --no-auto-rehash --silent --execute="SELECT User,Host,Password FROM mysql.user WHERE is_role = 'N' AND User NOT IN ('mariadb.sys','root','mysql');" | ${SedCmd} 's/\t/`@`/;s/\t/` IDENTIFIED BY `/;s/^/CREATE USER `/;s/$/`;/;1s/^/## Create Users ##\n/' >> ${WorkingDir}/db-grants.sql
  ## User grants ##
  ${MysqlCmd} --skip-column-names --no-auto-rehash --silent --execute="SELECT CONCAT('SHOW GRANTS FOR ''',User,'''@''',Host,''';') FROM mysql.user WHERE User <> '' AND is_role = 'N' AND user NOT IN ('mysql','mariadb.sys','root');" | ${MysqlCmd} --skip-column-names --no-auto-rehash | ${SedCmd} 's/$/;/g;1s/^/## Grants for Users ##\n/' >> ${WorkingDir}/db-grants.sql
  ## Default roles ##
  ${MysqlCmd} --skip-column-names --no-auto-rehash --silent --execute="SELECT default_role,User,Host FROM mysql.user WHERE is_role = 'N' AND User NOT IN ('mariadb.sys','root','mysql') AND default_role <> '';" | ${SedCmd} 's/\t/ FOR `/;s/\t/`@`/;s/^/SET DEFAULT ROLE /;1s/^/## Set Default Roles ##\n/;s/$/`;/' >> ${WorkingDir}/db-grants.sql
  f_archive_file ${WorkingDir}/db-grants.sql ${WorkingDir}/${Timestamp}-db-grants.${ArchiveMethod}
  f_encrypt ${WorkingDir}/${Timestamp}-db-grants.${ArchiveMethod} ${WorkingDir}/${Timestamp}-db-grants.${ArchiveMethod}.enc
} ## f_export_users() ##

function f_export_alldb()
{
  ## Get list of all current databases except those in the exclusion list.
  SqlCmd="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN (${DatabasesToExclude})"
  DBDumpList=""
  for DB in `"${MysqlCmd}" --no-auto-rehash --skip-column-names --execute="${SqlCmd}"`
  do
    DBDumpList="${DBDumpList} ${DB}"
  done
  ## Dump all user databases to single file, archive it, encrypt it, set permission, delete temp files
  ${MysqldumpCmd} --skip-lock-tables --databases ${DBDumpList} > ${WorkingDir}/db-all.sql
  f_archive_file ${WorkingDir}/db-all.sql ${WorkingDir}/${Timestamp}-db-all.${ArchiveMethod}
  f_encrypt ${WorkingDir}/${Timestamp}-db-all.${ArchiveMethod} ${WorkingDir}/${Timestamp}-db-all.${ArchiveMethod}.enc
} ## f_export_alldb() ##

function f_export_userdb()
{
  ## Get list of all current databases except those in the exclusion list.
  SqlCmd="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN (${DatabasesToExclude})"
  DBDumpList=""
  for DB in `"${MysqlCmd}" --no-auto-rehash --skip-column-names --execute="${SqlCmd}"`
  do
    DBDumpList="${DBDumpList} ${DB}"
  done
  ## Dump user databases into separate files, archive it, encrypt it, set permission, delete temp files
  for SingleDB in ${DBDumpList}
  do
    ## Backup individual database.
    ${MysqldumpCmd} ${SingleDB} > ${WorkingDir}/${SingleDB}.sql
    f_archive_file ${WorkingDir}/${SingleDB}.sql ${WorkingDir}/${Timestamp}-db-${SingleDB}.${ArchiveMethod}
    f_encrypt ${WorkingDir}/${Timestamp}-db-${SingleDB}.${ArchiveMethod} ${WorkingDir}/${Timestamp}-db-${SingleDB}.${ArchiveMethod}.enc
  done
} ## f_export_userdb() ##

function f_export_tables()
{
  ## Get list of all current databases except those in the exclusion list.
  SqlCmd="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN (${DatabasesToExclude})"
  DBDumpList=""
  for DB in `"${MysqlCmd}" --no-auto-rehash --skip-column-names --execute="${SqlCmd}"`
  do
    DBDumpList="${DBDumpList} ${DB}"
  done
  ## Dump tables into separate database folders, archive it, encrypt it, set permission, delete temp files
  for SingleDB in ${DBDumpList}
  do
    ## Create database sub-folder.
    mkdir -p ${WorkingDir}/${SingleDB}
    ## Export each table in the database individually.
    for SingleTable in `echo "show tables" | ${MysqlCmd} ${SingleDB}|grep -v Tables_in_`;
    do
      DataFile=${WorkingDir}/${SingleDB}/${SingleTable}.sql
      case "${SingleTable}" in
        general_log)
          ${MysqldumpCmd} ${SingleDB} ${SingleTable} --skip-lock-tables > ${DataFile}
          ;;
        slow_log)
          ${MysqldumpCmd} ${SingleDB} ${SingleTable} --skip-lock-tables > ${DataFile}
          ;;
        *)
          ${MysqldumpCmd} ${SingleDB} ${SingleTable} > ${DataFile}
          ;;
      esac
    done
    if [ "$(ls -A ${WorkingDir}/${SingleDB})" ]; then
      f_archive_folder ${WorkingDir}/${SingleDB} ${WorkingDir}/${Timestamp}-tbl-${SingleDB}.${ArchiveMethod}
      f_encrypt ${WorkingDir}/${Timestamp}-tbl-${SingleDB}.${ArchiveMethod} ${WorkingDir}/${Timestamp}-tbl-${SingleDB}.${ArchiveMethod}.enc
    else
      echo "[INFO] Database has no tables: ${SingleDB}" >> ${LogFile}
      rmdir ${WorkingDir}/${SingleDB}
    fi
  done
} ## f_export_tables() ##

#######################################
##       PRE-REQUISITE CHECKS        ##
#######################################

if [ -f ${LockFile} ]; then
  ## Program lock file detected.  Abort script.
  f_sendmail "${Title} 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

## 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_emergencyexit
fi

## 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 'sudo apt install p7zip-full'" >> ${LogFile}
    ErrorFlag=4
    f_emergencyexit
  fi
fi

## If destination folder does not exist, create it. Mainly for 1st time use.
if [ ! -d ${RemoteDir} ]; then
  mkdir -p ${RemoteDir}
  chmod 0700 ${RemoteDir}
fi

## Ensure working directory does not contain old data.
if [ -d ${WorkingDir} ]; then
  rm -rf ${WorkingDir}
fi
mkdir -p ${WorkingDir}
chmod 0700 ${WorkingDir}

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

echo "`date +%Y-%m-%d_%H:%M:%S` - ${Title} started." >> ${LogFile}
StartTime="$(date +%s)"

## Document the current partition status:
echo "-- Partition status:" >> ${LogFile}
df -h >> ${LogFile}

## Document the database version:
echo "-- Database version:" >> ${LogFile}
${MysqlCmd} --version >> ${LogFile}

## Document the current uptime.
${MysqlCmd} -e status | grep -i uptime >> ${LogFile}

## Document the current size of the database folder.
echo "-- Space consumed in ${DBDir} = `du -sh ${DBDir} | ${AwkCmd} '{ print $1 }'`" >> ${LogFile}

## Export users, roles and grants.
## NOTE: Useful for local restore, migration or disaster recovery.
f_export_users
## Export the system database.
## NOTE: Useful for local restore or disaster recovery.
f_export_systemdb
## Export all user databases into a single file.
## NOTE: Useful for local restore, migration or disaster recovery.
f_export_alldb
## Export all user databases into individual files.
## NOTE: Useful for local restore, migration or disaster recovery.
f_export_userdb
## Export each table into individual files.
## NOTE: Useful for local restore.
f_export_tables

FreeSpace=`df --block-size=1k ${BackupDir} | grep ${BackupDir} | ${AwkCmd} '{ print $4 }'`
BackupSize=`du --summarize --block-size=1k ${WorkingDir} | ${AwkCmd} '{ print $1 }'`

echo "-- FreeSpace=${FreeSpace}k BackupSize=${BackupSize}k" >> ${LogFile}

## Make sure space is available in the remote folder to copy the file.
if [ "${FreeSpace}" -lt "${BackupSize}" ]; then
  ## Not enough free space available.  Send email and exit.
    echo "[ERROR] Freespace: Not enough space (${BackupSize}k) in ${BackupDir}" | tee -a ${LogFile}
    ErrorFlag=32
else
  ## Copy archives to remote folder to be pulled by remote server.
  echo "-- Duplicating archives to ${RemoteDir}" >> ${LogFile}
  cp --preserve=all ${WorkingDir}/*.enc ${RemoteDir}/.
  cp --preserve=all ${WorkingDir}/*.sha512 ${RemoteDir}/.
  ## Move archives to local storage.
  echo "-- Moving archives to ${TargetDir}" >> ${LogFile}
  mv ${WorkingDir}/*.enc ${TargetDir}/.
  mv ${WorkingDir}/*.sha512 ${TargetDir}/.
fi

## 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 "-- Total backup time: ${Hours} hour(s) ${Minutes} minute(s) ${Seconds} second(s)" >> ${LogFile}

echo "`date +%Y-%m-%d_%H:%M:%S` - ${Title} 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/abc-db-backup.log

Code: Select all

2020-10-11_23:00:01 - db-backup started.
-- Partition status:
Filesystem            Size  Used Avail Use% Mounted on
udev                  5.9G     0  5.9G   0% /dev
tmpfs                 1.2G  2.4M  1.2G   1% /run
/dev/mapper/LVG-root  6.9G  5.2G  1.4G  80% /
tmpfs                 5.9G  4.0K  5.9G   1% /dev/shm
tmpfs                 5.0M     0  5.0M   0% /run/lock
tmpfs                 5.9G     0  5.9G   0% /sys/fs/cgroup
/dev/mapper/LVG-bak   6.8G  5.3G  1.3G  82% /bak
/dev/sda1             922M  200M  660M  24% /boot
/dev/mapper/LVG-opt    24G   16G  6.9G  70% /opt
/dev/mapper/LVG-home  473M  392K  446M   1% /home
/dev/mapper/LVG-tmp   979M  2.3M  916M   1% /tmp
/dev/mapper/LVG-var   6.9G  4.9G  1.7G  75% /var
tmpfs                 1.2G     0  1.2G   0% /run/user/1004
tmpfs                 1.2G     0  1.2G   0% /run/user/1000
-- Database version:
/bin/mysql  Ver 15.1 Distrib 10.5.6-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Uptime:     3 days 16 hours 59 min 28 sec
-- Space consumed in /opt/mariadb = 345M
2020-10-11_23:00:02 -- Created /tmp/db/2020-10-11_2300-db-grants.tar.7z.enc
2020-10-11_23:00:03 -- Created /tmp/db/2020-10-11_2300-db-system.tar.7z.enc
2020-10-11_23:00:08 -- Created /tmp/db/2020-10-11_2300-db-all.tar.7z.enc
2020-10-11_23:00:09 -- Created /tmp/db/2020-10-11_2300-db-db1.tar.7z.enc
2020-10-11_23:00:10 -- Created /tmp/db/2020-10-11_2300-db-db2.tar.7z.enc
2020-10-11_23:00:10 -- Created /tmp/db/2020-10-11_2300-db-db3.tar.7z.enc
2020-10-11_23:00:11 -- Created /tmp/db/2020-10-11_2300-db-db4.tar.7z.enc
2020-10-11_23:00:11 -- Created /tmp/db/2020-10-11_2300-db-db5.tar.7z.enc
2020-10-11_23:00:15 -- Created /tmp/db/2020-10-11_2300-db-db6.tar.7z.enc
2020-10-11_23:00:18 -- Created /tmp/db/2020-10-11_2300-tbl-db1.tar.7z.enc
2020-10-11_23:00:20 -- Created /tmp/db/2020-10-11_2300-tbl-db2.tar.7z.enc
2020-10-11_23:00:20 -- Created /tmp/db/2020-10-11_2300-tbl-db3.tar.7z.enc
2020-10-11_23:00:21 -- Created /tmp/db/2020-10-11_2300-tbl-db4.tar.7z.enc
2020-10-11_23:00:22 -- Created /tmp/db/2020-10-11_2300-tbl-db5.tar.7z.enc
2020-10-11_23:00:26 -- Created /tmp/db/2020-10-11_2300-tbl-db6.tar.7z.enc
-- FreeSpace=1289592k BackupSize=4956k
-- Duplicating archives to /bak/remote
-- Moving archives to /bak/db
-- Total backup time: 0 hour(s) 0 minute(s) 27 second(s)
2020-10-11_23:00:28 - db-backup completed.
2020-10-11_23:00:28 - db-backup exit code: 0

Here is a sample of the files in the folder waiting to be picked up by an offsite storage server:

/bak/remote/

Code: Select all

-rw------- 1 root root   1515072 Oct 11 23:00 2020-10-11_2300-db-all.tar.7z.enc
-rw------- 1 root root       172 Oct 11 23:00 2020-10-11_2300-db-all.tar.7z.enc.sha512
-rw------- 1 root root      2613 Oct 11 23:00 2020-10-11_2300-db-db1.tar.7z.enc
-rw------- 1 root root       171 Oct 11 23:00 2020-10-11_2300-db-db1.tar.7z.enc.sha512
-rw------- 1 root root      4145 Oct 11 23:00 2020-10-11_2300-db-db2.tar.7z.enc
-rw------- 1 root root       174 Oct 11 23:00 2020-10-11_2300-db-db2.tar.7z.enc.sha512
-rw------- 1 root root     25221 Oct 11 23:00 2020-10-11_2300-db-db3.tar.7z.enc
-rw------- 1 root root       178 Oct 11 23:00 2020-10-11_2300-db-db3.tar.7z.enc.sha512
-rw------- 1 root root    113218 Oct 11 23:00 2020-10-11_2300-db-db3.tar.7z.enc
-rw------- 1 root root       181 Oct 11 23:00 2020-10-11_2300-db-db3.tar.7z.enc.sha512
-rw------- 1 root root    902372 Oct 11 23:00 2020-10-11_2300-db-db4.tar.7z.enc
-rw------- 1 root root       174 Oct 11 23:00 2020-10-11_2300-db-db4.tar.7z.enc.sha512
-rw------- 1 root root    124906 Oct 11 23:00 2020-10-11_2300-db-db5.tar.7z.enc
-rw------- 1 root root       172 Oct 11 23:00 2020-10-11_2300-db-db5.tar.7z.enc.sha512
-rw------- 1 root root    387897 Oct 11 23:00 2020-10-11_2300-db-db6.tar.7z.enc
-rw------- 1 root root       173 Oct 11 23:00 2020-10-11_2300-db-db6.tar.7z.enc.sha512
-rw------- 1 root root      1104 Oct 11 23:00 2020-10-11_2300-db-grants.tar.7z.enc
-rw------- 1 root root       175 Oct 11 23:00 2020-10-11_2300-db-grants.tar.7z.enc.sha512
-rw------- 1 root root    276465 Oct 11 23:00 2020-10-11_2300-db-system.tar.7z.enc
-rw------- 1 root root       175 Oct 11 23:00 2020-10-11_2300-db-system.tar.7z.enc.sha512
-rw------- 1 root root      2883 Oct 11 23:00 2020-10-11_2300-tbl-db1.tar.7z.enc
-rw------- 1 root root       172 Oct 11 23:00 2020-10-11_2300-tbl-db1.tar.7z.enc.sha512
-rw------- 1 root root      4447 Oct 11 23:00 2020-10-11_2300-tbl-db2.tar.7z.enc
-rw------- 1 root root       175 Oct 11 23:00 2020-10-11_2300-tbl-db2.tar.7z.enc.sha512
-rw------- 1 root root     25914 Oct 11 23:00 2020-10-11_2300-tbl-db3.tar.7z.enc
-rw------- 1 root root       179 Oct 11 23:00 2020-10-11_2300-tbl-db3.tar.7z.enc.sha512
-rw------- 1 root root    115120 Oct 11 23:00 2020-10-11_2300-tbl-db4.tar.7z.enc
-rw------- 1 root root       182 Oct 11 23:00 2020-10-11_2300-tbl-db4.tar.7z.enc.sha512
-rw------- 1 root root    904568 Oct 11 23:00 2020-10-11_2300-tbl-db5.tar.7z.enc
-rw------- 1 root root       175 Oct 11 23:00 2020-10-11_2300-tbl-db5.tar.7z.enc.sha512
-rw------- 1 root root    126762 Oct 11 23:00 2020-10-11_2300-tbl-db6.tar.7z.enc
-rw------- 1 root root       173 Oct 11 23:00 2020-10-11_2300-tbl-db6.tar.7z.enc.sha512

[LHammonds]

This script will let you pick an encrypted archive to be decrypted and extracted to the temp folder. Once extracted, you can manually restore the database(s)/table(s) however you see fit.

Create the script:

Code: Select all

touch /var/scripts/prod/db-decrypt.sh
chown root:root /var/scripts/prod/db-decrypt.sh
chmod 700 /var/scripts/prod/db-decrypt.sh
vi /var/scripts/prod/db-decrypt.sh

/var/scripts/prod/db-decrypt.sh (GitHub Download)

Code: Select all

#!/bin/bash
#############################################################
## Name          : db-decrypt.sh
## Version       : 1.2
## Date          : 2020-10-12
## Author        : LHammonds
## Purpose       : Decrypt and extract database archives.
## Compatibility : Verified on to work on:
##                  - Ubuntu Server 20.04 LTS
##                  - MariaDB 10.4.13
## Requirements  : p7zip-full (if ArchiveMethod=tar.7z), sendemail
## Run Frequency : As needed
## Input         : (Optional) filename prefix to reduce list of options.
## Exit Codes    : (if multiple errors, value is the addition of codes)
##    0 = success
##    1 = lock file detected
##    2 = root access
##    4 = 7zip not installed
##    8 = decrypt failure
######################## CHANGE LOG #########################
## DATE       VER WHO WHAT WAS CHANGED
## ---------- --- --- -----------------------
## 2020-06-25 1.0 LTH Created script.
## 2020-10-09 1.1 LTH Added command-line option for filename prefix.
## 2020-10-12 1.2 LTH Code cleanup.
#############################################################

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

## Change the password in this file to anything other than the default! ##
source /etc/passenc

Title="db-decrypt"
SourceDir="${BackupDir}/db"
CryptPassFile="${TempDir}/${Title}.gpg"
Timestamp="`date +%Y-%m-%d_%H%M`"
LogFile="${LogDir}/${Company}-${Title}.log"
LockFile="${TempDir}/${Company}-${Title}.lock"
ErrorFlag=0

## Binaries, you can let the script find them or you can set the full path manually here. ##
TarCmd="$(which tar)"
ZipCmd="$(which 7za)"
TeeCmd="$(which tee)"
GpgCmd="$(which gpg)"

#######################################
##            FUNCTIONS              ##
#######################################

function f_cleanup()
{
  echo "`date +%Y-%m-%d_%H:%M:%S` - ${Title} 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
  if [ -f ${CryptPassFile} ]; then
    ## Remove temporary file
    rm ${CryptPassFile}
  fi
} ## f_cleanup()

function f_emergencyexit()
{
  ## Purpose: Exit script as cleanly as possible.
  f_cleanup
  exit ${ErrorFlag}
} ## f_emergencyexit()

function f_decrypt()
{
  EncryptedFile=$1
  DecryptedFile=$2
  ## Create temporary password file
  touch ${CryptPassFile}
  chmod 0600 ${CryptPassFile}
  echo ${CryptPass} > ${CryptPassFile}
  if ! ${GpgCmd} --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}" | ${TeeCmd} -a ${LogFile}
    ErrorFlag=8
    f_cleanup
  fi
  if [ -f ${CryptPassFile} ]; then
    ## Remove temporary file
    rm ${CryptPassFile}
  fi
} ## f_decrypt

function f_extract()
{
  mkdir ${TempDir}/decrypt
  ArcFile=$1
  case "${ArchiveMethod}" in
  tar.7z)
    ${ZipCmd} x -so -w${TempDir} ${ArcFile} | ${TarCmd} -C ${TempDir}/decrypt --strip-components=2 -xf -
    ReturnValue=$?
    ;;
  tgz)
    ${TarCmd} -C ${TempDir}/decrypt --strip-components=2 -xzf ${ArcFile}
    ;;
  *)
    ${TarCmd} -C ${TempDir}/decrypt --strip-components=2 -xzf ${ArcFile}
    ReturnValue=$?
    ;;
  esac
  if [ ${ReturnValue} -ne 0 ]; then
    ## Extract command failed. Display warning.
    echo "${Title} - Archive extract failure. Return value of ${ReturnValue}"
  else
    ## Remove decrypted archive file and list extracted file(s).
    rm ${ArcFile}
    find ${TempDir}/decrypt -name "*.sql"
  fi
} ## f_extract()


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

if [ -f ${LockFile} ]; then
  ## Program lock file detected.  Abort script.
  f_sendmail "${Title} 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

## 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." | ${TeeCmd} -a ${LogFile}
  ErrorFlag=2
  f_emergencyexit
fi

## 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 'sudo apt install p7zip-full'" >> ${LogFile}
    ErrorFlag=4
    f_emergencyexit
  fi
fi
## If a parameter was passed, set it as the filename prefix.
if [ -z "${1}" ]; then
  Prefix=""
else
  Prefix=${1}
fi

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

echo "`date +%Y-%m-%d_%H:%M:%S` - ${Title} started." >> ${LogFile}

echo "The following `*.enc` archives were found; select one:"
# set the prompt used by select, replacing "#?"
PS3="Use number to select a file or 'stop' to cancel: "
# allow the user to choose a file
cd ${SourceDir}
select EncFile in ${Prefix}*.enc
do
  if [[ "${REPLY}" == "stop" ]]; then
    ## User requested to terminate script.
    break;
  fi
  if [[ "${EncFile}" == "" ]]; then
    ## User made invalid selection.
    echo "'${REPLY}' is not a valid number"
    continue
  fi
  ## User selected a file.
  echo "${EncFile} selected" | ${TeeCmd} -a ${LogFile}
  # ArcFile= EncFile without .enc extension.
  ArcFile=$(echo "${EncFile%.*}")
  f_decrypt ${EncFile} ${ArcFile}
  f_extract ${ArcFile}
  ## Email the result to the administrator.
  if [ ${ErrorFlag} -eq 0 ]; then
    f_sendmail "[Success] ${Title}" "${Title} completed with no errors.\n\n${EncFile}"
  else
    f_sendmail "[Failure] ${Title}" "${Title} failed.  ErrorFlag = ${ErrorFlag}\n\nAttempted to decrypt ${EncFile}"
  fi
  break
done

echo "`date +%Y-%m-%d_%H:%M:%S` - ${Title} 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/abc-db-decrypt.log

Code: Select all

2020-10-09_09:56:24 - db-decrypt started.
2020-10-09_0945-db-all.tar.7z.enc selected
2020-10-09_09:56:33 - db-decrypt completed.
2020-10-09_09:56:33 - db-decrypt exit code: 0

[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 share folder, 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 an app server configured to make use of the script, take a look at the MediaWiki thread.

If a file such as /srv/nfs/inbound/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 (GitHub Download)

Code: Select all

#!/bin/bash
#############################################
## Name          : db-ondemand-backup.sh
## Version       : 1.3
## Date          : 2020-05-27
## Author        : LHammonds
## Purpose       : Backup of a single database
## Compatibility : Verified on Ubuntu Server 10.04 - 20.04 LTS
##                 MySQL 5.1.62 - 5.5.22
##                 MariaDB 10.3.8 - 10.4.13
## 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
################ 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.
## 2020-05-27 LTH Now expects offsite copy to be pulled for security reasons.
#############################################

## 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}/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` - Database 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] Database 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}/intranet" ]; then
  DatabaseName="intranet"
  rm "${ShareDir}/intranet"
elif [ -f "${ShareDir}/nextcloud" ]; then
  DatabaseName="nextcloud"
  rm "${ShareDir}/nextcloud"
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`_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] Database Backup - tar" "tar failed with return value of ${ReturnValue}"
  ErrorFlag=$((${ErrorFlag} + 2))
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 the offsite storage pick up location.
echo "`date +%Y-%m-%d_%H:%M:%S` --- Copying archive file to offsite pick up 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] Database Backup - Offsite Copy" "Offsite 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

## 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" "Database backup completed with no errors."
else
  f_sendmail "[Failure] Database Backup" "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

[LHammonds]

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

NOTE: Database accounts are restored as well because you are also restoring the "mysql" database which is included in the "--all-databases" option. If going from one version of the database another or a different database engine, you might want to avoid importing the "mysql" system database and just import individual databases and export the accounts from "mysql" database (see next section for details).

Here are the steps to import all 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 SCP, 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 which are located in the "mysql" database. 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

[LHammonds]

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 (GitHub Download)

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
 
# Crontab SYNTAX:
#       __________ Minute (0-59)
#      / _________ Hour (0-23)
#     / /  _______ Day Of Month (1-31)
#    / /  /   ____ MONth (1-12)
#   / /  /   /   _ Day Of Week (0-7) (Sun = 0 or 7)
#  / /  /   /   /  -------------------------------------------------------------
# m h dom mon dow  command <arguments> > /dev/null 2>&1
 
#
# 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 checks for available space
#
0 1 * * * /var/scripts/prod/check-storage.sh root 500 100 > /dev/null 2>&1
15 1 * * * /var/scripts/prod/check-storage.sh home 100 50 > /dev/null 2>&1
30 1 * * * /var/scripts/prod/check-storage.sh tmp 100 50 > /dev/null 2>&1
45 1 * * * /var/scripts/prod/check-storage.sh var 100 50 > /dev/null 2>&1
00 2 * * * /var/scripts/prod/check-storage.sh opt 100 50 > /dev/null 2>&1
15 2 * * * /var/scripts/prod/check-storage.sh bak 100 50 > /dev/null 2>&1
#
# Daily backup and software upgrade checks
#
0 1 1 * * /var/scripts/prod/backparts.sh > /dev/null 2>&1
0 6 * * * /var/scripts/prod/apt-upgrade.sh > /dev/null 2>&1
30 6 * * * /var/scripts/prod/reboot-check.sh > /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/2020-05-19-crontab.root
sudo vi /var/scripts/data/crontab.root (make your changes)
sudo crontab -u root /var/scripts/data/crontab.root

[LHammonds]

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

[LHammonds]

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 which means only local access, then use the specific hostname/IP after that to allow specific machines to access the database (such as web server). 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';

[LHammonds]

NOTE: I have not tested most of these since I tend to use the command-line. However, Adminer is enjoying frequent updates and is a single php file.

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.

[LHammonds]

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 (GitHub Download)

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 20.04 LTS
##               : Verified on MariaDB 10.1.32 thru 10.4.13
## Requirements : percona-toolkit (tested using version 3.0.6 and 3.1)
##              : gpgv (tested using version 2.2.4 and 2.2.19)
## 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}/db
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 (GitHub Download)

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 20.04 LTS
##               : Verified on MariaDB 10.1.32 thru 10.4.13
## Requirements : percona-toolkit (tested using version 3.0.6 and 3.1)
##              : gpgv (tested using version 2.2.4 and 2.2.19)
## 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}/db
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/db/abc-user-grant.sql
-rw------- 1 root root 35231 Oct  1 16:32 /bak/db/abc-user-revoke.sql
2019-10-01_16:32:51 - Decrypt finished.