Using MySQL under Windows NT

MySQL is a relational database management system. The same statement is true of large systems such as Oracle and Microsoft SQL Server. If you are looking to step up from using Microsoft Access files, but aren’t ready for the financial investment of one of the big two databases, then MySQL offers you a great middle ground. Many companies and individuals have started using MySQL for the development of their web projects and then never switched over to one of the larger databases because MySQL performed well enough that an upgrade was never needed. In fact, benchmarks on the MySQL web site show that MySQL outperforms SQL Server on just about every possible test.

If this were all true, then why would anyone want to run SQL Server over MySQL? There are plenty of reasons why corporations want to have a particular piece of software running over another, we won’t go into all of that here. Suffice it to say, you will probably find that technically speaking, MySQL will suite a large percentage of database projects out there.

I had worked with a similar product, mSQL, several years ago on Unix systems and had always been on the lookout for a low-cost, efficient database engine for Windows systems. Currently both mSQL and MySQL are both available for Windows. In comparing the two systems, I chose to work with the current version of MySQL as the ODBC drivers for MySQL are much more refined. Secondly, with several Windows based administration tools that work well with MySQL, the maintenance burden has been made much lighter.

Initially, I wanted to take an existing web site that was run off of Microsoft Access files and see how much effort would go into getting it up and running on MySQL. The website I had available to work with is a site that I run and maintain is wildrocketry. Every single page has at least one database call, and many have two or three database calls. Converting the site to MySQL done to improve the speed of the responses. With the entire site being finished, I wanted to make the transition to MySQL as painless as possible.

Using MySQL and the DBTools utility described in this article, I was able to very easily get MySQL up and running, create a user for the web site access, and import the data from the Access files directly into MySQL. But simply changing the ODBC configuration from pointing to the Access file to pointing to the MySQL database, transitioning was extremely simple. The only changes to the SQL queries had to change the format of date related fields. This is a process you would have to do if you were switching from Access to Microsoft’s SQL Server. The results were quite satisfying. Based on my results with the WildRocketry.com site, I will definitely be using MySQL for even larger and more ambitious projects.

Where do you get MySQL?

You can download MySQL directly from the MySQL website. It is currently available as a binary for most Unix systems and an installable for Windows. You will also want to get the ODBC drivers to get started with, although you can access MySQL directly through available APIs. The ODBC driver is called MyODBC and is available here. The cost of MySQL is a staggering zero as it is distributed through the GNU General Public License. Although you may use MySQL without a licensing fee, it is recommended that you get the support contract in case of any problems. Basic email support will run you about $200 per year.

Installing MySQL

Installation of MySQL is via a simple install program. Just follow the directions in the zip file, and you will be up and running in just a matter of minutes. The server will install as a service under Windows NT/2000 and an application under Win 9x. The same goes for the MyODBC drivers. At this point you have an operational database system up and running.

The first thing you will want to do is change the root password. The default installation does not have a password. Use the format of the following example:
mysqladmin -u root password your_password You are now ready to get started using MySQL!

Managing MySQL

Having worked in the web hosting business since 1995, I am no stranger to command line tools under different flavors of Unix. However, I am spoiled with the Enterprise Manager that comes with Microsoft SQL Server. Not that I really mind doing everything on a command line, but visual tools do make the job easier. So what’s a person to do with MySQL? I have looked at a number of Windows based management tools for MySQL and the one I keep going back to is simply titled DBTools. DBTools is available here and is also distributed for free. Working with DBTools is very similar to working with SQL Server’s Enterprise Manager. Once you create the connection to your MySQL server, you can easily manage the database, tables, and users.

DBTools also has a data import wizard to help migrate data from other data sources such as Access, Postgress, SQL Server, or other ODBC compliant database. Once you have your data in place, and a DSN created to point to the correct database and table. You can now access the data as you would from a normal ODBC connection.

Getting Started

So now we have our user set up, now it’s time to create a database table, populate some data, and try getting some results.

Create the database:

In the \mysql\bin directory you will find an application called winmysqladmin. Using winmysqladmin, you can view tons of information about your MySQL installation, you can also use it to create new databases.

For the purposes of this example, click on the Databases tab, right-click on the server name in the left hand window to create a new database. Name your database dbtest.

Setting up our test table:

You can use the command line tools to manage databases, tables, and users. I prefer using DBTools as described above. When you create a new user using DBTools, it gives you an easy interface to grant different permissions for each use to specific databases. The method for using the command line tool is given below those of you who like to do it “the hard way”.

Grant rights to our test user:

If you wish to use a different user account than root (and you should) then your next step would be to create the user account you want. You can either use the admin tools discussed later in this article, or simply do it from the command line as shown here with the fictional account bubba:

D:\mysql\bin>mysql -uroot -proot_password mysql> use mysql mysql> GRANT ALL PRIVILEGES ON *.* TO bubba@localhost IDENTIFIED BY ’some_pass’ WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO bubba@”%” IDENTIFIED BY ’some_pass’ WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;

This sets up our user Bubba; as a full superuser who can connect to the server from anywhere, but who must use a password some_pass to do so. Note that you must issue GRANT statements for both bubba@localhost and bubba@”%” to prevent the anonymous user created during the install will take precendence.

Create the table:

mysql -ububba dbtest mysql> create table linkdemo ( -> LinkName varchar(50), -> LinkURL varchar(150) -> ); Query OK, 0 rows affected (0.07 sec)Populate the table with some data:

Again, you can use either the command line tools or DBTools to import your data. To use DBTools, select the database table and use the SQL Query tool to run the following queries. From the command line, you can enter them by starting up the MySQL tool as follows:

D:\mysql\bin>mysql -ububba -psome_pass INSERT INTO LinkDemo (LinkName,LinkURL) VALUES (’Yahoo’,'http://www.yahoo.com’); INSERT INTO LinkDemo (LinkName,LinkURL) VALUES (’Google’,'http://www.google.com’); INSERT INTO LinkDemo (LinkName,LinkURL) VALUES (’Alta Vista’,'http://www.altavista.com’); INSERT INTO LinkDemo (LinkName,LinkURL) VALUES
(’InfoSeek’,'http://www.infoseek.com’); Next, create an ODBC connection to your table using the ODBC control panel. At this point your data is ready for you to access via and standard ODBC connection.

The following ASP code snippet was originally used with a Microsoft Access database and required no modifications to work with MySQL.

Set MyConn = Server.CreateObject(”ADODB.Connection”) MyConn.Open “dbTest” SQL_query = “SELECT * FROM Links;” Set RS = MyConn.Execute(SQL_query) WHILE NOT RS.EOF Response.write “” & rs(”LinkName”) & ”
” RS.MoveNext WendSo there you have it, a fully functioning SQL Database server populated with data and returning the data into and ASP page.

Summary

One further advantage of using MySQL is that it is available for multiple platforms. You may want to develop your website using Active Server Pages on a Windows NT/2000 platform but don’t want the financial hit of having to purchase a second copy on NT to run the database. By putting the MySQL database engine on an inexpensive Linux server, your cost savings could really add up. The same Windows based administration tools such as DBTools described above, will still connect to a Unix based MySQL server making administration identical regardless of platform.