Introduction

The mysql_secure_installation script is a command-line utility that helps you secure your MySQL installation by setting a root password, removing anonymous user accounts, disabling remote root logins, and removing test databases. It is an important tool for ensuring the security of your MySQL server. This script can be used to configure a variety of security settings, including setting a root password, removing anonymous user accounts, disabling remote root logins, and removing test databases. It is recommended that you run this script after installing MySQL to ensure that your server is secure.

mysql_secure_installation Script

#!/bin/bash

# This script will help you secure your MySQL installation

# Get the root password
echo “Please enter the root password for your MySQL installation”
read -s rootpass

# Stop the MySQL server
echo “Stopping MySQL server”
/etc/init.d/mysql stop

# Start the MySQL server
echo “Starting MySQL server”
/etc/init.d/mysql start

# Set the root password
echo “Setting the root password”
mysqladmin -u root password $rootpass

# Delete anonymous users
echo “Deleting anonymous users”
mysql -u root -p$rootpass -e “DELETE FROM mysql.user WHERE User=””

# Disallow remote root login
echo “Disallowing remote root login”
mysql -u root -p$rootpass -e “DELETE FROM mysql.user WHERE User=’root’ AND Host NOT IN (‘localhost’, ‘127.0.0.1’, ‘::1’)”

# Delete the test database
echo “Deleting the test database”
mysql -u root -p$rootpass -e “DROP DATABASE test”

# Reload privileges
echo “Reloading privileges”
mysql -u root -p$rootpass -e “FLUSH PRIVILEGES”

echo “MySQL secure installation complete”

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

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.

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

Other 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.

Securing Your MySQL Installation with the mysql_secure_installation Script

MySQL is one of the most popular open source databases in the world. It is used by millions of websites and applications to store and manage data. As with any software, it is important to keep your MySQL installation secure. The mysql_secure_installation script is a simple tool that can help you do just that.

What Does the mysql_secure_installation Script Do?

The mysql_secure_installation script is a command-line utility that helps you secure your MySQL installation. It performs several tasks, including:

• Removing anonymous users
• Disabling remote root logins
• Removing test databases
• Reloading privilege tables

By running the mysql_secure_installation script, you can ensure that your MySQL installation is secure and up-to-date.

How to Run the mysql_secure_installation Script

The mysql_secure_installation script is included with the MySQL server package. To run it, open a terminal window and type the following command:

$ mysql_secure_installation

You will be prompted to enter a password for the MySQL root user. Enter the password and press Enter. The script will then run and perform the tasks listed above.

Conclusion

The mysql_secure_installation script is a simple and effective way to secure your MySQL installation. By running it, you can ensure that your MySQL installation is up-to-date and secure.