Running Multiple MySQL 5.6 Instances on one server in CentOS 6/RHEL 6/Fedora

Standard

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 :

1. Utilize existing hardware properly

2. Long lead times to provide physical hardware

3. Reduce Licensing Hardware / OS costs

4. Smaller/manageable data center foot print

5. 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:

1. 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

2. 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

4. 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

$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 &

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 "$basedir"
then
  basedir=/usr
  bindir=/usr/bin
  if test -z "$datadir"
  then
    datadir=/var/lib/mysql
  fi
  sbindir=/usr/sbin
  libexecdir=/usr/sbin
else
  bindir="$basedir/bin"
  if test -z "$datadir"
  then
    datadir="$basedir/data"
  fi
  sbindir="$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 "$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 "$arg" | sed -e 's/^[^=]*=//'`
                    datadir_set=1
        ;;
      --pid-file=*) mysqld_pid_file_path=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
      --port=*)     port=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
      --service-startup-timeout=*) service_startup_timeout=`echo "$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 $bindir/my_print_defaults
then
  print_defaults="$bindir/my_print_defaults"
elif test -x $bindir/mysql_print_defaults
then
  print_defaults="$bindir/mysql_print_defaults"
else
  # Try to find basedir in /etc/my.cnf
  conf=/etc/my2.cnf
  print_defaults=
  if test -r $conf
  then
    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/bin/my_print_defaults"
      then
        print_defaults="$d/bin/my_print_defaults"
        break
      fi
      if test -x "$d/bin/mysql_print_defaults"
      then
        print_defaults="$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 $basedir/my.cnf"
else
  if test -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 "$mysqld_pid_file_path"
then
  mysqld_pid_file_path=$datadir/`hostname`.pid
else
  case "$mysqld_pid_file_path" in
    /* ) ;;
    * )  mysqld_pid_file_path="$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 "$mysqld_pid_file_path" ; then
      read mysqld_pid < "$mysqld_pid_file_path"
      kill -HUP $mysqld_pid && log_success_msg "Reloading service MySQL"
      touch "$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 "$mysqld_pid_file_path" ; then
      read mysqld_pid < "$mysqld_pid_file_path"
      if kill -0 $mysqld_pid 2>/dev/null ; then
        log_success_msg "MySQL running ($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 $mysqld_pid ; then
        if test -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.

5. 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

6. Start the new instance

service mysql2 start

mysql_5_6_multiple_10

7. 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

8. 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.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

About Prashanth Goriparthi

Sr. Database Administrator / Architect

10 Comments

  1. I found that if I ran two instances, the second would overwrite the socket from the first to the default socket location: –socket=/var/lib/mysql/mysql.sock

    This means that no matter if you use:
    ‘mysql -u root -p -P3307’ or ‘mysql -u root -p’ they would both connect to the second-started instance via the socket.

    I modifed the /etc/init.d/mysql2 file to include the –socket=/var/lib/mysql2/mysql.sock in the start sequence so I could then run:
    ‘mysql -u root -p –socket=/var/lib/mysql2/mysql.sock’ and connect from localhost to the second instance.

    Take-home: If you’re connecting from the local shell, verify you’re really connecting to the right instance.

  2. Thanks for Prashanth Goriparthi for such a nice documentation.
    It is mentioned in MySql website mysql always try to connect with Socket, so we have to give mysql connectivity command as per Mr. Goriparthi #mysql -uroot -h127.0.0.1 -P3307 -p Otherwise it will connect first mysql socket (which sometimes very confusing that you can see 3306 port databases). So, better modify Line number 284 as $bindir/mysqld_safe –datadir="$datadir" –pid-file="$mysqld_pid_file_path" –port="$port" –socket=/var/lib/mysql2/mysql2.sock $other_args >/dev/null 2>&1 &
    And you can see a new file generated in "/var/lib/mysql2/mysql2.sock" which gives you an idea that your server is also up and ready for connection.
    So, that MySQL2 can also be connected with new socket as ‘mysql -u root -p –socket=/var/lib/mysql2/mysql2.sock’

  3. Line 284:
    If you are specifying –defaults-file, I don’t understand why you have to explicity list –datadir="$datadir" –pid-file="$mysqld_pid_file_path" –port="$port" –socket="$datadir"/my.sock
    If these parameters are listed in the my2.cnf file under the [mysqld_safe]/[mysqld] heading, the mysqld_safe script will start it the instance using the values in the my2.cnf file, correct? Or have I missed something?
    If all these parameters were explicity stated under the [mysqld_safe] & [mysqld] headings, then coulnd’t our command be???:
    $bindir/mysqld_safe –defaults-file=/etc/my2.cnf $other_args >/dev/null 2>&1 &
    Thanks!

  4. if you define arguments in [mysqld_safe], add it to parse_server_arguments. Note, you can also use $conf variable for -c rather than explicity the defining the configuration file again!

    parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server mysqld_safe -c$conf`

  5. Hi , i am using above one but i am facing problem. could pls help me on this.

    [root@uspro1155 mysql5.6]# service mysql.server start
    Starting MySQL.The server quit without updating PID file (/root/mysql5.6/uspro1155.startdedicated.com.pid). [FAILED]
    [root@uspro1155 mysql5.6]#
    we have linux server, already 5.1 mysql is there, its running and i need to install 5.6 mysql but we are facing above error. Kindly advice.

  6. I was asked to add a WP site to a Red Hat server where my predecessor (who has left the company) created a WP site. I am using the instructions you provide to create a second MySQL db for this second WP site. I was able to follow along without a hitch, however, in the init.d directory I only see mysyqld NOT mysql and vi’ing the file I see it is not the file you show. I am not sure where else this init file would be or how the current instance is running without it.

    Suggestions?

  7. Pingback: MySQL多实例环境的创建(CentOS) | ASPIRE

Leave a Reply

* Captcha [Please fill/solve below] : * Time limit is exhausted. Please reload the CAPTCHA.