Using PhpMyAdmin on XAMPP, I want to add a super user that has the same privileges as 'root'@'localhost'.
This is because I want to create a MySQL instance that my local machine will serve as a remote database server.
-
Are you talking about a system user or a MySQL user? That's two unrelated things.– replayAug 19, 2013 at 10:07
-
Yes a user to connec to MySQL. thru phpmyadmin panel– user185296Aug 19, 2013 at 10:10
-
1A more pertinent question would be why you need a root-level user with non-localhost access? What specific things do you need this user to do? Super-user access is locked to localhost for a very good reason.– Craig WatsonAug 19, 2013 at 10:18
Add a comment
|
1 Answer
You can see the grants of root by executing "show grants for 'root'@'localhost';". This command will give you some output similar to this:
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'mypassword' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
Then you can simply replace the username/password/host out of the shown grant query, for example like this:
GRANT ALL PRIVILEGES ON *.* TO 'anotheruser'@'%' IDENTIFIED BY PASSWORD 'anotherpassword' WITH GRANT OPTION;
This should create a user 'anotheruser'@'%' with the specified password, and give it the same rights as 'root'@'localhost' has.
-
1It's worth noting that identified by password 'xxx' expects a hashed password in input, otherwise it will return an error "Password hash should be a 41-digit hexadecimal number". If you want to use a cleartext password, then you should run identified by 'clearpassword'– AlekcMar 8, 2016 at 18:04