Koozali.org: home of the SME Server

connect to mySQL with navicat

noidea

connect to mySQL with navicat
« on: June 03, 2006, 03:38:55 AM »
Hi as my login suggests I have no idea what I am doing.  I am a newbie to all this but I think I am trying to achive a few simple things.  I would like to connect to Mysql so that I can create databases that everyone in my office can use.  We have a mixture of MAC's , PC's and a SME server.  I downloaded a Navicat trial (someone might be able to suggest something better) and read the forums on how to connect to mySQL from a local network - all went well? Until I tried to connect from navicat.  All I get is an error message that states Host 'pc-00094.sunnyland.local' is not allowed to connect to this MySQL server.  Can any one help, what am I doing wrong?

DarkMirage

connect to mySQL with navicat
« Reply #1 on: June 03, 2006, 10:28:29 AM »
Well, I'm assuming you did something like:
Code: [Select]
## Set configuration for external MySQL access
config setprop mysqld LocalNetworkingOnly no
## Apply configuration changes
signal-event post-upgrade; signal-event reboot
[System reboots]

The above doesn't give you a login.

You should now create a user with access to the database(s).
Here is an example for creating a user MyUser, pw MyPW with 'administrator' rights:
Code: [Select]
## In the command below, \ escapes a linebreak.
## Either include them, or place the entire command on one line
mysql -e "\
create database MyDB; \
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER \
ON *.* \
TO 'MyUser'@'%' \
IDENTIFIED BY 'MyPW'; \
FLUSH PRIVILEGES;"


If this still doesn't help, you should probably check the location of the local system (is it in the local network). Although that would give you a 'Cannot connect to server' error.

If this still doesn't do the trick, please specify the exact actions you have taken to get there, so people here can see where you might have gone wrong.

Hth

Offline CharlieBrady

  • *
  • 6,918
  • +3/-0
connect to mySQL with navicat
« Reply #2 on: June 03, 2006, 06:07:43 PM »
Quote from: "DarkMirage"

You should now create a user with access to the database(s).
Here is an example for creating a user MyUser, pw MyPW with 'administrator' rights:
Code: [Select]
## In the command below, \ escapes a linebreak.
## Either include them, or place the entire command on one line
mysql -e "\
create database MyDB; \
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER \
ON *.* \
TO 'MyUser'@'%' \
IDENTIFIED BY 'MyPW'; \
FLUSH PRIVILEGES;"



Use % is too liberal IMO. You should restrict which hosts 'MyUser' can connect from.

DarkMirage

connect to mySQL with navicat
« Reply #3 on: June 03, 2006, 08:52:32 PM »
I agree.

Read more about the host field in the MySQL manual: 5.7.5 Access Control

It shows how to use wildcards and ip subnets in there... because you're talking about multiple hosts using a few databases right?

Edit by Cactus: removed to quotes around the URL to make the link work properly
« Last Edit: December 05, 2009, 05:34:24 PM by cactus »

noidea

Thanks
« Reply #4 on: June 05, 2006, 12:46:11 AM »
Thank guys,
Got access now.  I am trying to get my head around access control but I have a lot to get my head around at the moment.  

Anyway thak you for you for all of you help you guys are brilliant! :-D