Software Dev't Navigation

Latest news

16 May 2007: MySQL Server release 5.1.18 Beta with some changed functionality and bug fixes.

12 May 2007: MySQL GUI Tools 5.0.r12 have been released.

Using MySQL

This section describes some of the pitfalls encountered when installing MySQL and how to get started. The first objective is to get MySQL running and test connectivity via the localhost loopback (i.e. MySQL running on the local machine and talking to a database on the same local machine).

One important factor to bear in mind is that unless you are hosting your own website then you won't actually be installing MySQL on the server because the ISP has that responsibility. On the other hand you will want to test out your MySQL database on your machine requiring a MySQL installation that should mirror as close as possible that on the server. You will also want to create your tables, probably your stable tables and empty dynamic tables for uploading to the server. Also, it is quite feasible to use MySQL as your database manager for your existing desktop (i.e. non web based) applications. I have found this to be a very good solution for getting rid of Access, Paradox, dBase, Interbase and any other odd database that has outlasted its usefulness.

Installing MySQL

It is straightforward to go the MySQL website and download the basic version for Windows. This is open source and available for a number of platforms including the Mac and Linux; this section only deals with Windows XP.

Which Version of MySQL?

The first point is which version to use? There is the MySQL Community Server and the MySQL Enterprise version. What tends to happen is that the features in the Community version lag behind those found in the Enterprise, which I consider fair enough since there is no fee for using the Community version. In the past I had some problems with installing and running the Windows versions, but I am pleased to say that all of these have been ironed out. The first thing was that my Firewall would not let it run; so I had to create a new rule for localhost inbound on Port 3306 for inbound TCP. In general, I tend to use the latest beta version of the 5.1 Community Server, because I have found these sufficiently robust to deal with my home applications. When version 6.0 (Falcon) reaches beta stage I may be tempted to give it a try.

Connecting to the database

I was still having a bit of a problem connecting, so I downloaded MySQLAdministrator. Using the Administrator I found out that my user name had to be root (hangover from Unix?) and I managed to successfully connect.

InnoDB Support

This was also very useful, because I then realised that the default database for the Windows essential version did not include InnoDB support. So back to the MySQL site to download the standard Windows version. This time it did include InnoDB support and I used the Administrator to set up the InnoDB paths and used the Command Line Client in the tools section to set up a simple one table database definition. Now all Windows Community version downloads have InnoDB support as standard so I don't expect to have to worry about this in future.

Conclusions

In conclusion I think there were some early problems causing confusion with the downloads and installation. Now I believe all this is fixed with the new Community editions. Also it suggested that Windows should connect to MySQL using something called named pipes, but I found TCP/IP worked well. I later found out that you only have to use TCP/IP if you are planning to access a MySQL database from another machine, but as it worked with my desktop applications I decided to stick with TCP/IP. There is plenty of documentation on the site, including a very useful user forum, but as usual you have to know what you are looking for.

Upgrading MySQL

I recently upgraded to the new production release of MySQL (from version 4.1 to 5.0) and I am pleased to say that the upgrade was faultless and painless. First I used the Administrator to backup all my databases. I then removed version 4.1 and used the windows installer to set up version 5.0. Based on my previous experience of setting up this was very straightforward even more so now that the windows essential version includes InnoDB support. Then I used the restore function in Administrator to load up my existing databases without a hitch. I then made some basic checks on the tables and when all was well I connected up to Open Office using JDBC. This time I had to recreate the connections but this was expected. So, first impressions are very favourable, because you get much improved performance, better use of database storage, improved SQL and stored procedures. What more do you want for free!

Upgrade to Version 5.1

The upgrade from version 5.0 to version 5.1 was even simpler because this did not involve any table changes. Therefore I just had to install the new software and the installation procedure cleaned up by removing the old version automatically. However, when updating to the 5.1.24 Community Server Release Candidate the default is for the data to reside in a different folder i.e. 'C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data' instead of 'C:\Program Files\MySQL\MySQL\ Server 5.1\data'. This is a more sensible choice to separate out applications data from the application itself. Just make sure you move all the data files into the new folder including the .frm files which hold all the table and column defintions and the ibdata1 Inno database and the log files. After I upgraded I checked on the Administrator and found that the log group directory was still pointing at the old folder. I am not sure if this is an error, but decided to leave it for the moment - the log archive directory points to the correct directory.

Using The Command Line Editor

The Command Line editor is one of the tools that can be called by MySQLAdministrator. Using this you can fairly easily communicate with your database using fairly standard SQL syntax e.g. against the mysql prompt type 'use test'. This connects you to the test database. Then you can list any tables in test, using 'show tables'. Set up a new table using 'create table'. This is a good first exercise to show that you are fully communicating with the MySQL server. You can then view the results of your work on the test database by looking at the Catalogs tab which will show you the revised schema including any tables created and their column definitions.

MySQL GUI Tools

In a recent development the following tools have all been bundled into one download. This means it is not now possible to just select some of the tools.

MySQLQueryBrowser

As well as running queries on your database, you can use this to populate the tables and view content. If you have some SQL migration text then it is possible to run the insert queries in the Query Browser. recently, I upgraded to the 1.2.15 version on XP and so far this seems to be a mistake. The first thing I noticed was that when connecting to a default schema it is not possible to query on any of the other schemas without prefixing the data with the schema name. This may be what is intended but it seems a bit of a waste of time. Also if you try and switch across to the Administrator from the QueryBrowser, or vice versa, you get an XP crash. I hope the next version stands up better!!!

MySQLAdministrator

A much better alternative to using the Command Line Editor is to use the MySQLAdministrator to create or update the sub-schema.

MySQLMigrationTool

I haven't tried this but it looks like a fairly complex tool to use relying on the Java runtime libraries.

MySQLWorkBench

This looks very interesting as it should provide the opportunity to graphically create a database schema. However, as it is still in alpha release, I decided not to try this out for the moment.

ePlaice / For the Best Software on the Net

Mainly Free and Open Source Software