The MySQL database server is used by many web applications to store its data. For example, most of the free PHP content management systems (CMSes) and free PHP blog scripts use it to store the content of the website (or blog). Its use is not restricted to such scripts, of course, and it can be used to store data for web surveys, forums and so on.
Since it is so widely used, many webmasters have asked me how they can install it on their own Windows computer so that they can test a local copy of their scripts and website before uploading it "live" on the Internet. This article gives you a step-by-step guide for setting up MySQL on Windows Vista.
(If you are a new webmaster trying to find out what MySQL is, please see the article What is MySQL? What is a Database? What is SQL? instead.)
Before you start, please note a few things:
Unless you have a specific reason for wanting to install MySQL and integrate it with PHP yourself, the easier way is to use one of those software packages like XAMPP that bundles everything together. All you have to do is to install that bundle, and you will get a version that integrates Apache, MySQL and PHP out-of-the-box. If you don't mind that, please read How to Install and Configure Apache, PHP, Perl and MySQL on Windows the Easy Way (with XAMPP) instead. Otherwise, read on.
I am assuming in this article that you're installing MySQL so that you can use it to test a web application on your own computer. As such, you will probably need to install the Apache web server and a programming language like PHP. If you have not already done so, you may be interested in the guides listed below.
This article assumes that you have already installed Apache and PHP on your computer.
The guide is meant for installing MySQL on your own computer for the purpose of testing your own scripts offline. It is NOT meant to teach you how to install MySQL on a "live" web server that is connected to the Internet. A server that is connected to the Internet requires you to take security precautions to protect your computer. This tutorial ignores all that.
This guide is primarily written with Vista in mind, since the machine I tested this procedure on is running Vista. I don't know if the steps are any different on XP. It's possible that it's more or less the same, except that XP users won't get any of Vista's User Account Control, or UAC, warning prompts. (The way to invoke various Start menu or Control Panel items will also differ.)
Like all procedures, the exact steps may vary according to the version of the program you download. The version I used was 5.1.32, which was the latest at the time I checked the MySQL page. If you find that the dialog boxes and messages are different when you install, it may be because you have a later version. In such a case, use my procedure as a general guide to help you understand what to do, and if you have the time, drop me a note to let me know of any differences so that I can update this guide, if need be.
Before you begin, go to the MySQL download page, and click the link for the MySQL Community Edition (also known as "MySQL Community Server" elsewhere on the site). Look for the "Windows Essentials (x86)" package and download it. (Note: you may have to work a bit to find it: that is, scroll down pages, click links and whatnot.)
The installation itself is generally easy and fairly standard.
Double-click the MySQL installer that you downloaded. Read the information displayed in the install screens and click the necessary buttons (eg, "Next"). When you come to a screen entitled "Setup Type", leave the radio button selected at "Typical" and click the "Next" button. The installer should display a summary of its settings. Click the "Install" button to let it proceed with the installation.
Vista will issue a "User Account Control" dialog box, asking you for permission to let the installer do its job. Click "Continue".
You may then get two dialog boxes with advertisements. Just click "Next".
Finally, a "Wizard Completed" dialog box should appear. Uncheck the box "Configure the MySQL Server now" box. Don't worry, we'll do it manually from the Start menu ourselves. Then click the "Finish" button.
Now that MySQL has been installed on your computer, you will need to configure it.
Click the Start menu (the round button that has the Windows logo on the bottom left of your computer monitor). Type "MySQL" into the search field, and you should be able to see "MySQL Server Instance Config Wizard" highlighted at the top of the menu. Select it (if it's not already selected) and hit the ENTER key.
Vista's User Account Control will appear, asking you for permission to let the program run. Click "Continue".
The MySQL config wizard should appear. Click "Next" to go to the dialog box that lets you select the type of configuration you want. Select "Detailed Configuration" if it's not already chosen, then click "Next".
The next screen lets you select a server type. Since you're just using MySQL to test your scripts, and you will be running your editor, browser, etc, on the same computer, you should probably select the "Developer Machine". This way MySQL does not eat up a lot of your system memory, allowing your other programs to run smoothly. Click "Next".
Leave the default of "Multifunctional Database" selected in the window asking you about your "database usage", unless you know specifically how you're going to be using the database. Click "Next" when you're through deciding.
The next screen in the dialog box lets you select the drive where your database is to be stored. If you have no particular preference, leave it at the default and click "Next".
You will now need to set the number of concurrent connections that can be made to the MySQL server. In general, since you're only using the server to test your own scripts and you're the only one invoking those scripts, the default of "Decision Support (DSS)/OLAP" (20 connections) is probably more than enough. If this is not true, feel free to select one of the other options, or to manually set the maximum number of simultaneous connections. (Note: even if there are a few other people in your office or home helping you test your scripts, chances are that 20 connections is more than enough. If it isn't, you may need to rewrite your script so that it isn't quite so connection-intensive.) Click "Next" when you're through.
In the next screen, leave the "Enable TCP/IP Networking" option checked. By default, MySQL will listen to connections made through port 3306 on your computer. Unless you know for sure that port 3306 is being used by another program, just leave it at the default. If you don't know what I'm talking about, leave it at the default.
Tick the "Add firewall exception for this port". This will open up the port in Vista's firewall so that MySQL will be able to receive connection attempts made by other programs. If you use third-party firewalls or an antivirus program that includes a firewall, you will have to configure those firewalls to allow MySQL to receive connections yourself. (The exact method, unfortunately, differs from firewall to firewall, so I can't give a general procedure that works with every program.)
WARNING: making this "exception" in Vista's firewall effectively opens up a hole in your computer. If your computer is connected to a network or the Internet, other computers will also be able to connect to your MySQL server. This is the reason why many web hosts only allow machines on their own network (and not just any computer on the Internet) to access their MySQL server. I will describe a way how you can restrict the connections to your own network later in this article.
Leave "Enable Strict Mode" checked and click the "Next" button.
The next screen lets you set the default way your data is stored in your database.
If you're going to be using English, you can either leave the selection at "Standard Character Set" or choose "Best Support for Multilingualism". Otherwise, select "Best Support for Multilingualism". Alternatively, if you know what you're doing, check the "Manual Selected Default Character Set / Collation" and manually set the character set you want.
When you're finished, click "Next".
Make sure the "Install As Windows Service" option is checked in the next window that appears. Ignore the "Include Bin Directory in Windows PATH" (that is, leave it unticked). Click "Next".
In the screen that asks you for the root password for your database, enter the password you want to use. Leave the first field, which asks for the current password, blank. (By default, there is no password.) Then type in the same password in both the "New root password" and the "Confirm" fields.
Note that this is not the same as the password as the one you set for individual databases created for your scripts. This password gives you administrator access to everything, and lets you create users, grant user access rights and so on. If you're prone to forgetting passwords, you may want to write down the password somewhere.
(For those who are curious, in computer lingo, a "root" user is basically the most powerful user on a system. It's sometimes called the "superuser" or "Administrator". Such a user has the permissions to do anything, even delete everything. You should always set a password for the root user.)
Do not check the box "Enable root access from remote machines" unless you know what you're doing. Basically, as configured, the only way you can log in as "root" to your database is to do it from the computer you're installing MySQL on. If you tick that box, anyone connected to your machine (even over the network) will be able to log in as "root" if they know the password. If you do not check the box, even if your password is leaked, the hacker (or whatever) will need to have access to your computer to log in as root. Or at least that's the theory, anyway.
For security reasons, do NOT check the "Create An Anonymous Account" box.
Click "Next" when you're done with the passwords.
Click "Execute" in the next window to let the wizard configure MySQL. When it is done, click the "Finish" button.
By default, the MySQL configuration wizard opens a port in the Vista Firewall so that any computer on the Internet can access your MySQL server. Since this is just your test server, chances are that this is not what you want. You probably only want the scripts on your own computer, or perhaps on another computer on your intranet (your own office network or home network) to access it. This section describes how you can restrict access to port 3306 in the Vista Firewall to only machines in your own network. This does not mean that your server is now secure, but it should hopefully be slightly less vulnerable than the default set up by the wizard.
Open the Control Panel. You can do this from the Start menu. Click the "Classic View" line in the left column. In the right column, scroll down to locate "Windows Firewall" and doubleclick it.
Click the "Change settings" link in the Windows Firewall window. Vista's User Account Control dialog box will appear. Click "Continue".
In the Windows Firewall Settings dialog box that appears, click the "Exceptions" tab (near the top of the dialog box).
Scroll down the list in the "Program or port" box to locate "MySQL Server". Select it with one click, being careful not to uncheck the box. (If you accidentally unchecked it, just click it again to put the tick back.)
Click the "Properties" button. The "Edit a Port" dialog box appears. Click the "Change scope" button in that dialog box.
A new dialog box, "Change Scope", appears. The radio button "Any computer" should be currently selected. Click "My network (subnet) only" to select it. Click "OK".
When you return to the "Edit a Port" dialog box, click "OK" to dismiss it.
Finally, click "OK" in the "Windows Firewall Settings" to close it. (You will also probably want to close the Windows Firewall and Control Panel windows as well. Just click the "X" in the corner of those windows to get rid of them.)
Now that you have a MySQL server running on your machine, you will probably want to set up PHP so that your scripts can access databases on the server.
As mentioned earlier, I will assume that you have already set up PHP. If this is not true, please see my guide on this first.
Open up your php.ini file in Notepad. If you have followed the steps in my guide, you should already be familiar with how to
do this. The file itself can be found in c:\php\php.ini (unless you have installed it elsewhere).
Look for the following line.
Modify it so that it now looks like this:
If you used my guide to set up PHP before, "c:\php\ext" is where the extensions were installed. Those who installed PHP in a
different folder should of course put the correct path in the extension_dir setting.
Now search for the following line:
(Be careful. It's surrounded by a number of lines that look very similar. Be sure you get the line that has "php_mysql.dll" and not "php_msql.dll", "php_mssql.dll" or "php_mysqli.dll".)
Remove the initial semi-colon (";") so that the line now looks like this:
(A semi-colon indicates the start of a comment, which is ignored by the PHP interpreter. Removing it makes the line a configuration setting.)
If you did not previously set the session path in php.ini, either because you didn't need to use sessions previously
or because you read an earlier edition of my guide, please go back to the
PHP 5 install guide and set
the session path accordingly. (It's in the section entitled "Session Path".)
(You don't need to do this if you know for sure that your script does not use sessions. However, if you use third party scripts that need a MySQL database, it will probably need session support.)
Save the file and close Notepad.
Now start up Windows Explorer. That is, click the Start menu, followed by "Computer". A window should open. Look for "System properties" somewhere near the top of the window. Click it. Then click "Advanced system settings" in the left column. The Vista User Account Control should appear. Click "Continue".
In the "System Properties" dialog box that appears, click the "Environment Variables" button. In the list box under "System variables", look for the line that has "Path" in the first column. Click it to select it. Click the "Edit" button.
A dialog box entitled "Edit System Variable" appears. Click somewhere in the "Variable value" line and use your arrow key to move to the end of the line (or just use the "End" key on your keyboard). Append the following:
(Note: the above starts with a semi-colon and is followed by the directory/folder where you installed PHP.)
This adds "c:\php" to the "PATH" environment variable, which is basically a list of directories that Windows searches for when
it needs to look for a program or components of a program. You need to do this because PHP needs it to load a file called
libmysql.dll, located in that directory. For some reason, it doesn't seem to be able to do it if "c:\php" is not in the PATH
(even though it's the same directory as the PHP program itself).
Once you have done this, restart your computer.
After you reboot your computer, a simple PHP script like the following should be run to make sure that the PHP is able to load the MySQL libraries (DLLs).
Save the script as "test.php" in your "htdocs" directory and invoke it from your browser with "http://localhost/test.php". You should now be able to see a new section called "mysql" in the report. This means that PHP was successful in loading its MySQL-related libraries (the extension and libmysql.dll libraries).
Now that you've installed MySQL, you may also want to install phpMyAdmin, a well-known PHP script that lets you administer your MySQL database server from a web interface. You can then create users, set permissions, create databases and tables, etc, from your web browser. (This is, of course, optional.)
Otherwise, you now ready to write and test your own PHP/MySQL scripts.
Copyright © 2009-2018 by Christopher Heng. All rights reserved.
Get more free tips and articles like this,
on web design, promotion, revenue and scripting, from https://www.thesitewizard.com/.
Do you find this article useful? You can learn of new articles and scripts that are published on thesitewizard.com by subscribing to the RSS feed. Simply point your RSS feed reader or a browser that supports RSS feeds at https://www.thesitewizard.com/thesitewizard.xml. You can read more about how to subscribe to RSS site feeds from my RSS FAQ.
This article is copyrighted. Please do not reproduce or distribute this article in whole or part, in any form.
It will appear on your page as:
How to Install MySQL on Windows Vista and Configure PHP 5 to Use It
