How to Change the Default MySQL Port

By default, MySQL databases listen on port 3306. Changing this default port is a common practice to improve security through obscurity or to resolve port conflicts when running multiple database instances on the same server. This article provides a step-by-step guide on how to modify the MySQL configuration file, restart the database service, adjust firewall settings, and verify that your MySQL instance is successfully running on the new port.

Step 1: Locate and Edit the MySQL Configuration File

To change the port, you must edit the MySQL configuration file. The location of this file depends on your operating system:

• Linux (Ubuntu/Debian): /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/my.cnf
• Linux (CentOS/RHEL): /etc/my.cnf
• Windows: C:\ProgramData\MySQL\MySQL Server X.Y\my.ini (Note: ProgramData is a hidden folder).

Open the file in a text editor with administrative privileges. For example, on Linux, use:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Step 2: Update the Port Number

Within the configuration file, search for the [mysqld] section. This section defines the configuration for the MySQL server daemon.

Look for the port directive. If it is commented out with a # or does not exist, add or modify it to specify your new port (for example, 3307):

[mysqld]
port = 3307

(Optional) If you want local command-line clients to connect to the new port automatically without specifying it every time, locate the [client] section and update the port there as well:

[client]
port = 3307

Save the file and exit the text editor.

Step 3: Restart the MySQL Service

For the changes to take effect, you must restart the MySQL service.

• On Ubuntu/Debian:

  sudo systemctl restart mysql

• On CentOS/RHEL:

  sudo systemctl restart mysqld

• On Windows: Open the Command Prompt as Administrator and run:

  net stop MySQL
  net start MySQL

  (Note: Replace “MySQL” with your specific service name, such as “MySQL80”, if applicable).

Step 4: Configure the Firewall (If Applicable)

If your database needs to accept remote connections, you must open the new port in your system’s firewall and close the old port (3306).

• Using UFW (Ubuntu/Debian):

  sudo ufw allow 3307/tcp
  sudo ufw delete allow 3306/tcp
  sudo ufw reload

• Using Firewalld (CentOS/RHEL):

  sudo firewall-cmd --add-port=3307/tcp --permanent
  sudo firewall-cmd --remove-port=3306/tcp --permanent
  sudo firewall-cmd --reload

Step 5: Verify the Port Change

To confirm that MySQL is now listening on the new port, connect to the database from the command line by explicitly stating the new port with the -P flag:

mysql -u root -p -P 3307

Once logged in, run the following SQL query to verify the port configuration:

SHOW VARIABLES LIKE 'port';

The output should display the new port number in the Value column:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+