MySQL Install multiple instances.

Create a folder called Conf with Instance.1.ini, Instance.2.ini, and Instance.3.ini.
The Port each are listening on should all differ, as well as having a different data directory.

I named these



C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance1

mysqld --install "Mysql-Instance-1" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\Conf\Instance.1.ini"
mysqld --install "Mysql-Instance-2" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\Conf\Instance.2.ini"
mysqld --install "Mysql-Instance-3" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\Conf\Instance.3.ini"

in the ini file the commands to set these are:

• port=3306
• datadir=”C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance1″
• port=3307
• datadir=”C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance2″
• port=3308
• datadir=”C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance3″

After creating the folders, settings and executing the command lines to install mysql as a service. I found that all the services successfully started up and then terminated itself and in the folder “.\MySQLData\Instance1” etc, you should notice the following files, ib_logfile0, ib_logfile1, ibdata1 and %SystemName%.err, which displays the following error on each instance.

051220 22:16:28 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

Which simply means it cannot find the system tables. A quick resolve is to copy the entire data directory into each instance folder (Ensure that the standard instance is disabled if you wish to leave this service alone). Then the service should hopefully startup for all 3 services.

xcopy data "MySQLData/Instance1"
xcopy data "MySQLData/Instance2"
xcopy data "MySQLData/Instance3"

net start "Mysql-Instance-1"
net start "Mysql-Instance-2"
net start "Mysql-Instance-3"

PS. To remove the instances enter the following commands.

mysqld --remove "Mysql-Instance-1"
mysqld --remove "Mysql-Instance-2"
mysqld --remove "Mysql-Instance-3"

Now we can start playing with MySQL Slaves, and replication on a single server and now all thats left that I can think of is loadbalancing (On Windows 2003 Server its easy enough, as it is in Administrative Tools), Let me know if you have a solution for Windows XP.;)

For more on configuring windows see the following…
Parallel Instances of PHP on Windows
Apache & IIS Multiple Address on Port 80