Monday, July 18, 2011

Running MySQL from the command screen

In the time it has taken me to edge towards my current goal of encoding a link to MySQL in Java, I have rebuilt my computer again, and so lost the installation of MySQL described in my last entry. This has forced me to retrace my steps a bit, and in so doing I have stumbled upon another handy resource. On a site called developer.com, there is an article entitled "JDBC and MySQL: Installation and Preparation of MySQL". I could have done with this when I was first learning JDBC. It is very well written - concise, yet thorough.

I visited the MySQL download page before installing. The current version is 5.5.14 rc. I had previously downloaded 5.5.9, and as it is a 100MB download, I decided to use my existing installation package. I pretty much followed my own installation instructions.

I picked up one ambiguity. I recommended "Custom Installation" from the Server Instance Configuration Wizard. I should have noted for clarity that this wizard runs after the software installation, and that for the software installation the "Standard Installation" works fine.

I also picked up sloppy word ordering. I mentioned that I had opted to have the path to the MySQL binaries folder added as an environment variable in the section on running MySQL. So when I reinstalled after dinner and a couple of drinks, I forgot to do this. The path note would therefore have been better placed in the installation section. So I added the environment variable manually, and as often happens with me, it didn't work. So I ran MySQL from the binaries folder as follows:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -u root -p

And it ran fine, which confirmed that the installation had gone correctly.

This takes be back where I ended my last blog entry. This time I went a step or two further, taking my lead from the article mentioned above. First I created a local database, with the following one line command:

mysql> CREATE DATABASE JunkDB;

from which the response back was:

Query OK, 1 row affected (0.00 sec)

A small but satisfying point was that in the article the recorded time was 0.13 sec, indicating that 7 years after the article was written computers are running at least 26 times faster than they were then.

Second I added a new user:

mysql> GRANT SELECT,INSERT,UPDATE,
-> DELETE,CREATE,DROP
-> ON JunkDB.*
-> TO 'auser'@'localhost'
-> IDENTIFIED BY 'drowssap';
Query OK, 0 rows affected (0.00 sec)

Please note that if you want to copy and paste this, get rid of the little arrows. MySQL seems to add these after any line return, not preceded by ; or \g. Please note also that the last line is a response. So if you use a text file to clean up the command for pasting, after cleaning it should look this this:

GRANT SELECT,INSERT,UPDATE,
DELETE,CREATE,DROP
ON JunkDB.*
TO 'auser'@'localhost'
IDENTIFIED BY 'drowssap';

I copied exactly from the article, because my knowledge of the command syntax is too week to want to fiddle, although it was explained inter alia that 'auser' is the username and 'drowssap' is the password.

The third and final step before have a crack at some code was to download the JDBC connector driver for MySQL: Connector/J. I recommend doing this from the MySQL connector page, which can be reached easily from the main download page. The download from there was an ordinary zip file, and for now I just extracted the contents into an easy to find location. From the article, the key file was:

mysql-connector-java-5.1.17-bin.jar

which was in the root directory of the extracted files.

No comments: