Install and setup MySQL 4.0 on Windows

Table of contents

Updated: 20 April 2010

Introduction

This tutorial will go through the steps necessary to install, setup and configure the MySQL database server 4.0.x on a machine running Microsoft Windows. Setup of the MySQL root password and starting/stopping the MySQL server will be handled through command line operations.

Note: This tutorial does not use MySQL's administration tool, WinMySqlAdmin. If you prefer to use WinMySqlAdmin as opposed to command line operations you may want to look for another tutorial elsewhere.

As a guide, this tutorial will recommend the following installation folder:

  • MySQL: C:\www\mysql\

Where you choose to install MySQL is up to you, just be aware to substitute appropriately when necessary.

Download MySQL

Download the latest version of MySQL 4.0.x.

MySQL database server : zip package (Without installer)
http://dev.mysql.com/downloads/mysql/4.0.html
Example: mysql-4.0.24-win-noinstall.zip (21,691 Kb)

Install MySQL

Unzip the MySQL zip package into the C:\www\ directory and rename the C:\www\mysql-4.0.24-win\ directory to C:\www\mysql\.

Configure MySQL

Note: The directory C:\www\mysql\ will be referenced in the following section. If your installation folder differs from this, substitute appropriately.

There's not much that needs to be done to configure MySQL besides changing the root password. Although not explicitly required, it is a good practice to set/reset the root password for the MySQL database.

Reset the MySQL root password

Execute the following command to start the MySQL server in a special mode that will not perform password verification:

C:\www\mysql\bin> mysqld-nt --skip-grant-tables

Note: MySQL must be stopped before running the above command. If you receive the error "Can't start server", open Windows Task Manager and kill the mysqld.exe (or mysqld-nt.exe) process, then retry the above command.

Once MySQL has been successfully started with --skip-grant-tables, open a second DOS command prompt and execute the following commands:

C:\www\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.24-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE mysql.user SET Password=PASSWORD('myPass')
       > WHERE User='root';
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.05 sec)

mysql> quit
Bye

Note: The password 'myPass' can be anything you want just don't forget your password when connecting to MySQL in the future.

Note: In MySQL 4.1 the password hashing algorithm has been changed. This may cause problems for applications such as phpMyAdmin. To enforce the old password algorithm, use the OLD_PASSWORD() function.

More information on How to reset the MySQL root password.
More information on Password Hashing in MySQL 4.1.

Start and stop the MySQL database server

Note: The directory C:\www\mysql\ will be referenced in the following section. If your installation folder differs from this, substitute appropriately.

Personally, I find the most convenient way to start/stop the MySQL server is through simple command line operations. This will avoid the use of MySQL's administration tool, WinMySqlAdmin and give full control over when MySQL is running.

Setup batch files to start/stop/restart MySQL

Create a new file mysql_stop.cmd with the following content:

"C:\www\mysql\bin\mysqladmin" shutdown --user=root --password=myPass
sleep 5
exit

Note: Be sure to replace "myPass" with your password.

Create a new file mysql_start.cmd with the following content:

"C:\www\mysql\bin\mysqld" --basedir="C:\www\mysql" --datadir="C:\www\mysql\data"
sleep 5
exit

Note: The sleep command is used to give MySQL enough time to shutdown and also provides time to view any error messages if they occur. Download sleep.zip and unzip the sleep.exe file into your Windows directory (C:\WINDOWS or C:\WINNT).

To confirm if MySQL has started successfully, check Windows Task Manager for the mysqld.exe process

More information on Using MySQL options on the command line.

Test MySQL with PHP example code

Note: The following section assumes that Apache and PHP have been setup correctly. See the Install and setup Apache/PHP tutorial for more details.

Create a new text file with the following content:

<?
// This script will connect to MySQL, create a new database, create a
// new table, insert data into the table, select the data, then drop
// the database.

$dbHost = "localhost"; // The host "localhost" is the default
$dbUsername = "root"; // The user "root" is the default
$dbPassword = "myPass"; // *** Replace "myPass" with your password

$dbName = "example";
$dbTable = "smurfs";

// Establish connnection to the MySQL database server
$dbConn = mysql_connect($dbHost, $dbUsername, $dbPassword);
if ($dbConn) {
   echo("Connecting to MySQL ($dbHost, $dbUsername): <b>Success!</b><br><br>\n");
}
else {
   echo("<font color='red'>Connecting to MySQL ($dbHost, $dbUsername): <b>Failed!</b></font><br>\n");
   die(mysql_errno().": ".mysql_error()."<br>\n");
}

// Check if database already exists
if(mysql_select_db($dbName))
{
   // Drop the existing database
   if (mysql_query("DROP DATABASE $dbName")) {
      echo("<font color='green'>Database exists! Dropping <b>$dbName</b> database: <b>Success!</b></font><br>\n");
   }
   else {
      echo("<font color='red'>Database exists! Dropping <b>$dbName</b> database: <b>Failed!</b></font><br>\n");
      die(mysql_errno().": ".mysql_error()."<br>\n");
   }
}
   
// Create a new database
$sql = "CREATE DATABASE $dbName";
if(mysql_query($sql)) {
   echo("<font color='green'>Creating <b>$dbName</b> database: <b>Success!</b></font><br>\n");
}
else {
   echo("<font color='red'>Creating <b>$dbName</b> database: <b>Failed!</b></font><br>\n");
   die(mysql_errno().": ".mysql_error()."<br>\n");
}

// Select the database
if(mysql_select_db($dbName)) {
   echo("<font color='green'>Selecting <b>$dbName</b> database: <b>Success!</b></font><br>\n");
}
else {
   echo("<font color='red'>Selecting <b>$dbName</b> database: <b>Failed!</b></font><br>\n");
   die(mysql_errno().": ".mysql_error()."<br>\n");
}

// Create a new table
$sql = "CREATE TABLE $dbTable(
   name VARCHAR(50),
   sex CHAR(1),
   age SMALLINT(5)
);";
if (mysql_query($sql)) {
   echo("<font color='green'>Creating <b>$dbTable</b> table: <b>Success!</b></font><br>\n");
}
else {
   echo("<font color='red'>Creating <b>$dbTable</b> table: <b>Failed!</b></font><br>\n");
   die(mysql_errno().": ".mysql_error()."<br>\n");
}

// Inserting data into table
$sql = "INSERT INTO $dbTable (name, sex, age) VALUES
   ('Papa Smurf', 'M', '158'),
   ('Smurfette', 'F', '22');
";
if (mysql_query($sql)) {
   echo("<font color='green'>Inserting data into <b>$dbTable</b> table: <b>Success!</b></font><br>\n");
}
else {
   echo("<font color='red'>Inserting data into <b>$dbTable</b> table: <b>Failed!</b></font><br>\n");
   die(mysql_errno().": ".mysql_error()."<br>\n");
}

$dbResult = mysql_query("SELECT * FROM smurfs");
if ($dbResult)
{
   echo("<font color='green'>Selecting data from <b>$dbTable</b> table: <b>Success!</b></font><br>\n");
   // Create hash array for result data
   $rowIndex = 0;
   $aHash = array();
   while ($rowIndex < mysql_num_rows($dbResult))
   {
      $row = mysql_fetch_row($dbResult);
      
      $colIndex = 0;
      while ($colIndex < mysql_num_fields($dbResult))
      {
         $field = mysql_field_name($dbResult, $colIndex);
         $aHash[$rowIndex][$field] = $row[$colIndex];
         $colIndex++;
      }
      $rowIndex++;
   }
   // Print each result hash array
   echo "<pre>\n";
   for ($i=0; $i < count($aHash); $i++)
   {
      print_r($aHash[$i]) . "<br>\n";
   }
   echo "</pre>\n";
}
else {
   echo("<font color='red'>Selecting data from <b>$dbTable</b> table: <b>Failed!</b></font><br>\n");
   die(mysql_errno().": ".mysql_error()."<br>\n");
}

// Drop the example database
if (mysql_query("DROP DATABASE $dbName")) {
   echo("<font color='green'>Dropping <b>$dbName</b> database: <b>Success!</b></font><br>\n");
}
else {
   echo("<font color='red'>Dropping <b>$dbName</b> database: <b>Failed!</b></font><br>\n");
   die(mysql_errno().": ".mysql_error()."<br>\n");
}
?>

Save this file as mysql.php and place it in your Apache DocumentRoot directory (default: C:\www\Apache2\htdocs\). You may also download the mysql.php.zip file.

Note: Be sure to replace "myPass" with your MySQL root password.

Test the url:

A list of results will be displayed concerning tests of simple MySQL operations. Errors, if they occur, will be displayed in red.

Troubleshooting

MySQL is a relatively simple installation and if a problem occurs when running the mysql.php file it is most likely because of an incorrect password.

Can't connect to MySQL server on 'localhost'

Warning: mysql_connect(): Can't connect to MySQL server on 'localhost' (10061) in D:\Drive\Web\freeangle\mysql.php on line 15
Connecting to MySQL (localhost, root): Failed!
2003: Can't connect to MySQL server on 'localhost' (10061)

Cause: The MySQL server is not running.

  • Confirm that the mysqld.exe process is running in Windows Task Manager. If not, start MySQL using the mysql_start.cmd batch file we setup above.

Access denied for user: root@localhost

Warning: mysql_connect(): Access denied for user: root@localhost (Using password: YES) in ..\mysql.php on line 15
Connecting to MySQL (localhost, root): Failed!
1045: Access denied for user: 'root@localhost' (Using password: YES)

Cause: Bad username/password.

  • Make sure that the password specified in the mysql.php file is the correct password that we setup in the Reset the MySQL root password section.
  • Try entering an empty password, $dbPassword = "";
  • Stop the MySQL server (kill mysqld.exe process) and redo the Reset the MySQL root password section.

Call to undefined function mysql_connect()

Fatal error: Call to undefined function mysql_connect() in C:\Program Files\Apache Group\Apache2\htdocs\mysql.php on line 14

Cause: PHP5 does not have built in support for MySQL.

  • Read information on how to configure windows extensions in the php.ini file
  • Find and uncomment the line containing extension=php_mysql.dll

Contact Freeangle

If you continue to experience problems or have further questions, feel free to contact us. Be sure to include the following information:

  • your exact version of Windows, Apache, PHP, MySQL
  • a copy of any relevant sections from your httpd.conf and/or php.ini files
  • a copy of any error messages that you are experiencing

User comments

Add your comment

*
*