Mark Brown's Blog

As I'm playing with MySql I found that setting up remote access to the mysql server is a bit tricky at first, so I thought I'd share quickly with you how to set up account with remote access.

 Here's the whole process:

I've got a Ubuntu box running MySql 5 on it. Once I got MySql installed (sudo apt-get install mysql-server) I set up the root account with a password. (mysqladmin -u root password "passwordyouwant") <--Thanks for that Aaron Lynch.

Then, log into the MySql server by opening a terminal window locally and typing:

mysql -u root -p (then enter the password you set above)

Following the tutorial located here , I then created a db, and table, and populated it with some data.

At this point I think, what if I want to administer this thing from my laptop? So, I installed the MySql Administrator (sudo apt-get install mysql-admin).

I tried to log in using the sql admin tool after the install. Typed in the ip address of the MySql Server, username and password, but it threw this error:

Could not connect to host
MySQl Error Nr. 1130
Host '192.168.1.103' is not allowed to connect to this MySQL server

I started browsing some forums and blogs and found that I need to edit the /etc/mysql/my.conf file to allow other machines besides the localhost to access the server.

sudo nano /etc/mysql/my.cnf

and change the line: 

bind-address           = localhost

to your own internal ip address e.g. 192.168.2.10 

bind-address           = 192.168.2.10

CTRL-X to close nano, y to save, enter to confirm the location 

I try the MySql Administrator again, but get the same error or a 1045 error. So I get to doing some more research and find that for a account to access a MySql db remotely you have to tell the server the host name that you'll be connecting from or you can use a % as a wildcard meaning from everywhere. The MySql documentation does a great job of showing you how here.  

I went to the server, logged into mysql locally (or via ssh) and typed:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'typeusername'@'localhost' IDENTIFIED BY 'typepasswordhere' WITH GRANT OPTION; 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'typeusername'@'%' IDENTIFIED BY 'typepasswordhere' WITH GRANT OPTION;

 The first statement gives the username permission to log on locally to the db from the console of the server, and the second statement gives the username access remotely from anywhere.  I could have done 'typeusername'@'192.168.2.50' in that second statement restricting that username to only being able to connect from local and 192.168.2.50.

 One note on those usernames.  I created another username for remote access, instead of trying to edit root,  as I was scared to mess with the root account.   Plus I'm getting kinda tired of breaking these linux boxs and not knowing how to fix them:)  Also the part that says "PRIVILEGES ON *.*" is giving this new account superuser privileges, so be careful with this.  

Once I typed those two commands into MySql to create those accounts and privileges it started working like a champ from my workstation.

 Of course none of this is anything new, but I found it a  bit time consuming trying to get it to work. I'm still pretty new to  mySql. Hopefully this will save some other newbies some of their time.

 Later,

Mark