Setting up a test server can be quite a daunting task, as we can see from our Drupal Dev Environment Tutorial. There are plenty of things to take into account, and while setting up such an environment can be quick and easy, sometimes there are certain things you would like to have. Today we are going to cover one such feature – setting up MySQL for remote access.
Things to Consider
Setting up MySQL can be extremely easy, and there are plenty of packages out there that make it that way. However, if you have a little bit more advanced setup, such as a local server on your network that you use, you may want something a bit more custom. Today we are going to work with Ubuntu, which will be our server. Keeping that in mind, we will be setting up MySQL so that we can access it from other computers in our local network.
The technique we will be using involves opening up MySQL server for access from other computers. Just remember that like any such access, you should take the proper security steps, such as giving accounts strong passwords, setting up database-specific permissions, and creating accounts with database-specific permissions.
Lastly, I am assuming here that you have already set up your Ubuntu server, and assigned a static IP to it. As this process can have a thousand different permutations based on your network setup, I will not be going over how to do this.
Installing MySQL
Installing things on a Linux system is quite easy. To install MySQL, the Ubuntu documentation actually recommends that you use the following command:
However, I have seen another install command that offers a few extra tools which come in handy from time to time, so I prefer to use:
Either one of these commands will work for what we need to do, and installing these will also start the server so we can dig right into the setup. One thing to remember, though, is the root password you provided during the setup procedure.
Setting Up the User
Before we go digging into our configuration file, which we will have to do, we need to setup a user for remote access. To start we need to log into our MySQL server through the terminal:
This will log into your MySQL server and allow you to run commands against it. First we need create a new user, which we are going to call remote, as it will be our remote admin. We do this with a simple command:
There is one very important thing about this statement, the ‘%’ used to identify the host that this user can connect from. The % represents a wildcard, or any host, which means that this user can connect from anywhere. But this really means nothing without privileges.
Privileges in MySQL can be extremely complex, and this is something to consider when we grant permissions to our new user. For simplicity, we are going to grant our new user enough privileges to be dangerous, but not too dangerous.
Now our user has access to all the data they could ever want, but they can’t create new databases or tables, and they cannot remove any objects from the server. To me, this is a good set of permissions to start with.
So there is our user, all setup and ready to go. There is still one last thing to do however, and that involves our configuration file.
The Bind Address
MySQL is a very complex creature. Thankfully, most of these complexities are abstracted so they work and work well, but sometimes we need to tweak a few things. In today’s case we need to modify the “bind address” of our MySQL server.
The bind address refers to the IP address that MySQL listens to for connections. For security reasons, the default is always 127.0.0.0, or localhost for those host name addicts out there. We need to change this address so it accepts connections from the IP it has been assigned by the router on our network. For simplicity sake, lets say this is 192.168.1.2.
To change this value, we need to open our MySQL configuration file, which in Ubuntu using MySQL 5.1, can be found in /etc/mysql/my.conf. Using the text editor of your choice, open that baby up.
What we are looking for here is something like the following;
All you have to do is change the address to your server’s IP address, in this case 192.168.1.2. Once you save the file, all you need to do is save the configuration and restart your MySQL server:
And there you have it! Now you can access MySQL from any machine on your network, using your new remote user.
Well, that is going to wrap it up for this tutorial. I hope you learned a lot and just remember, when you need programming help, all you have to do is Switch On The Code.