Securing MySQL on your server

When it comes to MySQL security, many server administrators only think about strong passwords. But the reality is that a default MySQL installation includes several serious security weaknesses that, if left unaddressed, leave your server exposed to intrusion. Anonymous users, open remote connections, and users with excessive privileges are three common problems found on many production servers. In this article, you'll learn step by step how to fix these issues and deliver a secure and resilient database.

Why is MySQL Security Weak in a Default Installation?

After installation, MySQL runs by default on localhost with the root user (without a password or with a simple one). Additionally, the installation script creates an anonymous user with an empty name, allowing anyone to connect to the database without authentication. This might be acceptable in a development environment, but on a production server, it's a security disaster.

Many successful attacks on MySQL occur not through software bugs, but through misconfiguration. An attacker can find the MySQL service on port 3306 with a simple port scan, and if the anonymous user is active or the root password is weak, it's game over. Therefore, the first step for MySQL security is cleaning up the default installation.

Checking the Current Server Status

Before making any changes, check the current status. Connect to the server via SSH and run the following command:

mysql -u root -p

Then run this query to see the list of users:

SELECT user, host, authentication_string FROM mysql.user;

The output typically includes the root user with different hosts (localhost, 127.0.0.1, ::1) and a user with an empty name (user=''). That empty user is the anonymous user that needs to be removed.

Removing Anonymous Users and Cleaning Up Default Databases

The anonymous user allows anyone connecting from any host to log in without a password. This means if MySQL is accessible externally on port 3306, anyone can get in. To remove this user, run the following commands inside the MySQL environment:

DROP USER ''@'localhost';
DROP USER ''@'hostname';

If you don't know the exact hostname, use the previous query to find the host associated with the empty user, then run the DROP command with that host.

After removing the anonymous user, also drop the default databases test and test\_%. These databases are accessible to all users by default and can be used to fill up disk space or for exploitation:

DROP DATABASE IF EXISTS test;
DROP DATABASE IF EXISTS test\_%;

Finally, apply the changes:

FLUSH PRIVILEGES;

Common Mistake: Forgetting FLUSH PRIVILEGES

After any change to the mysql.user table or GRANT/REVOKE commands, you must run FLUSH PRIVILEGES so MySQL reloads the privilege tables. If you don't, changes won't take effect until the service restarts, and you might think the job is done while the anonymous user is still active.

Restricting Remote Connections to MySQL

One of the most important decisions in MySQL security is which address the service listens on. If your application runs on the same server (e.g., PHP-FPM and MySQL on one server), there's no need for remote connections, and you should bind MySQL only to localhost.

The main MySQL configuration file is usually located at /etc/mysql/mysql.conf.d/mysqld.cnf (on Ubuntu) or /etc/my.cnf (on CentOS). Find the bind-address line and change it as follows:

bind-address = 127.0.0.1

If this line doesn't exist, add it under the [mysqld] section. After making the change, restart the service:

sudo systemctl restart mysql

Now verify that MySQL is only listening on localhost with the following command:

sudo netstat -tlnp | grep 3306

The output should only include 127.0.0.1:3306. If you see 0.0.0.0 or a public IP address, it means it's still accessible from outside.

If You Really Need Remote Connections

In some architectures (e.g., multiple application servers connecting to a central database), remote connections are necessary. In this case, instead of fully opening the port, do the following:

• Instead of 0.0.0.0, set the server's private IP address in bind-address.
• Use a firewall (iptables or ufw) to allow only the application servers' IPs.
• Don't run the connection on a non-standard port; this doesn't provide real security and only adds complexity.
• Definitely enable SSL/TLS for remote connections to encrypt traffic.

Remember that any remote connection increases the attack surface. If there's a way to avoid it, avoid it.

Creating Users with Minimal Privileges (Principle of Least Privilege)

One of the golden rules of MySQL security is that each application or developer should only have the access they truly need. Using the root user for application connections is a huge mistake. If your application gets hacked, an attacker with root access can delete all databases.

To create a restricted user, first decide what access your application needs. A typical web application only needs SELECT, INSERT, UPDATE, and DELETE on a specific database. The following command creates a user with this level of access:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'YourStrongPassword!2024';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

In this command:

• 'app_user'@'localhost' means this user can only connect from the server itself.
• myapp_db.* means access is limited to the myapp_db database and all its tables.
• The GRANT command only allows the four main operations.

If your application needs to create tables or alter the schema (e.g., during development), you can add ALTER and CREATE privileges, but don't do this in production. For schema migrations, use a separate user with DDL access and remove it after the work is done.

Checking a User's Privileges

To make sure a user doesn't have excessive privileges, use the following command:

SHOW GRANTS FOR 'app_user'@'localhost';

The output should only include the same GRANT statement you defined. If you see something like GRANT ALL PRIVILEGES or USAGE ON *.*, it means the user has global access and needs to be fixed.

Common Mistake: Using Wildcards in Host

Some developers, for convenience, create users with a host of 'app_user'@'%'. This means the user can connect from any address. If your application is on the same server, definitely use localhost. If remote connections are necessary, instead of %, specify the exact IP address of the application server:

CREATE USER 'app_user'@'192.168.1.50' IDENTIFIED BY 'YourStrongPassword!2024';

This ensures that even if the password is compromised, an attacker can only connect from that specific IP.

Strengthening the Root Password and Password Policies

The root user in MySQL must have a strong and unique password. If it's still the default or a weak one, change it:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewVeryStrongPassword!2024';
FLUSH PRIVILEGES;

MySQL version 8 enables the caching\_sha2\_password plugin by default, which offers better security than mysql\_native\_password. If your older application has issues with this plugin, instead of reverting to the weaker plugin, update your application's libraries.

You can also enable a password complexity policy. In MySQL 8, this is done by installing the validate\_password component:

INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = STRONG;

With this setting, MySQL prevents the creation of weak passwords and enforces minimum length and character combinations.

Summary and Final Checklist

MySQL security is not a one-time process; it should be reviewed periodically. After completing all the steps above, go through this checklist:

1. Has the anonymous user been removed? (SELECT user FROM mysql.user WHERE user='';)
2. Have the test databases been dropped?
3. Is bind-address set only to 127.0.0.1 (or a restricted private IP)?
4. Is no application connecting to MySQL with the root user?
5. Does each application have a separate user with minimal privileges?
6. Is the root password strong and unique?
7. Is port 3306 blocked from outside by the firewall (unless there's a real need)?

If you've recently purchased a virtual or dedicated server from ServerNet and MySQL is installed on it, perform these steps today. Spending a few minutes on secure configuration is far less costly than dealing with the consequences of a breach. Remember that database security is part of your overall server security and should not be overlooked.