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 [email protected]: 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: [email protected] 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: [email protected] 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: [email protected] 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.
