[mySQL5.5] 02장. shared-memory-base-name

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

 

If the service is running

Start a Windows command window with "Run as Administrator"

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