Create a MySQL account for Windows

Operation confirmation environment

MySQL

• MySQL 8.4

MySQL Workbench

• 8.0

Windows

• Windows Server 2022

Required environment

MySQL

• MySQL 8.4

MySQL Workbench

• 8.0

Windows

• Windows 11
• Windows 10

Windows Server

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

precondition

• MySQL must be installed
• If you want to use MySQL Workbench, you must have MySQL Workbench installed
• Make sure you can connect to your MySQL database

About root accounts

The root account is the account that has almost all access to the database. By default, it can only be accessed locally, but if you change the settings, it can also be accessed from the outside. However, since the authority is strong and the security risk increases, it is better to create a separate account if you want to connect from the outside.

Create an account in MySQL Workbench

Select MySQL > MySQL Workbench from the Start menu.

Select the connection you want to connect to MySQL. If you don't have one, add it. If you haven't saved your connection password, you'll be prompted to enter it.

In the Nevigator pane, select Users and Privileges from the Administration tab.

A list of accounts is displayed. To add an account, click the Add Account button.

You may see the following dialog and click the OK button.

Window Title

MySQL Workbench

[Main Instructions]

Invalid Authentication Plugins

[Content]

The plugin type for newuser@% of users is None, but this is not listed as a known authentication plugin by the server.

In the Login tab, enter the following:

In the "Account Limits" tab, enter in all fields 0 . It should be included by default, but sometimes it is not. Even if you try to register in an empty state, you will get an error.

"Administrative Roles" are permissions granted to MySQL as a whole. It is a powerful permission, so set it only when you need it at a minimum.

Schema Privileges are permissions that you set on a per-schema basis. Set it up after you create the schema. Permissions do not affect this beyond the scope of the schema.

Once you're done with all the settings, click the "Apply" button in the bottom right corner. You can also change these settings later.

It will be added to the list.

If you want to delete your account, you can do so with the "Delete" button.

Create an account with a command

This section goes all the way to creating a user. I won't explain where to set permissions and detailed parameters because it would be long.

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

Enter your administrator password.

Enter the following SQL: Please set your username and password as optional. SQL can be multi-line, so you need a semicolon at the end. Run it and OK, you're done.

CREATE USER test_user2 IDENTIFIED BY 'password';

By default, the hostname is % . If you also want to set a hostname from the beginning, mark it with @ and put the hostname after it:

CREATE USER 'test_user3'@'192.168.0.%' IDENTIFIED BY 'password';

You can check the list of accounts you created with the following command:

SELECT Host, User FROM mysql.user;

If you want to change the hostname, run the following command:

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

To make it accessible from a specific IP address:

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

To delete an account, run SQL like this: You also need a hostname.

DROP USER 'test_user3'@'192.168.0.%';