Settings to allow other PCs to connect to MySQL databases (Windows version)

Operating environment

MySQL

• MySQL 8.0

Windows

• Windows 11

Prerequisites

MySQL

• MySQL 8.0

Windows

• Windows 11
• Windows 10

Windows Server

• Windows Server 2022
• Windows Server 2019
• Windows Server 2016
• Windows Server 2012 R2

precondition

• MySQL 8 is installed

Checking the Firewall

In MySQL, the firewall is set on the following screen during installation, so you do not need to configure any additional settings.

Check your firewall settings just in case. Since the operation until display differs depending on the version of Windows, enter "firewall" in the start menu to display it.

Select "Advanced" from the menu on the left.

If you select "Inbound 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.

Checking the configuration file

Previously, it was necessary to bind-address change from the configuration file, but in the current version, this item does not exist, so there is no need to change the setting.

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

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

User access permission settings

To log in to a MySQL database, you need to specify a user, but by default all users can only access it locally. So, here we will set permissions for the user.

I'm operating on an account to root save the account addition step this time. In actual operation, it is desirable to create a separate user account and allow connection to that account.

There are two ways to allow connections: by using a command or by using the GUI.

Setting User Access Privileges (GUI Operations)

From the Start menu, select "MySQL -> MySQL Workbench 8.0 CE" to start it.

When you start it, there is a default instance in the lower left corner, so select it.

You will be asked for a password, so log in with the root account.

Select the Administration tab from the Navigator menu on the left and select Users and Privileges.

In the list, select the account for which you want to change permissions. root In this case, we have selected an account.

Limit to Hosts Matching I think that there is an item and it is set to localhost by default. This means that it can only be accessed from localhost. Set this value to the IP addresses of the PCs that you want to allow connections to. If you enter normally, only one unit 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".

Once entered, you can apply it by clicking the "Apply" button at the bottom right.

You can see that the User list has also changed.

Setting User Access Privileges (Commands)

supplement

It is not definitive information because it is within the scope of research, but in MySQL 8, it is not possible to change the permission host with a command after the user is created, so it is necessary to confirm the host at the time of user creation.

From the Start menu, select "MySQL -> MySQL 8.0 Command Line Client" and run it.

You will be prompted for a password, so enter the root password.

You can browse the list of users and hosts by entering the following command:

select user,host from mysql.user;

To create a user, enter the following command: Enter the IP address of the PC you want to connect to externally to the "Permission Host". Since the wildcard "%" can be used, enter "%" to allow access from all, and "192.168.0.0" ~ "192.168.0.255" to allow "192.168.0.%". If you want to include "%", enclose it in single quotation marks.

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

Example Input

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

I was able to create a user with the above command, but I don't have any access privileges, so I add permissions with the following command.

The types of privileges include "ALL [PRIVILEGES]", "CREATE (permission to create databases, tables, etc.)", and "SELECT (browse table records)". Please check the official website for the contents of the permissions.

Permissions to be set can be specified on a per-database or per-table basis. If you enter "*.*", you can set access permissions for all databases and tables.

grant <権限の種類> on <データベース名>.<テーブル名> to <ユーザー名>@<アクセス許可ホスト>;

Example Input

grant all privileges on *.* to user1@'192.168.0.%';

To reflect the settings, enter the following command:

flush privileges;

If you check the user list again, you can confirm that it has been created and configured.

Connection check

Any tool that can be connected from the outside is fine, but this time we will try to connect with Workbench, the official client of MySQL.

Start Workbench if you have it installed on another PC.

Add a MySQL Connection.

Enter the settings required for the connection. You can use a DNS name, but for some reason I couldn't connect to Workbench, so I specified it by IP address.

If the connection is checked and successful, it can be judged that the setting is normal.