How to install MySQL 8 on Debian 10

MySQL is an open-source relational database system that works on many Operating Systems, including Windows, Linux, macOS, and FreeBSD. It is probably the most popular OpenSource RDBMS and a central component of the LAMP and LEMP Stacks. Many applications depend on MySQL like WordPress, Joomla, TYPO3, phpBB, and many more.

Several new features have been added to the MySQL 8 including, JSON support, Transactional Data Dictionary, Persistent runtime configuration, Document Store, Optimizer Hints, SQL roles, CTEs, window functions, Invisible indexes, and many more.

What you will learn?

In this tutorial, you will learn some stuff related to the MySQL server, and below are details about:

• Install MySQL Server 8 on Debian Buster 10
• Set Up MySQL Root Password
• Secure MySQL Server Deployment (Change Bind Address, Port, etc.)
• Setup SSL and Enable Remote Connection for MySQL Server
• Install and Configure phpMyAdmin
• Basic MySQL Query for Creating User, Database, etc
• Set up automatic backup MySQL databases

Prerequisites

We will install the MySQL Server 8.04 on the latest Debian Buster 10 with 2GB of RAM and 4 CPUs for this guide. Also, we need the root privilege for installing packages and editing system configurations.

Step 1 - Add MySQL Repository

For this tutorial, we will install the MySQL Server 8 from the official MySQL repository. To do that, we need to add the official MySQL repository to our Debian Server.

Before adding the MySQL repository, install the 'GnuPG' package using the apt command below.

sudo apt install gnupg -y

Once all installation is completed, download the MySQL APT repository and add it to your system.

wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb

Leave the repository as default and select 'OK'.

And the official MySQL repository has been added.

Next, update all available repositories on the Debian system using the following command.

sudo apt update

And you will get the official MySQL repository on the list below.

As can be seen, the official MySQL repository has been added, and we're ready to install the MySQL Server 8.0 to the Debian Buster 10.

Step 2 - Install MySQL Server

To install the MySQL Server 8.0 to the Debian system, run the apt command below.

sudo apt install mysql-server

Now type the root password for your MySQL installation and choose 'OK'.

Repeat the password for your MySQL root user.

After that, you will be asked to configure the MySQL default authentication plugin. Select the 'Strong Password Encryption' as recommended and choose 'OK'.

And the MySQL Server 8.0 installation is finished.

Now check the MySQL service using the following command.

systemctl is-enabled mysql
systemctl status mysql

Below is the result you will get.

As a result, the MySQL service is up and running on the Debian Server. The service will be running automatically at every system boot.

Step 3 - Secure MySQL Deployment using mysql_secure_password

In this step, we will improve the MySQL server installation's security using the 'mysql_secure_installation' command-line tool.

The 'mysql_secure_installation' tool will help you improve your MySQL deployment from different aspects such as setup the password strength, disable remote access, and removing default anonymous user and test databases.

Now run the 'mysql_secure_installation' command on your shell.

mysql_secure_installation

At the beginning of the MySQL installation, you've already configured the root password for MySQL. Now type the password to continue to the process.

Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD

After that, type 'Y' to configure the 'VALIDATE PASSWORD' component.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of the password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

Now choose the password validation policy level for all users. Type '2' to set up the 'STRONG' password level policy.

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters, and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Using existing password for root.

Now 'mysql_secure_installation' command will check your current password strength. As can be seen, the strength of our current password is '50'. Now type 'Y' to change the current password with a new stronger password.

Estimated strength of the password: 50 
Change the password for root? ((Press y|Y for Yes, any other key for No) : Y

Now type your strong password and make sure to have the score 100, then type 'Y' to continue with the new password.

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided? (Press y|Y for Yes, any other key for No) : Y

As a result, the MySQL component 'VALIDATE PASSWORD' has been configured with a strong password policy, and the new MySQL password has been created.

Next, you will be asked to remove the default Anonymous user. Type 'Y' to remove the default anonymous user for your installation.

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without creating a user account. This is intended only for testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y

Next, type 'Y' again to disable MySQL remote login and if you want the MySQL server accessible only locally.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y

After that, remove the default database 'test' from your MySQL Server. Type 'Y' and press Enter to continue.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y

Next, type 'Y' again to reload the privilege tables. In effect, and new MySQL configuration changes have been applied.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

As a result, the Secure MySQL Server Deployment using te 'mysql_secure_installation' script has been completed. And at this stage, you've enabled the 'VALIDATE PASSWORD' component with the level '2' strong password policy, also the new strong root password for MySQL has been configured.

Step 4 - Secure MySQL Server Using Additional Configuration

For this tutorial, we will add additional configuration to improve the deployment of the MySQL Server. We will specify the MySQL Server's bind-address, change the default port, disable load data file, and set up a password expiration policy for all MySQL users.

Now go to the '/etc/mysql/conf.d/' directory and create a new configuration 'mysqld.cnf' using vim editor.

cd /etc/mysql/conf.d/
vim mysqld.cnf

Paste the following configuration into it.

[mysqld]

# MySQL running on 127.0.0.1 with port 8320
bind-address=127.0.0.1
port=8320

# Disable LOAD DATA LOCA
local-infile=0

# Password Expiration to 60 Days
default_password_lifetime=60

# MySQLX Plugin running on 127.0.0.1 with port 8321
mysqlx_bind_address=127.0.0.1
mysqlx_port=8321

Save and close.

Next, restart the MySQL service and check open ports on the system using the following command.

systemctl restart mysql
ss -plnt

Below is the result you will get.

As can be seen, the MySQL Server is up and running on the local IP address '127.0.0.1' on the non-default TCP port '8320'.

Next, to log in to the MySQL shell with the custom port, you can use the 'mysql' command with '-P port' option as below.

mysql -u root -P 8320 -p

Type the root password for the MySQL Server.

Once you've logged in, run the following query to check the MySQL Server's port configuration.

show variables like 'port';

And you will get the MySQL Server is running on the custom port '8320' as below.

As a result, you've configured the MySQL Server to be running on the local IP address '127.0.0.1' with the custom TCP port '8320'.

Step 5 - Secure and Enable MySQL Remote Connection using SSL

Suppose you want to deploy the MySQL Server on the internal network and allow other hosts on the same network to access databases on the MySQL Server. In that case, it's recommended to enable SSL for your MySQL Server deployment.

In this step, you will learn how to set up and enable SSL and remote access on the MySQL Server.

Generate SSL Certificates

By default, the MySQL Server 8.0 is generated SSL certificates automatically during the installation. All default SSL certificates are available at the '/var/lib/mysql' directory.

For this step, we will generate a new SSL certificate for the MySQL Server using the 'mysql_ssl_rsa_setup' command-line tool.

Now create a new SSL directory '/etc/mysql/certs' and change that directory's ownership to the user 'mysql'.

mkdir -p /etc/mysql/certs/
sudo chown -R mysql:mysql /etc/mysql/certs

Next, generate new SSL certificates for the MySQL Server using the following command. And make sure to change the '--suffix=STRING' with your Common Name.

sudo mysql_ssl_rsa_setup --suffix=HakasMySQLSrv --uid=mysql --datadir=/etc/mysql/certs/

Once it's completed, new SSL certificates for the MySQL Server has been generated at the '/etc/mysql/certs' directory.

Check your SSL certificates using the following command.

ls -lah /etc/mysql/certs/
openssl verify -CAfile /etc/mysql/certs/ca.pem /etc/mysql/certs/server-cert.pem /etc/mysql/certs/client-cert.pem

Below is the result you will get.

The new SSL certificates for MySQL Server has been generated to the '/etc/mysql/certs/' directory.

Enable SSL and Remote Access MySQL Server

To enable the SSL and remote access on the MySQL Server, you will need to change the 'bind-address' option with your internal network's IP address and add the SSL configuration for MySQL.

Now go to the '/etc/mysql/conf.d/' directory and edit the configuration that we've created on top 'mysqld.cnf' using vim editor.

cd /etc/mysql/conf.d/
vim mysqld.cnf

Change the 'bind-address' with your internal IP address as below.

bind-address=172.16.0.3

Next, add the SSL configuration for the MySQL Server below and change the SSL path with your own.

ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

Additionally, you can specify the TLS version of al your clients and force them to use only the secure SSL connection using the following configuration.

#require_secure_transport=ON
tls_version=TLSv1.1,TLSv1.2

Save and close.

Next, restart the MySQL service to apply the new configuration and check the MySQL server's IP address using the following command.

systemctl restart mysql
ss -plnt

And you will get the MySQL Server is up and running on the internal network IP address with SSL enabled on it.

Setup Client and Test SSL Connection

To connect to the MySQL Server from another host/client, you must create a new MySQL user, copy the client certificates, and add the MySQL configuration for the client.

Log in to the MySQL shell using the following command.

mysql -u root -P 8320 -p

Now create a new MySQL user for remote connection using the following query.

CREATE USER 'remoteuser'@'%' IDENTIFIED WITH mysql_native_password BY 'MyRemoteUserPasswordMySQL39==' REQUIRE SSL;
GRANT ALL PRIVILEGES ON *.* TO 'remoteuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Type 'exit' to log out from the MySQL shell.

Next, you need to copy the CA and client certificate and key to the client machine using the SCP command as below.

scp /etc/mysql/certs/ca.pem root@clientIP:/certificates/directory/
scp /etc/mysql/certs/client-cert.pem root@clientIP:/certificates/directory/
scp /etc/mysql/certs/client-key.pem root@clientIP:/certificates/directory/

After that, connect to your client machine and create a new configuration using the vim editor.

vim ~/.my.cnf

Paste the MySQL client configuration as below and change the client SSL certificate path with your own.

[client]
ssl-ca=/home/gpk/certs/ca.pem
ssl-cert=/home/gpk/certs/client-cert.pem
ssl-key=/home/gpk/certs/client-key.pem

Save and close.

To connect to the remote MySQL Server with a custom port, run the 'mysql' command below.

mysql -u remoteuser -h 172.16.0.3 -P 8320 -p

Once you've logged in, check your connection status using the following query.

STATUS
SELECT * FROM performance_schema.session_status
       WHERE VARIABLE_NAME IN ('Ssl_version','Ssl_cipher');

Below is the result you will get.

As can be seen, you've logged in with SSL secure connection TLSv1.2 to the MySQL Server.

As a result, the remote access with a secure SSL connection for MySQL Server has been enabled.

Step 6 - Install and Configure phpMyAdmin

In this step, you will learn how to install the latest phpMyAdmin with the Apache webserver. You will also learn how to connect to the MySQL Server on the remote host with a custom port and alose securely through SSL from the phpMyAdmin.

Before going any further, install some packages dependencies using the apt command below.

sudo apt install apache2 libapache2-mod-php php-imagick php-phpseclib php-php-gettext php-common php-mysql php-gd php-imap php-json php-curl php-zip php-xml php-mbstring php-bz2 php-intl php-gmp unzip