Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

【MySQL】MySQL 8 でユーザー作成しGRANTで権限追加する際にエラーが発生

ターミナル
## MySQLに root で接続
$ mysql -uroot -p

## ユーザー作成
> CREATE USER 'homestead'@'localhost' IDENTIFIED BY 'XXX';

## ユーザーに全権限追加
> GRANT ALL ON *.* TO 'homestead'@'localhost' IDENTIFIED BY 'XXX';
>> エラー発生

## 権限の変更をデータベースに反映
FLUSH PRIVILEGES;

GRANTでエラー発生
原因:MySQL8.0ではGRANT構文でユーザを作成できない
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
解決策

ターミナル
## MySQLに root で接続
$ mysql -uroot -p

## ユーザー作成
> CREATE USER 'homestead'@'localhost' IDENTIFIED BY 'XXX';

## ユーザーに全権限追加
> GRANT all ON *.* TO 'homestead'@'localhost';

## 権限の変更をデータベースに反映
> FLUSH PRIVILEGES;
log
## MySQLに root で接続
$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.19 Homebrew

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

## ユーザー作成
mysql>
mysql> CREATE USER 'homestead'@'localhost' IDENTIFIED BY 'XXX';
Query OK, 0 rows affected (0.00 sec)

## ユーザーに権限追加
mysql> GRANT ALL ON *.* TO 'homestead'@'localhost' IDENTIFIED BY 'XXX';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'XXX'' at line 1
>> エラー発生

参考記事:https://www7390uo.sakura.ne.jp/wordpress/archives/456

namari
プログラマーとしてぴよこです。でも頑張ります。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away