1. Before Attaching /local

1.1 Configuring App Armor

If your Linux distribution features App Armor, which restricts what directories programs can access, it may be necessary to add several directories to the App Armor configuration file for MySQL:

/etc/apparmor.d/usr.sbin.mysqld

We add the following -- the first two entries allow the mysql data directory to be stored under a '/local' directory, while the second two entries allow the retrieval of SSL certificates and keys from under a '/local' directory.

/local/data/mysql/   r,
/local/data/mysql/** rwk,
/local/data/mysql-replication/   r,
/local/data/mysql-replication/** rwk,
/local/settings/services/mysql/ssl/   r,
/local/settings/services/mysql/ssl/** r,

Important!!! You must then reload the apparmor profiles.

service apparmor restart

It goes without saying that if App Armor is not configured to allow access to directories that are used later in these instructions that the various services can show some very strange behaviour. Best to configure this first!

1.3 Configuring Apache

1.3.1 Installing Apache

If not already installed, Apache can be installed by executing the following line as root:

apt-get install apache2
apt-get install php5 libapache2-mod-php5 php5-cli php5-mysql

1.3.2 Setting Server name

Put the following text at the top of the 'apache2.conf' file.

ServerName localhost

1.3.3 Setting up Apache modules

Navigate to the /etc/apache2/mods-enabled directory and execute the following commands as root.

a2enmod expires
a2enmod headers
a2enmod rewiret
a2enmod ssl

1.3.4 Allow Apache access to '/local/served/'

In the '/etc/apache2/apache2.conf' file, fine the location of the following commented out text, and replace '/srv/', with '/local/served/'.

The following:

#{Directory /srv/}
#       Options Indexes FollowSymLinks
#       AllowOverride None
#       Require all granted
#{/Directory}

should become:

{Directory /local/served/}
       Options Indexes FollowSymLinks
       AllowOverride None
       Require all granted
{/Directory}

2. Attaching /local

2.1 Creating and formatting the partition

Next, the '/local' partition needs to be setup.

mke2fs /dev/xvdb1
e2label /dev/xvdb1 LOCAL

2.2 Adding entry to fstab file

Next the '/etc/fstab' needs to be edited so that the '/local' partition will be mounted when the system boots. The 'nofail' option prevents booting from stalling if the 'LOCAL' partition is not present.

LABEL=LOCAL    /local    ext2    nofail,defaults    0 0

mkdir /local
mount /local

3. After Attaching /local

3.1 Setting up MySQL (if it is needed)

3.1.1 Creating MySQL Data Dir

service mysql stop
mysql_install_db --datadir /local/data/mysql --basedir /usr --insecure

3.1.2 Configuring MySQL

You will now edit the '/etc/mysql.conf.d/mysqld.conf' file.

First, replace the data dir directive to point to '/local/data/mysql'.

#datadir        = /var/lib/mysql
datadir         = /local/data/mysql

Next, add an SQL mode line at the start of the [mysqld]

[mysqld]
sql_mode = "IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION"

Now restart MySQL and run mysql_secure_installation to set a root password and secure the install.

service mysql start
# mysql_secure_installation

If you intend to have clients connect to your database using SSL, cut and paste the following to the top of the config file.

[client]
ssl-ca      = /local/settings/services/mysql/ssl/{DNS fully qualified domain name}/ca-cert.pem
ssl-cert    = /local/settings/services/mysql/ssl/{DNS fully qualified domain name}/client-cert.pem
ssl-key     = /local/settings/services/mysql/ssl/{DNS fully qualified domain name}/client-key.pem

[mysqld]
ssl-ca      = /local/settings/services/mysql/ssl/{DNS fully qualified domain name}/ca-cert.pem
ssl-cert    = /local/settings/services/mysql/ssl/{DNS fully qualified domain name}/server-cert.pem
ssl-key     = /local/settings/services/mysql/ssl/{DNS fully qualified domain name}/server-key.pem

Then replace '{DNS fully qualified domain name}' with the fully qualified domain name. Note, you will need to use this domain name later when generating keys.

3.1.3 Setting up SSL

To enable SSL, you need to generate public key certificates and private keys for each host. Unlike web SSL, as the communication to be secured is only between hosts you control, there is no need for a third party Certificate Authority. The next section will walk you through generating a self-signed Certificate Authority (CA) certificate, as well as private keys and certificates for each host.

3.1.3.1 Generating Certificates and Private Keys for MySQL

This section will assume that the keys are generated within the following directory:

/local/settings/services/mysql/ssl

The following generates a 2048 bit RSA private key, which will be used to sign the certificates you generate for each host, as well as the certificate that stores the public key used to validate each certificate.

First create directories to store the ca-key, server certificate and private key, and client certificate and private key.

# mkdir -p /local/settings/services/mysql/ssl/{domain}/ca-key/
# chmod 700 /local/settings/services/mysql/ssl/{domain}/ca-key/

# openssl genrsa 2048 > ca-key/ca-key.pem
# openssl req -new -x509 -nodes -days 3600 -key ca-key/ca-key.pem -out ca-cert.pem

Now create a private key and certificate for the mysql "server" and the mysql "client": The following creates a private key and also a key signing request.

# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem

Make sure that when it asks for "Common Name" that you enter your fully qualified domain name.

The following strips the password from the key.

# openssl rsa -in server-key.pem -out server-key.pem

Finally, an X509 certificate is generated.

openssl x509 -req -days 3600 -CA ca-cert.pem -CAkey ca-key/ca-key.pem -set_serial 01 -in server-req.pem -out server-cert.pem

# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
# openssl rsa -in client-key.pem -out client-key.pem
# openssl x509 -req -days 3600 -CA ca-cert.pem -CAkey ca-key/ca-key.pem -set_serial 01 -in client-req.pem -out client-cert.pem

Once you have repeated the proceeding three commands, substituting 'server' for 'client', you should now be left with the following files:

The following may be used to verify the certificates:

# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

3.1.3.2 Restart

Now restart MySQL for the configuration changes to take place.

$ service mysql restart

Once logged into MySQL you should see:

mysql> SHOW STATUS
...
| Ssl_cipher      | DHE-RSA-AES256-SHA                                  |
| Ssl_cipher_list | DHE-RSA-AES256-SHA:AES256-SHA:DHE-RSA-AES128-SHA:...|
...

3.1.3.3 Enforcing SSL Usage

When accounts are created by granting privileges to users the 'require' keyword can be used to require that an SSL connection is used, as well as require that the user connecting has a certificate signed by the same CA as the server.

To require an ssl connection 'require ssl' is used:

mysql> GRANT SELECT ON *.* TO 'someone'@'somewhere' IDENTIFIED BY 'password' require ssl;

To require a valid certificate 'require x509' is used:

mysql> GRANT SELECT ON *.* TO 'someone'@'somewhere' IDENTIFIED BY 'password' require x509;

References
http://dev.mysql.com/doc/refman/5.0/en/creating-ssl-certs.html

3.1.4 Setting up replication

3.1.4.1 Enabling binary logging

MySQL replication is achived by the slave server asynchronously copying the binary log of the master into a relay log. Binary logging can be enabled by editing the my.cnf file. Note that the only difference between the configurations is the unique server ID that is given to each host and that the master is not read only while the slave is.

The directory for the replication logs will need to be created, owned by the mysql account, and only accessible by that account, i.e.:

# mkdir -p          /local/data/mysql-replication
# chown mysql:mysql /local/data/mysql-replication
# chmod 700         /local/data/mysql-replication

server_id         = 10
read_only         = 0
log_bin           = /local/data/mysql-replication/mysql-bin.log
relay_log         = /local/data/mysql-replication/mysql-relay.log
log_slave_updates = 1
sync_binlog       = 1

server_id         = 11
read_only         = 1
log_bin           = /local/data/mysql-replication/mysql-bin.log
relay_log         = /local/data/mysql-replication/mysql-relay.log
log_slave_updates = 1
sync_binlog       = 1

Restart each mysql server to apply the settings.

# service mysql restart

3.1.4.2 Setting up replication accounts

For both the master and slave add the following account substituting {IP Address} for the appropriate address and {Password} for an appropriate password. The 'REQUIRE x509' clause ensures that the connection is protected by SSL and that that a client certificate is also required -- this prevents somebody from spoofing the IP address of the slave and connecting even if they know the password.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'{IP Address}' IDENTIFIED BY '{Password}' REQUIRE x509;

If you want to change the account you should delete the account first using:

mysql> DROP USER repl@'{IP Address}';

3.1.4.3 Pointing the Slave at the Master

Finally, the slave needs to be pointed to the master and told to start replicating. Login to the slave mysql prompt and enter a varient of the below:

According to the MySQL documentation it shouldn't be necessary to specify the SSL lines, however, I can't get it working without them...

First, ensure the slave process is not running.

STOP SLAVE;

Then change the master information.

CHANGE MASTER TO MASTER_HOST         = '{IP Address}',
                 MASTER_USER         = 'repl',
				 MASTER_PASSWORD     = '{Password}',
				 MASTER_LOG_FILE     = 'mysql-bin.000001,
				 MASTER_LOG_POS      = 0,
				 MASTER_SSL          = 1,
				 MASTER_SSL_CA       = '/local/settings/services/mysql/ssl/ca-cert.pem',
				 MASTER_SSL_KEY      = '/local/settings/services/mysql/ssl/client-key.pem',
				 MASTER_SSL_CERT     = '/local/settings/services/mysql/ssl/client-cert.pem';

Finally, start the slave process again:

START SLAVE;

You can check the status of the slave by typing:

SHOW SLAVE STATUS;

3.2 Apache Setup

This section describes a common configuration that is used and how to setup SSL.

3.3 Local account setup

# cd /local/home
# cp -Rp /home/local .

Now edit the /etc/passwd file to point to the new user directory.

local:x:1000:1000:local,,,:/local/home/local:/bin/bash

It is best to logout and relogin so the changes take hold.

3.4 Dropbox Setup

Dropbox is a useful service for syncing data with a VM.

3.4.1 Installing Dropbox

Dropbox should be installed at /local/software/services/Dropbox.

$ cd /local/software/services
$ wget -O - "https://www.dropbox.com/download?plat=lnx.x86_64" | tar xzf -
$ mv .dropbox-dist Dropboxd

Now you can edit the /etc/rc.local file so that Dropbox will be automatically started for specific users on reboot.

su local -c /local/software/services/Dropbox/dropboxd &

However you should first run the daemon on the command line as it will give you a URL that use to link the Dropbox daemon with your dropbox account. As the user account:

$ /local/software/services/Dropbox/dropboxd &