Set up a Railo/Apache/MySQL host on Ubuntu - Part 2: install MySQL and enable remote access

This is the second part of the series "Set up a Railo/Apache/MySQL host on Ubuntu", which is composed of 4 parts:

• Part 1: install Apache and Railo
• Part 2: install MySQL and enable remote access
• Part 3: install FTP server and set up user access
• Part 4: install subdomains

In part 1, I showed you how to install Railo and Apache server on Ubuntu. If you would like to follow the series from the beginning, you can read part 1 at Set up a Railo/Apache/MySQL host on Ubuntu - Part 1: install Railo and Apache. However, the topics of these 2 articles are quite independent. So you don't need to read the first article if you are only interested in installing MySQL server.

At the end of every installation step, if necessary I will include a little part called Step verification for you to know if you have completed that step successfully. It's important that you check this as a step depends on the one before it to be completed. So if you could not get one step done successfully, there is chance you will encounter errors at the steps that follow.

If you did read part 1, you would notice that it was quite a long article due to the large number of components that need to be configured along the way. Fortunately, installing MySQL is a lot easier and shorter as I will show you.

Please note: this article is only about installing MySQL and configuring it. It is not about teaching you SQL commands (including how to create a new database). Please consult other resources if you would like to learn these.

PART 2: Install MySQL and enable remote access

1. Assumptions and notes

1.1 You have SSH access

Similar to part 1, this article assumes that you already installed Ubuntu 9 and you got SSH access to your host. Plus, you have permission to run certain commands on it. If you receive error saying "Permission denied" or something similar, try to use sudo before the command and login with your password when asked at the first time.

1.2 If you are using Windows, you have a Unix-mode editor installed

During this process, you will need to edit some config files. If you are on Windows, make sure you use an editor that supports Unix-mode to do this. A good and free one is Crimson Editor. This is very important. You have to edit the files under this editor otherwise it will cause errors when you reupload it to the server. Do NOT use Notepad.

2. Preparation

It is a good idea to update the applications running on your host before proceeding with the installation of MySQL. From SSH, type the following commands one by one:

3.Install MySQL

Once the applications on your host have been upgraded with latest patches, type the following command at SSH to install MySQL:

You will be asked to set the root password during installation. Set one when it happens. This will be the password you use to login with the root account which, as you could possibly guess, gives you full control over your entire sql server. Do NOT forget this password. I haven't forgotten mine before so I can't tell you what you need to do in case you forgot. If you know, please leave a comment below. But better not lose it :-)

It is recommended that you secure your MySQL installation by running the following command from SSH:

MySQL is now installed and you can start it by typing this command from SSH:

(Use sudo if you receive permission error and enter your password if prompted)

4. Enable MySQL remote access

By default, MySQL only allows connections from the same machine (i.e. only from localhost). So if you only plan to access MySQL from an application that runs on the same machine, you are already good to go. However, I find it very handy to be able to connect to the MySQL server remotely from my local computer, either for debugging purpose or to see how my new design will look like with real data. If you also want to do that, read on.

Note: there is some argument against this saying that enabling remote access makes MySQL server insecure. I don't quite agree with that. For a computer to have access to MySQL server, you need to enter its IP address into the config file. That means as soon as your computer has a static IP address (which I think almost every ISP provides), you can add that in and only that computer can connect to the server remotely.

4.1 Open up remote connection

There is a line inside the MySQL configuration file that limits MySQL connections to localhost only. So the first thing we need to do is to remove (or comment out) that line.

Open the MySQL server config file at /etc/mysql/my.conf and look for the line that says:

bind-address = 127.0.0.1

Comment it out by adding a hash (#) at the front:

#bind-address = 127.0.0.1

You can do this by editing the file directly on Unix or downloading it onto you computer, edit it and reupload it to the server. If you choose the later approach (like myself) and you are on Windows, make sure you use an editor that supports Unix mode such as Crimson Editor. Do NOT use Notepad.

4.2 Restart MySQL

You need to restart MySQL for this change to take effect. From SSH:

5. Grant access to remote hosts

Although the connection to MySQL is no longer limited to localhost, you still cannot connect to it remotely yet. You need to specify the IP addresses and accounts you want to grant access to.

5.1 Login to MySQL

Before granting user access, you need to login MySQL. From SSH:

Enter the root password when prompted. This is the password that you specified during the MySQL installation.

5.2 Grant user access

In this step, I assume that:

• You already have a database called MY_DATABASE
• You would like to allow remote access to that database from a computer at the IP address 12.34.56.78
• The MySQL username is MY_USERNAME and the password is MY_PASSWORD

Change the IP address, database name, username and password to fit yours.

To grant remote access, type the following command from MySQL prompt:

(Notice that there is a semicolon at the end of every command. This is required. Without it, the command won't be executed.)

A couple of notes here:

• The first line creates a new user with the specified password. Localhost is used here instead of the computer IP address. If the user already exists and you only want to grant access to a newly created database, you do not need this line.
• As you can see, the database name is required as well as the computer IP address within the GRANT command. That means you have to run this command again if you want to grant access to another database even if that is for the same user from the same computer. You can replace the database name with a '*' to indicate all databases but I think it's better to limit to the databases instead of granting global access to the entire sql server. Similarly, the '*' after the database name ('MY_DATABASE'.*) means all tables. You can limit it to particular table by specifying the table name instead of a '*'.

You should be able to connect to MySQL server remotely now. If you can't, restart the server and try again. You can restart MySQL by firstly quitting MySQL:

Then type the following command at the SSH prompt:

6. Credits

This article is from my experience of setting up my VPS and I could not do it alone. Here are some articles that I looked at which helped me set up MySQL and produce this article:

• Install MySQL on Ubuntu 9.10 (Karmic) from Linode library
• Allow remote connections to your MySQL server from Jack Wallen

7. Useful commands

This section includes the list of commands you might find useful when working with MySQL. I use sudo here but the command may still work without it depending on what account you login.

To start MySQL:

To stop MySQL:

To restart MySQL:

8. What's next?

This is the end of part 2 of the series "Set up a Railo/Apache/MySQL host on Ubuntu". If you follow the first part as well, you should now have a host installed with Railo, Apache and MySQL and you could connect to the MySQL from your local computer. In part 3, I will show you how to install FTP server on Ubuntu and configure it, especially if you have multiple users working on the same or different projects.

Finally, thank you for reading. Please let me know how I do in this article. Is it too brief? Is it too detailed? Is it easy to follow? And more importantly, does it work for you? I love to hear from you.

Leave comment

Hi, please enter your real name and email address. The email won't be published. Comments are moderated and will appear after checked for spams.

Comment *

Reply to :

Name * Email * Website URL Send me an email when there are new comments