MySQL 5.x Installation on Windows

A website intended to do anything beyond serving as an online brochure, is going to need some way of storing data that could change at any time: blog posts, staff login credentials, e-commerce transactions, etc. This could be accomplished with a file-based data storage system such as SQLite, but it is far more commonly done with a full relational database management system (RDBMS). In the realm of open-source software, the most popular RDBMS is MySQL, at one time owned and supported by Sun Microsystems, and now currently under the purview of Oracle.

Database developers are accustomed to utilizing features such as stored procedures, triggers, views, cursors, sub-SELECTs, and ACID transactions. MySQL supports all of these capabilities, and much more.

Starting with a Clean Slate

Prior to installing MySQL on any computer, you should first see if an older version has already been installed and, optionally, is already running (as a Windows service in the background). Check for the latter possibility first, because if MySQL was added to the computer using the standard installer process, then the uninstaller process can stop the service for you. Otherwise, you will need to manually stop the service before uninstalling its underlying files. Go to "Control Panel" > "Administrative Tools" > "Services", which lists alphabetically all of the enabled Windows services, whether they are running (with a Status value of "Started") or not.

Even if no MySQL service is enabled (illustrated in the figure above), it is still possible that a MySQL instance has been installed somewhere on the computer. Check in the places where you usually install applications. In the case of Windows 7, that might include the directories C:\Program Files and C:\Program Files (x86). To be more thorough, search the computer's hard drives in their entirety for any files named mysql.exe — using the native Windows file searching utility or a third-party alternative.

If one or more instances of MySQL are discovered, then you should check if any of them are recent enough to serve your current and near-term needs. To learn the version number of any MySQL instance, open a Windows terminal, go into the bin subdirectory of the instance, and run the command mysql --version.

If you discover any instances that are not worth keeping, then you should uninstall them completely. For each one, look for any existing databases associated with the instances. They are usually located in its data subdirectory. If you find any, then it is recommended that you export them, as SQL files, or at least save all of their directories and files, in case you later need to reinstate those databases in your new MySQL server, or at least reference their values and table structures if the need arises.

Then check if any MySQL instances are registered as programs installed in the conventional manner. If so, they will be listed in "Control Panel" > "Programs and Features". Click on any such entry, and click the "Uninstall" link that is then displayed. Also, be aware that the standard uninstallation process can leave behind custom files, including your old MySQL server admin password, somewhere in the directory data\mysql, wherever that might be located. This can prevent you from applying security settings when installing the latest version, because you cannot login as root, and it will result in the dreaded Error 1045. Thus, you should delete any leftover files and directories, after first setting aside any information worth saving. Some tutorials claim that, after installation, you can copy the old database directory data\mysql into the new databases directory, thereby allowing yourself to login with your old password. This may be true, but I have not confirmed it, and it will likely fail if there have been password encryption or table schema changes between versions.

For any instance for which there is no entry in the list of installed programs, then it was not installed by running an MSI file (as I will do below), but rather by opening a Zip archive file. In that case, deleting the installation directory and all of its contents, should be sufficient to purge it from the computer.

Downloading MySQL

There are several editions of MySQL available. The Community Edition is free and does not include technical support from Oracle. The Enterprise Edition entails license fees and is intended for use by organizations with budgets and dedicated database administrators. In this tutorial, I will use the Community Edition.

Visit the MySQL Community Server download page.

Click the MySQL Installer banner image for the most recent stable version, which is 5.6.10 as of this writing.

Beginning with version 5.6, Oracle is no longer offering separate MSI packages for the multiple Windows platforms — in other words, 32-bit and 64-bit — but instead has combined them into a single MySQL Installer package. The page has a note to the visitor: "MySQL Installer is 32 bit, but will install both 32 bit and 64 bit binaries." This presumably means that the installation file one downloads is a 32-bit executable, but can install either the 32-bit or 64-bit binaries. With each release, MySQL gets bigger, and this new installation package is larger than 170 megabytes.

Obtain the installation file by clicking the blue "Download" button, and saving the file somewhere on the computer where you can easily find it later.

Installing MySQL

Run the MySQL installer executable file that you had downloaded. You may first see several dialogs as Windows begins the installer. Note that these modal dialogs can end up behind the Windows Explorer window (that you presumably used for starting the installation process), making it appear that the process has frozen. Eventually you will reach the welcome dialog.

Click the first link to "Install MySQL Products", which ushers you into a sadly typical thicket of legalese.

When you click the "I accept the license terms" checkbox, the "Next" button is enabled. Click it.

On the resultant screen, you can check for and download a newer version of MySQL (than what is contained in the installer) or download updates to any MySQL instance already on the computer. In our case, neither option is applicable, so I will use the "Skip the check for updates" checkbox, and click the "Next" button (which replaces the initial "Execute" button).

There are five setup types from which to choose: Developer Default, Server only, Client only, Full, and Custom. To learn the details of each option, click the radio button for the option and read the description to the right. Here I will opt for the server only.

The dialog shows that the default "Installation Path" is C:\Program Files\MySQL\, which is misleading because the installation files would actually end up in C:\_a\MySQL\MySQL Server 5.6, not C:\Program Files\MySQL. In this article, I will specify C:\_a, so the installation path becomes C:\_a\MySQL Server 5.6. I use C:\_a instead of C:\Program Files as a different directory for all self-installed programs because it allows me to quickly distinguish them from those programs already installed by Windows 7, programs installed surreptitiously, and programs that do not allow me to change their default installation paths. The directory name _a is short, saving space in the Windows Path environment variable, and the underscore forces it to the top of any directory listing of C:\ that is sorted alphabetically by filename, thereby making _a more visible.

The dialog also shows that the default "Data Path" is C:\Program Files\MySQL\MySQL Server 5.6\, which is misleading because the individual database subdirectories instead would be put in C:\Program Files\MySQL\MySQL Server 5.6\data. I will specify D:\_m\computer\MySQL. As a result, in that directory both the configuration file my.ini and the data subdirectory will be created automatically. The installer will warn you if the "Data Path" already exists, as illustrated in the screenshot below. I am using the D: drive, to keep the data stores on a separate and larger partition than that of Windows 7 — thereby allowing more focused disk imaging of the operating system.

Do not bother changing these two directory values until you have finalized the setup type, because if you change the setup type (to read its description, for instance), then the new directory choice(s) you made are wiped out, and you have to set them again.

In this case, there are no further requisite components that need to be installed.

I am now ready to begin installation.

Click the "Execute" button. The green progress bar indicates the extent of completion.

If all goes well, the status column will indicate success.

The next screen begins the configuration, which, for MySQL, is usually the most problematic phase.

The configuration process is initially divided into three steps, indicated by the "1 / 3" in the upper right of the dialog.

In the first step, you can specify the server configuration type, whose values and descriptions are listed in the select box. I will use the default, "Development Machine", to minimize memory usage. For the TCP/IP networking options, it is probably best to use the recommended values. To see the advanced configuration options available, click the "Show Advanced Options" checkbox before clicking the "Next" button. Note that the number of steps changes to four ("1 / 4").

In the second step, you must set a password for the root account. Since this is a fresh installation, no value is entered in the "Current Root Password" field. Optionally, you may add database users, which I will not do.

In the third step, you can change the default Windows service name ("MySQL56"), elect to not have the server begin on Windows startup, and have the server run under a user account different from the standard system one. In our case, the only change is to remove the version number from the service name, since it is quite unlikely you will be running multiple MySQL servers simultaneously.

The optional fourth and final configuration step is only encountered if one had earlier requested to see the advanced options. Here one can change the error logging options, which I will leave as is.

The configuration will complete, but it may result in a warning message, displayed in yellow on a white background, and thus difficult to read. In this case, the message reads "Last Warning: Failed to add firewall rule". Clicking the "Show Details" button reveals the same warning amid other log messages, but no information as to what caused the firewall failure or how to resolve it.

In fact, since it is a warning but not an error, it is not clear if it will cause problems later. After all, if the rule is to allow bypassing the firewall, then it may be of no consequence for local development since I do not want the MySQL server sending or receiving anything over the Internet. It is possible that the firewall rule could not be set as a result of not explicitly running the installer as an administrator, but usually Windows 7 helpfully prompts for administrator privileges if needed.

The last dialog indicates that the installation process is done.

If you encounter any problems during the installation, note that all errors and warnings are logged in the file X.err, located in the data subdirectory specified during the installation process, where X is the Windows name of your computer ("Control Panel" > "System").

Verifying MySQL

Using any file management utility, such as Windows Explorer, go to the MySQL installation directory and confirm that all the directories and files are present.

If you right-click on the subdirectory "MySQL Server 5.6", and choose "Properties" from the context menu, it should report that there are 310 files in 38 folders, unless earlier you specified different components than what were chosen in this article.

Go to the interface listing all the Windows services ("Control Panel" > "Administrative Tools" > "Services"), and confirm that MySQL is installed and has been successfully started.

Open a Windows terminal, go to the MySQL binaries directory (in our case, C:\_a\MySQL Server 5.6\bin), and execute the command to view the MySQL version number (mysql --version) and the command to login to the server (mysql --password --user=root), which will prompt you for the password you had assigned earlier. Then change the current database to mysql — the only one that exists after a regular fresh installation — with the command use mysql;. List all of the tables in that database, with show tables;. To exit the MySQL monitor program, simply use the command quit.

MySQL has been successfully installed, and you can now begin adding your own databases, populating them with tables and data, creating users and granting them privileges, and anything else possible in a fully-functional RDBMS.