MySQL Production Setup

Introduction

This post covers installation of MySQL on Ubuntu for production usage. We will cover the following:

• Install MySQL
• Backup procedure
• Restore procedure

---

Installation

We start with installing MySQL via the Ubuntu package manager: sudo apt install mysql-server

After the installation is complete, we need to secure the MySQL installation by running sudo mysql_secure_installation. This will prompt you to set a root password and remove anonymous users, disallow root login remotely, and remove the test database. Do all of these steps to secure your MySQL installation.

Now we can log in to the MySQL shell using sudo mysql -u root -p. When you execute this it will prompt you for password if you've set one in the previous step — input it here, if not just hit enter.

Setup database and users

When you have logged in to MySQL you can create a database and a user that will be used to connect to it.

In our example we will set up 2 users — one read-write and one read-only. It's generally a good practice to have those 2 kinds of users in order to secure your application and other sources of connection.

CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'db_user_rw'@'%' IDENTIFIED BY 'StrongPassword123!';
CREATE USER 'db_user_ro'@'%' IDENTIFIED BY 'AnotherStrongPassword456!';
GRANT ALL PRIVILEGES ON db_name.* TO 'db_user_rw'@'%';
GRANT SELECT ON db_name.* TO 'db_user_ro'@'%';
FLUSH PRIVILEGES;

Let's break down what each of these commands does:

• CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; — Creates a new database named db_name with the character set utf8mb4 and collation utf8mb4_unicode_ci. This ensures that the database can store a wide range of characters, including emojis and other special characters.
• CREATE USER 'db_user_rw'@'%' IDENTIFIED BY 'StrongPassword123!'; — Creates a new user named db_user_rw that can connect from any host (indicated by the % wildcard) and sets the password to StrongPassword123!.
• CREATE USER 'db_user_ro'@'%' IDENTIFIED BY 'AnotherStrongPassword456!'; — Creates another user named db_user_ro with the same host access and a different password.
• GRANT ALL PRIVILEGES ON db_name.* TO 'db_user_rw'@'%'; — Grants all privileges on the db_name database to the db_user_rw user, allowing them to perform any action on the database.
• GRANT SELECT ON db_name.* TO 'db_user_ro'@'%'; — Grants only SELECT privileges on the db_name database to the db_user_ro user, allowing them to read data but not modify it.
• FLUSH PRIVILEGES; — Reloads the privilege tables in MySQL, ensuring that the changes take effect immediately.

After running these commands, you will have a new database and two users with different levels of access. You can now use these credentials to connect to the database from your application or other tools.

Note: When creating users with 'db_user_rw'@'%' and 'db_user_ro'@'%' you are allowing them to connect from any host. In a production environment, it's generally recommended to restrict access to specific IP addresses or ranges for better security. You can replace the % wildcard with the specific IP address or range that should have access to the database. For example, if you want to allow access only from the local machine, you can use 'db_user_rw'@'localhost' and 'db_user_ro'@'localhost'.

---

Backup Procedure

First let's install the AWS CLI, which we will use to upload our backups to S3. We will also install unzip since it's not included by default on Ubuntu:

sudo apt install unzip
curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install

After that we need to configure the AWS CLI with our credentials. You can do this by running aws configure and inputting your AWS Access Key ID, Secret Access Key, region, and output format. You can see how to create an IAM user and get the credentials in the AWS documentation.

Next, create a new S3 bucket where you will store your backups. You can do this via the AWS Management Console or using the AWS CLI:

aws s3 mb s3://your-backup-bucket-name

Backup script

Now we can create a backup script that will dump the MySQL database and upload it to S3. Create a new file named backup.sh and add the following content:

#!/bin/bash

DATE=$(date +%F-%H-%M)
BACKUP_DIR="/tmp"
DB_NAME="db_name"
DB_USER="db_user_rw"
DB_PASS="StrongPassword123!"
S3_BUCKET="s3://your-backup-bucket-name/mysql-backups/$DB_NAME"

FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql.gz"

# Dump + compress
mysqldump $DB_NAME | gzip > $FILE

# Upload to S3
aws s3 cp $FILE $S3_BUCKET/

# Remove local file
rm $FILE

Make sure to replace db_name, db_user_rw, StrongPassword123!, and your-backup-bucket-name with your actual database name, user, password, and S3 bucket name.

After creating the script, make it executable:

chmod +x backup.sh

You can now run the backup script manually with ./backup.sh to test it.

Automating with cron

You can add this script to your crontab so it runs every day at a given time. Run crontab -e and add the following:

0 2 * * * /path/to/file/backup.sh

This will run the script every day at 2 AM.

---

Restore Procedure

It's important to know how to restore your database if something goes wrong. First, go to your S3 bucket and find the latest backup file. Then run:

aws s3 cp s3://your-backup-bucket-name/mysql-backups/backup-file-name.gz .
gunzip < db_name-*.sql.gz | mysql db_name -u db_user_rw -p

---

S3 Lifecycle Rules

Over time the number of backups will grow, so it's good practice to have a cleanup process in place. For production backups, keeping them for up to 30 days is a reasonable policy.

To set this up, go to the AWS Console, navigate to your bucket, open the Management tab, and create a new lifecycle rule. Configure the rule to delete objects older than 30 days.

---

Wrapping Up

This should be enough to get you going with MySQL on your own server. There are many more tweaks and settings you can adjust to tune MySQL for your specific needs, but for basic production usage this will do.

The natural next step is to add monitoring to your MySQL installation so you can keep an eye on performance and catch issues early. Check out MySQL Monitoring with Grafana and Alloy for a step-by-step guide.

Thank you for reading, and let's connect!