Access Mysql from other machines

For security reason, MySQL doesn't allow access from other computer.
If you try to connect with a MySQL administration client like MySQL Administrator you will get a Mysql Error Number 2003.

All this operations modify system settings, so they must be executed with root privileges

If you want connect from a computer in your LAN to your mysql server you have to :

1) Edit your MySQL configuration file

root@namazu:~#sudo vi /etc/mysql/my.conf
and change the line
bind-address = 127.0.0.1
with
bind-address = 192.168.178.100

Now, to apply changes you have to reload the MySQL server
root@namazu:~#sudo /etc/init.d/mysql start
No MySQL listen on your external interface, to verify that the server is up and accept connection type

root@namazu:~#telnet 192.168.178.100 3306

and you should get:

Trying 192.168.178.100...
Connected to 192.168.178.100.
Escape character is '^]'.
@
5.0.75-0ubuntu10.2

2) Give grants

Now MySQL is reachable on the "network" level, we have to enable access to databases.

Let's access MySQL with the mysql client, we still use localhost because we don't have privileges to access MySQL from others hosts yet.

root@namazu:~#mysql -uroot -pmyRootPass -h localhost

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 5.0.22-Debian_0ubuntu6.06-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Modify access privileges to the server

mysql>GRANT ALL PRIVILEGES ON DB_NAME TO 'USERNAME'@'HOST' IDENTIFIED BY
'PASSWORD';

N.B.

• if you want to give access from any host set HOST to '%'
• if you want to grant access to all the DBs set DB_NAME to *.*

So, for instance, with this command :

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'foopass';

we allow root (whose pass is foopass) to access all databases whichever host.

Exit the client

mysql>exit

Reload the privileges

root@namazu:~#mysqladmin reload

For more info on GRANT command: GRANT Syntax on MySQL Reference Manual

Have fun …

Aug 29, 2006 by simone Trackback
Filed in: MySQL, Things I forget
Comment feed