Settings to allow other PCs to connect to a MySQL database (Windows version)

Operation confirmation environment

MySQL

• MySQL 8.4

Windows

• Windows Server 2022

Required environment

MySQL

• MySQL 8.4 or later
• It can also work with past versions

Windows

• Windows 11
• Windows 10

Windows Server

• Windows Server 2022
• Windows Server 2019
• Windows Server 2016

precondition

• MySQL is installed on a different PC than the one you are connecting to.

Checking the Firewall

MySQL configures the firewall on the following screen during installation, so you don't need to configure anything extra.

Check your firewall settings just in case. The operation to display it varies depending on the version of Windows, so enter "Firewall" in the Start menu to display it.

Select Advanced from the left menu.

If you select "Receipt Rules" from the left, you can see that "Port 3306" and "Port 33060" have been added to the list. This is what was added in MySQL.

Check the configuration file

Previously, you had to change from the configuration file bind-address , but in the current version, there is no need to change the settings.

The MySQL configuration file on Windows can be found at: ProgramData The folder is a hidden folder, so enter the path directly into the address bar of Explorer or set the hidden folder to be visible.

C:\ProgramData\MySQL\MySQL Server 8.4\my.ini

Account access settings

To log in to the MySQL database, you need to specify an account, but by default, all accounts can only be accessed locally. Therefore, you need to configure certain accounts to be accessed externally.

As for the account, you have an account from root the beginning, but root since the account has almost all the permissions, it is not a security advisable to allow it to be accessed from the outside. Therefore, here we will create a separate test_user account called "" and connect with that account.

See the following tips on how to create an account:

• Create a MySQL account for Windows

Account access settings (GUI operation)

Select MySQL > MySQL Workbench from the Start menu to launch it.

Once launched, there is a default instance in the bottom left, select it.

If you haven't saved your password, you'll be asked for it and log in with your root account.

With the Administration tab selected from the left Navigator menu, select Users and Privileges.

Select the account you want to change permissions for from the list. test_user Here we are selecting an account. If you haven't created an account, add it from "Add Account".

Limit to Hosts Matching If this localhost is , it means that it can only be accessed from localhost. This value sets the IP address of the PC that you want to allow connections to. If you enter an IP address normally, only one device will be targeted, but if you want to select a range, you can use the wildcard "%", so if you specify "192.168.0.%", you will allow "192.168.0.0" ~ "192.168.0.255". Note that if you connect via IPv6, you will not be able to connect by IPv4 definition.

Once you have entered it, you can click the "Apply" button in the bottom right corner to apply it.

You can see that the User list has also changed.

Set up MySQL and schema access permissions for your account as needed.

Set account permissions (with commands)

Select MySQL > MySQL 8.4 Command Line Client from the Start menu to run it.

You will be asked for a password, so enter your root password.

You can enter the following command to browse the list of accounts and hosts:

select user,host from mysql.user;

To create an account, enter the following command: Enter the IP address of the PC you want to connect to the Permission Host externally. Since the wildcard "%" can be used, if you want to allow access from all, enter "%" and "192.168.0.0" if you want to allow "192.168.0.0" ~ "192.168.0.255", such as "192.168.0.%". If you want to include "%", please enclose it in a single quote. Note that if you connect via IPv6, you will not be able to connect by IPv4 definition.

create user '<ユーザー名>'@'<アクセス許可ホスト>' identified by '<パスワード>';

Input Example

create user 'test_user2'@'192.168.0.%' identified by 'xxxxxxxx';

To change the hostname of an existing account, enter the following command:

RENAME USER '現在のユーザー名'@'現在のホスト名' to '新ユーザー名'@'新ホスト名';

Input Example

RENAME USER 'test_user2'@'192.168.0.%' to 'test_user2'@'192.168.%.%';

Set up MySQL and schema access permissions for your account as needed.

Connection Verification

Any tool that can be connected from the outside is fine, but this time I'm going to connect it with Workbench, the official client of MySQL.

Launch Workbench that you have installed on your other PC.

Add a MySQL Connection.

Enter the settings you need to connect. DNS name is fine, but for some reason I couldn't connect to Workbench, so I specified it as an IP address.

If the connection is successful, you can determine that the configuration is successful.