Hi Judy,
I had just come across some nice examples of doing this in a MySQL book (see attribution at the end of this post) so I have included some notes based on those examples below:
You can run multiple instances on the same host and specify configuration options either by adding the options as arguments on the command line, or by storing them in configuration files.
for example, from the command line:
shell> mysqld --port 3306 --log=/path/to/server1/log
shell> mysqld --port 3307 --log=/path/to/server2/log
However, when mysql starts up it reads various config files in sequence. This means that you can set global options for all instances in one file and then specific options for individual instances in separate files.
The files it reads by default are (on unix)
/etc/my.cnf
the_mysql_data_dir/my.cnf
~/.my.cnf
you can direct the server to read specific config files after it reads the default /etc/my.cnf file with the following directive.
--defaults-extra-file=/path/to/extra/config/file
So, if you wanted 2 instances running with some shared options and some specific options, you could create additional configuration files in /etc say:
/etc/my.server1.cnf
/etc/my.server2.cnf
where my.server1.cnf has the following lines:
port=3306
log=/path/to/log/file/for/server1
(other options here)
and my.server2.cnf is:
port=3307
log=/path/to/log/file/for/server2
(other options here)
then you could start the separate instances of mysqld as follows:
shell> mysqld --defaults-extra-file=/etc/my.server1.cnf
shell> mysqld --defaults-extra-file=/etc/my.server2.cnf
Note: port numbers etc shown here are examples and you should use the correct options for your environment.
If you are going to run multiple instances and configurations of MySQL then it is probably a good idea to refer to the MySQL Reference Manual.
Attribution for the book that I have mentioned is as follows:
"High Performance MySQL: Optimisation, Backups, Replication, and Load Balancing, by Jeremy D. Zawodny and Derek J. Balling. Copyright 2004 O'Reilly Media, Inc., 0-596-00306-4"
Regards,
--
Nick Roper
Partner
logical elements
information & internet solutions
e:
nroper@logical.co.uk
p: +44 (0)1749 676798
w: www.logical.co.uk