Environment Configuration:
Server: Debian11 bullseye
Database: MySQL 8.0
- Check if MySQL allows remote connections: By default, MySQL only allows local connections. To check if MySQL has allowed remote connections, you can enter the MySQL command line terminal and enter the following command:
mysql -u root -p
If you successfully enter the MySQL command line terminal, it means that MySQL has allowed remote connections. Otherwise, you need to modify the MySQL configuration file to allow remote connections.
- (Optional according to requirements) Modify the MySQL configuration file: If MySQL does not allow remote connections, you can achieve this by modifying the MySQL configuration file. The location of the MySQL configuration file:
Linux: /etc/mysql/mysql.conf.d/mysqld.cnf
Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
Note: The ProgramData folder needs to enable the display of hidden files, please refer to the relevant information.
Find the following line:
#bind-address = 127.0.0.1
Change it to:
bind-address = 0.0.0.0
This way, MySQL will allow connections from any IP address.
3. Restart the MySQL service: After modifying the MySQL configuration file, you need to restart the MySQL service for the changes to take effect. You can restart the MySQL service with the following command:
net stop mysql
net start mysql
4. Open the MySQL port in the firewall: By default, the Windows 10 built-in firewall will block MySQL connections from external networks. If you want to allow MySQL connections from external networks, you need to open the MySQL port in the firewall. You can open the MySQL port by following these steps:
- Open "Control Panel" and go to "Windows Defender Firewall".
- Click on "Advanced settings".
- Select "Inbound Rules" and click on "New Rule".
- Select "Port" and click on "Next".
- Select "TCP" and "Specific local ports", and enter the port number used by MySQL (default is 3306).
- Select "Allow the connection" and click on "Next".
- Select "All network connections" and click on "Next".
- Name the rule and click on "Finish".
- Login in CMD or MySQL graphical client such as Navicat.
mysql -h Your_IP -u Your_UserName -p