Setting up Database Software

It isn't very difficult to set up MySQL on your own computer, so that you can complete the labs more easily. These installation notes are pretty Windows-centric, but if you are running Linux, you probably already know what to do. If you have any notes about installing MySQL for your system then please let me know.

The most important thing to know when setting up your own MySQL server is that MySQL's default storage engine doesn't support transactions or referential integrity. The default storage engine is MyISAM. What you want to do is to change the default storage engine to InnoDB, which supports both transactions and referential integrity. (The alternative is to specify the storage engine to use, each time you create a new table. But this gets ugly pretty quickly, and it isn't standard SQL syntax.)

If you are curious, you can read more about the different storage engines in MySQL in the manual.

You can go to this page to download the MySQL 5.0 Community Server. You should get the latest 5.0 release (currently this is 5.0.27). Open-source database systems, MySQL in particular, continually update the SQL syntax that they support, so if you happen to be using an older version, you might not be able to do everything required on the assignments.

Choose your poison:

• Windows
• MacOS/X
• Linux

Windows setup should be pretty simple. Download the "Windows (x86) ZIP/Setup.EXE" package. The .zip file will contain a single file, setup.exe, which you can extract and then run.

• The Typical installation is fine for our needs.
• Skip the MySQL.com account signup.
• Make sure that the "Configure the MySQL Server now" option is checked! You can use this to set up the best defaults for your assignments.

Once the installer is completed, the "MySQL Server Instance Configuration Wizard should start up. Choose the "Reconfigure Instance" option, then hit the "Next" button.

• Choose the "Detailed Configuration" option, then hit Next.
• Choose the "Developer Machine" option, then hit Next.
• Choose the "Transactional Database Only" option. This is very important, since the default MySQL storage engine does not use either transactions or referential integrity. We want these options enabled by default, for the purposes of this course.
• On the "InnoDB Tablespace Settings" page, just hit Next; you don't need to tinker with any of these settings.
• Select the "Decision Support (DSS)/OLAP" option, then hit Next.
• Disable TCP/IP networking, so that only local connections are allowed to your MySQL database. This will minimize the potential for system break-ins. After this, hit Next.
• The Standard Character Set option is fine. Hit Next.
• Make sure that both the "Install As Windows Service" and "Include Bin Directory in Windows PATH" options are selected. The first option ensures that you won't have to manually start the MySQL database server yourself. The second option ensures that you won't have to manually edit the path to use MySQL from the command-line.
• You can deselect the "Modify Security Settings" option, to leave the MySQL "root" account password-less. Or, if you are more cautious, you can specify a root password. Obviously, having a root database user without a password is only safe if you disable remote network access!
• After all of this configuration, the MySQL server should be set up for you to use from the command prompt. The server is installed as a Windows service that will start automatically when your computer boots up, so you don't need to manually start the server.

Once you have completed the installation, test your MySQL database server by starting a Windows Command-Prompt, and trying the following. (Note that if you gave the root database user a password, you will have to type mysql -u root -p instead of what is given below.)

    C:\>mysql -u root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3 to server version: 5.0.27-community-nt

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | test               |
    +--------------------+
    3 rows in set (0.03 sec)

    mysql> show variables;

This should print out a large number of internal configuration variables. Make sure that the storage_engine variable in particular says "InnoDB"; if it doesn't, you need to reconfigure your database server.

Finally:

    mysql> \q
    Bye

    C:\>
  

And that should be that!

The MySQL website actually provides MacOS/X installation files, which should greatly simplify installation. Choose the "Standard" installation package that is appropriate for your version of MacOS/X, and follow the installation instructions.

This page in the MySQL 5.0 Manual describes how to set up MySQL. Note that the MySQL installer doesn't know how to upgrade a previous installation of MySQL. If you are already using an older version of MySQL, you may need to take additional steps to get everything to work properly.

Once you have installed MySQL 5.0 onto your computer, you need to change the default storage-engine from MyISAM to InnoDB. This can probably be done by finding and editing the MySQL my.cnf file for your installation; however, I don't have a Mac, so I can't tell you where that file lives, or if there's an easier way of doing it.

You need to find the default-storage-engine option (or, alternatively, the default-table-type option), and set it to INNODB. That way you don't have to type extra stuff when you create tables.

Have fun. If you have more information on this, let me know.

Your options for installing MySQL 5.0 boil down to building and installing from source, or using your distribution's package management tools. Either way, have fun with that.

MySQL will have a my.cnf configuration file somewhere, with the default configuration options for that server installation. You should find the default-storage-engine option (or, alternatively, the default-table-type option), and set it to INNODB. That way you don't have to use crazy nonstandard SQL syntax to create tables.