Mysql ошибка 1410

Just my 2 cents on the subject. I was having the exact same issue with trying to connect from MySQL Workbench. I’m running a bitnami-mysql virtual machine to set up a local sandbox for development.

Bitnami’s tutorial said to run the ‘Grant All Privileges’ command:

/opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'PASSWORD' with grant option";

This was clearly not working, I finally got it to work using Mike Lischke’s answer.

What I think happened was that the root@% user had the wrong credentials associated to it. So if you’ve tried to modify the user’s privileges and with no luck try:

1. Dropping the user.
2. Create the user again.
3. Make sure you have the correct binding on your MySQL config file.
   In my case I’ve commented the line out since it’s just for a sandbox environment.

1. Dropping the user.

From Mysql Console:

List Users (helpful to see all your users):

select user, host from mysql.user;

Drop Desired User:

drop user '{{ username }}'@'%';

2. Create the user again.

Create User and Grant Permissions:

CREATE USER '{{ username }}'@'%' IDENTIFIED BY '{{ password }}';
GRANT ALL PRIVILEGES ON *.* TO '{{ username }}'@'%' WITH GRANT OPTION;

Run this command:

FLUSH PRIVILEGES;

3. Make sure you have the correct binding on your MySQL config file.

Locate your MySQL config file (additional notes at the end). If you want to have MySQL listen for connections on more than one network find the following line on the config file:

bind-address=127.0.0.1

and comment it using a ‘#’:

#bind-address=127.0.0.1

For production environments you might want to use limit the network access (additional notes at the end).

Then restart your MySQL service.

Hope this helps someone having the same issue!

Binding: If you want to know more about this I suggest looking at the following
solution How to bind MySQL server to more than one IP address. It
basically says you can leave MySQL open and limit connections by using
a firewall, or natively if you have MySQL version 8.0.13 and above.

MySQL Config File The file could have different locations depending on your
Linux distribution and installation. On my system it was located at
'/etc/my.cnf'. Here are other suggested locations:

• /etc/mysql/mysql.conf.d
• /etc/mysql/my.cnf

You can also search for the config locations as shown in this website:
How to find locations of MySQL config files.

I was trying to install wordpress on ubuntu using the link and struck at https://ubuntu.com/tutorials/install-and-configure-wordpress#4-configure-database

When I run this command,I get the error-

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER
    -> ON wordpress.*
    -> TO wordpress@localhost
    -> IDENTIFIED BY 'root';


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 'root'' at line 1

I tried almost every syntax by changing the wordpress@localhost to 'wordpress'@'localhost'

I tried removing and adding the APOSTROPHE on 'root' in IDENTIFIED BY 'root',

then after searching I tried this command as well — GRANT ALL PRIVILEGES ON *.* TO 'wordpress'@'localhost' IDENTIFIED BY 'root‘ and got

Error — ERROR 1410 (42000): You are not allowed to create a user with GRANT

Nothing found working for me. Please help where am doing mistake. Thanks

P.S. Database is already there —

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
5 rows in set (0.01 sec)

Just my 2 cents on the subject. I was having the exact same issue with trying to connect from MySQL Workbench. I’m running a bitnami-mysql virtual machine to set up a local sandbox for development.

Bitnami’s tutorial said to run the ‘Grant All Privileges’ command:

/opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'PASSWORD' with grant option";

This was clearly not working, I finally got it to work using Mike Lischke’s answer.

What I think happened was that the root@% user had the wrong credentials associated to it. So if you’ve tried to modify the user’s privileges and with no luck try:

1. Dropping the user.
2. Create the user again.
3. Make sure you have the correct binding on your MySQL config file.
   In my case I’ve commented the line out since it’s just for a sandbox environment.

1. Dropping the user.

From Mysql Console:

List Users (helpful to see all your users):

select user, host from mysql.user;

Drop Desired User:

drop user '{{ username }}'@'%';

2. Create the user again.

Create User and Grant Permissions:

CREATE USER '{{ username }}'@'%' IDENTIFIED BY '{{ password }}';
GRANT ALL PRIVILEGES ON *.* TO '{{ username }}'@'%' WITH GRANT OPTION;

Run this command:

FLUSH PRIVILEGES;

3. Make sure you have the correct binding on your MySQL config file.

Locate your MySQL config file (additional notes at the end). If you want to have MySQL listen for connections on more than one network find the following line on the config file:

bind-address=127.0.0.1

and comment it using a ‘#’:

#bind-address=127.0.0.1

For production environments you might want to use limit the network access (additional notes at the end).

Then restart your MySQL service.

Hope this helps someone having the same issue!

Binding: If you want to know more about this I suggest looking at the following
solution How to bind MySQL server to more than one IP address. It
basically says you can leave MySQL open and limit connections by using
a firewall, or natively if you have MySQL version 8.0.13 and above.

MySQL Config File The file could have different locations depending on your
Linux distribution and installation. On my system it was located at
'/etc/my.cnf'. Here are other suggested locations:

• /etc/mysql/mysql.conf.d
• /etc/mysql/my.cnf

You can also search for the config locations as shown in this website:
How to find locations of MySQL config files.

Не пойму где накосячил, мб кто увидит?

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY my_password;
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 my_password' at line 1

UP:
Попробовал ввести без пароля, но получилось вот

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

Интересно как так, если я сижу за этим юзером

UPP
Вот чтобы меня за дурака не держали, я взял копипастом команду пользователя из комментариев, которая у него сработала.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 'my_password';
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 'my_password'' at line 1

Следом вот заменил имя пользователя

mysql> GRANT REPLICATION SLAVE ON *.* TO 'admin'@'%' IDENTIFIED BY 'my_password';
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 'my_password'' at line 1