Introduction

This article describes how to install, configure, test and deploy a simple database using MySQL running on a Windows NT (NT4, 2000, and XP), based machine. It is intended to provide the reader with a direct windows specific set of instructions on the installation, configuration and testing of a MySQL installation, as the supplied manual is verbose, and intermixes different operating systems, making it hard to find what you need right away, and perhaps a little intimidating for those not familiar with Linux or Unix.

MySQL is the world's most popular Open Source Database, designed for speed, power and precision in mission critical, heavy load use. MySQL is maintained by MySQL AB [1], the company is owned by the MySQL founders. MySQL is available free under the GNU General Public Licence (GPL). Commercial licences are sold to users who prefer not to be restricted by the GPL terms.

Obtaining MySQL

MySQL can be downloaded free (under the GPL), from www.mysql.com, this article deals with the Windows version found directly at: http://www.mysql.com/downloads/mysql-3.23.html, I suggest you download the installation files, and this article will assume you have done so.

Installing MySQL

Once downloaded, unzip the file to a directory, and run setup.exe. I suggest you accept the default options for installation, which will install all the required files to c:\mysql\. Once the installation program is has finished you’ will need to open a console window, and the change to the directory c:\mysql\bin, now it’s time to verify that you have correctly installed MySQL. Type mysqld –install, as shown below to install the server as a service:

Next we need to actually start the service for this use net start mysql as shown below:

If we later want to shutdown the service, it is simply net stop mysql:

Now would be a good time to set you computers PATH environment variable to include c:\mysql\bin, You can do this with the command line:

You might also want to add this to the end of your autoexec.bat (located in C:\) or use Control Panel - > System -> Environment Variables on Windows 2000/XP, to ensure the path remains set for next time you reboot you PC.

Testing the Installation

We can use the program mysqlshow to display the details of the databases in the server (do not forget the restart the service if you have just shut it down):

Which lists the databases in this server, or we can get the details of one specific database as so:

There are number of other useful facilities provided by mysqlshow, so use the command line mysqlshow --help to see what else is available. Congratulations, you have successfully installed MySQL.

Administering the MySQL

To administer the MySQL server we use the program mysqladmin, which allows you to perform general administration task on the MySQL server. Such tasks might be:

• Create a database.
• Delete a database.
• Change the admin password.
• Check the status of the server.
• Shutdown the server.

Try the following command line for a full list:

For a full list of available commands, they are all self-explanatory.

Securing the Database

By default MySQL allows all local users to logon to the MySQL server with full privileges, I suggest if you are going to deploy anything more than a toy application that you changes this immediately. To do so we use the command line tool mysql, which provides a command prompt from which we can send commands and SQL to the server. The process of removing the default access rights is shown below:

Here you can see we have used mysqladmin to tell the server to reload the user table, thus updating its list of allowable users. This is known as reloading the grants table, as the table “grants” users’ privileges.

Of course, I suggest you pick a more secure password than ‘secret’, for your server. The downside of this is of course the commands we have already learnt now need to be modified to run with your username and password, otherwise you will be seeing results like the following:

So we need to modify the command it as so (once again replace secret with you password):

Here we are connecting as the user “root” (the –uroot), with the password “secret” (-psecret), setting our active database to mysql.

Creating a Test Database

In this section, we will create a simple example database, and write some simple programs to connect to the MySQL server and query the database. To do this start up mysql, as so: 

We have now created a new database called ‘dev’, and made it the active database, in other words all the queries we run from now on will be against that database. We can then create our tables, and query the database using SQL, as we would for any other RDBMS. As SQL is beyond the scope of this article, we will just quickly create a few tables and enter some simple data.

I prefer to execute most queries as scripts, especially queries to create tables, indexes and views, populate the static tables or the main tables with test data, and to drop the database. This allows us to create and drop the database as part of an automated build process, making it easy to test the database, with confidence that we can easily restore it to a known, working state. These scripts can now be kept safely in a version control system. We can run a SQL script as follows:

Where create_tables.sql is the SQL script used to create the tables we need (the script is shown in full in the appendix). Note that we can also specify the database to use on the command line.

Connection to the MySQL Database using ODBC

For testing I prefer to use a simple scripting language, as such I use PERL [2] to test that the MySQL ODBC driver is correctly installed and working. The script, test_odbc.pl given in the appendix performs the test. Note that you do not have to use ODBC to connect to the database, MySQL comes with it’s own C++ library, and many open source languages/environments come with support built in.

Installing the MySQL ODBC Driver

I’ll assume you’ve downloaded the ODBC driver from the website, and have unzipped the installer, so now run setup.exe. Accept all the defaults, but do not enter a Data Source (just select close on this dialog box). ODBC is now installed, however to use it we will need to configure a DSN, so open up Control Panel, and find the Data Sources icon (Windows 2000/XP users will find in in Adminstrative Tools).

Now select Add, then select the MySQL driver, and enter the following values:

Windows DSN name:               MySQLDev

MySQL Host:                          localhost

MySQL Database:                   dev

User:                                        root

Password:                                secret

Once again swap secret for your password, and then leave the rest as they are, and click OK, and OK.

Running the Test script

Before we can run the test script we need to create some tables in our database, and populate them with some test data, so we will:

We are now ready to run test_odbc.pl, assuming you’ve chosen to use ActiveState’s PERL distribution we can run it as so:

Congratulations you’ve installed MySQL and configured the ODBC driver.

References

[1] MySQL AB – www.mysql.com

[2] Active State Perl – www.activestate.com

Appendix

Test Scripts; the following scripts used in the preceding examples. This first script is used to create a table called customers.

create_tables.sql

The following script can be used to drop our table, this removes the table from the database and will allow us to start over from scratch after we have filled our database with either bad data, or test data during development.

drop_tables.sql

populate_tables.sql

test_odbc.pl