Monday, August 27, 2012

Accessing MySQL Remotely With MySQL Workbench

Command line be darned. Visual tools are there for a reason and if you honestly find using the GUI to be easier in visualizing complex queries then you should use it. It also helps in saving time when you want to scan the database to see what’s in it. But that’s not really the point here. A few days ago I began developing some major systems for internal use at the workplace and it was finally time to let the SQLite databases go. Not so much because of data storage needs but mostly because of the need for SQL side validation of the Foreign Key constraints and also to ensure that I didn’t have to do a lot of extra work to ensure the data integrity stayed intact. But I digress. The problem was that I needed to connect the desktop application and MySQL Workbench as well to the MySQL database that was on a server and there’s really nothing on the internet that actually addresses this problem directly. The best ‘alternative’ to this is to actually use a web service to send the data back and forth. Since I’m not going online for now it’s not really needed for me to actually be transferring data like this.

In order to this I got my own virtual server setup using Ubuntu Server (ooo he’s taking the easy way out. No I’m not. Wikipedia uses Ubuntu Server. I’m using the best tool for the job) and my way of access into it is through SSH. For the record I have no idea how I got SSH into powershell and I suspect it happened at some point while I was installing the libraries for cygwin. Anyway, after SSHing into my server I checked around, discovered that the network admin had already installed LAMP and phpMyAdmin and thus my MySQL instance was up and ready. This would end up causing more problems for me than I had anticipated. At this point I’m not willing to go reverse all the steps I took to find out which ones worked completely right but I know which ones are absolutely necessary and can give options if the steps don’t work properly.

So the first thing you want to go do is actually read the manuals on how to create and manage user privileges. I’m in a bit of a rush here so I’ll add the code later but the main steps are as follows.

First up, create a user apart from root who has all privileges. Later when you learn the full privilege list then you can actually revoke what you don’t really need but for now I’m not entirely sure of what I need and don’t so I granted all privileges. The code went something like (no I’m not being at all precise over here.)

CREATE USER newusername IDENTIFIED BY ‘type your pass here with single quotes’;

GRANT ALL PRIVILEGES ON *.* TO newusername IDENTIFIED BY ‘your pass word’;

FLUSH PRIVILEGES;

If you read the manual you’ll find this creates a user that can basically be accessed from any host. The reason for me wanting to do this is because I’m going to be needing a user that can be accessed from any machine inside the company as I’ll be making a desktop application that needs to access the db.

Exit the MySQL server. The next thing you need to do is turn off MySQL accepting only local requests. For this, open up the my.cnf file found under etc/mysql/ using sudo vim my.cnf. What you want to do here is comment out the lines bind-address = 127.0.0.1 and skip-networking. Easy way to do this?

:%s/bind-address/#bind-address/g

:%s/skip-networking/#skip-networking/g

And that’s it. I think we are ready. I did go to the extremes of forwarding the 3306 port using iptables. This is the only thing that is really server specific and you’ll want to refer the manuals of your particular distro. I don’t think this step is necessary so skip it for now but in case the actual step of accessing the db through the workbench or app doesn’t work you’ll want to come do this (or the equivalent of this if you aren’t using Ubuntu Server)

sudo iptables –A INPUT –p tcp –dport 3306 –j ACCEPT

sudo iptables –A FORWARD –p tcp –dport 3306 –j ACCEPT

sudo iptables-save

Hopefully this step works without needing the iptables step above.

It’s time to connect MySQL workbench to the db. Here’s where I made the biggest mistake. I assumed that since I actually connected to the server through SSH, I should use that method to connect to the db when using Workbench. Turned out that I was wrong. Or at least, not wrong, but it turned out that after all of this, using a standard tcp connection worked fine. Give the server name as the ip address of the server you are connecting to. XXX.XXX.XXX.XXX that kind of thing. Port should ideally be 3306 (by the way if you don’t think your mysql instance is running on port 3306, highly unlikely as it may seem, just type mysqladmin version into the command line of your ssh session and check the results. There’s one that says port. That’s your port. If your port is different then change everything to match it. Doh!)

After you put the ip, put the username and the password that you created and test your connection.

You’re welcome.

And that’s how you connect a desktop based application or a MySQL Workbench to a mysql database that’s on a server.

No comments:

Post a Comment