MySQL server

Root password preseed

OpenSSL is required to generate strong passwords so install it if needed:

which openssl >/dev/null || apt-get install --no-install-recommends openssl

Generate a strong password for the root user and store it under "/root/.my.cnf" to avoid typing it manually:

MYSQLPW="$(openssl rand -base64 33)"

cat << EOF | debconf-set-selections
mysql-server        mysql-server/root_password_again        password $MYSQLPW
mysql-server        mysql-server/root_password      password $MYSQLPW
EOF

touch /root/.my.cnf && chmod 600 /root/.my.cnf
printf "[client]\n user\t= root\n password\t= %s\n" "$MYSQLPW" > /root/.my.cnf

Package installation

Install the MySQL server package as well as the convenient "mysqltuner" helper:

apt-get install --no-install-recommends mysql-server mysqltuner

MYSQL_CONFD="/etc/mysql/mysql.conf.d"
[ -d "$MYSQL_CONFD" ] || MYSQL_CONFD="/etc/mysql/conf.d"

Low RAM configuration

If your MySQL instance is lightly loaded, you can easily reduce the RAM it consumes with those configurations:

cat << EOF > "$MYSQL_CONFD/low-ram.cnf"
[mysqld]
max_connections = 8
key_buffer_size = 128K
query_cache_size = 1M
sort_buffer_size = 64K
EOF

To apply those newly added configurations MySQL needs to be restarted:

service mysql restart

Disable TCP/IP (optional)

If your MySQL client is located on the same machine as the MySQL server, you can get slightly better performance (and security) by disabling TCP/IP and relying on Unix socket communication only:

cat << EOF > "$MYSQL_CONFD/skip-networking.cnf"
[mysqld]
skip-networking
EOF

To apply this newly added configuration MySQL needs to be restarted:

service mysql restart

Daily DB dumps

Keeping local DB dumps ensures consistent file level backups. To perform a daily dump and rotate old dumps, download this cron job into "/etc/cron.daily/mysql-backup" and make it executable:

wget -O /etc/cron.daily/mysql-backup https://sdeziel.info/mysql/mysql-backup
chmod +x /etc/cron.daily/mysql-backup

You can test the script and confirm that backups are stored under "/var/backups/mysql". Each DB is stored individually and compressed to minimize storage usage.

/etc/cron.daily/mysql-backup

ls -lh /var/backups/mysql