MySQL for Local Databases
This article was published by ComputorEdge, issue #2842, , as a feature article, in both their PDF edition (on pages 9-25) and their website.
Word processor documents, spreadsheets, and plain text files are just some of the different types of files that are used for storing information. Even though files are the most common way to store data on a computer, they are certainly not optimal for storing data records so that they can be easily queried and modified. For such cases, the best option is to use a database.
If you would like to create a database on your own computer, there are a number of relational database management systems (RDBMSs) that can be set up and configured to run on a standard PC. Most of these options are free and open source. While they may not have as many advanced features as the proprietary RDBMSs, they are generally easier to set up and learn how to use. The favorite free RDBMS nowadays is MySQL.
The average computer user might assume that databases are far too complex to be ever understood by non-geeks. But, as you shall see, it is not that difficult to install a database server such as MySQL on your own computer. (This discussion assumes that the computer in question is running a modern version of Windows — in this case, XP. But the steps involved are roughly the same on any other operating system.)
Downloading MySQL
The first step in installing MySQL is to obtain the installation file from the MySQL downloads page. For this article, instead of using the very latest version, I will be using an earlier version, 5.0.51a, which is quite stable and commonly used by Web hosting companies — which may be an advantage should you decide to put your database on the Web, perhaps as a data store for a website.
Begin by going to the Web page for downloading the MySQL Community Server.
By default, Microsoft Windows is the selected platform. The page lists over half a dozen download options, so you should choose the one that matches your PC (32- or 64-bit) and what type of installation file you prefer (ZIP archive or MSI installer). Most people choose the "Windows (x86, 32-bit), MSI Installer" option. If the MSI Installer does not currently reside on your PC, then you can choose the ZIP file or download the MSI Installer for free from Microsoft.
Click the appropriate "Download" link. The MySQL website may request personal contact information, but that is not required. A subsequent dialog will allow you to specify a destination for the installation file on your PC.
Installing MySQL
After the installation file has finished downloading, open it by double-clicking it, which begins the MySQL Setup Wizard.
At the welcome dialog, click the "Next" button.
The MySQL Setup Wizard makes it possible for you to choose an installation directory on your PC, and whether to install the optional components. In the "Setup Type" dialog, you can pick from three types of setup: Typical, Complete, and Custom. Select the first one if you would like MySQL to be installed in the default directory ("C:\Program Files\MySQL\MySQL Server 5.0") and if you also want all three client programs to be installed (the Command-Line Shell, Command Line Utilities, and Server Instance Config), but none of the C language 'include' or library files. For most people, this setup type should be adequate. Alternatively, if you are a C programmer interested in examining MySQL's source code, then select a "Complete" setup to get all of the above.
In this article, I won't be using the default directory, and so I will choose the "Custom" option, and specify a different installation directory, "C:\_a\MySQL". Some computer users opt to install all programs in the default Windows directory, which should be "C:\Program Files" (unless you set it to a different directory). But using a non-default directory offers the advantage of clearly distinguishing user-installed programs from those already installed by Windows or forcibly installed by any software that does not allow you to specify an installation directory. The top-level directory name "_a" is concise, saving space in your Windows's PATH environment variable, and its underscore pushes it to the top of any directory listing that is sorted alphabetically by filename — thereby making it more visible.
After you have made all of these choices, click the "Next" button.
You should now be ready to install MySQL's files, so click the "Install" button.
During this process, you may see some MySQL Enterprise advertisement dialogs.
Feel free to immediately click through any such advertisement dialogs.
When the Setup Wizard has finished, you can — and should — configure your new database server. Click the "Finish" button.
Configuring MySQL
At the initial Server Instance Configuration Wizard dialog, click the "Next" button.
The configuration wizard allows you to opt for either a detailed configuration, which gives you more control, or a standard configuration. In this article, I will choose the former option (the default). Click the "Next" button.
At this point, you will need to specify how MySQL is going to be used on your computer: simply for development purposes, or as one of many server applications, or as an exclusively MySQL database server. For the sake of simplicity, you can choose the first option (the default). Click the "Next" button.
MySQL offers two different database storage engines: InnoDB and MyISAM. Only InnoDB supports transaction functionality (I won't get into the details of that here), but does so at a cost of somewhat inferior processing speed (i.e., performance). If you are unfamiliar with transactional operations, or sure that you will not be needing them for any of the database applications that you plan to use with this MySQL instance, then choose the third option, "Non-Transactional Database Only", which is what I will choose for this article. Yet if you suspect that your applications will be requiring transactions, then choose the second option, "Transactional Database Only". (This option does not preclude you from using the MyISAM engine.) If you are not yet certain as to your future needs for transactions, or you if think they will be limited, then you can select the first option, "Multifunctional Database" (the default). Bear in mind that the InnoDB engine does consume considerably more disk space.
Since your database is probably for learning purposes at this point, then it won't be hit by a sizable number of client programs running concurrently and thus need a proportionally larger number of active connections. So you can select the default option, "Decision Support (DSS)/OLAP", and then click the "Next" button.
The default values again are the best choices for MySQL's networking, namely, enabling TCP/IP networking and port number 3306. Ignore the option to enable strict mode, which is only applicable if you will be using your database with applications, such as a proprietary shopping cart whose SQL queries you cannot modify. Click the "Next" button.
Opt for the standard character set (the default).
Your MySQL server can be run by Windows as a service, and begun automatically when you boot up your computer. Those are the default and recommended settings. MySQL's binary directory — in this case, "C:\_a\MySQL\bin" — contains more than a dozen executable programs. The Configuration Wizard can add that directory to the Windows PATH environment variable, so the programs can be run on the command line without explicitly including the directory. Click the checkbox to choose that, and then click the "Next" button.
When you reach the security options dialog, enter a new root password twice. For security reasons, do not select the option to "Enable root access for remote machines". Also, do not create an anonymous account, since you should do everything with named accounts, for safety's sake. Record your chosen root password somewhere safe, and click the "Next" button.
The Configuration Wizard is now ready to apply the settings that you have indicated in the earlier dialogs. Click the "Execute" button.
If the configuration process does not get any errors, then you will be told that the configuration file was created, the MySQL service was installed and started, and your security settings have been applied.
Verifying MySQL
Now that you have successfully installed a new MySQL server, you should verify that at a minimum its basic operations work without error. Go to a Windows command-line prompt, change the directory to the MySQL binary directory (in our case, "C:\_a\MySQL\bin"), and start the MySQL monitor program, mysql.exe:
cd \_a\MySQL\bin mysql --password --user=root
It should prompt you for the password you had chosen during the configuration process. When you enter that password, you should see a welcome banner and a command prompt, "mysql>".
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.0.51a-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
You can then go into the MySQL database, and display a list of the tables contained therein:
mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 17 rows in set (0.00 sec) mysql> quit Bye
To gain insight into how MySQL works, consider exploring the structure of those tables.
At this point, you have installed and configured your MySQL database server successfully, and you can begin creating databases and everything that can be stored in them, including tables.
Online Help
To obtain further information about MySQL, check with the many resources available on the Web. In addition to all the books that focus on MySQL in considerable detail, be sure to explore the online MySQL Reference Manual. For most experienced database users, it is an excellent source of information. It contains over two dozen chapters and seven appendices, discussing installation, server administration, performance optimization, data types, functions, operators, stored procedures, triggers, and much more.
No matter what you plan on using local databases for, MySQL should prove to be an excellent choice.
