April 26, 2014

I’ve been trying to learn a bit more about MySQL recently, and decided the other day to install the MySQL server on my desktop so I can so some experimentation from my laptop.  Presumably this could also be done with virtual machines, but I figured two separate machines would make it a little more realistic.  This is a great way to experiment a little with SQL if you want to learn web forms and database access better.

The first step was to install MySQL server on my desktop and MySQL client on my laptop.  Supposedly at least the client is usually installed with many Linux distributions, but on my Ubuntu laptop I had to add it myself.  This can be done with a standard sudo apt-get install mysql-client, followed by the same thing for the server on my desktop: sudo apt-get install mysql-server. During MySQL server installation you’ll be asked to set a root password; as always, make this something secure and memorable. The default settings for MySQL server are to allow connections only from the host computer. The configuration settings are stored (at least under Ubuntu) in the /etc/mysql/my.cnf file. You’ll need to find the line in this file that says bind-address (or it may just say localhost) and comment it out by putting a # at the beginning of that line. This shouldn’t introduce any security issues as long as you’re on a local intranet that you trust, and your router isn’t configured to forward SQL packets on port 3306 (the default for MySQL, though you can make it anything you want). Lastly, you’ll have to actually start the service: sudo net service start mysql.

Now you should be able to actually use MySQL on your server. To be able to do anything, though, you’ll need to grant yourself privileges. This has to be done from the system running MySQL server, either sitting physically in front of it or using SSH (I did the latter from my laptop). The basic usage for running MySQL is mysql [-h host] [-u user] [-p], where each bit in brackets is an optional argument. If you’re running commands from the server, you don’t need to enter a host address, but I found that I did need to specify the root user, so I used mysql -u root -p (the -p signifies that we’re using a password). Assuming you’re using the -p option, you’ll be prompted for a password (in this case the root password), and then if everything is configured properly you should see a mysql prompt.

To grant privileges in MySQL you use the grant command, which takes arguments for which privileges you want to grant, which database you’re granting privileges to, and what user is begin granted privileges. In a test situation like this, I wanted to grant all privileges for all databases to my username on my laptop, so I used the command grant all on *.* to 'username'@'hostname'; (don’t forget to end all commands with a semicolon!). For me that took care of any connection and authentication issues, and I was able to connect to the MySQL server on my desktop from my laptop. In future posts I’ll talk a little about about installing Apache and PHP if you want to play around with web forms as well. Of course this can all be done on a hosted web site, but I think this is a little more fun.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: