Knowledgebase

Setup remote and secure connections for MySQL on CentOS 7  Print this Article

In this tutorial we will show you how to login to your  mysql database system remotely and securely using SSL, username and password.

Install MySQL

The latest stable MySQL version is 5.7, so that’s the version we will install and configure in this tutorial.

The package is the first thing you’ll need to add, and it is available in the MySQL repository. Execute the following command to get started:

# yum localinstall -y https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

This command will add the MySQL repository which can then be used to install the database system:

# yum install -y mysql-community-server

At the end of the installation process, start MySQL using systemd tools:

# systemctl start mysqld

Check MySQL status:

mysqld.service - MySQL Server
 Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
 Active: active (running) ...

MySQL listens on port 3306, as you can see by executing the netstat tool:

# netstat -plntu | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 8776/mysqld

MySQL configuration

At the initial start up of the MySQL server, a superuser account ( 'root'@'localhost ) is created with a default password set and stored in the error log file. Reveal this password by executing the following command:

# grep 'temporary password' /var/log/mysqld.log

The output being:

[Note] A temporary password is generated for root@localhost: en>_g6syXIXq

The first step is to change this root password.

Log in to the MySQL shell:

# mysql -u root -p

Enter the automatically generated password that was revealed with the previous command.

Next, change the password with the following query:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'RootStrongPassword1!';

Flush privileges and exit:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> EXIT;
Bye

Enable SSL for MySQL

By default, MySQL has its own SSL certificates, stored in /var/lib/mysql. For the purpose of this tutorial, these certificates are good enough.

Note: in production, always use more secure and “personal” certificates.

Check SSL from the MySQL shell.

# mysql -u root -p

mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.01 sec)

Check the SSL status:

mysql> STATUS;
--------------
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper

Connection id: 4
Current database: 
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 27 min 25 sec

Threads: 1 Questions: 12 Slow queries: 0 Opens: 113 Flush tables: 1 Open tables: 106 Queries per second avg: 0.007
--------------

As you can see, SSL is not in use. So, the next step is to enable it.

Enable SSL in MySQL configuration file

Edit the MySQL configuration file:

# $EDITOR /etc/my.cnf

In the [mysqld]section, paste the following content:

ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

Save, exit and restart MySQL service.

# systemctl restart mysqld

Check again the SSL status in MySQL shell.

# mysql -u root -p

mysql> STATUS;
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper

Connection id: 5
Current database: 
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 min 2 sec

Threads: 1 Questions: 6 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.096

Enable clients

SSL is still not in use at this point. This is because we need to force all our client connections through SSL. So, exit the MySQL shell and edit the my.cnf file again.

# $EDITOR /etc/my.cnf

There, at the end of the file, paste the following content:

[client]
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/client-cert.pem
ssl-key=/var/lib/mysql/client-key.pem

Save, exit and restart MySQL once again:

# systemctl restart mysqld

Check the MySQL status as explained above:

mysql> STATUS
--------------
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper

Connection id: 3
Current database: 
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 min 32 sec

Threads: 1 Questions: 6 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.065

SSL is now enabled and connections are secured through it.

Enable remote connections

Last step of the tutorial is to enable remote connections. As every sysadmin knows, it is fundamental to allow only verified clients.

Edit the MySQL configuration file:

# $EDITOR /etc/my.cnf

At the end of the [mysqld] section paste the following lines:

bind-address = *
require_secure_transport = ON

Save, exit and restart MySQL.

# systemctl restart mysqld

Create a new user for remote connections

At this point, SSL and remote connections are enabled. Next thing to do is to create a new MySQL user:

# mysql -u root -p

Create a new user:

mysql> CREATE USER 'YOUR-USER-NAME'@'%' IDENTIFIED BY 'STRONG-PASSWORD' REQUIRE X509;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR-USER-NAME'@'%' IDENTIFIED BY 'STRONG-PASSWORD' REQUIRE X509;
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

You are now ready to remotely access your mysql server securely.

Was this answer helpful?

Related Articles

Install PHP 7, NGINX & MySQL 5.6 on CentOS/RHEL 7.1 & 6.7
CentOS / RHEL 7 # yum install epel-release # rpm -Uvh...
Install Fail2Ban on CentOS 7
To install Fail2Ban on CentOS 7, we will have to install EPEL (Extra Packages for Enterprise...
Optimize Apache on CentOS 7
Introduction Apache is a powerful and capable open-source web server, designed to provide a...
Setup WordPress on CentOS 7/Fedora 2x with MySQL 5.6 and PHP5.6
In this article we will setup WordPress from scratch in just a few easy steps. I will use a...
Getting started with Ansible on CentOS 6
What is Ansible? Long story short, Ansible is a tool which helps server administrators...