Over a million developers have joined DZone.

11 Steps to Secure Your Servers Part 11: Database Server Specific Security

In the 11th and final part of this series, here's how to set up your security (including encryption) for your database servers and their backups.

· Performance Zone

Download Forrester’s “Vendor Landscape, Application Performance Management” report that examines the evolving role of APM as a key driver of customer satisfaction and business success, brought to you in partnership with BMC.

This is part 11 of a series of posts on server security from Inversoft's 2016 Guide to User Data Security.

This section will cover security measures that are specific to the Database Server using MySQL. You can translate these instructions for nearly any database solution you use.


First, install MySQL. To install MySQL on the Database Server, execute this command in the root terminal window:

$ apt-get install mysql-server

During this installation process, you will be prompted to create a password for the MySQL root user. Make sure that you select a very strong password and store it somewhere safe.

By default, MySQL only listens on the loopback interface. Change this configuration so that it will listen on the private IP address you created for the Application Server above. Open the file /etc/mysql/mysql.conf.d/mysqld.cnf (NOTE: This is a Ubuntu/Debian specific location, and your server might have this file in another directory with a different name) and find and edit the listen configuration setting to this value:

bind-address =

This is the private IP address from our example above. You will need to change this to your private IP address. This will ensure that only servers on the same LAN will be able to connect to the database.

Lastly, verify that the MySQL data directory is properly secured and the MySQL user cannot be logged into.

This is the default on nearly all Linux distributions, but let's double check. In the MySQL configuration file, you can determine where the data directory is by finding the configuration property datadir. On most Debian distributions, the data directory is located at /var/lib/mysql. Do a long directory listing in /var/lib to see if the directory is secure. Here's the command and what the output should look like:

$ ls -alu /var/lib
drwx------  5 mysql mysql 4096 Apr 28 19:32 mysql
drwx------  2 mysql mysql 4096 Apr 28 19:33 mysql-files
drwx------  2 mysql mysql 4096 Apr 28 19:33 mysql-keyring

Your directories should look similar. They should all be owned by the user and group named mysql. The permissions should be read, write, execute for the user only (that's what "drwx------" means). If this is not how your directories are set, you can change the ownership and permissions for these directories by executing these commands:

$ chown -R mysql:mysql /var/lib/mysql*
$ chmod -R go-rwx /var/lib/mysql*

Also, open the file /etc/password and ensure that the line that starts with "mysql" ends with "/bin/false". It should look something like this:

mysql:x:110:117:MySQL Server,,,:/nonexistent:/bin/false

If the MySQL user's line doesn't end in "/bin/false", you can edit this file manually and change it using nano or vi.


You'll want to make backups of your Database Server and store them offsite in case of a server failure. One issue that arises with backups is that they are often not secured. The server they are stored on might have been built by a hosting provider or some other third party. Therefore, the Backup Server might not have the level of security you require. You should encrypt database backups before transferring them offsite. This reduces the likelihood that a Backup Server breach will allow the hacker to gain access to your data.

First, set up encryption keys. To properly encrypt backups from a script, you need a public/private key pair. As the root user on your Database Server, change to the root user's home directory and then execute these commands:

$ openssl genrsa -out private-key.pem 2048
$ openssl rsa -in private-key.pem -out public-key.pem -outform PEM -pubout

Now that you have the key pair, leave the public-key.pem on the Database Server and move the private-key.pem file to the Backup Server (or any other secure location). Now you are ready to back up the database and encrypt the backup file.

First, get the backups running on your Database Server. The simplest way to run a backup is using the mysqldump command via cron. To start, you need to create a shell script in a protected directory. For this example, we are going to use the root user's home directory. From the root user terminal, type these commands:

$ cd
$ mkdir bin
$ touch bin/backup.sh
$ chmod +x bin/backup.sh
$ nano bin/backup.sh

In this file, you will need to dump the contents of your database to a file. The command to do this is mysqldump. Here's an example script that creates a backup of a database called production to the /tmp/backups directory:


DATE=`date +%Y%m%d`

# Make the directory just in case it doesn't exist
mkdir ${BACKUP_DIR}

# Delete the oldest files by only listing out everything older than the newest 7 files
ls *.gz.enc | sort | tail -n +7 | xargs rm
ls *.gz.passphrase.enc | sort | tail -n +7 | xargs rm

# Backup the MySQL databases
mysqldump -u<username> -p<password> production > production.sql

# Tar GZ everything (modify this line to include more files and directories in the backup)
tar -pczf ${BACKUP_FILE} *.sql

# Generate a random passphrase
openssl rand 32 -out ${BACKUP_PASSPHRASE_FILE}

# Encrypt the backup tar.gz
openssl enc -aes-256-cbc -pass file:${BACKUP_PASSPHRASE_FILE} < ${BACKUP_FILE} > ${BACKUP_FILE_ENCRYPTED}

# Encrypt the passphrase
openssl rsautl -encrypt -pubin -inkey ~/public-key.pem < ${BACKUP_PASSPHRASE_FILE} > ${BACKUP_PASSPHRASE_FILE_ENCRYPTED}

# Clean up

# Copy offsite
scp ${BACKUP_FILE_ENCRYPTED} ${BACKUP_PASSPHRASE_FILE_ENCRYPTED} <username>@<backup-server>:backups/.

You might want to backup additional files and directories in this script as well. The last line is used to copy the file offsite to a Backup Server. If you choose to store your backups offsite, you will need to create a Backup Server and secure it using a similar process as above. The setup of the Backup Server is out of scope for this guide, but the main differences in the process above are as follows:

  • The Backup Server will not use two-factor authentication.
  • The Backup Server will only allow SSH access from the Database Server and all other ports will be locked down.
  • The Database Server will be setup with an SSH public/private key pair specifically for the Backup Server. This key pair will be stored in the root user's ~/.ssh folder on the Database Server and the public key will be stored on the Backup Server in a special user account just for backups.
  • The SSH key cannot have a passphrase since that would prevent the backup script from running automatically.

The last step to set up your backups is to enable them via cron. To accomplish this, type this command in as the root user on the Database Server:

$ crontab -e

You can select the editor you want to use and then add this line to the end of the crontab file:

1 0 * * * /root/bin/backup.sh

Save this file. Your backups will run each night at 12:01am.

If you ever need to use a backup, you will need to decrypt it first. To decrypt a backup, you will need the private key you created above and the two encrypted files that the backup script created (the encrypted tar.gz and the encrypted passphrase file). If you are in a directory that contains all of these files, execute these commands to decrypt the backup (you'll need to change the file names below to match your file names):

$ openssl rsautl -decrypt -inkey private-key.pem < 20160502.passphrase.enc > 20160502.passphrase
$ openssl enc -aes-256-cbc -d -pass file:20160502.passphrase < 20160502.tar.gz.enc > 20160502.tar.gz

Find our Github project here: https://github.com/inversoft/2016-security-scripts. This project contains a set of scripts you can execute from your local computer to secure a remote server. 

See Forrester’s Report, “Vendor Landscape, Application Performance Management” to identify the right vendor to help IT deliver better service at a lower cost, brought to you in partnership with BMC.

database,database security,database server,server,security best practices,security,secure code,mysql,backup solutions,backups

Published at DZone with permission of Kelly Strain. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}