How To Change The MySQL Port On Your Server

If you’re setting up your Linux server, one of the first things on your checklist might be to change the MySQL port. Or perhaps you want to integrate your server with a 3rd party application that only connects to a specific MySQL port. Whatever the reason, this tutorial will show you how to change the MySQL port, what steps you need to take after changing the port, and what the risks are. So let’s get into it!

Using The Command Line To Change The MySQL Port On Linux

For this tutorial, I’m assuming that you have already installed MySQL, and have managed to connect to your server via SSH. If you haven’t done that yet, check out our tutorial on how to connect to your server using SSH keys Part 1 and Part 2. Once you’ve connected to your VPS via SSH, and have ensured that you’ve installed MySQL and set it up, here’s how to change the port.

1. Modify The MySQL Configuration File On Linux

MySQL stores its configuration in a “.cnf” file located in:

/etc/mysql/my.cnf

To open this, you’ll need a text editor. My preferred editor is vi, and I’ve written a short tutorial on how to use it. If you’re not comfortable editing files using the command line like this, then I suggest you log into your VPS using the browser and access the file through your control panel’s file explorer. This way, you can ensure that you don’t mess anything up.

Alternative Locations for the my.cnf File

While the my.cnf file is almost always located in the above directory, here are a few other places you might want to check in case you don’t find it:

1. /etc/my.cnf
2. /etc/mysql/mysql.conf.d/mysqld.cnf
3. /etc/mysql/conf.d/

It’s hard to maintain an exhaustive list of which versions of which operating systems have a different location for the my.cnf file, and it wouldn’t be much good anyway because it can all change. The best bet is to go through the list of the above locations and find it.

Note: You need sudo permissions to modify this file, so if you don’t know how to do it, you should read my tutorial on how to grant sudo permissions to a user who’s not root.

If you’ve configured MySQL before, you should see the following:

If you haven’t configured a port yet, you might not see this, because by default, MySQL uses port 3306 for incoming connections and you don’t need to set it explicitly. In the screenshot, I’ve purposely set the MySQL port to 3306 for purposes of illustration.

So to change the MySQL port, simply edit this file and change the number to the port through which you want MySQL to accept connections. Save your changes.

Restart MySQL

Saving the changes to the configuration file isn’t enough. You also need to restart MySQL for the changes to take effect. To do this, type the following:

sudo systemctl restart mysql

This will restart the mysql daemon and apply your changes. And you’re done!

2. Modify The MySQL Configuration File On Windows

On Windows too, you can modify the MySQL configuration file, but it can be located in a wider variety of places than Linux. For one thing, it can also be called “my.ini” in addition to “my.cnf”.

Check the following folders:

1. C:\Program Files\MySQL\MySQL Server 5.7\, depending on your MySQL version
2. %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini
3. %WINDIR%\my.ini
4. C:\

In all these locations, the file can be called “my.ini” or “my.cnf”. Chances are, it’ll be located in the first folder, but check them all in case you don’t get it on the first try. Open the file, and edit it. Change the port number – the procedure is the same as that for the Linux file.

Restart the MySQL service

Once you’ve made the change to the MySQL configuration file, you need to restart the MySQL service on your Windows machine to let the changes take effect. To do this, click the Start button and type Services, and click on the app that shows up like this:

This will open a list of services that run on your Windows system. Scroll down to find the one called MySQL, right-click it, and select “Restart”. Wait for a few seconds for the system to restart the MySQL service, and you’re done!

Benefit Of Changing The MySQL Port – Security Through Obscurity

By default, MySQL listens at port 3306. This means that any external application attempting to connect to the MySQL service running on your server has to specify the number “3306” in its connection data so that the server knows to which service it must direct the request. By having a default port, MySQL ensures that you don’t have to explicitly configure external applications and that they can work “out of the box”.

However, this also presents a security risk. Attackers know that most servers running MySQL will not change their default port, so they can direct all kinds of malicious traffic to your application, hoping to find a weak spot – especially on older MySQL databases that haven’t been updated. Even if your MySQL is up-to-date and secure, it means that your server has to deal with all the useless traffic, instead of rejecting it outright.

Changing the MySQL port to something else allows you to easily filter out this malicious traffic and block it without your server needing to examine it and judge its validity. Only someone who knows the exact port on which your MySQL runs will be able to connect to it. And, of course, you only give the port number to applications that you trust. This is what security experts call “security through obscurity”, and many of them treat it like a dirty word. However, I feel that security through obscurity is worth it, particularly when it doesn’t have any downsides, and you’re not using it as an excuse to shun security in other areas.

Disadvantage Of Changing The MySQL Port – Potential Configuration Conflicts

When you change the MySQL port, you have to ensure that all applications connecting to it are aware of the new port. For example, WordPress uses MySQL, and the configuration settings that allow WordPress to connect to MySQL are specified in the wp-config.php file. Among these settings, the port number is NOT one of them. That’s because WordPress expects the MySQL server to be available on the default port number 3306. If you change the MySQL port, you need to specify the new port number in the wp-config.php file like this:

define('DB_HOST', 'localhost:1734');

Where “1734” is the new port number. Only now, will WordPress how to connect to your MySQL server. You might need to perform this for all applications that use MySQL. Indeed, since MySQL is such a common application, it can be hard to know exactly which applications are using the default port of 3306, so when you change it, there’s a danger that a number of your existing applications will break.

So while changing the MySQL port is a good thing from a security point of view, it can introduce long-term headaches in the future that might be difficult to debug, especially if the person who changed it, left no documentation that they did so.

Conclusion

Changing your MySQL port is easy on both Linux and Windows systems. It can improve your security and reduce the load on your server. However, make sure that you’re prepared for the potential conflicts of applications that are configured to use the default port. And make sure that you have an established system of transmitting the new port number for all future administrators so they won’t be saddled with broken configurations.