allow remote mysql

[chunlix]

Hello,

I'm brand new to MySQL in the linux environment, particularly in the command line aspect.

What I'm wondering is how to allow myself to connect to my server from home via MySQL GUI interfaces such as Navicat. I've just made the move from shared hosting to dedicated server, on the shared hosting all was done via convenient web interfaces to allow access and create the databases, so now I am lost.

Ideally what I would like to be able to do from start to finish is:

1) create the databases (empty ones) via command line.
2) assign users to certain databases
3) allow those users to connect both via localhost (for the websites to run off of) and via remote clients such as from my home or office, with full permissions.

I tried some guides I found online but they always lead to access denied messages, so I just scrapped the whole MySQL installation and now have a clean one to go with what people tell me here.

The version is 5.0.77.

I know the connection itself was being established as I could telnet to port 3306 from home, in addition to the access denied messages in Navicat, so I'm quite confident that on that end all was well.

Also, I am on a dynamic IP, so I would need to change fairly often.

Thanks very much in advance for any help.

[NightShade03]

1) create the databases (empty ones) via command line.

CREATE DATABASE <db_name>;

2) assign users to certain databases

CREATE USER my_new_user'@'server_ip' IDENTIFIED BY 'user_password';
GRANT ALL PRIVILEGES ON <database>.<table> TO 'my_new_user'@'server_ip';

3) allow those users to connect both via localhost (for the websites to run off of) and via remote clients such as from my home or office, with full permissions.

As you can see above the GRANT ALL statement assigns where the users can connect from. You might run into a few issues with dynamic ip addresses as this is going to be changing all the time. My suggestion would be to write a bash script which detects ip changes and updates the mysql permissions accordingly.

[Kloschüssel]

or use dynamic ip services. you may also need to check the firewall (usually iptables) and the server configuration - it can be set up to listen only for local connections and disallow remote connects.

[chunlix]

Thanks for the help, got it working!