Resolving Connection Issues in Ubuntu's WSL2 MySQL: Changing bind-address in /etc/mysql/mysql.conf.d/mysqld.cnf
In this article, we will discuss the common issue of trouble connecting to the Ubuntu operating system running on WSL2 (Windows Subsystem for Linux 2) after changing the bind-address in the MySQL configuration file. Specifically, we will cover changing the bind-address from 127.0.0.1 to 0.0.0.0 in the /etc/mysql/mysql.conf.d/mysqld.cnf file.
Understanding the bind-address Configuration
The bind-address configuration option in the MySQL configuration file specifies the network interface to which the MySQL server should bind. By default, this is set to 127.0.0.1, which means that the MySQL server will only listen for connections on the localhost interface.
However, if you are running MySQL on WSL2 and want to connect to it from another machine on your network, you will need to change the bind-address to 0.0.0.0. This will allow the MySQL server to listen for connections on all network interfaces, making it accessible from other machines on your network.
Changing the bind-address in /etc/mysql/mysql.conf.d/mysqld.cnf
To change the bind-address in the MySQL configuration file, follow these steps:
- Open the
/etc/mysql/mysql.conf.d/mysqld.cnffile in a text editor with sudo privileges:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf- Find the
bind-addressoption and change its value to0.0.0.0:
bind-address = 0.0.0.0- Save and close the file.
After changing the bind-address, you will need to restart the MySQL server for the changes to take effect:
sudo systemctl restart mysqlTroubleshooting Connection Issues
After changing the bind-address, you may still encounter connection issues when trying to connect to the MySQL server from another machine on your network. Here are some troubleshooting steps you can take:
- Check that the MySQL server is running and listening on the correct network interface:
sudo netstat -tlnp | grep mysqlThis should output something like:
tcp 0 0 0.0.0.0:3306 0.0.0.0:\* LISTEN 12345/mysqldWhere 0.0.0.0:3306 indicates that the MySQL server is listening on all network interfaces on port 3306.
- Check that the MySQL server is configured to allow remote connections:
By default, MySQL is configured to only allow local connections. To allow remote connections, you will need to edit the MySQL configuration file and set the bind-address option to 0.0.0.0 as described above. Additionally, you will need to grant remote access to the MySQL user you want to connect with:
GRANT ALL PRIVILEGES ON \*.\* TO 'username'@'%' IDENTIFIED BY 'password';Where username and password are the username and password of the MySQL user you want to grant remote access to.
- Check that the firewall on the WSL2 machine is configured to allow incoming connections on port 3306:
If you are using the Windows Firewall, you will need to allow incoming connections on port 3306 for the WSL2 machine. To do this, follow these steps:
- Open the Windows Defender Firewall with Advanced Security app.
- Click on Inbound Rules and then New Rule.
- Select Port and then TCP, and enter 3306 as the specific local port.
- Select Allow the connection and then click Next.
- Select the network locations where you want to apply the rule and then click Next.
- Enter a name and description for the rule and then click Finish.
- The
bind-addressconfiguration option in the MySQL configuration file specifies the network interface to which the MySQL server should bind. - To allow remote connections to the MySQL server running on WSL2, you will need to change the bind-address to
0.0.0.0and grant remote access to the MySQL user you want to connect with. - Make sure that the firewall on the WSL2 machine is configured to allow incoming connections on port 3306.