Sam Stelfox's Thoughts & Notes

Server

Installation

Install the package.

yum install mysql-server -y

Set to start up auto-magically and get it going now.

systemctl enable mysqld.service
systemctl start mysqld.service

Run the quick and dirty secure installation script that comes with the server. The questions are pretty straight-forward just set a secure password when it asks for it.

mysql_secure_installation

Configuration

Before applying the configuration file in /etc/my.cnf. Safely shutdown the server and delete the logfiles /var/lib/mysql/ib_logfile*. This configuration file will change the size of the log buffer and it will cause an error when starting the server backup that will look like:

InnoDB: Error: log file ./ib_logfile0 is of a different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!

There is still one major security feature that I haven't added to this configuration yet. Running the mysql daemon in a chroot environment. Details on setting up a chroot environment for mysql can be found, here.

Detailed information on all of the configuration options can be found here. There is a script that can assist in tuning larger production databases I've stored here.

There is some additional configuration in the systemd init script, specifically which user/group the service will fork too (default is mysql/mysql). If you need to change this you'll need to make the adjustment in /etc/systemd/system/multi-user.target.wants/mysqld.service.

Firewall

-A SERVICES -m tcp -p tcp --dport 3306 -j ACCEPT

Resetting the Root Password

Stop the MySQL daemon, and disable remote access to the server (block in firewall etc). Start up MySQL with privilege checking disabled (In this mode any credential will be accepted for any username and that user will have full root database privileges (thus blocking remote access).

mysqld_safe --skip-grant-tables

In a separate terminal window connect to the database:

mysql -u root

Refer to the section on changing a user's password at this point. When done be sure to kill the mysqld_safe instance, start up the daemon normally and restore access to the server.

Quick Self Signed SSL Cert w/ Client

This should be used for testing and development only, it requires a bit more than a simple SSL cert as MySQL requires a CA cert as well so we'll generate a self signed CA, a server certificate, and then a client certificate that can be used for user authentication.

First the CA:

openssl req -new -x509 -newkey rsa:4096 -keyout ca.key -nodes -days 365 \
  -out ca.crt

Then the server cert:

openssl req -newkey rsa:4096 -keyout server.key -nodes -days 365 \
  -out server.csr
openssl x509 -req -in server.csr -days 365 -CA ca.crt -CAkey ca.key \
  -set_serial 01 -out server.crt
rm server.csr

Then the client cert:

openssl req -newkey rsa:4096 -keyout client.key -nodes -days 365 \
  -out client.csr
openssl x509 -req -in client.csr -days 365 -CA ca.crt -CAkey ca.key \
  -set_serial 02 -out client.crt
rm client.csr

Client

Remotely Calculate the Size of a Database

Paste the following query into an interactive console session to collect the sizes of all databases the current user has access to:

SELECT table_schema "Database Name", SUM(data_length + index_length) / 1024
  / 1024 "Size in Mb" FROM information_schema.TABLES GROUP BY table_schema;

Drop Tables

Without being able to drop a database and create a new one it can be kind of frustrating to empty a database of all it's tables so I've written a quick script to drop all the tables from a specified database.

#!/bin/bash

DBUSER="$1"
DBPASS="$2"
DBNAME="$3"
DBHOST="$4"

if [ $# -ne 4 ]; then
  echo "Usage: $0 {username} {password} {database} {hostname}"
  echo "Drops all tables from a MySQL"
  exit 1
fi

TABLES=$(mysql -u $DBUSER -p$DBPASS -h$DBHOST $DBNAME -e 'show tables;' |
  awk '{ print $1}' | grep -v '^Tables' )

for t in $TABLES; do
  echo "Deleting $t table from $MDB database..."
  mysql -u $DBUSER -p$DBPASS -h$DBHOST $DBNAME -e "drop table $t;"
done

New Database and User

Note: For security reasons user creation should be performed over an encrypted channel. See the section on Using SSL.

CREATE DATABASE example;
GRANT ALL ON example.* TO 'some-example-user'@'%.home-network.net' IDENTIFIED
  BY 'mYsUperSt0ngpassWordD0ntcoPyM3!';
FLUSH PRIVILEGES;

Change User Password

Note: For security reasons this password update should be performed over an encrypted channel. See the section on Using SSL.

UPDATE mysql.user SET password=PASSWORD("my-new-super-secure-password") WHERE
  user='some-user-name';

Configuration

Quick and simple change I make to my client configuration to make the prompt a little more verbose. Create the following file in ~/.my.cnf:

[mysql]prompt=(\\u@\\h) [\\d]>\\_

This can also be set system wide in /etc/my.cnf for all users by appending the above (which I do in all my server configurations).

Using SSL

Pretty straight forward, one off can be done like the following:

mysql -u user -ppassword -h host --ssl-ca=./ca.crt database

Once connected you can verify the session is encrypted with the following query:

(root@127.0.0.1) [(none)]> show status like 'ssl_cipher';

If the second column returned is non-empty then your session is encrypted.

See Remote Version

(user@example-mysql) [(none)]> SELECT VERSION();

Useful Diagnostic / Testing / Maintenance Scripts

Percona Toolkit

TCMalloc

Due to high thread contention in some versions of MySQL a huge performance boost can be gained by using a third party malloc, TCMalloc has been proven to reduce thread contention in MySQL and provide as much as a 30% boost in all query timing.

MORE TODO