Running Multiple MySQL DB Servers

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

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

To start the Service,

  1. Start a Windows command window with "Run as Administrator"
  2. 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