How to Automate mysql_secure_installation Script

mysql_secure_installation is a script that helps database administrators make their MySQL installation secure using a series of prompts to customize your installation. For example, it asks you want to set root password, remove anonymous users, disable remote root login, and remove test databases and access to them. But answering these prompts can be tedious every time, and prevents automation. In this article, we will learn how to automate mysql_secure_installation script in Linux.

How to Automate mysql_secure_installation Script

mysql_secure_installation is basically a script that gets executed when you call the command on your terminal. Here’s the default list of prompts you get to see on your screen when you run mysql_secure_installation, along with sample user inputs.

Enter current password for root (enter for none): <enter>
Set root password? [Y/n] y
New password: abc
Re-enter new password: abc
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

There are a couple of ways to automate this script.

1. Copy SQL queries to .sql file

In this case, we basically open the original mysql_secure_installation to find the lines that run SQL queries and copy them to another file, which we will execute later. First of all take a backup of the mysql_secure_installation script on your system. Then open it in a text editor. It is located in mysql/scripts/mysql_secure_installation.sh.

Look for the line that starts with do_query . Please note, it will have a space after do_query not a ( which indicates a function.

Here you will find the following SQL queries.

UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

It is basically a set of SQL queries to be executed by mysql_secure_installation to make your installation secure. First UPDATE command sets root password. Next DELETE command disables anonymous users. Next DELETE command disabled remote root logins. Next DROP command deleted test database. Next DELETE command removes access to test database. Lastly, we issue FLUSH PRIVILEGES to apply changes. You can modify these lines as per your requirements. In the above example, we have set root password as ‘root’. You can change it as per your requirement.

Copy those SQL queries and save it to a new file mysql_secure_installation.sql. Save and close the file. Then run the following command to run queries in mysql_secure_installation.sql file we just created.

# mysql -sfu root < "mysql_secure_installation.sql"

In the above command, -s option silences errors, -f forces command execution even if there is an error in between, -u indicates username, which in our case, is root.

2. Create Bash Script

In this approach, we create a separate shell script with the same queries that mysql_secure_installation would run but without any prompts. We will run this script instead of running mysql_secure_installation.

You can also add the above SQL queries in a shell script and run it instead of running mysql_secure_installation. Here is an example. Create a blank shell script.

$ vi /home/ubuntu/secure_mysql.sh

Add the following lines to it. Replace CHANGEME with your desired root password

#!/bin/bash

# Make sure that NOBODY can access the server without a password
mysql -e "UPDATE mysql.user SET Password = PASSWORD('CHANGEME') WHERE User = 'root'"
# Kill the anonymous users
mysql -e "DROP USER ''@'localhost'"
# Because our hostname varies we'll use some Bash magic here.
mysql -e "DROP USER ''@'$(hostname)'"
# Kill off the demo database
mysql -e "DROP DATABASE test"
# Make our changes take effect
mysql -e "FLUSH PRIVILEGES"
# Any subsequent tries to run queries this way will get access denied because lack of usr/pwd param

The above commands explain what each SQL query does. Save and close the file.

Make it executable.

$ sudo chmod +x /home/ubuntu/secure_mysql.sh

Now when you run this shell script with the following command.

$ /home/ubuntu/secure_mysql.sh

it will execute all the SQL queries normally executed by mysql_secure_installation script, but without any prompts.

In this article, we have learnt two similar ways to automate mysql_secure_installation script, one using a .sql file and other using a shell script. In both cases, we find out and run the same queries run by mysql_secure_installation but without prompts. You can run them on terminal and call them via another shell script to automate MySQL secure installation.

How to Automate mysql_secure_installation Script