Link to home
Avatar of ClintonK
ClintonKFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to administer MySQL on Ubuntu from MySQL Workbench on Windows 7

I have MySQL running on a dedicated Ubuntu server and I have installed MySQL Workbench on my Windows PC. I can connect to MySQL and view stuff but I can't create users or do much admin as it seems that the root@% user doesn't have the same level of privilege as does root@localhost.
How can I give more power to root@% or better still, create a new user that is specific to my Windows PC IP address that has more power?
ASKER CERTIFIED SOLUTION
Avatar of TobiasHolm
TobiasHolm
Flag of Sweden image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ClintonK

ASKER

When I ran GRANT ALL PRIVILEGES ON database.* TO 'user'@'yourremotehost' IDENTIFIED BY 'newpassword';
(with substituting 'user'@'yourremotehost' and 'newpassword' accordingly) I get a syntax error. It didn't seem to like database.*
I changed that to *.* and it accepted that. However, when I connect through Workbench and look at the admin roles for the new user I see
User generated image
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Thanks for the help. I now have a user that can administer MySQL from Workbench on a PC.
The "WITH GRANT OPTION" gave me the extra privilege that I required.
What was complicating the issue was a problem with my DNS setting in that it had an entry against the wrong PC name for the IP address so the user I was setting up always failed because it looked up the PC name in DNS and came back with the wrong answer.

Now for the next step to get Dreamweaver to connect to MySQL! That's for a separate question to EE

Thanks again.