A computer can run more than one instance of MySQL server with different versions. This article provides a step-by-step server configuration guide for MySQL 4.1 (release 4.1.18) on Windows as a development box when there is already another instance of different version of MySQL server installed.

What you need

1. A previous version of MySQL server has already been installed and is running. Here we assume MySQL 4.0.25

2. You should have installed MySQL 4.1 release 4.1.18 (other releases for version 4.1 should be fine). If not, see How to install MySQL 4.1 on Windows.

3. Windows 2000 or XP (may work on Windows 9x but never tested).

MySQL Server Configuration Steps

1. Start MySQL Server Instance Configuration Wizard.

   If you have selected the checkbox 'Configure the MySQL Server now' in the last step when installing MySQL 4.1, the configuration wizard should start automatically when you click [Finish] button. Alternatively, you can launch the wizard from Start menu. Start -> Programs -> MySQL -> MySQL Server 4.1 -> MySQL Server Instance Config Wizard.

   

2. Select Detailed Configuration and click [Next].

   

3. Select Developer Machine and click [Next].

   

4. Select Transactional Database Only (this option optimizes InnoDB tables to enforce data and referential integrity). Click [Next].

   

5. InnoDB Tablespace Settings. We are installing on a developer machine. So the default setting can be left as is. Click [Next].

   

6. Choose Online Transactional Processing (OLTP) and click [Next].

   

7. Select TCP/IP port 3307 because 3306 is the default and has been used by your previous installation of MySQL. This will allow two instances of MySQL server to be accessed via different ports on the same box. If you select 3306, the wizard will prompt that the port 3306 is already in use.

   

8. Choose the Standard Character Set (this is the default character set).

   

9. Install MySQL server as a Windows service. Select the Service Name MySQL41 from the drop-down box to match with the MySQL version you are installing. By default, the Service Name 'MySQL' has already been used by your previous installation of MySQL. You can also check 'Include Bin Directory in Windows PATH' if you want to operate MySQL from command line.

   

10. Set a new password to root account. When MySQL was installed, the password for root account was blank. Now it is time to set a new password to it. Don't select the Create An Anonymous Account checkbox. This can lead to an insecure system.

    

11. Now everything is ready to execute. Click [Execute] button to run it.

    

12. The configuration file has been created successfully. Click [Finish] to close the wizard.

    

Errors you may receive

If you re-install MySQL 4.1 and your previous installation directory has not been deleted, you will receive the following error when you have pressed [Execute] button (see screenshot above). In this case, just click Skip and the configuration will continue to execute. When finishes, it will show that the security settings could not be applied (Error Number 1045 - Access denied for user 'root'@'localhost' (using password: NO).

Start -> Programs -> MySQL -> MySQL Server 4.1 -> MySQL Server Instance Config Wizard.

How to access two instances of MySQL on the same computer

1. Two different versions of MySQL server can be accessed by free MySQL client tool - SQLyog. When launching SQLyog, specify the port number that is going to be used to connect to MySQL. In our case, use port 3306 to access MySQL version 4.0 and use port 3307 to access MySQL version 4.1.

   To open two MySQL servers in SQLyog, you need to open two instances of SQLyog with different port numbers. But this practice is not recommended because you can easily get confused and may accidentaly change data in a database that is on a different server.

   

2. Two different versions of MySQL server can be accessed by PHP's mysql_connect() function. When specifying the server parameter in mysql_connect(), include the port number that is used by MySQL. Below is the format:

   hostname:port

   If port number is omitted, the default port number 3306 is assumed. In our case, port 3306 is used by MySQL 4.0.25 and port 3307 is used by MySQL 4.1.18. This way, it is possible that the same web application can access two MySQL servers of different versions.

   PHP website connects to northwind database in MySQL 4.0.25 at default port 3306:

   define('DB_HOST_1', 'localhost');
   define('DB_NAME_1', 'northwind');
   define('DB_USER_1', 'root');
   define('DB_PASS_1', 'password_1');
   
   $mysql_link_1 = mysql_connect(DB_HOST_1, DB_USER_1, DB_PASS_1) or die("Could not connect to database"); 
   mysql_select_db(DB_NAME_1, $mysql_link_1) or die("Could not select database");

   The same PHP website connects to northwind database in MySQL 4.1.18 at port 3307:

   define('DB_HOST_2', 'localhost:3307');
   define('DB_NAME_2', 'northwind');
   define('DB_USER_2', 'root');
   define('DB_PASS_2', 'password_2');
   
   $mysql_link_2 = mysql_connect(DB_HOST_2, DB_USER_2, DB_PASS_2) or die("Could not connect to database"); 
   mysql_select_db(DB_NAME_2, $mysql_link_2) or die("Could not select database");