Grant access to my database from root user to another computer for remote access

0
votes

I want to connect to SQL database from a computer that is under a different network, for some reason in the SQL console it tells me that I have a syntax error, I've tried to type it in different ways with no result and I don't see where I'm messing it up

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 'mypassword' WITH GRANT OPTION' at line 1 mysql>

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

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 'mypassword' WITH GRANT OPTION' at line 1

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY mypassword WITH GRANT OPTION;

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 'mypassword' WITH GRANT OPTION' at line 1

GRANT ALL PRIVILEGES ON *.* TO 'root'@'ipv4 adress from the other computer' IDENTIFIED BY mypassword WITH GRANT OPTION;

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 'mypassword' WITH GRANT OPTION' at line 1

GRANT ALL PRIVILEGES ON *.* TO 'root'@'ipv4 adress from the other computer' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

Also, how do I verify if it has connection to the database from the other computer if I don't have access to that computer?

1
mysqlphpmyadmin
A) Don't create another "root" account, give this a meaningful name. B) The first and last forms here should be valid. The password must be in quotes. – tadman
When something fails, please take the time to include the exact error text so we can help diagnose the problem. Saying "failed" does not communicate much to us. We're left guessing what you saw, and that's really frustrating. – tadman
@tadman Can I put any name I want? And then when I login from the other computer (for example in phpmyadmin) what do I have to put as username? The name I put in the console or root? – FlowMafia
Yeah, you can call this whatever you want, and it's best that it relates to the person or organization you're granting access. This helps so later you can check who's allowed in to your server and it's not fifty different "root" accounts. If you're having a lot of trouble here it's worth noting the MySQL Workbench tool can walk you through this process more visually. You configure your database client, phpMyAdmin or otherwise, with the username/password credentials as usual. – tadman
@tadman I edited the question, now it has the specific error I get when I try to do this – FlowMafia

1 Answers

-1
votes

If this is MySQL 8+ then you need to do a two-step account initialization. The GRANT statement has been changed, auth_option was removed, so it no longer accepts IDENTIFIED BY as part of the syntax.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
SET PASSWORD FOR 'user'@'%' = 'user_password';

The SET PASSWORD is now necessary as a separate operation.