Introduction
MySQL is an open-source relational database management system and part of the popular LAMP stack. The mysql_secure_installation script allows you to significantly improve MySQL server security.
In this guide, you will learn to secure a MySQL server.
Note: MariaDB is a popular fork of MySQL. The information provided in this article applies to MariaDB as well.
Prerequisites
- A Linux system with MySQL installed (Using a different OS? See how to install and configure MySQL on a Windows server, CentOS 7, or CentOS 8).
- Access to the terminal (Ctrl+Alt+T).
- A network connection.
- An account with administrator privileges.
Note: Deploy a MySQL database instance on one of our pre-configured Bare Metal Cloud instances for superior performance, starting at only $0.10/h.
mysql_secure_installation Command
After installation, the MySQL server instance on your machine is insecure and susceptible to attacks. mysql_secure_installation is a shell script developed for securing the MySQL server installation on Unix systems. The script configures security settings and allows you to:
- Set a password for root accounts (see how to reset or change MySQL root password)
- Remove the root accounts accessible from outside the localhost.
- Remove anonymous-user accounts.
- Delete the test database, accessible by anonymous users.
- Reload the user privileges tables.
The [option] arguments are optional and discussed in the following section.
Note: Can't decide which database solution to use? Read our comparison article on MongoDB vs. MySQL and PostgreSQL vs. MySQL to help you make up your mind.
1. Execute the mysql_secure_installation script using the following syntax:
sudo mysql_secure_installation [option]2. Type your password and press Y to set up the VALIDATE PASSWORD component which checks whether the new password is strong enough.
3. Next, enter 0, 1, or 2 depending on the password strength you want to set :
0- Low. The password consists of at least 8 characters.1- Medium. The password consists of at least 8 characters (including numeric, mixed case, and special characters).2- Strong. The password consists of at least 8 characters (including numeric, mixed case, and special characters, and compares the password to a dictionary file).
4. Once you specify the required strength, enter and re-enter the password.
5. The program evaluates the strength of your password and requires confirmation with Y to continue.
6. Next you need to answer the following security features:
- Remove anonymous users?
- Disallow root login remotely?
- Remove test database and access to it?
- Reload privilege tables now?
To run the script with the default setting, the recommended answer to all these questions is Y.
mysql_secure_installation Options
The mysql_secure_installation script accepts certain options that customize MySQL security configurations. Specify the options in the command line or in the [client] group of the option file.
Note: MySQL can read startup options from option files (also called configuration files). To check whether the program reads option files, use the mysql --help command. If the program reads option files, the output indicates the name of the files and which option groups it recognizes. Open the file and add the wanted options to the [client] group. It will be read by all MySQL clients, and the options specified will apply to all clients.
The most commonly used mysql_secure_installation options are --host and --port.
For example, you can configure MySQL to permit IPv6 connections by clients that connect to the local server using port 3307. To do so, you need to add the ::1 local host address and change the default port (3306) to 3307.
Therefore, when running the installation script, you would use the command:
mysql_secure_installation --host=::1 --port=3307Other supported options include:
--basedir=dir | Specify the base directory. |
--print-defaults | Print the program argument list and exit. |
--no-defaults | Prevents the script from reading the default options from any option file. |
--defaults-file=# | Instructs the script to read only the specified option file #. |
--defaults-extra-file=# | Reads the specified file # after reading the usual option files. |
--defaults-group-suffix=str | Reads the usual option groups, but also groups with the usual names and a str suffix. |
--help | Displays a help message and exits. |
--host=host_name | Connects to the MySQL server on the specified host. |
--no-defaults | Prevents script from reading option files (except .mylogin.cnf file). |
--password | The script accepts this option but always ignores it. Hence, the script prompts for a password whenever invoked. |
--port=# | Specify the TCP/IP port number to connect to. |
--print-defaults | Prints the program name and the default options. |
--protocol={#} | Specify a transport protocol to use for connecting to the server {TCP | SOCKET | PIPE | MEMORY}. |
--socket=path | Specify the Unix socket file or Windows named_pipe variable to connect to localhost. |
--ssl=[1 | 0] | Enables or disables connection encryption, respectively. The options beginning with --ssl can also indicate the path to SSL keys and certificates. |
--ssl-ca=filename | Specify the file containing the trusted SSL Certificate Authorities list. |
--ssl-capath=dir | Specify the directory path containing trusted SSL Certificate Authority certificate files. |
--ssl-cert=filename | The path to the file containing the client SSL public key certificate. |
--ssl-cipher=list | A list of permissible ciphers for connection encryption. |
--ssl-crl=filename | The path to the file containing certificate revocation lists. |
--ssl-crlpath=dir | The directory containing certificate revocation list files. |
--ssl-key=filename | The path to the file containing the client SSL private key certificate. |
--ssl-mode=mode | Specify one of the server connection security states, in order of increasing strictness: [DISABLED | PREFERRED | REQUIRED | VERIFY_CA | VERIFY_IDENTITY]. |
--ssl-verify-server-cert | Instruct the client to verify host name identity against the server certificate containing the Common Name identity. |
--tls-version=list | Specify a comma-separated list of permissible TLS protocols for encrypted connections. |
--use-default | The script executes without interaction. |
--user=username | Specify the MySQL account user name for connecting to the server. |
Conclusion
This guide showed how to improve MySQL server security in Linux. After securing MySQL, we recommend improving MySQL performance to ensure a smooth server operation.
Continue learning about MySQL in our article on important MySQL commands that includes a downloadable cheat sheet.
