MySQL Install multiple instances
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
Powered by FireStats
on March 10th, 2006 at 2:28 am
Oh, I didn’t find your post yestoday.
I try to install multiple instance of mysql 5.x in Linux. I encountered some problems.
Finally, I made it run.
The most importance is the –defaults-file and –datadir.
on March 10th, 2006 at 4:03 am
Hi Robin,
I generally develop on windows, and linux servers in production, so I generally don’t get the opportunity to run multiple distributions on Nix, however my MacBook Pro arrived last night, and I’m starting from scratch as I know absolutely nothing about Macs.
No doubt I will end up setting up a similar enviroment to what I run in windows, which I automate the installation. (I will post the script up shortly). Anyway, thanks for the pointers on your blog, as I will be using them soon, when I get a chance to play with my Mac.
how-to-run-multiple-mysql-instances
on September 4th, 2008 at 9:12 am
Hi,
Thanks for this clear and concise guide on something I needed to set up for evaluating replication.
One thing I stumbled into as a complete novice was that the ini files need to start with [mysqld]. I know this is standard fair for mysql configuration but I’ve only ever ran the configuration wizard before in the past to generate my files and then tweaked them in an editor so had no real experience of creating a config from scratch. I couldn’t figure out for a while why my services were failing for no reason or logs but adding that and reinstalling the service worked a treat, thanks!
Richard
on September 6th, 2008 at 6:54 pm
ednqi olbygxva gbzn xnvtqkcs iaqs lqmoghazy rfhvb