MySQL: Deployment

MySQL is used over a large number of applications. And, it is out there for quite a while. The ecosystem is quite mature. Although, every once in a while developers like us stumble on trivial issues, which, in turn, becomes time consuming to resolve. And, this often happens when the application needs to be deployed, maybe just during the deployment on a staging server.

This happens often because applications get complicated while they gain maturity. And, deployment sometimes turn into rituals, because of the obvious difference between the context of deployment and development. While we encounter development phase on a regular basis, the deployment phase is encountered occasionally, and most of the times we just leave it to the automation tools (e.g. CICD) once configured. And, this is why, an infra. team is often required, even for a seemingly trivial application, cause otherwise we'll have to spend some valuable time over investigating the potential deployment options. Considering deployment, just the concept of scalability can make things even more complicated than it already is.

Since, RDBMS like MySQL has a bad reputation when it comes to scalability, not to mention it's considerably inconvenient to hook it up to the scalability and relevant automation aiding tools.

In this article, we'll go through some basic but crucial deployment phases of MySQL.

This article considers a systemd based Debian host system, and uses MySQL server 5.7 for demonstration. All the procedures has been tested and verified under Ubuntu Server 18.04.

Basic Installation

The basic MySQL installation can be performed by following the steps below.

First, update the repository cache on the target machine.

apt update

Now, we need to install the server.

apt install mysql-server-5.7

Once the server is installed successfully, we are going to run mysql_secure_installation in order to make the installation secure. This tool will prompt for the configuration update of some security related parameters. It is recommended that you continue with the defaults. The following steps are recommended.

• select password type hard
• generate a strong password using pwgen -sy 16 1 (installable through apt install pwgen)
• remove anonymous user
• remove root login remotely
• remove test database and access to it
• reload changes

MySQL has several authentication modes, and supports a number of authentication plugins. The most significant ones that we are going to encounter, includes,

• auth_socket: A UNIX specific authentication plugin, which enables connection to the server through a UNIX socket.
• sha256_password: This authentication method implements SHA-256 hashing for the account passwords, and by nature of it's cryptography, during the time of writing of this post, it's a strong authentication method.
• mysql_native_password: MySQL native password is the native format, as the name suggests. It's a relatively weak authentication method.

It is important to note that on some host systems (including the one we are using), the default authentication is set to auth_socket for the root user account.

We need to make another change to our configuration, if our database server expects remote client (which is almost always the case). By default, MySQL server only listens to localhost (the loopback address 127.0.0.1, to be specific.). In order to enable remote clients, edit the MySQL server configuration file (the primary configuration file is usually located in /etc/mysql/mysql.conf.d/mysql.cnf on Debian systems, but you may choose any of the existing configuration files or even create your own), and update the bind-address parameter under the [mysqld] section, by setting it to the external IP address of the host machine.

And, finally, reload the changes by restarting the service as follows.

systemd restart mysql.service

Just don't forget to keep your MySQL port (3306, by default) open on the server firewall, if there's any.

Basic Client Communication

We finished the basics of server installation phase. And, now we are ready to connect our client. Since, auth_socket is configured as the root authentication, by default, we simply cannot connect using,

mysql -uroot -p

A successful connection will require invoking mysql from the root prompt to authenticate through Unix socket,

mysql

And, now we're in the MySQL root shell! Yey!