Installing MySQL and phpMyAdmin on a local Windows-based, Apache, server

• February 24, 2006
• James Skemp
• software, tutorials / guides

MySQL will allow us to create databases on our local server. With PHP, this will allow us to install applications like the free WordPress, as well as number of open source content management systems, not to mention bulletin boards and the like. phpMyAdmin will allow us to administer MySQL quite easily.

MySQL

While MySQL 5.0 is the recommend version, we'll be downloading the most current 4.x release – at the time of this writing – 4.1.18. Head over to http://dev.mysql.com/downloads/, and under MySQL Community Edition, you'll find an Older Releases heading. Download the Windows Essentials (x86), at a whopping 16.5 MB.

Once the download has completed, open the MSI file. When you're prompted on what kind of setup you'd like to use, select Custom. Use the default options, however, and note where MySQL will be installed to. Continue along until you've installed MySQL. Once you have, you'll be prompted to sign-up to MySQL.com, login, or skip sign-up. Honestly, I recommend you go ahead and sign-up for an account, since there's no reason not to. However, you can simply skip this step if you'd like to. Finally, before you Finish, you'll want to verify that the checkbox next to "Configure the MySQL Server now" is checked (it should be).

On the Configuration Wizard, select Detailed Configuration. Then Developer Machine. Then Multifunctional Database. For InnoDB Tablespace Settings, we'll leave things as they are (Installation Path). We'll leave the next setting as it is, since we're only interested in development (Decision Support (DSS)/OLAP). We'll also leave the next setting as it is – Port Number 3306. We'll change MySQL character set to the Best Support For Multilingualism option.

Since we want MySQL to show up in the Service control panel, leave the first option checked in the next window. We'll also want to check the second box, in case we want to troubleshoot our installation using the command line.

Finally, create a password for the root account. Make sure you write this password down somewhere safe. Keep the checkbox unchecked, since we only want our machine to be able to access the root. Now, hit Execute and Finish once it's started.

If we now open up the Services control panel, we'll find a MySQL item, which, like Apache, can be started, restarted, and stopped. While you're in the Services panel, restart Apache.

phpMyAdmin

For phpMyAdmin, we'll be happy with the latest stable release. As of the time of this writing, that's phpMyAdmin 2.7.0-pl2. Download the zip version, which is about 3.5 MB.

Once the download has completed, unzip the contents of this file to our main server directory – C:\home\. Ultimately, it's your call whether you keep the folder with the version information, or whether you rename the folder to just phpMyAdmin.

Once you've unzipped the contents, we'll want to create an easy way to access the folder. To do this, we'll setup a subdomain under localhost. While you could call the folder up using http://localhost/phpMyAdmin-2.7.0-pl2/, or whatever you named the folder, if we use a VirtualHost entry, we'll make things easier in the future (if we want to upgrade, we can continue to have older versions available when we have newer versions). Now, setup http://phpMyAdmin.localhost/. Once you've finished, or if you need help, come back to this guide – we'll walk through this in the next paragraph.

First, we'll open up the httpd.conf file. Since we've already got at least two other instances of how we'll need to set this up, simply copy the last one. Next, change the values accordingly. For ease, I did the below.

<VirtualHost 127.0.0.4>
ServerName phpMyAdmin.localhost
DocumentRoot C:\home\phpMyAdmin-2.7.0-pl2
ErrorLog logs/phpMyAdmin-error.log
TransferLog logs/phpMyAdmin-access.log
</VirtualHost>

Save and close httpd.conf. Next, open the hosts file (in the C:\WINDOWS\system32\drivers\etc\ folder) and insert the appropriate line at the end of the file.

127.0.0.4 phpMyAdmin.localhost

Again, save and close this file. Finally, restart Apache, and head over to http://phpMyAdmin.localhost/. The first thing you'll notice is that you get an error message. If we look, we see this is because the password isn't correct. If we go looking for the config.inc.php file, however, we won't be able to find it in our phpMyAdmin directory. If we look at the documentation, we'll find that we'll have to create one.

So, copy the config.default.php file and open it in WordPad (as you may lose formatting in Notepad). You can open WordPad easily by pressing the Window + R key, and typing 'write' (no quotes) in the prompt. Now look for a line that starts with $cfg['Servers'][$i]['password'], and enter the root password here. Now, save this file, and close out of WordPad. Finally, rename the file (Copy of config.default.php) to config.inc.php.

If we were to try loading http://phpMyAdmin.localhost/ again, we'd still have connection problems. Unfortunately, this is because the password we entered in the installer isn't encoded quite correctly, for whatever reason. This means we'll have to do some extra work, at the command line.

Now, type Windows + R once again, and type 'cmd' (no quotes) to bring up the command line interface. Now, type

mysql -u root -p

at the command line. What we'll be doing is changing the password that we just setup. We need to do this for a number of reasons, unfortunately. When it prompts you for a password, type the password you used at setup, and above, once again. Next, type the following, where your_password is your password.

UPDATE mysql.user SET Password = OLD_PASSWORD('your_password') WHERE Host = 'localhost' AND User = 'root';

Once you run this command (by pressing enter) you'll have updated your password. Type exit, press enter, and type exit and press enter once again (the first time to exit out of MySQL, the second time to exit out of the command prompt). Now, restart MySQL using the Services control panel.

If we load http://phpMyAdmin.localhost/ again, we'll now be logged in. However, we'll notice that the images are not loading correctly. So, let's open up config.inc.php once again, and change some values.

First, find the line that begins with $cfg['PmaAbsoluteUri'] and change it's value to http://phpMyAdmin.localhost/ (include the trailing slash). Next, look for the line that starts with $cfg['Servers'][$i]['auth_type'], and change config to http. Save the file and reload http://phpMyAdmin.localhost/. Once we do that, we'll be prompted for a username (root) and a password (whatever your password happens to be).

Finally, open Notepad and insert the following text. Make sure you change your_password accordingly.

<?php
$user="root";
$password="your_password";
mysql_connect(localhost,$user,$password) or die ("Can't connect to MySQL.");
echo "Connected to MySQL.";
mysql_close();
?>

Save the file as mysqlconnection.php, in the C:\home\ folder. Now, load http://localhost/mysqlconnection.php. If you see "Connected to MySQL.", then PHP can now access MySQL. Otherwise, you'll see "Can't connect to MySQL.", and you'll need to review the above once again. If you want, you can Stop MySQL via the Services panel and load this page to see what happens.

One last thing we'll need to clear up is a mbstring error in phpMyAdmin. To fix this, we'll open up the php.ini file, which is at c:\windows\php.ini (or you may have a shortcut stashed away somewhere). Do a search for "php_mbstring.dll" (without the quotes) and remove the ';' on this line. Save this file and close it. Now, restart Apache. Once you do so, and reload http://phpMyAdmin.localhost, you'll notice that the error is gone.

Now that you've setup MySQL and phpMyAdmin, you may want to head over to Analog and add the new log file. You'll notice some new status codes in the Report.html, if you do so. However, we'll be dumping Analog pretty soon, so don't worry if you don't.

Next time, we'll be installing MySQL Administrator, so we can setup some additional users and databases easily. For now, stay clear of using the root user to do much of anything. We'll create some user accounts with limited access, next time.

View all of the steps to creating a local Web server, for development.

• mysql
• phpmyadmin