mysql_secure_installation Script

January 6, 2022

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.

Securing a MySQL server using the mysql_secure_installation script.

Prerequisites

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:

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 01, 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.

Secure MySQL on Ubuntu.

5. The program evaluates the strength of your password and requires confirmation with Y to continue.

MySQL estimating the password strength.

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=3307

Other supported options include:

--basedir=dirSpecify the base directory.
--print-defaultsPrint the program argument list and exit.
--no-defaultsPrevents 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=strReads the usual option groups, but also groups with the usual names and a str suffix.
--helpDisplays a help message and exits.
--host=host_nameConnects to the MySQL server on the specified host.
--no-defaultsPrevents script from reading option files (except .mylogin.cnf file).
--passwordThe 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-defaultsPrints the program name and the default options.
--protocol={#}Specify a transport protocol to use for connecting to the server {TCP | SOCKET | PIPE | MEMORY}.
--socket=pathSpecify 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=filenameSpecify the file containing the trusted SSL Certificate Authorities list.
--ssl-capath=dirSpecify the directory path containing trusted SSL Certificate Authority certificate files.
--ssl-cert=filenameThe path to the file containing the client SSL public key certificate.
--ssl-cipher=list A list of permissible ciphers for connection encryption.
--ssl-crl=filenameThe path to the file containing certificate revocation lists.
--ssl-crlpath=dirThe directory containing certificate revocation list files.
--ssl-key=filenameThe path to the file containing the client SSL private key certificate.
--ssl-mode=modeSpecify one of the server connection security states, in order of increasing strictness: [DISABLED | PREFERRED | REQUIRED | VERIFY_CA | VERIFY_IDENTITY].
--ssl-verify-server-certInstruct the client to verify host name identity against the server certificate containing the Common Name identity.
--tls-version=listSpecify a comma-separated list of permissible TLS protocols for encrypted connections.
--use-defaultThe script executes without interaction.
--user=usernameSpecify 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.

Was this article helpful?
YesNo
Bosko Marijan
Having worked as an educator and content writer, combined with his lifelong passion for all things high-tech, Bosko strives to simplify intricate concepts and make them user-friendly. That has led him to technical writing at PhoenixNAP, where he continues his mission of spreading knowledge.
Next you should read
MySQL Show User Privileges
October 1, 2021

The best practice in MySQL is to have separate users with minimal permissions needed to work. Learn how to check which permissions each user currently has in this simple guide.
Read more
Deploying MySQL on Kubernetes {Guide}
September 9, 2021

Deploying a functional database in a containerized environment can be a demanding task. This article will show you how to deploy a MySQL database instance on Kubernetes using persistent volumes.
Read more
MySQL Data Types
March 4, 2021

Data types are important to understand as a name and a data type define each column in a database table. Learn all about MySQL data types and when to use them in this comprehensive guide.
Read more
How to Allow Remote Connections to MySQL
March 26, 2020

MySQL servers are initially set up to accept local connections. Use this tutorial to change these settings in the MySQL configuration file and enable remote connections.
Read more
  • © 2022 Copyright phoenixNAP | Global IT Services. All Rights Reserved.