Using MySQL under Windows NTBy Kerry Garrison
|Running mySQL & Chili!Soft on a Cobalt Server|
|If you are interested in running ASP pages on a non-Microsoft platform/Web server, be sure to read another article of mine: Does The Cobalt Live up to its Claim?! That article details how to setup a Cobalt server and run ASP pages via Chili!Soft's Chili!ASP!|
What is MySQL?
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 http://www.wildrocketry.com. 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 (http://www.mysql.com). 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 at http://www.mysql.com/downloads/api-myodbc.html. 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.
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:
You are now ready to get started using 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 at http://dbtools.vila.bol.com.br/ 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.
In Part 2 we'll look at how to perform common database tasks using mySQL!