Installing MySQL V 4.1.10 under Windows XP
This section of the document explains installing MySQL for the first time, under Windows. Later sections deal with upgrading.
I assume you are logged on as a Windows user with administrative rights.
Each choice documented below is based on what I think is the most appropriate alternative for my operating environment (which includes development machines and a farm of single-user production machines).
Install the MySQL server
To start: Download mysql-4.1.10-win32.zip or later from http://dev.mysql.com/downloads/mysql/4.1.html.
Note: The installation prompts change slightly from version to version. Sigh.
The file used in this document was mysql-4.1.10-win32.zip.
- Unzip mysql-4.1.10-win32.zip and run Setup.exe
- Click Next
- Click Custom and Next
Custom is chosen to allow MySQL to be installed on a drive other than c:\.
- Click Change
- Change folder from c:\ to d:\, say.
- Click OK
- Click Next
- Click Install
- Click Skip sign-up
- Click Next
- Ensure 'Configure the MySQL Server now' is selected
- Click Finish
- Click Next
- Ensure 'Detailed Configuration' is selected
- Click Next
- Ensure 'Developer Machine' is selected
- Click Next
- Ensure 'Multifunctional Database' is selected
- Click Next
- InnoDB Tablespace Settings
If you are installing MySQL on d:\, say, rather than c:\, change the InnoDB drive to d:\.
- Click Next
- Ensure 'Decision Support (DSS)/OLAP' is selected
- Click Next
- Ensure 'Enable TCP/IP Networking' and 'Port number: 3306' are selected
- Click Next
- Ensure 'Standard Character Set' is selected
- Click Next
- Ensure the following are selected:
These 3 choices are all the defaults.
Note: Because the uninstaller (V 4.1.7 was tested) is so buggy, the service name 'MySQL' will be in use if you are re-installing.
Don't panic, just choose another service name. Eg: MySQL4.1.
See the MySQL developer support forums for other people's comments on uninstalling.
Briefly: To fix a failed uninstall, edit the registry and remove all references to MySQL except Legacy_*.
Then, start again.
- Click Next
- Ensure 'Modify Security Settings' is selected
- Type in a root password twice
In this document, I'll use 'root_pw' several times, to refer to your new password (don't type the quotes).
You, of course, use something even harder to guess.
- Ensure 'Enable root access from remote machine' is NOT selected
- Click Next
- Click Execute
Time passes.
The ini file referred to is d:\Program Files\MySQL\MySQL Server 4.1\my.ini
- Click Finish
Fix the PATH
But you are not finished! V 4.1.10 does not update the PATH environment variable. Sigh.
- Click Start/Control Panel
- Double-click System
- Click Advanced
- Click Environment Variables
- Scroll down until 'Path' is visible under System Variables
- Click Path
- Click Edit
- Click Home
This moves the cursor to the start of the edit field.
- Type: d:\Program Files\MySQL\MySQL Server 4.1\bin;
Yes, include the ';' at the end to separate this path component from what was there previously.
- Click OK
- Click OK
- Click OK
- Click [X] to close the Control Panel
Connect using a MySQL tool
Now it's time to test by connecting from the command line.
These steps fire up the MySQL command line client 'mysql', and enter a couple of commands.
- Open a DOS shell
Do this with these steps:
- Type: mysql -uroot -p mysql
Here, -u is the user, root, and -p says ask for the password, and mysql is the database name.
When asked, type the password 'root_pw' (don't type the quotes) you entered above during installation of the MySQL server.
Warning: In the next line, you do indeed type 6 quotes.
- Type: set password for 'root'@'localhost' = old_password('root_pw');
See: http://dev.mysql.com/doc/mysql/en/Old_client.html for details of this strange command.
Briefly: Perl uses a MySQL client which does not support the new style passwords used by the MySQL server, so the latter must be told explicitly to accept connexions from old style clients who use the password 'root_pw'.
- Type: quit
If you were able to issue the 2 commands 'set password ...' and 'quit' to the MySQL command line client, then installation has been successful.
Install MySQL Connector/ODBC
This will allow us to use ODBC to connect to the MySQL server from a Perl database client.
To start: Download MyODBC-3.51.11-1-win.msi or later from http://dev.mysql.com/downloads/connector/odbc/3.51.html.
The file used in this document was MyODBC-3.51.11-1-win.msi.
- Double-click MyODBC-3.51.11-1-win.msi
- Click Next
- Click I accept the license agreement
- Click Next
- Click Next
- Click Next
- Click Finish
Creating Data Source Names (DSNs)
See http://en.wikipedia.org/wiki/Database_Source_Name for a short explanation of DSNs.
This document will cover a DSN for a MySQL database.
- Click Start/Control Panel
- Double-click Administrative Tools
- Double-click Data Sources (ODBC)
- Click System DSN
Using System DSN means any logged on user has access to the DSN you define.
- Click Add...
- Click the Down arrow and select MySQL ODBC 3.51 Driver
- Click Finish
- Type (for Data Source Name): test
Here, choose some meaningful name for the DSN. It is this name which you will use in Perl programs.
I use upper and lower case letters, and underscores.
- Type (as Description): Testing
- Type (as User): root
- Type (as Password): root_pw
Type the password 'root_pw' (don't type the quotes) you entered above during installation of the MySQL server.
- Click the Down arrow and select 'test'
The MySQL database test will have been installed along with the MySQL server.
- Click Test
The message should say 'Success: connection was made!'
- Click OK
- Click OK
- Click OK
Connect using ODBC and a simple Perl program.
After installing Perl, try this test program:
#!/usr/bin/perl
use strict; use warnings;
use Carp; use DBI;
# -----------
my($dbh) = DBI -> connect('dbi:ODBC:test', 'root', 'root_pw') || Carp::croak("Can't connect to DSN 'test'");
eval{$dbh -> do('drop table test')};
$dbh -> do('create table test(i int, v varchar(255) )') || Carp::croak("Can't create table 'test'");
$dbh -> do("insert into test (i, v) values (1, 'Hello World')") || Carp::croak("Can't create table 'test'");
my($sth) = $dbh -> prepare('select * from test') || Carp::croak("Can't prepare SLQ");
$sth -> execute();
my($data);
while ($data = $sth -> fetchrow_hashref() )
{
print map{"$_: $$data{$_}. \n"} sort keys %$data;
}
$dbh -> disconnect();
The expected output is:
i: 1. v: Hello World.
Editing my.ini
Remember the my.ini file mentioned above?
If you need to edit this file, although I don't expect this to happen:
Upgrading MySQL from V 4.0 to V 4.1.7
Version: 1.00 of this document. 9-Jan-2005.
- Download mysql-4.1.7-win.zip
- Unzip it and run Setup.exe
- Choose Custom
- Install to: Change c:\ to d:\
Well, some of us do this.
- Skip sign-up
- Choose Configure
- Choose Detailed Configuration
- Choose Server Machine
- Choose Multifunctional Database
- Choose InnoDB Tablespace Settings: d:\
- Choose Decision Support (DSS)/OLAP
- Choose Enable TCP/IP Networking on port 3306
- Choose Standard Character Set
- Choose Install as Windows Service
- Use the default service name: MySQL
Because the uninstaller is so buggy, this name will be in use if you are re-installing.
Don't panic, just choose another name. Eg: MySQL4.1.
See the MySQL developer support forums for other people's comments on uninstalling.
Briefly: To fix a failed uninstall, edit the registry and remove all references to MySQL except Legacy_*.
- Tick: Launch automatically
- Use the default service name: MySQL
- Choose Modify Security Settings
- The ini file is in d:\Program Files\MySQL\MySQL Server 4.1\my.ini
- Test by connecting from the command line
- shell>mysql -uroot -p mysql
Here, -u is the user, root, and -p says ask for the password, and mysql is the database name.
- Within mysql, issue this command to make the server accept connexions from old clients
mysql-shell>set password 'root'@'localhost' = old_password('some password');
See: http://dev.mysql.com/doc/mysql/en/Old_client.html
mysql-shell>quit;
- shell>mysql -uroot -p mysql
- Now connect from a client, eg x.pl
Here, x.pl will have to use 'some password'.
- Create the required databases
- Editing my.ini
If you need to edit this file, although I don't expect this to happen:
Upgrading MySQL from V 3 to V 4.0.12
These are the previous instructions.
- These instructions are for MS Windows NT/2K/XP
- I assume all tables in all databases are of type MyISAM. If not, see 2.5.2 in the MySQL manual
- I assume Perl is installed
- Edit the path environment variable, if necessary:
- Download:
- mysql-4.0.12-win.zip from http://www.mysql.com
- manual.zip from http://www.mysql.com
- MyODBC-3.51.06.exe from http://www.mysql.com
- secure-MySQL.pl from http://savage.net.au/Perl-tutorials/tut-23.zip.
- Backup all databases
- Stop and uninstall the MySQL service (note the 2 single- and 1 double-hyphens):
- Uninstall MySQL via /Start/Control Panel/Add/Remove Programs
- Reboot to:
- Remove:
- Install MySQL:
- Unzip manual.zip into \mysql\docs
- Install the server (note the double hyphens):
- Start the server:
- Upgrade DBD::mysql (note the double hyphens):
- See the next point if you have firewall problems
- shell>ppm install --location=http://theoryx5.uwinnipeg.ca/ppms DBD-mysql
If you get this message: Version 2.1025 of 'DBD-mysql' is already installed. Remove it, or use 'verify --upgrade DBD-mysql'. then do this: - shell>ppm verify --upgrade --location=http://theoryx5.uwinnipeg.ca/ppms DBD-mysql
- If your firewall stops the previous step working, download:
- DBD-mysql.ppd and DBD-mysql.tar.gz from http://theoryx5.uwinnipeg.ca/ppms. Eg into:
- \temp\DBD-mysql.ppd and - \temp\x86\DBD-mysql.tar.gz
- Patch DBD-mysql.ppd. Edit line 9 to read:
- CODEBASE HREF="./x86/DBD-mysql.tar.gz" and use - c:\Temp>ppm install --location=. DBD-mysql or - c:\Temp>ppm verify --upgrade --location=. DBD-mysql
- DBD-mysql.ppd and DBD-mysql.tar.gz from http://theoryx5.uwinnipeg.ca/ppms. Eg into:
- Run secure-MySQL.pl, or anything equivalent, to:
- Install and test MyOBDC
Resources
This document: http://savage.net.au/Perl/html/upgrade-mysql.html.
DSN: http://en.wikipedia.org/wiki/Database_Source_Name
MySQL server: http://dev.mysql.com/downloads/mysql/4.1.html
MyODBC: http://dev.mysql.com/downloads/connector/odbc/3.51.html
Upgrading Perl and Apache: http://savage.net.au/Perl/html/upgrade-perl-apache.html.
MS VC++ V 6: http://savage.net.au/Perl/html/install-ms-vc++-v6.html.
Various tools I use: http://savage.net.au/Perl/html/install-various-tools.html.
Author
Ron Savage.
Home page: http://savage.net.au/index.html
This POD was converted to HTML by /Perl.html#fancy-pom2.pl
- Version: 1.03 01-Jun-2006
This version disguises my email address.
- Version: 1.02 21-Apr-2005
Fix typo in the 'set password' command to handle old-style passwords.
- Version: 1.01 13-Apr-2005
Add initial section on installing MySQL from scratch.
- Version: 1.00 18-Feb-2002
Original version.
Licence
Australian Copyright © 2002 Ron Savage. All rights reserved. 
All Programs of mine are 'OSI Certified Open Source Software'; you can redistribute them and/or modify them under the terms of The Artistic License, a copy of which is available at: http://www.opensource.org/licenses/index.html