setting up remote access to mysql on Ubuntu

Binary Royale is an IT consultancy company based in the East Midlands. We spend all of our time with clients, helping them to make good decisions about their IT. When we come across issues that would be useful to others we “try” to post the answers on our website – binaryroyale.com . We cover Derby and Derbyshire, Nottingham and Nottinghamshire mainly, but do also have clients further afield. Please browse our website to see what we offer – thanks, and enjoy the blog

Hi All

Today I also had to setup mysql on Ubuntu and allow for remote connections to it.

There are a few things that need to be done, to get this working, after you have installed mysql-server

1. Edit my.cnf to bind to the servers REAL IP address

sudo vi /etc/mysql/my.cnf

edit the line which reads “bind-address = 127.0.0.1”

and change this to “bind-address = 192.168.80.85” or whatever the mysql server’s IP address is

2. Restart mysql

sudo /etc/init.d/mysql restart

or

service mysql restart

3. Create an account with to use from a remote machine. On the mysql server

mysql -u root -p

type in the password you entered during mysql-server setup

GRANT ALL PRIVILEGES ON *.* TO newrootuser@’%’ IDENTIFIED BY ‘newpassord’ WITH GRANT OPTION;

mysql>Flush Privileges;

mysql> exit

4. On a remote machine now try and connect using the newrootuser (name can obviously be anything) and the newpassword

and that’s it

IF you want to be a tad more particular about the remote account use

GRANT ALL PRIVILEGES ON *.* TO newrootuser@’machinename’ IDENTIFIED BY ‘newpassword’ WITH GRANT OPTION;

Hope this helps.

JK