Running Multiple MySQL 5.6 Instances on one server in CentOS 6/RHEL 6/Fedora
There are numerous advantages to run multiple database instances on the save physical server. Here are some reasons that I would like to point out :
-
Utilize existing hardware properly
-
Long lead times to provide physical hardware
-
Reduce Licensing Hardware / OS costs
-
Smaller/manageable data center foot print
-
Reduced overhead
To run multiple instances using MySQL we need to have a couple of things separate from the initial install on MySQL like data directory, init script and config file. It is quite that simple and here is how we do it, I will subscript 2 for all the files/directories that I am going to create to indicate this new second instance:
- Create a new data directory [/var/lib/mysql2] and make mysql user own it.
mkdir /var/lib/mysql2
chown mysql.mysql /var/lib/mysql2/
msql_5_6_multiple_1
- Create / copy existing mysql configuration file, call it my2.cnf and update data directory/port values
cp /etc/my.cnf /etc/my2.cnf
vi /etc/my2.cnf
mysql_5_6_multiple_2
Update the lines as shown in the screenshot above [If you have a custom path, use it]:
datadir=/var/lib/mysql2
port=3307
3. Create/copy existing mysql init file to start/stop/reload etc on this new instance
cp /etc/init.d/mysql /etc/init.d/mysql2
mysql_5_6_multiple_3
- Edit the init file and make some minor changes to make it this instance specific [Four edits required]
vi /etc/init.d/mysql2
Edit 1: Add the following line after line 138 for the init script to handle ports
–port=*) port=echo "$arg" | sed -e 's/^[^=]*=//'
;;
mysql_5_6_multiple_4
Edit 2: At line 215 update my.cnf to point to the new config file my2.cnf for this instance
conf=/etc/my2.cnf
mysql_5_6_multiple_5
Edit 3: At line 257 add -c flag to the arguments to read this config file while parsing server arguments
parse_server_arguments $print_defaults $extra_args mysqld server mysql_server mysql.server -c/etc/my2.cnf
mysql_5_6_multiple_7
Edit 4: Add port argument to mysql_safe command on line 284
b
i
n
d
i
r
/
m
y
s
q
l
d
s
a
f
e
−
−
d
e
f
a
u
l
t
s
−
f
i
l
e
=
/
e
t
c
/
m
y
2.
c
n
f
−
−
d
a
t
a
d
i
r
=
"
bindir/mysqld_safe --defaults-file=/etc/my2.cnf --datadir="
bindir/mysqldsafe−−defaults−file=/etc/my2.cnf−−datadir="datadir" --pid-file=“
m
y
s
q
l
d
p
i
d
f
i
l
e
p
a
t
h
"
−
−
p
o
r
t
=
"
mysqld_pid_file_path" --port="
mysqldpidfilepath"−−port="port” --socket="$datadir"/mysql2.sock $other_args >/dev/null 2>&1 &
update
You final init script looks like the following:
#!/bin/sh
Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB
This file is public domain and comes with NO WARRANTY of any kind
MySQL daemon start/stop script.
Usually this is put in /etc/init.d (at least on machines SYSV R4 based
systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
When this is done the mysql server will be started when the machine is
started and shut down when the systems goes down.
Comments to support chkconfig on RedHat Linux
chkconfig: 2345 64 36
description: A very fast and reliable SQL database engine.
Comments to support LSB init script conventions
BEGIN INIT INFO
Provides: mysqls
Required-Start: $local_fs $network $remote_fs
Should-Start: ypbind nscd ldap ntpd xntpd
Required-Stop: $local_fs $network $remote_fs
Default-Start: 2 3 4 5
Default-Stop: 0 1 6
Short-Description: start and stop MySQL
Description: MySQL is a very fast and reliable SQL database engine.
END INIT INFO
If you change base dir, you must also change datadir. These may get
overwritten by settings in the MySQL configuration files.
basedir=
datadir=
Default value, in seconds, afterwhich the script should timeout waiting
for server start.
Value here is overriden by value in my.cnf.
0 means don’t wait at all
Negative numbers mean to wait indefinitely
service_startup_timeout=900
Lock directory for RedHat / SuSE.
lockdir=’/var/lock/subsys’
lock_file_path="$lockdir/mysql"
The following variables are only set for letting mysql.server find things.
Set some defaults
mysqld_pid_file_path=
if test -z “
b
a
s
e
d
i
r
"
t
h
e
n
b
a
s
e
d
i
r
=
/
u
s
r
b
i
n
d
i
r
=
/
u
s
r
/
b
i
n
i
f
t
e
s
t
−
z
"
basedir" then basedir=/usr bindir=/usr/bin if test -z "
basedir"thenbasedir=/usrbindir=/usr/biniftest−z"datadir”
then
datadir=/var/lib/mysql
fi
sbindir=/usr/sbin
libexecdir=/usr/sbin
else
bindir=“
b
a
s
e
d
i
r
/
b
i
n
"
i
f
t
e
s
t
−
z
"
basedir/bin" if test -z "
basedir/bin"iftest−z"datadir”
then
datadir=“
b
a
s
e
d
i
r
/
d
a
t
a
"
f
i
s
b
i
n
d
i
r
=
"
basedir/data" fi sbindir="
basedir/data"fisbindir="basedir/sbin”
libexecdir="$basedir/libexec"
fi
datadir_set is used to determine if datadir was set (and so should be
not set inside of the --basedir= handler.)
datadir_set=
Use LSB init script functions for printing messages, if possible
lsb_functions="/lib/lsb/init-functions"
if test -f $lsb_functions ; then
. $lsb_functions
else
log_success_msg()
{
echo " SUCCESS! $@"
}
log_failure_msg()
{
echo " ERROR! $@"
}
fi
PATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"
export PATH
mode=$1 # start or stop
[ $# -ge 1 ] && shift
other_args="$*" # uncommon, but needed when called from an RPM upgrade action
# Expected: “–skip-networking --skip-grant-tables”
# They are not checked here, intentionally, as it is the resposibility
# of the “spec” file author to give correct arguments only.
case echo "testing\c"
,echo -n testing
in
c,-n*) echo_n= echo_c= ;;
c,*) echo_n=-n echo_c= ;;
*) echo_n= echo_c=’\c’ ;;
esac
parse_server_arguments() {
for arg do
case “
a
r
g
"
i
n
−
−
b
a
s
e
d
i
r
=
∗
)
b
a
s
e
d
i
r
=
‘
e
c
h
o
"
arg" in --basedir=*) basedir=`echo "
arg"in−−basedir=∗)basedir=‘echo"arg” | sed -e 's/[=]=//'bindir="$basedir/bin" if test -z "$datadir_set"; then datadir="$basedir/data" fi sbindir="$basedir/sbin" libexecdir="$basedir/libexec" ;; --datadir=*) datadir=
echo “KaTeX parse error: Double superscript at position 20: … | sed -e 's/^[^̲=]*=//'` …arg” | sed -e 's/[=]=//';; --port=*) port=
echo “KaTeX parse error: Double superscript at position 20: … | sed -e 's/^[^̲=]*=//'` ;; …arg” | sed -e ‘s/[=]*=//’` ;;
esac
done
}
wait_for_pid () {
verb="$1" # created | removed
pid="$2" # process ID of the program operating on the pid-file
pid_file_path="$3" # path to the PID file.
i=0
avoid_race_condition=“by checking again”
while test $i -ne $service_startup_timeout ; do
case "$verb" in
'created')
# wait for a PID-file to pop into existence.
test -s "$pid_file_path" && i='' && break
;;
'removed')
# wait for this PID-file to disappear
test ! -s "$pid_file_path" && i='' && break
;;
*)
echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path"
exit 1
;;
esac
# if server isn't running, then pid-file will never be updated
if test -n "$pid"; then
if kill -0 "$pid" 2>/dev/null; then
: # the server still runs
else
# The server may have exited between the last pid-file check and now.
if test -n "$avoid_race_condition"; then
avoid_race_condition=""
continue # Check again.
fi
# there's nothing that will affect the file.
log_failure_msg "The server quit without updating PID file ($pid_file_path)."
return 1 # not waiting any more.
fi
fi
echo $echo_n ".$echo_c"
i=`expr $i + 1`
sleep 1
done
if test -z “$i” ; then
log_success_msg
return 0
else
log_failure_msg
return 1
fi
}
Get arguments from the my.cnf file,
the only group, which is read from now on is [mysqld]
if test -x ./bin/my_print_defaults
then
print_defaults="./bin/my_print_defaults"
elif test -x
b
i
n
d
i
r
/
m
y
p
r
i
n
t
d
e
f
a
u
l
t
s
t
h
e
n
p
r
i
n
t
d
e
f
a
u
l
t
s
=
"
bindir/my_print_defaults then print_defaults="
bindir/myprintdefaultsthenprintdefaults="bindir/my_print_defaults"
elif test -x
b
i
n
d
i
r
/
m
y
s
q
l
p
r
i
n
t
d
e
f
a
u
l
t
s
t
h
e
n
p
r
i
n
t
d
e
f
a
u
l
t
s
=
"
bindir/mysql_print_defaults then print_defaults="
bindir/mysqlprintdefaultsthenprintdefaults="bindir/mysql_print_defaults"
else
Try to find basedir in /etc/my.cnf
conf=/etc/my2.cnf
print_defaults=
if test -r KaTeX parse error: Double superscript at position 27: … subpat='^[^̲=]*basedir[^=]*…’
dirs=sed -e "/$subpat/!d" -e 's//\1/' $conf
for d in $dirs
do
d=echo $d | sed -e 's/[ ]//g'
if test -x “
d
/
b
i
n
/
m
y
p
r
i
n
t
d
e
f
a
u
l
t
s
"
t
h
e
n
p
r
i
n
t
d
e
f
a
u
l
t
s
=
"
d/bin/my_print_defaults" then print_defaults="
d/bin/myprintdefaults"thenprintdefaults="d/bin/my_print_defaults”
break
fi
if test -x “
d
/
b
i
n
/
m
y
s
q
l
p
r
i
n
t
d
e
f
a
u
l
t
s
"
t
h
e
n
p
r
i
n
t
d
e
f
a
u
l
t
s
=
"
d/bin/mysql_print_defaults" then print_defaults="
d/bin/mysqlprintdefaults"thenprintdefaults="d/bin/mysql_print_defaults”
break
fi
done
fi
Hope it’s in the PATH … but I doubt it
test -z “$print_defaults” && print_defaults=“my_print_defaults”
fi
Read defaults file from ‘basedir’. If there is no defaults file there
check if it’s in the old (depricated) place (datadir) and read it from there
extra_args=""
if test -r “$basedir/my.cnf”
then
extra_args="-e
b
a
s
e
d
i
r
/
m
y
.
c
n
f
"
e
l
s
e
i
f
t
e
s
t
−
r
"
basedir/my.cnf" else if test -r "
basedir/my.cnf"elseiftest−r"datadir/my.cnf"
then
extra_args="-e $datadir/my.cnf"
fi
fi
parse_server_arguments $print_defaults $extra_args mysqld server mysql_server mysql.server -c/etc/my2.cnf
Set pid file if not given
if test -z "
m
y
s
q
l
d
p
i
d
f
i
l
e
p
a
t
h
"
t
h
e
n
m
y
s
q
l
d
p
i
d
f
i
l
e
p
a
t
h
=
mysqld_pid_file_path" then mysqld_pid_file_path=
mysqldpidfilepath"thenmysqldpidfilepath=datadir/hostname
.pid
else
case “
m
y
s
q
l
d
p
i
d
f
i
l
e
p
a
t
h
"
i
n
/
∗
)
;
;
∗
)
m
y
s
q
l
d
p
i
d
f
i
l
e
p
a
t
h
=
"
mysqld_pid_file_path" in /* ) ;; * ) mysqld_pid_file_path="
mysqldpidfilepath"in/∗);;∗)mysqldpidfilepath="datadir/$mysqld_pid_file_path” ;;
esac
fi
case “$mode” in
‘start’)
# Start daemon
# Safeguard (relative paths, core dumps..)
cd $basedir
echo $echo_n "Starting MySQL"
if test -x $bindir/mysqld_safe
then
# Give extra arguments to mysqld with the my.cnf file. This script
# may be overwritten at next upgrade.
$bindir/mysqld_safe --defaults-file=/etc/my2.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --port="$port" --socket="$datadir"/mysql2.sock $other_args >/dev/null 2>&1 &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?
# Make lock for RedHat / SuSE
if test -w "$lockdir"
then
touch "$lock_file_path"
fi
exit $return_value
else
log_failure_msg "Couldn't find MySQL server ($bindir/mysqld_safe)"
fi
;;
‘stop’)
# Stop daemon. We use a signal here to avoid having to know the
# root password.
if test -s "$mysqld_pid_file_path"
then
mysqld_pid=`cat "$mysqld_pid_file_path"`
if (kill -0 $mysqld_pid 2>/dev/null)
then
echo $echo_n "Shutting down MySQL"
kill $mysqld_pid
# mysqld should remove the pid file when it exits, so wait for it.
wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?
else
log_failure_msg "MySQL server process #$mysqld_pid is not running!"
rm "$mysqld_pid_file_path"
fi
# Delete lock for RedHat / SuSE
if test -f "$lock_file_path"
then
rm -f "$lock_file_path"
fi
exit $return_value
else
log_failure_msg "MySQL server PID file could not be found!"
fi
;;
‘restart’)
# Stop the service and regardless of whether it was
# running or not, start it again.
if $0 stop $other_args; then
$0 start $other_args
else
log_failure_msg “Failed to stop running server, so refusing to try to start.”
exit 1
fi
;;
‘reload’|‘force-reload’)
if test -s “KaTeX parse error: Expected 'EOF', got '&' at position 52: …ead mysqld_pid &̲lt; "mysqld_pid_file_path”
kill -HUP KaTeX parse error: Expected 'EOF', got '&' at position 12: mysqld_pid &̲amp;& log_s…mysqld_pid_file_path"
else
log_failure_msg “MySQL PID file could not be found!”
exit 1
fi
;;
‘status’)
# First, check to see if pid file exists
if test -s “KaTeX parse error: Expected 'EOF', got '&' at position 52: …ead mysqld_pid &̲lt; "mysqld_pid_file_path”
if kill -0 KaTeX parse error: Expected 'EOF', got '&' at position 13: mysqld_pid 2&̲gt;/dev/null ; …mysqld_pid)"
exit 0
else
log_failure_msg “MySQL is not running, but PID file exists”
exit 1
fi
else
# Try to find appropriate mysqld process
mysqld_pid=pidof $libexecdir/mysqld
if test -z
m
y
s
q
l
d
p
i
d
;
t
h
e
n
i
f
t
e
s
t
−
f
"
mysqld_pid ; then if test -f "
mysqldpid;theniftest−f"lock_file_path" ; then
log_failure_msg “MySQL is not running, but lock file ($lock_file_path) exists”
exit 2
fi
log_failure_msg “MySQL is not running”
exit 3
else
log_failure_msg “MySQL is running but PID file could not be found”
exit 4
fi
fi
;;
*)
# usage
basename=basename "$0"
echo “Usage: $basename {start|stop|restart|reload|force-reload|status} [ MySQL server options ]”
exit 1
;;
esac
exit 0
If you plan on deploying more instances you just need to work through edit 2 and 3 mentioned above after you copy the above init file.
- Install default tables for this new database instance
mysql_install_db --datadir=/var/lib/mysql2 --defaults-file=/etc/my2.cnf --user=mysql
mysql_5_6_multiple_8
- Start the new instance
service mysql2 start
mysql_5_6_multiple_10
- Set password for this instance and connect to this new instance
/usr/bin/mysqladmin -u root -h127.0.0.1 -P3307 password ‘opensourcedbmsadmin’
mysql_5_6_multiple_11
mysql -uroot -h127.0.0.1 -P3307 -p
mysql_5_6_multiple_12
- Finally add it to server start-up list
chkconfig --add mysql2
chkconfig mysql2 --level 2345 on
mysql_5_6_multiple_14
If you want to deploy more instances change the subscript and follow above. Make sure that you properly manage memory and processor allocations when running multiple mysql instances on the same server. Please post any questions below and I will answer as soon as I can.