If you want to access your MySQL server from a remote machine you must adjust some settings first. Be warned though, remote access might attract some unwanted visitors :-)
Although if you want to access your MySQL server from a machine within your LAN you still need to adjust these settings.
Connect to your machine running the MySQL server.
Edit the my.cnf configuration file, which is located in /etc or /etc/mysql in my case (Debian Lenny) with your favorite editor.
Add the following line under [mysqld]
bind-address = ip-address-of-this-machine # skip-networking
So for example if your machine is directly connected to the Internet and your IP-address is: 220.127.116.11
bind-address = 18.104.22.168 # skip-networking
For internal use do the following:
bind-address = 192.168.199.5 # skip-networking
Assuming your MySQL server runs on a machine with the IP-address 192.168.199.5
bind-address: The IP address to bind
skip-networking: (comment this, notice the #). When you remove the # skip-networking will be enabled and there will be no network connectivity possible at all. This is recommended for MySQL servers which listen only to local requests.
Then restart the MySQL server:
Or on Redhat based systems:
service mysqld restart
Open up the mysql console:
mysql -p -u root mysql
This will connect you to the MySQL server, with the user root and right in the database ‘mysql’.
If you want to add a new database to a specific username and host do the following:
mysql> CREATE DATABASE remote; mysql> GRANT ALL ON remote.* TO cleverdude@'22.214.171.124' IDENTIFIED BY 'foo123';
The above example will create a database ‘remote’, a user ‘cleverdude’ with the password ‘foo123’ and give access to the host ‘126.96.36.199’ only!
mysql> UPDATE db SET Host='188.8.131.52' WHERE Db='existingdatabase'; mysql> UPDATE user SET Host='184.108.40.206' WHERE user='existingusername' mysql> exit
The above example will give the host 220.127.116.11 access to the ‘existingdatabase’ and the ‘existingusername’.
Also make sure your Firewall (you do have one right?) has a NAT rule which allows TCP connections on port 3306 to your server.
Any -> 3306 -> MySQL Server
Logon to the remote machine and test it:
mysql -p -u existingusername -h 18.104.22.168
This will connect to the remote MySQL server (22.214.171.124) with the username ‘existingusername’.
Or use telnet
telnet 126.96.36.199 3306
Which will return some rubbish upon a successful connection, and that’s good!
Good luck!Labels: linux mysql