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 <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_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_PING_FILE} > ${MYSQL_STAT_FILE}
${GREP} "UNIX socket:" < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
${GREP} "Uptime:" < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
${GREP} "Threads:" < ${MYSQL_PING_FILE} >> ${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}"
${ECHO} $"Usage: $0 help"
exit 1
esac
exit
Next, create mysql instance file for a specfic port.
For example, here is the service mysql3307
#!/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
Make sure that the mysql instance file has the port number defined in PORT_NUMBER between 3307 and 3399.
As mysql instance file calls mysqlservice, please note that mysqlservice will check for the config file for the specfic port.
In the case of mysql3307, mysqlservice will look for /etc/my3307.cnf.
Make sure you have the following explicitly defined in /etc/my3307.cnf
[mysqld]
datadir=/var/lib/mysql3307
socket=/var/lib/mysql3307/mysql.sock
port=3307
[mysqld_safe]
log-error=/var/log/mysqld3307.log
The datadir and socket can be at places of your choosing.
To create mysql3308 and other services, following the same paradigm but use the exactly port number defined throughout.
Give it a Try !!!
The two constraints that you have specified:
- Very low downtime
- Limited disk space use
conflict rather seriously.
You have (wisely, IMO) excluded the option of using pg_upgrade with a custom rebuilt Pg, which is the only option I see that'd satisfy both those constraints.
I suspect you'll have to drop the disk space constraint. The only way out of this that I see is to configure Slony-I to replicate from your 8.3 database to a new 9.2 instance deployed either on new storage on the same machine, or on a separate machine. Allow Slony-I to bring the replica up to date, then shut the old server down, disable replication and cut over to the new server. "New server" here can be a new 9.2 cluster running on a different port on the existing server hardware, it doesn't have to be a new physical or virtual machine.
At this point you can use streaming replication - or Slony-I again - to replicate the data back to a 9.2 instance on the original server and fail back to it. The temporary server used for migration can be retired, or (preferably, if it's real hardware) configured to operate as a streaming replication slave and WAL archiving repository for PITR and failover purposes.
Slony-I is a bit of an experience to work with and places some constraints on how you can use the DB (mainly: All DDL must go through Slony, not direct DDL commands), so I recommend taking a dump of the 8.3 server, restoring it to a test workstation, and testing the proposed replication setup there before attempting it in production. You will need to test your application on 9.2 anyway, so this is a good chance to do both. Test it on 8.3-with-slony, and on 9.2.
This might be a good opportunity to see professional support from people who have experience dealing with these issues.
Best Answer
1) No. You have to restart MySQL and that means downtime.
2) To minimize the downtime you need an identical server which you can switch to and back. The procedure on high level:
you can build a passive master using percona-xtrabackup for example without downtime
Set up master-master replication
Upgrade the passive master to 5.6 and wait for it to catch up in replication
Switch active master to be the upgraded one (running 5.6)
Stop and rebuild the old master with the same process you did in step #1 but now from the new version (5.6)
Repeat the procedure to upgrade for 5.7
If you don't need it trash the passive master