Requirement

To run multiple instances on a server, each instance should

Have its own data directory c:\mysql\data1\
Have its own variables
- Unique TCP/IP port
- If running on Unix, unique Unix socket files
- If running on Windows, unique socket files & Windows named pipes

Multiple MySQL Instance (Windows)

Create a separate options file for each instance

c:\mysql\mysql-5.1\my_inst1.ini

In c:\mysql\mysql-5.1\my_inst1.ini, add the variable/value under [mysqld] group

[mysqld]
basedir=c:\mysql\mysql-5.1
datadir=c:\mysql\data1\
port=3310
socket=MYSQL_INST1.SOCK
shared-memory-base-name=MYSQL_INST1

To start the server

cmd> mysqld --defaults-file=c:\mysql\mysql-5.1\my_inst1.ini

To start the server

cmd> mysqladmin -p -u root --port=3310 shutdown

Client Connection (Windows)

In my_inst1.ini, add the following variable/value under the [client] group

[client]
port=3310
socket=MYSQL_INST1.SOCK
shared-memory-base-name=MYSQL_INST1

To connect to mysqld

cmd> mysql -u myuser -p --defaults-file=c:\mysql\mysql-5.1\my_inst1.ini

Multiple MySQL Instance (Ubuntu)

Add multiple instance's variable/value pair in /etc/mysql/my.cnf

#Instance 101
[mysqld101]
basedir=/usr
datadir=/var/lib/mysql_data/data1
mysqld=/usr/bin/mysqld_safe
port=3310
socket=/var/lib/mysql/mysql1.sock

#Instance 102
[mysqld102]
basedir=/usr
datadir=/var/lib/mysql_data/data2
mysqld=/usr/bin/mysqld_safe
port=3311
socket=/var/lib/mysql/mysql2.sock

To start the instance(s)

sh> mysqld_multi start 101

sh> mysqld_multi start 101,102-103

To stop

sh> sqld_multi -user=root -password=mypassword stop 102-103

Multiple Windows Service

To install a new service called MySQL_Inst1

cmd> "c:\mysql\mysql-5.1\bin\mysqld"
         --install MySQL_Inst1
         --defaults-file=c:\mysql\mysql-5.1\my_inst1.ini

To remove

If the service is running
1. Start a Windows command window with "Run as Administrator"
2. Stop the service
```
cmd> net stop MySQL_Inst1
```
Remove the service
```
cmd> mysqld --remove MySQL_Inst1
```

To start the Service,

Start a Windows command window with "Run as Administrator"
Start the service
```
cmd> net start MySQL_Inst1
```

Log Location

By default, log files are located under data directory. However, if it is override by the Option file or by scripts like mysql_safe. Make sure each instance is pointed to a different location.

multiple instance

Option	Description
pid-file=filename	PID file
log-bin=file_name	Binary log file
log-bin-index=file_name	Binary log index file
log-error=filename	Error log file
slow_query_log=filename	Slow query log file
general_log=filename	General log file
relay-log=filename	Relay log file
relay-log-index=filename	Relay log index file
master-info-file=filename	Replication status

Running Multiple MySQL DB Servers