patternsqlMinor

Running multiple instances on the same host

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

sametheinstancesrunninghostmultiple

Problem

I need to set up multiple MySQL instances on the same host for one of our customers. As far as I see I could go with:

mysqld_multi

MySQL Sandbox

This should be a long term solution for an enterprise class production environment.

We need to migrate an already existing environment which currently uses the RedHat Cluster with active/passive nodes to a new VM without the clustering software (VMware HA solutions will be used, it will be different, I know).

There are 3 separate MySQL instance/cluster resources. I don't know the original motivation for this separation, but I suppose that the customer wants to be able to stop/start (patch, upgrade ...) the different resources autonomously.

Any comments and suggestions - pros, cons and real world experience are very welcome!

Solution

To answer your immediate question on how to set it up, I and Randy Melder addressed this question May 31, 2011.

DTest and Laurynas Biveinis answered this more recently (Sep 20, 2011).

Some swear by its usage these days.

Years ago, mysql used to provide a script called safe_mysqld which made it simple to have multiple instances of mysql. All you did was create a my.cnf for the what instance on whatever port you wanted. Then called:

safe_mysqld custom_my.cnf

MySQL stopped distributing safe_mysqld in favor of mysqld_multi.

However, I have learned how to use mysqld_safe. Believe it or not, because of understanding mysqld_safe, I actually wrote my own mysql multi-instance engine back in Feb 2011. It is in production use right now with many of my employer's clients. Here is how I did it:

First, make this service engine called /etc/init.d/mysqlservice

``


#!/bin/bash

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

WHICH=/usr/bin/which
ECHO=

${WHICH} echo


GREP=

${WHICH} grep



#
#  Check for a four digit number greater than 3306
#  Make sure the my.cnf for the Port Number Exists
#
P1=${1}
if [ "${P1}" == "" ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
X=

${ECHO} "${P1}" | ${GREP} -c "^[Hh][Ee][Ll][Pp]$"


if [ ${X} -eq 1 ]
then
  ${ECHO} "Usage: service mysqlservice  {start|stop|restart|status|mycnf}"
  exit
fi
X=

${ECHO} "${P1}" | ${GREP} -c "^33[0-9][0-9]$"


if [ ${X} -eq 0 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYSQLD_PORT=${P1}
if [ ${MYSQLD_PORT} -lt 3307 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYCNF=/etc/my${MYSQLD_PORT}.cnf
if [ ! -f ${MYCNF}  ]
then
  ${ECHO} "${MYCNF} Does Not Exist"
  exit
fi
MYCNF_BACKUP=${MYCNF}_backup

NOHUP=

${WHICH} nohup


SLEEP=

${WHICH} sleep


TAIL=

${WHICH} tail


AWK=

${WHICH} awk


CAT=

${WHICH} cat

RM=

${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'


MYSQLD_SAFE=

${WHICH} mysqld_safe


MYSQLADMIN=

${WHICH} mysqladmin


MYSQL=

${WHICH} mysql


MYSQL_CONN="-uroot -p -P${MYSQLD_PORT} -h127.0.0.1"
MYSQL_PING_FILE=/tmp/MySQL${MYSQLD_PORT}Ping.txt
MYSQL_STAT_FILE=/tmp/MySQL${MYSQLD_PORT}Status.txt
MYSQL_ERROR_LOG=/var/log/mysqld${MYSQLD_PORT}.log
MYSQLD_START="${MYSQLD_SAFE} --defaults-file=${MYCNF} --port=${MYSQLD_PORT}"
MYSQLD_STOP="${MYSQLADMIN} ${MYSQL_CONN} shutdown"
MYSQLD_PING="${MYSQLADMIN} ${MYSQL_CONN} ping"
PROGNAME="MySQL (Port ${MYSQLD_PORT})"

#
#       This service will use mysqld_safe to run mysql server instances on other ports
#

start() {
  cd /tmp
  ${ECHO} -n $"Starting ${PROGNAME}"
  ${NOHUP} ${MYSQLD_START} 2>/dev/null 1>/dev/null &
  ATTEMPTS=0
  STARTING_MYSQLD=1
  MINUTES_TO_TRY=3
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STARTING_MYSQLD} -eq 1 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    READY_FOR_CONNECTIONS=

${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "ready for connections\.$"


    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STARTING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STARTING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STARTING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Started ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

stop() {
  ${ECHO} -n $"Stopping ${PROGNAME}"
  ${MYSQLD_STOP}
  ATTEMPTS=0
  STOPPING_MYSQLD=1
  MINUTES_TO_TRY=10
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STOPPING_MYSQLD} -eq 0 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    MYSQL_HAS_BEEN_SHUTDOWN=

${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "Shutdown complete$"


    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STOPPING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STOPPING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STOPPING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Stopped ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

# See how we were called.

${MYSQLD_PING} 2>/dev/null > ${MYSQL_PING_FILE}
MYSQL_RUNNING=

grep -c "mysqld is alive" ${MYSQL_PING_FILE}`
${RM} ${MYSQL_PING_FILE}

case "$2" in
start)
if [ ${MYSQL_RUNNING} -eq 0 ]
then
start
else
${ECHO} ${PROGNAME} is Running
fi
;;
stop)
if [ ${MYSQL_RUNNING} -eq 0 ]
then
${ECHO} ${PROGNAME} is Not Running
else
stop
fi
;;
restart)
stop
${SLEEP} 1
start
;;
status)
if [ ${MYSQL_RUNNING} -eq 0 ]
then
${ECHO} ${PROGNAME} is Not Running
else
${MYSQL} ${MYSQL_CONN} -A -e"status" > ${MYSQL_PING_FILE}
${GREP} "Server version:" ${MYSQL_STAT_FILE}
${GREP} "UNIX socket:" > ${MYSQL_STAT_FILE}
${GREP} "Uptime:" > ${MYSQL_STAT_FILE}
${GREP} "Threads:" > ${MYSQL_STAT_FILE}
${CAT} ${MYSQL_STAT_FILE}
fi
;;
mycnf)
less ${MYCNF}
;;
edit)
cp ${MYCNF} ${MYCNF_BACKUP}
vi ${MYCNF}
;;
*)
${ECHO} $"Usage: $0 {start|stop|restart|status|mycnf}"
${

Code Snippets

safe_mysqld custom_my.cnf

#!/bin/bash

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

WHICH=/usr/bin/which
ECHO=`${WHICH} echo`
GREP=`${WHICH} grep`

#
#  Check for a four digit number greater than 3306
#  Make sure the my.cnf for the Port Number Exists
#
P1=${1}
if [ "${P1}" == "" ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
X=`${ECHO} "${P1}" | ${GREP} -c "^[Hh][Ee][Ll][Pp]$"`
if [ ${X} -eq 1 ]
then
  ${ECHO} "Usage: service mysqlservice <port-number [3307-3399]> {start|stop|restart|status|mycnf}"
  exit
fi
X=`${ECHO} "${P1}" | ${GREP} -c "^33[0-9][0-9]$"`
if [ ${X} -eq 0 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYSQLD_PORT=${P1}
if [ ${MYSQLD_PORT} -lt 3307 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYCNF=/etc/my${MYSQLD_PORT}.cnf
if [ ! -f ${MYCNF}  ]
then
  ${ECHO} "${MYCNF} Does Not Exist"
  exit
fi
MYCNF_BACKUP=${MYCNF}_backup

NOHUP=`${WHICH} nohup`
SLEEP=`${WHICH} sleep`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
CAT=`${WHICH} cat`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
MYSQLD_SAFE=`${WHICH} mysqld_safe`
MYSQLADMIN=`${WHICH} mysqladmin`
MYSQL=`${WHICH} mysql`
MYSQL_CONN="-uroot -p<rootpassword> -P${MYSQLD_PORT} -h127.0.0.1"
MYSQL_PING_FILE=/tmp/MySQL${MYSQLD_PORT}Ping.txt
MYSQL_STAT_FILE=/tmp/MySQL${MYSQLD_PORT}Status.txt
MYSQL_ERROR_LOG=/var/log/mysqld${MYSQLD_PORT}.log
MYSQLD_START="${MYSQLD_SAFE} --defaults-file=${MYCNF} --port=${MYSQLD_PORT}"
MYSQLD_STOP="${MYSQLADMIN} ${MYSQL_CONN} shutdown"
MYSQLD_PING="${MYSQLADMIN} ${MYSQL_CONN} ping"
PROGNAME="MySQL (Port ${MYSQLD_PORT})"

#
#       This service will use mysqld_safe to run mysql server instances on other ports
#

start() {
  cd /tmp
  ${ECHO} -n $"Starting ${PROGNAME}"
  ${NOHUP} ${MYSQLD_START} 2>/dev/null 1>/dev/null &
  ATTEMPTS=0
  STARTING_MYSQLD=1
  MINUTES_TO_TRY=3
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STARTING_MYSQLD} -eq 1 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    READY_FOR_CONNECTIONS=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "ready for connections\.$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STARTING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STARTING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STARTING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Started ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

stop() {
  ${ECHO} -n $"Stopping ${PROGNAME}"
  ${MYSQLD_STOP}
  ATTEMPTS=0
  STOPPING_MYSQLD=1
  MINUTES_TO_TRY=10
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STOPPING_MYSQLD} -eq 0 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    MYSQL_HAS_BEEN_SHUTDOWN=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "Shutdown complete$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STOPPING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STOPPING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STOPPING_MYS

#!/bin/sh
#
# readahead:    Prereads programs required for startup into memory
#
# chkconfig: 2345 4 99
# description:  This service causes the programs used during startup \
#               to be loaded into memory before they are needed,\
#               thus improving startup performance
#
#

# Sanity checks.
[ -x /usr/sbin/readahead ] || exit 0

# Check for > 384 MB
#free -m | gawk '/Mem:/ {exit ($2 >= 384)?0:1}' || exit 0

# Source function library.
#. /etc/rc.d/init.d/functions

WHICH=/usr/bin/which
SERVICE=`${WHICH} service`

MYSQL=`${WHICH} mysql`
PORT_NUMBER=3307

# See how we were called.
case "$1" in
    start)      ${SERVICE} mysqlservice ${PORT_NUMBER} start    ;;
    stop)       ${SERVICE} mysqlservice ${PORT_NUMBER} stop     ;;
    status)     ${SERVICE} mysqlservice ${PORT_NUMBER} status   ;;
    restart)    ${SERVICE} mysqlservice ${PORT_NUMBER} restart  ;;
    mycnf)      ${SERVICE} mysqlservice ${PORT_NUMBER} mycnf    ;;
    edit)       ${SERVICE} mysqlservice ${PORT_NUMBER} edit     ;;
    *)
        echo $"Usage: $0 {start|stop|restart|status|mycnf}"
        ;;
esac

[mysqld]
datadir=/var/lib/mysql3307
socket=/var/lib/mysql3307/mysql.sock
port=3307

[mysqld_safe]
log-error=/var/log/mysqld3307.log

Context

StackExchange Database Administrators Q#6370, answer score: 5

Revisions (0)

No revisions yet.