TechNote 3: WordPress Install – MySQL 5.1.x Setup

Introduction:

TechNote 3 addresses the setup of MySQL 5.1 Database Server on an XP Pro SP3 desktop environment. It has been extremely difficult to find data on setting up WordPress for my development environment (versus a Host server); except as regards MySQL. In this case, the data has been so voluminous I had a hard time keeping track of it all.

These notes were made during the install process, however I must note that this work was based on input from a number of different sources on the subject. Though the full list is in TechNote: 0, following are the list of key references that I originally used in my research. As appropriate in the text, I’ve indicated where I’ve taken direct quotes.

1. http://dev.mysql.com/doc/ — the most complete document is “refman-5.1-maria-en.pdf
2. http://dev.mysql.com/tech-resources/articles/ — articles on architecture, etc.
3. http://www.iisadmin.co.uk – Note that these install notes are geared for Server 2003, IIS6
4. http://dpotter.net/Technical/ — Again, these are geared towards Server 2003, IIS6 but they are invaluable nonetheless

In addition to describing basic mechanics, I have  added “reasons why” I chose the settings I did. However. Your mileage may vary. Use these as guidelines not as absolute “rules of the road”.

Prerequisites:

See TechNote: 0-WordPress Install Sequence On XP Pro plus Reference Links

My Development Environment:

See TechNote: 0-WordPress Install Sequence On XP Pro plus Reference Links

VIRUS SCAN Caution:

“Using virus scanning software such as Norton/Symantec Anti-Virus on directories containing MySQL data and temporary tables can cause issues, both in terms of the performance of MySQL and the virus-scanning software mis-identifying the contents of the files as containing spam. This is because of the fingerprinting mechanism used by the virus scanning software, and the way in which MySQL rapidly updates different files, which may be identified as a potential security risk.

“After installing MySQL Server, it is recommended that you disable virus scanning on the main directory (datadir) being used to store your MySQL table data. There is usually a system built into the virus scanning software to allow certain directories to be specifically ignored during virus scanning.

“In addition, by default, MySQL creates temporary files in the standard Windows temporary directory. To prevent the temporary files also being scanned, you should configure a separate temporary directory for MySQL temporary files and add this to the virus scanning exclusion list. To do this, add a configuration option for the tmpdir parameter to your my.ini configuration file. For more information, see ….” Ref: “refman-5.1-maria-en.pdf” from http://dev.mysql.com

Note: I personally have not run into this issue but as you may I’ve kept the caution.

Trademark Note: the inclusion of screenshots, in this or other documents without a Trademark ™ symbol by my “FromTheRanks” Blog-site Title or Logo does not imply these are not Trademarked. They are. These are early in-development screenshots taken before the ™ was added.

Installing MySQL 5.1.x

Homework:

Before beginning the installation I suggest you study Chapter 2 of the above referenced Reference Manual. There are a number of key questions you need to investigate and answer before beginning:

1. My installs are geared towards Windows XP Pro Sp3. If you are running any other O/S, determine if it will run on your platform – most are acceptable but verify yours.
   
   
2. Choose which version.
   1. Current is 5.1.x This is my choice.
   2. There is a 6.0 but it is still in beta/testing. Go for the General Availability (Production) version. It will be far more stable.
      
      
3. Choose a distribution format:
   1. Binary – this is far easier to install and unless you have an overwhelming urge to dig into source code I would stick with it.
   2. Source Distribution – this is the “guru level” version and unless you have a lot of time on your hands and/or plan on doing database level debugging I wouldn’t recommend it.
      
      
4. Choose Download type (ref: http://dev.mysql.com/downloads/ ):
   1. MySQL Community Server – This is my choice, to start with. It will allow me to get it up and running without the initial investment – I’m doing my first site on the cheap as I’m trying to keep the cost of my learning curve as low as possible. I also have a fairly strong background in database design, security, monitoring and so forth.
   2. MySQL Enterprise Server – if you don’t have a lot of experience in DB’s then I recommend this route as you’ll get a lot more “hands on” support than with the Community Server version. $599 (at the time of this writing) is dirt cheap for a good solid database server. You can’t beat it anywhere else that I know of.
      
      
5. Download the correct version for your environment (Windows, Linux, etc.) and the correct Installation Package:
   1. Essentials Package – this is the bare-bones version. At the moment, I can’t think of a reason why anyone would use this but I’m sure there is one. It comes as an automated installer (using an .MSI).
   2. Complete Package – this was my choice as it has everything I will (eventually) need. It comes as a zip file, which, when unzipped includes the automated installer .MSI.
   3. Noinstall Archive – this requires manual installation and manual configuration and is recommended for pro’s only.
      
      
6. Next, read up on Installing MySQL on your environment. In my case, its Windows, a.k.a. XP so I went through Chapter 2.3 “Installing MySQL on Windows”. Included here is:
   1. Running MySQL as a service
   2. TCP/IP protocol (named pipes can be used but is not preferred)
   3. ODBC Connector for MySQL
   4. ADO.Net Connector for MySQL

Okay. Here we go. As I’m taking these screen snapshots as I go, there may be later “tweaks” that aren’t reflected visually. Where this has occurred (later on) you will find a note below the screenshot.

SETUP Wizard Options:

Here’s where I started.  Double click on the .MSI to start up the Wizard.

Note: I do not load anything into my C: drive that I am not forced at gun-point to add. In my mind, that is for O/S level installs, only. As I have multiple drives, everything else goes elsewhere.

MySQL 5.1 Install Start Location - Click on image to enlarge

Here we go:

MySQL Setup Wizard - Click on image to enlarge

Setup Type:

MySQL Wizard Setup Type - Click on image to enlarge

I’ve chosen “Custom”.   I never do Typical installs on anything.   I want to know where it is going and what is being installed.

Custom Setup:

MySQL Wizard Custom Setup 1 - Click on image to enlarge

I tweaked these settings, by adding the developer components and changing the install location:

MySQL Wizard Custom Setup 2 - Click on image to enlarge

Note:  I chose to not install this under my project directory (..fromtheranks…) because MySQL is a server not just a single database engine and so can be used for multiple projects. Whether you put it all on your C: drive as recommended, or not, you need to be aware of this following:

“MySQL clients and utilities such as the mysql and mysqldump command-line clients are not able to locate the my.ini file located in the server installation directory. To configure the client and utility applications, create a new my.ini file in the Windows installation directory (for example, C:WINDOWS).” Ref: “refman-5.1-maria-en.pdf” Chapter 2.3

Ready to Install:

MySQL Wizard Ready To Install - Click on image to enlarge

Even with my tweaks, the installer is going to dump some stuff on the C: drive (grumble, grumble) but the bulk of the install will not clog up my primary drive, leaving room for future Microsoft upgrades:

Install Process:

MySQL Wizard Install Display 1 - Click on image to enlarge

The initial install process only takes a couple of minutes.  When done, you end up here.  The “More…” button takes you to some more advertising and info.  Click “Next”. And then here for the Configuration part:

MySQL Wizard Install Display 2 - Click on image to enlarge

MySQL Server Configuration:

Configuration Type:

MySQL Wizard Configuration Type - Click on image to enlarge

I selected Detailed Configuration option as it is meant for advanced users who want more fine-grained control over server configuration The next config item is one of the reasons why.

Server Instance Configuration:

MySQL Wizard Server Configuration - Click on image to enlarge

I’ve selected Developer Machine for my server as I have a number of other desktop applications running. Per the install doc, this instance is configured to use minimal system resources. As a note, this implies that it may be slower than on a full blown web server, but that’s generally okay for development.

Database Usage:

MySQL Wizard Database Configuration - Click on image to enlarge

This option I “dithered” on a bit, I wasn’t sure how to set it, and then did some research into the effective differences. Finally, for now, I selected Multifunctional Database as this choice enables “both the InnoDB and MyISAM storage engines and divides resources evenly between the two”. There is a three part article at http://dev.mysql.com/tech-resources/articles that discusses the difference between these two database types. I haven’t found a lot that gets into when/when not to use a given type, yet, so, for now, I’m installing both and I’ll play.

InnoDB Tablespace Settings:

MySQL Wizrd InnoDB Configuration - Click on image to enlarge

You can put the InnoDB files just about anywhere you want. I kept them on the same drive and folder as I set up above, though I added a “…Data” directory. This makes it easier to backup everything. Note: be careful entering the path, it is safer to browse to it than to type it in.

Concurrent Connections:

MySQL Wizard Connections Configuration - Click on image to enlarge

This was an easy choice: Manual Setting @ 10 connections. This is (a) a development box so there won’t be a lot of connections (thus no need to chew up resources) and (b) my “server” is XP Pro running IIS 5.1 and IIS 5.1 only allows 10 simultaneous connections, and one web site. This tidbit came from Wikipedia:

“IIS 5.1 for Windows XP Professional. Windows XP has a restricted version of IIS 5.1 that supports only 10 simultaneous connections and a single web site.” – Wikipedia

Networking Options:

MySQL Wizard Networking Configuration - Click on image to enlarge

This configuration window is a little more complicated than the other settings. I gladly took the defaults save that I may very well change the Port Number later on. As a rule, accept these defaults. And, especially, accept Enable Strict Mode – you don’t want to be allowed to “get sloppy” on your dev box, and then find that your host doesn’t allow some of your “ooop’s”.

Default Character Set:

MySQL Wizard Character Set Configuration - Click on image to enlarge

I almost set this to Standard Character Set, but, then, I began thinking that I’m not sure who from where will be on my web-site in the future so to ensure they aren’t excluded any more than my inability to write in any other language than English (some might say American), I’ve set it to Best Support For Multilingualism.

Windows Options:

MySQL Wizard Windows Options - Click on image to enlarge

A couple of notes are warranted here:

1. You want to Include (MySQL’s) Bin Directory in Windows PATH – if you don’t do it here you will have to add it manually later, so do it here.
2. You want to install MySQL Server as a service. This is a good thing.
3. You do not, or at least I did not, want to launch MySQL Server automatically. Why? I don’t figure to be developing my web-site(s) 24/7 and it will eat up resources.

   The only downside to not launching it automatically is that each time I do want to work on the site I will have to remember to go into Services and enable it.

   By the way, there are three ways to do so that are equivalent:

   1. Run –> enter: “Services.msc”
   2. Start –> Control Panel –> Administrative Tools –> Services or Component Services –> Services
   3. Start –> Programs –> Administrative Tools –> Services or Component Services –> Services

Security Options:

MySQL Wizard Security Settings - Click on image to enlarge

Though I filled it in during my install, I have left the root password blank on this screen-shot for obvious reasons.  I have kept “Create An Anonymous Account” unchecked for also obvious reasons – the same ones described on the window.  Having an anonymous account is a really really really bad idea.

Note 1:  the account name (for future reference) is indeed ‘root’.

Note 2:  do NOT forget your ‘root’ password!

As a final note, in the production environment on your web host’s site, you will most likely NOT have ‘root’ access to your MySQL database. They will setup an account and password for you that gives you full access to YOUR WordPress database, only. This is a good reason to accept the “vanilla flavor” configurations as much as possible when building your MySQL database, although this is not a guarantee your host has done so. Just be aware that there may be slight “tweaks” that you’ll have to make once you move to production.

Ready To Execute:

MySQL Wizard Ready To Execute - Click on image to enlarge

You do not have to panic when you get to this window.  You can fix it.  Except, forgotten root passwords – there may be ways to fix this but it is a pain when/if it does work.  Don’t do it! … If you do do it, research on the mysql.com web-site will be in order. … Best is:  DON’T FORGET YOUR ROOT PASSWORD!

An Install Issue – Could not start the service MySQL.Error:0 :

MySQL Wizard Start Of (Windows) Service Issue - Click on image to enlarge

I run the ZoneAlarm firewall (in addition to the firewall on my router), and, among other things, it looks for “bad guys” trying to start services and stops them until you/I authorize it. As soon as the install started, ZoneAlarm caught it (a good thing), and “killed” the install. Actually the service did start, but it wasn’t a complete install.

To fix it I did the following:

1. I did NOT stop the install by closing the Processing Configuration window
2. I killed the existing service with: run –> “sc delete MySQL” (don’t enter the quotes)
   
   
   1. This was from a tip found on the mysql.com forum after entering “could not start the service MySQL.Error:0”.
      
      
3. I then clicked on the “Back” button to go to the prior window (see “Ready To Execute” above) and then clicked “Execute” again. The results are as follows:

MySQL Wizard (Windows) Service Issue Resolved - Click on image to enlarge

MySQL (Windows) Service Display - Click on image to enlarge

Verifying the Install:

Windows Setup:

To verify that Windows desktop was setup properly, do the following:

Start –> Programs –> MySQL …

You should see something like the following:

MySQL Windows Programs List - Click on image to enlarge

Environment Variable:

To verify that the install properly set up the path to MySQL, do the following:

1. Right click on My Computer
2. Select “Properties”
3. Go to “Advanced” tab
4. Click on the “Environment Variables” button
5. Under “System Variables” scroll down until you see the “Path” variable
6. Double click on this line. It will pop-up an “Edit System Variable” window
7. IF you checked “Include Bin Directory in Windows Path” as per the above Windows options suggestion, you should see the path to your MySQL Bin folder at the end:

MySQL Windows Environment Variable Display - Click on image to enlarge

Do not edit/change this if it is correct. If you do have to, be VERY careful to get it right!

Testing the connection:

Note: The test examples given below are from: “refman-5.1-maria-en.pdf” listed above.

IF you checked the “Include Bin Directory in Windows Path”, the initial test is very easy:

1. Run –> cmd.exe (or get to it from Start –> Programs –> Accessories –> Command Prompt
2. At the command prompt, enter:

   mysqlshow –u root –p<your password>

   Note that there is a space between “-u” and root, and no space between “-p” and the password.
   
   

3. The results should look like this, since you have done absolutely zilch in the way of creating database
   

   MySQL Windows Command Line Test - Click on image to enlarge

4. If you wish to be really thorough, in the Command Prompt enter:
   mysqladmin –u root –p<your password> version status proc
   
   
5. If you wish to jump into the test database, in Command Prompt enter:
   mysql –u root –p<your password> test
   
   
6. This will put you at a “mysql>” prompt, which means … you are IN.
   To get OUT, at the prompt type:  exit
   
   
7. If you wish to jump into a database with content, in the Command Prompt enter:
   mysqlshow –u root –p<your password> mysql
   This will give you a list of all of the current Tables in the “mysql” database.

Next Step:

Next up is setting up MySQL Administrator (and MySQL Workbench). Many WordPress installation docs have you using “phpMyAdmin” (which you can do and should learn) but locally I prefer a GUI tool; probably from years of using DB Artisan. See: TechNote: 4-WordPress Install – MySQL Admin-Workbench Setup .

DP Harshman

PDF Link