MySQL授权报错求助：执行GRANT语句时提示ERROR 1064语法错误

Ah, I see the issue here—this is a common syntax change between MySQL 5.7 and 8.0 that trips up a lot of folks. The error you’re hitting happens because MySQL 8.0+ removed support for combining user creation and privilege granting in a single GRANT statement using IDENTIFIED BY. This syntax worked in older versions, but newer releases enforce separate steps for creating users and assigning permissions to align with SQL standards.

Here's the correct approach for MySQL 8.0+ (which is almost certainly what you’re running): #

1. First, explicitly create the user with your desired password:

   CREATE USER 'root'@'10.113.49.246' IDENTIFIED BY 'admin';
   

2. Then grant the privileges to the existing user:

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.113.49.246' WITH GRANT OPTION;
   

3. Finally, flush privileges to ensure the changes take effect immediately:

   FLUSH PRIVILEGES;
   

If you’re still using MySQL 5.7 or older (less likely given the error), your original syntax just needs a small tweak to fix the order: #

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.113.49.246' IDENTIFIED BY 'admin' WITH GRANT OPTION;

Note the adjusted order: TO comes before IDENTIFIED BY here, which was the valid syntax for older MySQL versions.

To confirm your MySQL version and be sure which approach to use, run this quick query:

SELECT VERSION();

内容的提问来源于stack exchange，提问作者Nishant Singh