MySQL is an open source SQL database management system developed by Oracle Corporation.
SQL stands for Structured Query Language, which is a standardized language used to access databases. The current version of the language follows the SQL:2003 standard.
MySQL is a relational database management system (RDBMS). This means that the database stores data in separate tables, with structures organized into physical files optimized for speed. Users set up rules governing the relationships between different data fields, using SQL.
In this tutorial we will talk about how to install MySQL and configure a secure remote connection in a CentOS 7 environment.
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.rpmThis 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 'gmolica'@'%' IDENTIFIED BY 'Unixmen1!' REQUIRE X509;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'gmolica'@'%' IDENTIFIED BY 'Unixmen1!' REQUIRE X509;
mysql> FLUSH PRIVILEGES;
mysql> EXIT;
Conclusion
With the previous step we concluded the MySQL configuration. Now, it is possible to log in to the database system remotely, using the credentials created. Of course, the client must have copies of the certificates in order to connect through SSL.
