How to install and configure MySQL server on Ubuntu/CentOS

MySQL is a popular open-source software for creating and managing relational databases. Running your own MySQL server provides many benefits over storing data locally, such as stability, scalability, and data integrity through atomic transactions.

In this tutorial, I will be using a Ubuntu 20.04 container, with 8 GB storage and 512 MB memory. Feel free to adjust storage and memory to your anticipated needs, but 8 GB minimum is recommended.

Step 1: Install MySQL


First, ensure your packages are up to date.
apt-get update
apt-get upgrade

We will be using MariaDB, a community fork of Oracle’s MySQL.
apt-get install mariadb-server

On Red Hat systems, use:
yum install mariadb-server

After the necessary packages install, you can verify the installation was successful:
mysql --version

Step 2: Accessing MySQL


Next, set a password for the root account.
mysqladmin -u root password

This will then prompt you to type in your new password, and again to verify.

You can now log in to the root account with the newly set password. You may need to log in to mysql’s root user with sudo or root privileges.
mysql -u root -p

The prompt MariaDB [(none)]> means that no database has been selected. There are a few system databases that already exist, which you can list out using the SQL query:
SHOW databases;

To select a database to work within:
USE mysql;

From here, you can list all the tables within the mysql database, and run a simple query to display the current MySQL users in the user table.
SHOW tables;
SELECT host,user FROM user;

You can log in to MySQL with the root user, but best practice is to set up separate, less privileged users for each service that will be using MySQL.

Step 3: Users and permissions


To create a user account for local use:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Now you can grant permissions to your new user:
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Note that the above query gives full privileges to every table within every database. You won’t have to log in with sudo/root for this account; just using mysql -u newuser -p is sufficient.

For services such as WordPress, you will want to create a new database and new service user. If your installation is local to your application, you can use localhost as your host. Otherwise, use the IP address or hostname the service is connecting from.
CREATE DATABASE wordpress;
CREATE USER 'wordpress'@'192.168.5.211' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress'@'192.168.5.211';
FLUSH PRIVILEGES;

Make sure the password is unique, as this is hard-coded in configuration for many services.

If you have more than one WordPress instance on different servers, you can have multiple accounts with the same username, as long as the host is unique. You could have ‘wordpress’@’192.168.5.211’ and ‘wordpress’@’192.168.5.212’, and grant each user privileges to a different database.

Conclusion


Congratulations, you’re now ready to use MySQL to store and retrieve data in a robust and reliable database management system! For additional information and troubleshooting, check out the Appendix section below. I anticipate I’ll be building on this article with more use cases and solutions for any errors people encounter.

Appendix


Public accessibility

The default MySQL port is TCP 3306, so if your application is on a remote network, you will want to ensure TCP port 3306 is open on the server’s firewall and any upstream firewall, and the port is forwarded to your MySQL server if using a NAT’d public IP.

If you got this far, your server should already be listening on 3306, but you can use a tool like ss or netstat to check for 3306, as shown below (*:3306):
ss -plnt | grep 3306

And an obvious note: If your MySQL server is publicly accessible, make sure you are using strong, unique passwords for all service accounts, and use the principle of least privilege when granting access to databases and tables.

Troubleshooting

Issue: Can’t connect remotely
My server is listening on 3306, but I cannot make a MySQL connection, even from a machine on the same network.

First, run ss -plnt to examine what the server is listening on. Does it show *:3306, or 127.0.0.1:3306, like below?

If it shows 127.0.0.1:3306 and not *:3306, it is only listening on localhost 3306, which means MySQL is configured to bind to 3306 only on 127.0.0.1. To configure MySQL to bind to all addresses, check /etc/mysql/my.cnf and check if it contains the line: bind-address=127.0.0.1

If so, change this to bind-address=0.0.0.0, or remove the line.

If you’re having an issue not listed in this article, leave a comment below and I’ll answer as best as I can, and add to this Appendix as needed.

November 2021 update: Due to a WordPress migration I lost the images in this tutorial. I will try to find time to run through this process again to grab pictures. Sorry!

Leave a Reply

Your email address will not be published. Required fields are marked *