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 restart
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 …