In questo breve howto presento due soluzioni per poter avere più istanze (=server) MySQL in un unico server. Le istanze saranno perfettamente isolate l'una dall'altra.
I vantaggi di questa soluzione sono la riduzione dell'hardware (basta un solo server) e la possibilità di avere ambienti/istanze isolate con poca fatica e bassissimi costi.
L'ambiente di riferimento in questo testo è:

• sistema operativo Red Hat 6.5 o derivata
• layout di installazione: “basic server”
• MySQL 5.1.73
• Apache 2.2.15
• PHP 5.3.3 con le librerie: php-cli, php-common, php-gd, php-imap, php-mbstring, php-mcrypt, php-mysql, php-pdo, php-pear, php-pear-Auth-SASL, php-php-gettext, php-process, php-xml.

In fine si tenga presente che questa soluzione non implementa alcuna forma di clusterizzazione volutamente.
Scarica l'howto in forma ODT, DOC e PDF: clicca qui

La realizzazione di più istanze indipendenti sullo stesso host, senza alcuna forma di cluster, è possibile realizzarla tramite due strategie con relativi pro e contro:

1. ogni istanza è in ascolto su una porta TCP/IP diversa
2. ogni istanza è in ascolto su un indirizzo TCP/IP diverso.

NOTA 1: alcune applicazioni popolari PHP (Joomla, WordPress, GLPI, ...) non permettono di impostare la porta pertanto non sono usabili se il server MySQL lavora su una porta diversa dalla 3306 .
NOTA 2: il server MySQL, per le connessioni provenienti da localhost (indirizzo 127.0.0.1), le esegue, di default, tutte tramite socket UNIX e non tramite TCP/IP.
Vediamo opportunità e limiti per ciascun soluzione. Successivamente vedremo l'implementazione delle due soluzioni in modo pratico.

1. Pro e contro: porte vs. indirizzi

In sintesi la soluzione sul multi-porta richiede applicazioni client di tipo enterprise.
La soluzione multi-indirizzi è compatibile con ogni tipo di applicazione.

1.1. Istanze su porte diverse

A pro:

• facile da implementare
• poca configurazione richiesta
• non necessita di riserva altri indirizzi IP.

A contro:

• potrebbe richiedere il ritocco delle regole di firewall
• non è compatibile con applicazioni client "semplici".

1.2. Istanze su indirizzi diversi

A pro:

• compatibile con ogni applicazione client
• se si usano indirizzi di loopback non è richiesto il ritocco delle regole di firewall.

A contro:

• è più laboriosa l'implementazione
• richiede una pianificazione previa rigida.

2. HowTo di MySQL multi-istanza

Presentiamo la realizzazione di entrambe le soluzioni procedendo passo-passo.
Vogliamo ottenere 4 istanze totalmente isolate tra loro con datadir separati:

A fattore comune poniamo le due seguenti attività/configurazioni prima di iniziare. Da qui in poi ogni attività è svolta come utente root:
1. disattiviamo SELinux.
1.1. editiamo il file selinux 

# vi /etc/sysconfig/selinux 

1.2. e correggiamo la seguente riga

...
SELINUX=disabled
...

2. riavviamo la macchina

# reboot

3. creiamo e impostiamo le directory per le relative istanze:

# mkdir -p /opt/mysql/pro /opt/mysql/pre /opt/mysql/dev /opt/mysql/adm
# chown mysql.mysql -R /opt/mysql/pro /opt/mysql/pre /opt/mysql/dev /opt/mysql/adm

2.1. Istanze su porte diverse

Il disegno di questa soluzione è il seguente (arbitrariamente pongo un offset di porta a 100)

NOTA: le configurazioni riportate qui di seguito sono minimali.
1. Iniziamo assicurandoci che il server MySQL sia down e sia a off come servizio

# service mysqld stop
# chkconfig mysqld off

2. rinominiamo il file standard di configurazione (in alcune situazioni può generare falsi errori)

# mv /etc/my.cnf /etc/my.cnf.OLD

3. creiamo i file di configurazione delle rispettive istanze. Qui di seguito riporto i file completi con una configurazione di esempio base adatta per istanze di piccola dimensione.
3.1. istanza "pro"
3.1.1. creiamo il file my-pro.cnf

# vi /etc/ my-pro.cnf

3.1.2. popoliamolo con il seguente contenuto

# PRO 3306

[client]
port=3306
socket=/var/lib/mysql/mysql-pro.sock

[mysqld]
datadir=/opt/mysql/pro
port=3306
socket=/var/lib/mysql/mysql-pro.sock
user=mysql
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld-pro.log
pid-file=/var/run/mysqld/mysqld-pro.pid

3.2. istanza "pre"
3.2.1. creiamo il file my-pre .cnf

# vi /etc/ my-pre.cnf

3.2.2. popoliamolo con il seguente contenuto

# PRE 3406
[client]
port=3406
socket=/var/lib/mysql/mysql-pre.sock

[mysqld]
datadir=/opt/mysql/pre
port=3406
socket=/var/lib/mysql/mysql-pre.sock
user=mysql
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld-pre.log
pid-file=/var/run/mysqld/mysqld-pre.pid

3.3. istanza "dev"
3.3.1. creiamo il file my-dev .cnf

# vi /etc/ my-dev.cnf

3.3.2. popoliamolo con il seguente contenuto

# DEV 3506
[client]
port=3506
socket=/var/lib/mysql/mysql-dev.sock

[mysqld]
datadir=/opt/mysql/dev
port=3506
socket=/var/lib/mysql/mysql-dev.sock
user=mysql
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld-dev.log
pid-file=/var/run/mysqld/mysqld-dev.pid

3.4. istanza "adm"
3.4.1. creiamo il file my-adm .cnf

# vi /etc/ my-adm.cnf

3.4.2. popoliamolo con il seguente contenuto

# ADM 3606
[client]
port=3606
socket=/var/lib/mysql/mysql-adm.sock

[mysqld]
datadir=/opt/mysql/adm
port=3606
socket=/var/lib/mysql/mysql-adm.sock
user=mysql
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld-adm.log
pid-file=/var/run/mysqld/mysqld-adm.pid

4. creiamo i file di bootstrap delle istanze. Per comodità cloneremo lo script originale e successivamente personalizzeremo ciascuna copia. Riporterò i file con il codice completo evidenziando i punti in qui le stringhe sono aggiornate rispetto all'originale (si tratta sempre di aggiunte)
4.1. creiamo le copie

# cp -p /etc/init.d/mysqld /etc/init.d/mysqld-pro
# cp -p /etc/init.d/mysqld /etc/init.d/mysqld-pre
# cp -p /etc/init.d/mysqld /etc/init.d/mysqld-dev
# cp -p /etc/init.d/mysqld /etc/init.d/mysqld-adm

4.2. personalizziamo ciascuno script
4.2.1. editiamo mysqld-pro

# vi /etc/init.d/mysqld-pro

4.2.2. popoliamolo con il seguente contenuto

#!/bin/sh
#
# PRO
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my-pro.cnf
# pidfile: /var/run/mysqld/mysqld-pro.pid
### BEGIN INIT INFO
# Provides: mysqld
# Required-Start: $local_fs $remote_fs $network $named $syslog $time
# Required-Stop: $local_fs $remote_fs $network $named $syslog $time
# Short-Description: start and stop MySQL server
# Description: MySQL database server
### END INIT INFO

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

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


exec="/usr/bin/mysqld_safe"
prog="mysqld-pro"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog


# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults -c /etc/my-pro.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/opt/mysql/pro"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql-pro.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld-pro.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld-pro.pid"
mypidfile="$result"


start(){
[ -x $exec ] || exit 5
# check to see if it's already running
MYSQLDRUNNING=0
if [ -f "$mypidfile" ]; then
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ] && [ -d "/proc/$MYSQLPID" ] ; then
MYSQLDRUNNING=1
fi
fi
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
if [ $MYSQLDRUNNING = 1 ] && [ $? = 0 ]; then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
elif [ $MYSQLDRUNNING = 1 ] && echo "$RESPONSE" | grep -q "Access denied for user"
then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
else
# prepare for start
touch "$errlogfile" 2>/dev/null
if [ $? -ne 0 ]; then
# failed to touch log file, probably insufficient permissions
action $"Starting $prog: " /bin/false
return 4
fi
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
# First, make sure $datadir is there with correct permissions
if [ ! -e "$datadir" -a ! -h "$datadir" ]
then
mkdir -p "$datadir" || exit 1
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
[ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
# Now create the database
action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# We check if there is already a process using the socket file,
# since otherwise this init script could report false positive
# result and mysqld_safe would remove the socket file, which
# actually uses a different daemon.
if fuser "$socketfile" &>/dev/null ; then
echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
action $"Starting $prog: " /bin/false
return 1
fi
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure. (An exception is that we don't force the
# log-error setting, since this script doesn't really depend on that,
# and some users might prefer to configure logging to syslog.)
# Note: set --basedir to prevent probes that might trigger SELinux
# alarms, per bug #547485
$exec --defaults-file="/etc/my-pro.cnf" --datadir="$datadir" \
--socket="$socketfile" --pid-file="$mypidfile" \
--basedir=/usr --user=mysql >/dev/null 2>&1 &
safe_pid=$!
# Spin for a maximum of N seconds waiting for the server to come up;
# exit the loop immediately if mysqld_safe process disappears.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
ret=0
TIMEOUT="$STARTTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
mret=$?
if [ $mret -eq 0 ]; then
break
fi
# exit codes 1, 11 (EXIT_CANNOT_CONNECT_TO_SERVICE) are expected,
# anything else suggests a configuration error
if [ $mret -ne 1 -a $mret -ne 11 ]; then
echo "$RESPONSE"
echo "Cannot check for MySQL Daemon startup because of mysqladmin failure."
ret=1
break
fi
echo "$RESPONSE" | grep -q "Access denied for user" && break
if ! /bin/kill -0 $safe_pid 2>/dev/null; then
echo "MySQL Daemon failed to start."
ret=1
break
fi
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
ret=1
fi
if [ $ret -eq 0 ]; then
action $"Starting $prog: " /bin/true
chmod o+r $mypidfile >/dev/null 2>&1
touch $lockfile
else
action $"Starting $prog: " /bin/false
fi
fi
return $ret
}

stop(){
if [ ! -f "$mypidfile" ]; then
# not running; per LSB standards this is "ok"
action $"Stopping $prog: " /bin/true
return 0
fi
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
TIMEOUT="$STOPTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f $lockfile
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
# failed to read pidfile, probably insufficient permissions
action $"Stopping $prog: " /bin/false
ret=4
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$mypidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?

4.2.3. editiamo mysqld-pre

# vi /etc/init.d/mysqld-pre

4.2.4. popoliamolo con il seguente contenuto

#!/bin/sh
#
# PRE
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my-pre.cnf
# pidfile: /var/run/mysqld/mysqld-pre.pid
### BEGIN INIT INFO
# Provides: mysqld
# Required-Start: $local_fs $remote_fs $network $named $syslog $time
# Required-Stop: $local_fs $remote_fs $network $named $syslog $time
# Short-Description: start and stop MySQL server
# Description: MySQL database server
### END INIT INFO

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

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


exec="/usr/bin/mysqld_safe"
prog="mysqld-pre"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog


# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults -c /etc/my-pre.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/opt/mysql/pre"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql-pre.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld-pre.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld-pre.pid"
mypidfile="$result"


start(){
[ -x $exec ] || exit 5
# check to see if it's already running
MYSQLDRUNNING=0
if [ -f "$mypidfile" ]; then
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ] && [ -d "/proc/$MYSQLPID" ] ; then
MYSQLDRUNNING=1
fi
fi
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
if [ $MYSQLDRUNNING = 1 ] && [ $? = 0 ]; then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
elif [ $MYSQLDRUNNING = 1 ] && echo "$RESPONSE" | grep -q "Access denied for user"
then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
else
# prepare for start
touch "$errlogfile" 2>/dev/null
if [ $? -ne 0 ]; then
# failed to touch log file, probably insufficient permissions
action $"Starting $prog: " /bin/false
return 4
fi
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
# First, make sure $datadir is there with correct permissions
if [ ! -e "$datadir" -a ! -h "$datadir" ]
then
mkdir -p "$datadir" || exit 1
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
[ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
# Now create the database
action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# We check if there is already a process using the socket file,
# since otherwise this init script could report false positive
# result and mysqld_safe would remove the socket file, which
# actually uses a different daemon.
if fuser "$socketfile" &>/dev/null ; then
echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
action $"Starting $prog: " /bin/false
return 1
fi
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure. (An exception is that we don't force the
# log-error setting, since this script doesn't really depend on that,
# and some users might prefer to configure logging to syslog.)
# Note: set --basedir to prevent probes that might trigger SELinux
# alarms, per bug #547485
$exec --defaults-file="/etc/my-pre.cnf" --datadir="$datadir" \
--socket="$socketfile" --pid-file="$mypidfile" \
--basedir=/usr --user=mysql >/dev/null 2>&1 &
safe_pid=$!
# Spin for a maximum of N seconds waiting for the server to come up;
# exit the loop immediately if mysqld_safe process disappears.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
ret=0
TIMEOUT="$STARTTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
mret=$?
if [ $mret -eq 0 ]; then
break
fi
# exit codes 1, 11 (EXIT_CANNOT_CONNECT_TO_SERVICE) are expected,
# anything else suggests a configuration error
if [ $mret -ne 1 -a $mret -ne 11 ]; then
echo "$RESPONSE"
echo "Cannot check for MySQL Daemon startup because of mysqladmin failure."
ret=1
break
fi
echo "$RESPONSE" | grep -q "Access denied for user" && break
if ! /bin/kill -0 $safe_pid 2>/dev/null; then
echo "MySQL Daemon failed to start."
ret=1
break
fi
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
ret=1
fi
if [ $ret -eq 0 ]; then
action $"Starting $prog: " /bin/true
chmod o+r $mypidfile >/dev/null 2>&1
touch $lockfile
else
action $"Starting $prog: " /bin/false
fi
fi
return $ret
}

stop(){
if [ ! -f "$mypidfile" ]; then
# not running; per LSB standards this is "ok"
action $"Stopping $prog: " /bin/true
return 0
fi
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
TIMEOUT="$STOPTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f $lockfile
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
# failed to read pidfile, probably insufficient permissions
action $"Stopping $prog: " /bin/false
ret=4
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$mypidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?

4.2.5. editiamo mysqld-dev

# vi /etc/init.d/mysqld-dev

4.2.6. popoliamolo con il seguente contenuto

#!/bin/sh
#
# DEV
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my-dev.cnf
# pidfile: /var/run/mysqld/mysqld-dev.pid
### BEGIN INIT INFO
# Provides: mysqld
# Required-Start: $local_fs $remote_fs $network $named $syslog $time
# Required-Stop: $local_fs $remote_fs $network $named $syslog $time
# Short-Description: start and stop MySQL server
# Description: MySQL database server
### END INIT INFO

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

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


exec="/usr/bin/mysqld_safe"
prog="mysqld-dev"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog


# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults -c /etc/my-dev.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/opt/mysql/dev"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql-dev.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld-dev.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld-dev.pid"
mypidfile="$result"


start(){
[ -x $exec ] || exit 5
# check to see if it's already running
MYSQLDRUNNING=0
if [ -f "$mypidfile" ]; then
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ] && [ -d "/proc/$MYSQLPID" ] ; then
MYSQLDRUNNING=1
fi
fi
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
if [ $MYSQLDRUNNING = 1 ] && [ $? = 0 ]; then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
elif [ $MYSQLDRUNNING = 1 ] && echo "$RESPONSE" | grep -q "Access denied for user"
then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
else
# prepare for start
touch "$errlogfile" 2>/dev/null
if [ $? -ne 0 ]; then
# failed to touch log file, probably insufficient permissions
action $"Starting $prog: " /bin/false
return 4
fi
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
# First, make sure $datadir is there with correct permissions
if [ ! -e "$datadir" -a ! -h "$datadir" ]
then
mkdir -p "$datadir" || exit 1
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
[ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
# Now create the database
action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# We check if there is already a process using the socket file,
# since otherwise this init script could report false positive
# result and mysqld_safe would remove the socket file, which
# actually uses a different daemon.
if fuser "$socketfile" &>/dev/null ; then
echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
action $"Starting $prog: " /bin/false
return 1
fi
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure. (An exception is that we don't force the
# log-error setting, since this script doesn't really depend on that,
# and some users might prefer to configure logging to syslog.)
# Note: set --basedir to prevent probes that might trigger SELinux
# alarms, per bug #547485
$exec --defaults-file="/etc/my-dev.cnf" --datadir="$datadir" \
--socket="$socketfile" --pid-file="$mypidfile" \
--basedir=/usr --user=mysql >/dev/null 2>&1 &
safe_pid=$!
# Spin for a maximum of N seconds waiting for the server to come up;
# exit the loop immediately if mysqld_safe process disappears.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
ret=0
TIMEOUT="$STARTTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
mret=$?
if [ $mret -eq 0 ]; then
break
fi
# exit codes 1, 11 (EXIT_CANNOT_CONNECT_TO_SERVICE) are expected,
# anything else suggests a configuration error
if [ $mret -ne 1 -a $mret -ne 11 ]; then
echo "$RESPONSE"
echo "Cannot check for MySQL Daemon startup because of mysqladmin failure."
ret=1
break
fi
echo "$RESPONSE" | grep -q "Access denied for user" && break
if ! /bin/kill -0 $safe_pid 2>/dev/null; then
echo "MySQL Daemon failed to start."
ret=1
break
fi
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
ret=1
fi
if [ $ret -eq 0 ]; then
action $"Starting $prog: " /bin/true
chmod o+r $mypidfile >/dev/null 2>&1
touch $lockfile
else
action $"Starting $prog: " /bin/false
fi
fi
return $ret
}

stop(){
if [ ! -f "$mypidfile" ]; then
# not running; per LSB standards this is "ok"
action $"Stopping $prog: " /bin/true
return 0
fi
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
TIMEOUT="$STOPTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f $lockfile
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
# failed to read pidfile, probably insufficient permissions
action $"Stopping $prog: " /bin/false
ret=4
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$mypidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?

4.2.7. editiamo mysqld-adm

# vi /etc/init.d/mysqld-adm

4.2.8. popoliamolo con il seguente contenuto

#!/bin/sh
#
# ADM
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my-adm.cnf
# pidfile: /var/run/mysqld/mysqld-adm.pid
### BEGIN INIT INFO
# Provides: mysqld
# Required-Start: $local_fs $remote_fs $network $named $syslog $time
# Required-Stop: $local_fs $remote_fs $network $named $syslog $time
# Short-Description: start and stop MySQL server
# Description: MySQL database server
### END INIT INFO

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

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


exec="/usr/bin/mysqld_safe"
prog="mysqld-adm"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog


# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults -c /etc/my-adm.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/opt/mysql/adm"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql-adm.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld-adm.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld-adm.pid"
mypidfile="$result"


start(){
[ -x $exec ] || exit 5
# check to see if it's already running
MYSQLDRUNNING=0
if [ -f "$mypidfile" ]; then
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ] && [ -d "/proc/$MYSQLPID" ] ; then
MYSQLDRUNNING=1
fi
fi
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
if [ $MYSQLDRUNNING = 1 ] && [ $? = 0 ]; then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
elif [ $MYSQLDRUNNING = 1 ] && echo "$RESPONSE" | grep -q "Access denied for user"
then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
else
# prepare for start
touch "$errlogfile" 2>/dev/null
if [ $? -ne 0 ]; then
# failed to touch log file, probably insufficient permissions
action $"Starting $prog: " /bin/false
return 4
fi
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
# First, make sure $datadir is there with correct permissions
if [ ! -e "$datadir" -a ! -h "$datadir" ]
then
mkdir -p "$datadir" || exit 1
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
[ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
# Now create the database
action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# We check if there is already a process using the socket file,
# since otherwise this init script could report false positive
# result and mysqld_safe would remove the socket file, which
# actually uses a different daemon.
if fuser "$socketfile" &>/dev/null ; then
echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
action $"Starting $prog: " /bin/false
return 1
fi
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure. (An exception is that we don't force the
# log-error setting, since this script doesn't really depend on that,
# and some users might prefer to configure logging to syslog.)
# Note: set --basedir to prevent probes that might trigger SELinux
# alarms, per bug #547485
$exec --defaults-file="/etc/my-adm.cnf" --datadir="$datadir" \
--socket="$socketfile" --pid-file="$mypidfile" \
--basedir=/usr --user=mysql >/dev/null 2>&1 &
safe_pid=$!
# Spin for a maximum of N seconds waiting for the server to come up;
# exit the loop immediately if mysqld_safe process disappears.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
ret=0
TIMEOUT="$STARTTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
mret=$?
if [ $mret -eq 0 ]; then
break
fi
# exit codes 1, 11 (EXIT_CANNOT_CONNECT_TO_SERVICE) are expected,
# anything else suggests a configuration error
if [ $mret -ne 1 -a $mret -ne 11 ]; then
echo "$RESPONSE"
echo "Cannot check for MySQL Daemon startup because of mysqladmin failure."
ret=1
break
fi
echo "$RESPONSE" | grep -q "Access denied for user" && break
if ! /bin/kill -0 $safe_pid 2>/dev/null; then
echo "MySQL Daemon failed to start."
ret=1
break
fi
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
ret=1
fi
if [ $ret -eq 0 ]; then
action $"Starting $prog: " /bin/true
chmod o+r $mypidfile >/dev/null 2>&1
touch $lockfile
else
action $"Starting $prog: " /bin/false
fi
fi
return $ret
}

stop(){
if [ ! -f "$mypidfile" ]; then
# not running; per LSB standards this is "ok"
action $"Stopping $prog: " /bin/true
return 0
fi
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
TIMEOUT="$STOPTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f $lockfile
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
# failed to read pidfile, probably insufficient permissions
action $"Stopping $prog: " /bin/false
ret=4
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$mypidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?

5. avviamo le nostre istanze

# service mysqld-pro start
# service mysqld-pre start
# service mysqld-dev start
# service mysqld-adm start

6. se vogliamo porle a on come servizio procediamo con le seguenti istruzioni

# chkconfig mysqld-pro on
# chkconfig mysqld-pre on
# chkconfig mysqld-dev on
# chkconfig mysqld-adm on

NOTA: è possibile che qualche errore scappi in mezzo a qualche script. Se un'istanza non parte è una buona pratica consultare il relativo log. Qui di seguito riporto il log dell'istanza “pre”. Con il lancio del servizio ho visto la creazione (corretta) del datadir, ma subito dopo ho ottenuto un errore e non è partita. Il log evidenzia che non ho aggiornato la porta dell'istanza (punta ancora alla 3306) ed entra in conflitto con l'istanza “pro”.

140704 10:19:52 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/pre
140704 10:19:52 InnoDB: Initializing buffer pool, size = 8.0M
140704 10:19:52 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
140704 10:19:52 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
140704 10:19:52 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
140704 10:19:53 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
140704 10:19:53 InnoDB: Started; log sequence number 0 0
140704 10:19:53 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
140704 10:19:53 [ERROR] Do you already have another mysqld server running on port: 3306 ?
140704 10:19:53 [ERROR] Aborting

2.2. Istanze su indirizzi diversi

Il disegno di questa soluzione è il seguente:

1. Iniziamo assicurandoci che il server MySQL sia down e sia a off come servizio

# service mysqld stop
# chkconfig mysqld off

2. aggiungiamo gli indirizzi IP e attiviamoli
2.1. editiamo il file ifcfg-lo

# vi /etc/sysconfig/network-scripts/ifcfg-lo

2.2. aggiungiamo le seguenti righe al fondo del file

...
IPADDR1=127.0.0.30
IPADDR2=127.0.0.31
IPADDR3=127.0.0.32
IPADDR4=127.0.0.33

2.3. attiviamo i nuovi indirizzi

# ifdown lo
# ifup lo

2.4. editiamo il file hosts

# vi /etc/hosts

2.5. aggiungiamo le seguenti righe

...
127.0.0.30 pro.db.example.loc
127.0.0.31 pre.db.example.loc
127.0.0.32 adm.db.example.loc
127.0.0.33 dev.db.example.loc

3. rinominiamo il file standard di configurazione (in alcune situazioni può generare falsi errori)

# mv /etc/my.cnf /etc/my.cnf.OLD

4. creiamo i file di configurazione delle rispettive istanze. Qui di seguito riporto i file completi con una configurazione di esempio base adatta per istanze di piccola dimensione.
4.1. istanza "pro"
4.1.1. creiamo il file my-pro .cnf

# vi /etc/my-pro .cnf

4.1.2. popoliamolo con il seguente contenuto

# PRO 3306
[client]
port = 3306
socket = /var/lib/mysql/mysql-pro.sock

[mysqld]
datadir=/opt/mysql/pro
port=3306
socket=/var/lib/mysql/mysql-pro.sock
user=mysql
# bind-address=127.0.0.30
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld-pro.log
syslog
pid-file=/var/run/mysqld/mysqld-pro.pid

4.2. istanza "pre"
4.2.1. creiamo il file my-pre .cnf

# vi /etc/my-pre.cnf

4.2.2. popoliamolo con il seguente contenuto

# PRE 3306
[client]
port=3306
socket=/var/lib/mysql/mysql-pre.sock

[mysqld]
datadir=/opt/mysql/pre
port=3306
socket=/var/lib/mysql/mysql-pre.sock
user=mysql
# bind-address=127.0.0.31
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld-pre.log
pid-file=/var/run/mysqld/mysqld-pre.pid

4.3. istanza "dev"
4.3.1. creiamo il file my-dev .cnf

# vi /etc/my-dev.cnf

4.3.2. popoliamolo con il seguente contenuto

# DEV 3306
[client]
port=3306
socket=/var/lib/mysql/mysql-dev.sock

[mysqld]
datadir=/opt/mysql/dev
port=3306
socket=/var/lib/mysql/mysql-dev.sock
user=mysql
# bind-address=127.0.0.33
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld-dev.log
pid-file=/var/run/mysqld/mysqld-dev.pid

4.4. istanza "adm"
4.4.1. creiamo il file my-adm .cnf

# vi /etc/my-adm.cnf

4.4.2. popoliamolo con il seguente contenuto

# ADM 3306
[client]
port = 3306
socket = /var/lib/mysql/mysql-adm.sock

[mysqld]
datadir=/opt/mysql/adm
port=3306
socket=/var/lib/mysql/mysql-adm.sock
user=mysql
# bind-address=127.0.0.32
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld-adm.log
pid-file=/var/run/mysqld/mysqld-adm.pid

5. creiamo i file di bootstrap delle istanze. Per comodità cloneremo lo script originale e successivamente personalizzeremo ciascuna copia. Riporterò i file con il codice completo evidenziando i punti in cui le stringhe sono state aggiornate rispetto all'originale (si tratta sempre di aggiunte)
5.1. creiamo le copie

# cp -p /etc/init.d/mysqld /etc/init.d/mysqld-pro
# cp -p /etc/init.d/mysqld /etc/init.d/mysqld-pre
# cp -p /etc/init.d/mysqld /etc/init.d/mysqld-dev
# cp -p /etc/init.d/mysqld /etc/init.d/mysqld-adm

5.2. personalizziamo ciascuno script
5.2.1. editiamo mysqld-pro

# vi /etc/init.d/mysqld-pro

5.2.2. popoliamolo con il seguente contenuto

#!/bin/sh
#
# PRO
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my-pro.cnf
# pidfile: /var/run/mysqld/mysqld-pro.pid
### BEGIN INIT INFO
# Provides: mysqld
# Required-Start: $local_fs $remote_fs $network $named $syslog $time
# Required-Stop: $local_fs $remote_fs $network $named $syslog $time
# Short-Description: start and stop MySQL server
# Description: MySQL database server
### END INIT INFO

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

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


exec="/usr/bin/mysqld_safe"
prog="mysqld-pro"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog


# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults -c /etc/my-pro.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/opt/mysql/pro"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql-pro.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld-pro.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld-pro.pid"
mypidfile="$result"


start(){
[ -x $exec ] || exit 5
# check to see if it's already running
MYSQLDRUNNING=0
if [ -f "$mypidfile" ]; then
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ] && [ -d "/proc/$MYSQLPID" ] ; then
MYSQLDRUNNING=1
fi
fi
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
if [ $MYSQLDRUNNING = 1 ] && [ $? = 0 ]; then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
elif [ $MYSQLDRUNNING = 1 ] && echo "$RESPONSE" | grep -q "Access denied for user"
then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
else
# prepare for start
touch "$errlogfile" 2>/dev/null
if [ $? -ne 0 ]; then
# failed to touch log file, probably insufficient permissions
action $"Starting $prog: " /bin/false
return 4
fi
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
# First, make sure $datadir is there with correct permissions
if [ ! -e "$datadir" -a ! -h "$datadir" ]
then
mkdir -p "$datadir" || exit 1
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
[ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
# Now create the database
action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# We check if there is already a process using the socket file,
# since otherwise this init script could report false positive
# result and mysqld_safe would remove the socket file, which
# actually uses a different daemon.
if fuser "$socketfile" &>/dev/null ; then
echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
action $"Starting $prog: " /bin/false
return 1
fi
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure. (An exception is that we don't force the
# log-error setting, since this script doesn't really depend on that,
# and some users might prefer to configure logging to syslog.)
# Note: set --basedir to prevent probes that might trigger SELinux
# alarms, per bug #547485
$exec --defaults-file="/etc/my-pro.cnf" --datadir="$datadir" \
--socket="$socketfile" --pid-file="$mypidfile" \
--basedir=/usr --user=mysql >/dev/null 2>&1 &
safe_pid=$!
# Spin for a maximum of N seconds waiting for the server to come up;
# exit the loop immediately if mysqld_safe process disappears.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
ret=0
TIMEOUT="$STARTTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
mret=$?
if [ $mret -eq 0 ]; then
break
fi
# exit codes 1, 11 (EXIT_CANNOT_CONNECT_TO_SERVICE) are expected,
# anything else suggests a configuration error
if [ $mret -ne 1 -a $mret -ne 11 ]; then
echo "$RESPONSE"
echo "Cannot check for MySQL Daemon startup because of mysqladmin failure."
ret=1
break
fi
echo "$RESPONSE" | grep -q "Access denied for user" && break
if ! /bin/kill -0 $safe_pid 2>/dev/null; then
echo "MySQL Daemon failed to start."
ret=1
break
fi
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
ret=1
fi
if [ $ret -eq 0 ]; then
action $"Starting $prog: " /bin/true
chmod o+r $mypidfile >/dev/null 2>&1
touch $lockfile
else
action $"Starting $prog: " /bin/false
fi
fi
return $ret
}

stop(){
if [ ! -f "$mypidfile" ]; then
# not running; per LSB standards this is "ok"
action $"Stopping $prog: " /bin/true
return 0
fi
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
TIMEOUT="$STOPTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f $lockfile
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
# failed to read pidfile, probably insufficient permissions
action $"Stopping $prog: " /bin/false
ret=4
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$mypidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?

5.2.3. editiamo mysqld-pre

# vi /etc/init.d/mysqld-pre

5.2.4. popoliamolo con il seguente contenuto

#!/bin/sh
#
# PRE
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my-pre.cnf
# pidfile: /var/run/mysqld/mysqld-pre.pid
### BEGIN INIT INFO
# Provides: mysqld
# Required-Start: $local_fs $remote_fs $network $named $syslog $time
# Required-Stop: $local_fs $remote_fs $network $named $syslog $time
# Short-Description: start and stop MySQL server
# Description: MySQL database server
### END INIT INFO

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

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


exec="/usr/bin/mysqld_safe"
prog="mysqld-pre"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog


# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults -c /etc/my-pre.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/opt/mysql/pre"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql-pre.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld-pre.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld-pre.pid"
mypidfile="$result"


start(){
[ -x $exec ] || exit 5
# check to see if it's already running
MYSQLDRUNNING=0
if [ -f "$mypidfile" ]; then
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ] && [ -d "/proc/$MYSQLPID" ] ; then
MYSQLDRUNNING=1
fi
fi
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
if [ $MYSQLDRUNNING = 1 ] && [ $? = 0 ]; then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
elif [ $MYSQLDRUNNING = 1 ] && echo "$RESPONSE" | grep -q "Access denied for user"
then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
else
# prepare for start
touch "$errlogfile" 2>/dev/null
if [ $? -ne 0 ]; then
# failed to touch log file, probably insufficient permissions
action $"Starting $prog: " /bin/false
return 4
fi
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
# First, make sure $datadir is there with correct permissions
if [ ! -e "$datadir" -a ! -h "$datadir" ]
then
mkdir -p "$datadir" || exit 1
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
[ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
# Now create the database
action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# We check if there is already a process using the socket file,
# since otherwise this init script could report false positive
# result and mysqld_safe would remove the socket file, which
# actually uses a different daemon.
if fuser "$socketfile" &>/dev/null ; then
echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
action $"Starting $prog: " /bin/false
return 1
fi
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure. (An exception is that we don't force the
# log-error setting, since this script doesn't really depend on that,
# and some users might prefer to configure logging to syslog.)
# Note: set --basedir to prevent probes that might trigger SELinux
# alarms, per bug #547485
$exec --defaults-file="/etc/my-pre.cnf" --datadir="$datadir" \
--socket="$socketfile" --pid-file="$mypidfile" \
--basedir=/usr --user=mysql >/dev/null 2>&1 &
safe_pid=$!
# Spin for a maximum of N seconds waiting for the server to come up;
# exit the loop immediately if mysqld_safe process disappears.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
ret=0
TIMEOUT="$STARTTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
mret=$?
if [ $mret -eq 0 ]; then
break
fi
# exit codes 1, 11 (EXIT_CANNOT_CONNECT_TO_SERVICE) are expected,
# anything else suggests a configuration error
if [ $mret -ne 1 -a $mret -ne 11 ]; then
echo "$RESPONSE"
echo "Cannot check for MySQL Daemon startup because of mysqladmin failure."
ret=1
break
fi
echo "$RESPONSE" | grep -q "Access denied for user" && break
if ! /bin/kill -0 $safe_pid 2>/dev/null; then
echo "MySQL Daemon failed to start."
ret=1
break
fi
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
ret=1
fi
if [ $ret -eq 0 ]; then
action $"Starting $prog: " /bin/true
chmod o+r $mypidfile >/dev/null 2>&1
touch $lockfile
else
action $"Starting $prog: " /bin/false
fi
fi
return $ret
}

stop(){
if [ ! -f "$mypidfile" ]; then
# not running; per LSB standards this is "ok"
action $"Stopping $prog: " /bin/true
return 0
fi
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
TIMEOUT="$STOPTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f $lockfile
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
# failed to read pidfile, probably insufficient permissions
action $"Stopping $prog: " /bin/false
ret=4
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$mypidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?

5.2.5. editiamo mysqld-dev

# vi /etc/init.d/mysqld-dev

5.2.6. popoliamolo con il seguente contenuto

#!/bin/sh
#
# DEV
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my-dev.cnf
# pidfile: /var/run/mysqld/mysqld-dev.pid
### BEGIN INIT INFO
# Provides: mysqld
# Required-Start: $local_fs $remote_fs $network $named $syslog $time
# Required-Stop: $local_fs $remote_fs $network $named $syslog $time
# Short-Description: start and stop MySQL server
# Description: MySQL database server
### END INIT INFO

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

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


exec="/usr/bin/mysqld_safe"
prog="mysqld-dev"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog


# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults -c /etc/my-dev.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/opt/mysql/dev"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql-dev.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld-dev.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld-dev.pid"
mypidfile="$result"


start(){
[ -x $exec ] || exit 5
# check to see if it's already running
MYSQLDRUNNING=0
if [ -f "$mypidfile" ]; then
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ] && [ -d "/proc/$MYSQLPID" ] ; then
MYSQLDRUNNING=1
fi
fi
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
if [ $MYSQLDRUNNING = 1 ] && [ $? = 0 ]; then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
elif [ $MYSQLDRUNNING = 1 ] && echo "$RESPONSE" | grep -q "Access denied for user"
then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
else
# prepare for start
touch "$errlogfile" 2>/dev/null
if [ $? -ne 0 ]; then
# failed to touch log file, probably insufficient permissions
action $"Starting $prog: " /bin/false
return 4
fi
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
# First, make sure $datadir is there with correct permissions
if [ ! -e "$datadir" -a ! -h "$datadir" ]
then
mkdir -p "$datadir" || exit 1
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
[ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
# Now create the database
action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# We check if there is already a process using the socket file,
# since otherwise this init script could report false positive
# result and mysqld_safe would remove the socket file, which
# actually uses a different daemon.
if fuser "$socketfile" &>/dev/null ; then
echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
action $"Starting $prog: " /bin/false
return 1
fi
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure. (An exception is that we don't force the
# log-error setting, since this script doesn't really depend on that,
# and some users might prefer to configure logging to syslog.)
# Note: set --basedir to prevent probes that might trigger SELinux
# alarms, per bug #547485
$exec --defaults-file="/etc/my-dev.cnf" --datadir="$datadir" \
--socket="$socketfile" --pid-file="$mypidfile" \
--basedir=/usr --user=mysql >/dev/null 2>&1 &
safe_pid=$!
# Spin for a maximum of N seconds waiting for the server to come up;
# exit the loop immediately if mysqld_safe process disappears.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
ret=0
TIMEOUT="$STARTTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
mret=$?
if [ $mret -eq 0 ]; then
break
fi
# exit codes 1, 11 (EXIT_CANNOT_CONNECT_TO_SERVICE) are expected,
# anything else suggests a configuration error
if [ $mret -ne 1 -a $mret -ne 11 ]; then
echo "$RESPONSE"
echo "Cannot check for MySQL Daemon startup because of mysqladmin failure."
ret=1
break
fi
echo "$RESPONSE" | grep -q "Access denied for user" && break
if ! /bin/kill -0 $safe_pid 2>/dev/null; then
echo "MySQL Daemon failed to start."
ret=1
break
fi
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
ret=1
fi
if [ $ret -eq 0 ]; then
action $"Starting $prog: " /bin/true
chmod o+r $mypidfile >/dev/null 2>&1
touch $lockfile
else
action $"Starting $prog: " /bin/false
fi
fi
return $ret
}

stop(){
if [ ! -f "$mypidfile" ]; then
# not running; per LSB standards this is "ok"
action $"Stopping $prog: " /bin/true
return 0
fi
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
TIMEOUT="$STOPTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f $lockfile
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
# failed to read pidfile, probably insufficient permissions
action $"Stopping $prog: " /bin/false
ret=4
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$mypidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?

5.2.7. editiamo mysqld-adm

# vi /etc/init.d/mysqld-adm

5.2.8. popoliamolo con il seguente contenuto

#!/bin/sh
#
# ADM
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my-adm.cnf
# pidfile: /var/run/mysqld/mysqld-adm.pid
### BEGIN INIT INFO
# Provides: mysqld
# Required-Start: $local_fs $remote_fs $network $named $syslog $time
# Required-Stop: $local_fs $remote_fs $network $named $syslog $time
# Short-Description: start and stop MySQL server
# Description: MySQL database server
### END INIT INFO

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

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


exec="/usr/bin/mysqld_safe"
prog="mysqld-adm"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog


# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults -c /etc/my-adm.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/opt/mysql/adm"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql-adm.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld-adm.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld-adm.pid"
mypidfile="$result"


start(){
[ -x $exec ] || exit 5
# check to see if it's already running
MYSQLDRUNNING=0
if [ -f "$mypidfile" ]; then
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ] && [ -d "/proc/$MYSQLPID" ] ; then
MYSQLDRUNNING=1
fi
fi
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
if [ $MYSQLDRUNNING = 1 ] && [ $? = 0 ]; then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
elif [ $MYSQLDRUNNING = 1 ] && echo "$RESPONSE" | grep -q "Access denied for user"
then
# already running, do nothing
action $"Starting $prog: " /bin/true
ret=0
else
# prepare for start
touch "$errlogfile" 2>/dev/null
if [ $? -ne 0 ]; then
# failed to touch log file, probably insufficient permissions
action $"Starting $prog: " /bin/false
return 4
fi
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
# First, make sure $datadir is there with correct permissions
if [ ! -e "$datadir" -a ! -h "$datadir" ]
then
mkdir -p "$datadir" || exit 1
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
[ -x /sbin/restorecon ] && /sbin/restorecon "$datadir"
# Now create the database
action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# We check if there is already a process using the socket file,
# since otherwise this init script could report false positive
# result and mysqld_safe would remove the socket file, which
# actually uses a different daemon.
if fuser "$socketfile" &>/dev/null ; then
echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
action $"Starting $prog: " /bin/false
return 1
fi
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure. (An exception is that we don't force the
# log-error setting, since this script doesn't really depend on that,
# and some users might prefer to configure logging to syslog.)
# Note: set --basedir to prevent probes that might trigger SELinux
# alarms, per bug #547485
$exec --defaults-file="/etc/my-adm.cnf" --datadir="$datadir" \
--socket="$socketfile" --pid-file="$mypidfile" \
--basedir=/usr --user=mysql >/dev/null 2>&1 &
safe_pid=$!
# Spin for a maximum of N seconds waiting for the server to come up;
# exit the loop immediately if mysqld_safe process disappears.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
ret=0
TIMEOUT="$STARTTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1`
mret=$?
if [ $mret -eq 0 ]; then
break
fi
# exit codes 1, 11 (EXIT_CANNOT_CONNECT_TO_SERVICE) are expected,
# anything else suggests a configuration error
if [ $mret -ne 1 -a $mret -ne 11 ]; then
echo "$RESPONSE"
echo "Cannot check for MySQL Daemon startup because of mysqladmin failure."
ret=1
break
fi
echo "$RESPONSE" | grep -q "Access denied for user" && break
if ! /bin/kill -0 $safe_pid 2>/dev/null; then
echo "MySQL Daemon failed to start."
ret=1
break
fi
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
ret=1
fi
if [ $ret -eq 0 ]; then
action $"Starting $prog: " /bin/true
chmod o+r $mypidfile >/dev/null 2>&1
touch $lockfile
else
action $"Starting $prog: " /bin/false
fi
fi
return $ret
}

stop(){
if [ ! -f "$mypidfile" ]; then
# not running; per LSB standards this is "ok"
action $"Stopping $prog: " /bin/true
return 0
fi
MYSQLPID=`cat "$mypidfile" 2>/dev/null`
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
TIMEOUT="$STOPTIMEOUT"
while [ $TIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let TIMEOUT=${TIMEOUT}-1
done
if [ $TIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f $lockfile
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
# failed to read pidfile, probably insufficient permissions
action $"Stopping $prog: " /bin/false
ret=4
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$mypidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?

6. avviamo ciascuna istanza e procediamo inserendo l'utente root abilitato per il relativo host
6.1. istanza "pro"
6.1.1. avviamo l'istanza

# service mysqld-pro start

6.1.2. colleghiamoci con il monitor MySQL
NOTA: usiamo una sintassi personalizzata per garantire il collegamento con i diversi parametri impostati lato server

# mysql --socket=/var/lib/mysql/mysql-pro.sock --protocol=socket -u root

6.1.3. creiamo l'utente root per l'host pro.db.example.loc (sostituire password con una reale password!)

mysql> CREATE USER 'root'@'pro.db.example.loc' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'root'@'pro.db.example.loc' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

6.1.4. stoppiamo l'istanza

# service mysqld-pro stop

6.2. istanza "pre"
6.2.1. avviamo l'istanza

# service mysqld-pre start

6.2.2. colleghiamoci con il monitor MySQL
NOTA: usiamo una sintassi personalizzata per garantire il collegamento con i diversi parametri impostati lato server

# mysql --socket=/var/lib/mysql/mysql-pre.sock --protocol=socket -u root

6.2.3. creiamo l'utente root per l'host pre.db.example.loc (sostituire password con una reale password!)

mysql> CREATE USER 'root'@'pre.db.example.loc' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'root'@'pre.db.example.loc' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

6.2.4. stoppiamo l'istanza

# service mysqld-pre stop

6.3. istanza "dev"
6.3.1. avviamo l'istanza

# service mysqld-dev start

6.3.2. colleghiamoci con il monitor MySQL
NOTA: usiamo una sintassi personalizzata per garantire il collegamento con i diversi parametri impostati lato server

# mysql --socket=/var/lib/mysql/mysql-dev.sock --protocol=socket -u root

6.3.3. creiamo l'utente root per l'host dev.db.example.loc (sostituire password con una reale password!)

mysql> CREATE USER 'root'@'dev.db.example.loc' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'root'@'dev.db.example.loc' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

6.3.4. stoppiamo l'istanza

# service mysqld-dev stop

6.4. istanza "adm"
6.4.1. avviamo l'istanza

# service mysqld-adm start

6.4.2. colleghiamoci con il monitor MySQL
NOTA: usiamo una sintassi personalizzata per garantire il collegamento con i diversi parametri impostati lato server

# mysql --socket=/var/lib/mysql/mysql-adm.sock --protocol=socket -u root

6.4.3. creiamo l'utente root per l'host adm.db.example.loc (sostituire password con una reale password!)

mysql> CREATE USER 'root'@'adm.db.example.loc' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'root'@'adm.db.example.loc' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

6.4.4. stoppiamo l'istanza

# service mysqld-adm stop

7. aggiorniamo i file di configurazione delle istanze introducendo il bind su un solo indirizzo IP . Per fare questo de-commentiamo, nei rispettivi file *.cnf, la riga con la chiave “bind-address”.
7.1. istanza “pro”
7.1.1. editiamo il file my-pro.cnf

# vi /etc/my-pro.cnf

7.1.2. de-commentiamo la riga “bind-address”

...
user=mysql
bind-address=127.0.0.30
symbolic-links=0
...

7.2. istanza “pre”
7.2.1. editiamo il file my-pre.cnf

# vi /etc/my-pre.cnf

7.2.2. de-commentiamo la riga “bind-address”

...
user=mysql
bind-address=127.0.0.31
symbolic-links=0
...

7.3. istanza “dev”
7.3.1. editiamo il file my-dev.cnf

# vi /etc/my-dev.cnf

7.3.2. de-commentiamo la riga “bind-address”

...
user=mysql
bind-address=127.0.0.32
symbolic-links=0
...

7.4. istanza “adm”
7.4.1. editiamo il file my-adm.cnf

# vi /etc/my-adm.cnf

7.4.2. de-commentiamo la riga “bind-address”

...
user=mysql
bind-address=127.0.0.33
symbolic-links=0
...

8. avviamo le nostre istanze

# service mysqld-pro start
# service mysqld-pre start
# service mysqld-dev start
# service mysqld-adm start

9. se vogliamo porle a on come servizio procediamo con le seguenti istruzioni

# chkconfig mysqld-pro on
# chkconfig mysqld-pre on
# chkconfig mysqld-dev on
# chkconfig mysqld-adm on

NOTA: è possibile che qualche errore scappi in mezzo a qualche script. Se un'istanza non parte è una buona pratica consultare il relativo log.

3. Appendice

A titolo di esempio riporto la configurazione base per PhpMyAdmin per usarlo come multi-dominio sia nella situazione di MySQL multi-porte, sia su multi-IP.
Il file di configurazione di PhpMyAdmin è “config.inc.php”. Se lo si è installato da rpm il file si trova:

Se si è installato manualmente si trova nel path della docroot di PhpMyAdmin.
Nel caso di MySQL in multi-porta possiamo usare il socket UNIX e configurare PhpMyAdmin come segue:

?php

$cfg['blowfish_secret'] = 'cbahdterpofsg237543oduryDVH2q-we';

$i = 0;

$i++;
/* PRO */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['socket'] = '/var/lib/mysql/mysql-pro.sock';
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|mysql)';
$cfg['Servers'][$i]['verbose'] = 'PRO';
$cfg['Servers'][$i]['verbose_check'] = TRUE;
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

$i++;
/* PRE */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '3406';
$cfg['Servers'][$i]['socket'] = '/var/lib/mysql/mysql-pre.sock';
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|mysql)';
$cfg['Servers'][$i]['verbose'] = 'PRE';
$cfg['Servers'][$i]['verbose_check'] = TRUE;
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

$i++;
/* DEV */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '3506';
$cfg['Servers'][$i]['socket'] = '/var/lib/mysql/mysql-dev.sock';
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|mysql)';
$cfg['Servers'][$i]['verbose'] = 'DEV';
$cfg['Servers'][$i]['verbose_check'] = TRUE;
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

$i++;
/* ADM */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '3606';
$cfg['Servers'][$i]['socket'] = '/var/lib/mysql/mysql-adm.sock';
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|mysql)';
$cfg['Servers'][$i]['verbose'] = 'ADM';
$cfg['Servers'][$i]['verbose_check'] = TRUE;
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

$cfg['UploadDir'] = '/var/lib/phpMyAdmin/upload';
$cfg['SaveDir'] = '/var/lib/phpMyAdmin/save';

$cfg['PmaNoRelation_DisableWarning'] = TRUE;
?></pre>
<p>Nel caso di MySQL in multi-IP possiamo usare il socket TCP/IP e configurare PhpMyAdmin come segue:</p>
<pre><?php

$cfg['blowfish_secret'] = 'cbahdterpofsg237543oduryDVH2q-we';

$i = 0;

$i++;
/* PRO */
$cfg['Servers'][$i]['host'] = 'pro.db.example.loc';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|mysql)';
$cfg['Servers'][$i]['verbose'] = 'PRO';
$cfg['Servers'][$i]['verbose_check'] = TRUE;
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

$i++;
/* PRE */
$cfg['Servers'][$i]['host'] = 'pre.db.example.loc';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['socket'] = '/var/lib/mysql/mysql-pre.sock';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|mysql)';
$cfg['Servers'][$i]['verbose'] = 'PRE';
$cfg['Servers'][$i]['verbose_check'] = TRUE;
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

$i++;
/* DEV */
$cfg['Servers'][$i]['host'] = 'dev.db.example.loc';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['socket'] = '/var/lib/mysql/mysql-dev.sock';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|mysql)';
$cfg['Servers'][$i]['verbose'] = 'DEV';
$cfg['Servers'][$i]['verbose_check'] = TRUE;
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

$i++;
/* ADM */
$cfg['Servers'][$i]['host'] = 'adm.db.example.loc';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['socket'] = '/var/lib/mysql/mysql-adm.sock';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|mysql)';
$cfg['Servers'][$i]['verbose'] = 'ADM';
$cfg['Servers'][$i]['verbose_check'] = TRUE;
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
$cfg['Servers'][$i]['bs_garbage_threshold'] = 50;
$cfg['Servers'][$i]['bs_repository_threshold'] = '32M';
$cfg['Servers'][$i]['bs_temp_blob_timeout'] = 600;
$cfg['Servers'][$i]['bs_temp_log_threshold'] = '32M';

$cfg['UploadDir'] = '/var/lib/phpMyAdmin/upload';
$cfg['SaveDir'] = '/var/lib/phpMyAdmin/save';

$cfg['PmaNoRelation_DisableWarning'] = TRUE;
?></pre>
<h1>4. Conclusione</h1>
<p>MySQL permette diverse architetture per rispondere alle richieste enterprise. Le indicazioni per configurare repliche automatiche, cluster o soluzioni HA sono abbondanti.<br /> Queste pagine raccolgono una piccola valutazione di opportunità e le indicazioni operative per un caso meno richiesto (e, pertanto, un po' meno documentato), ma utile in alcune situazioni.
 Seguendo queste indicazioni, opportunamente aggiornate per i nostri specifici bisogni, nel giro di poco tempo possiamo avere running un server MySQL multi-istanza con un comodo PhpMyAdmin funzionante per gestire il nostro DBMRS.