Last modified: Jan. 27, 2014

Contents
1 - Summary
2 - Create new directories
3 - Create new configuration file
4 - Create new init script
5 - Enable the service
6 - Install system databases
7 - Start new instance
8 - Set password
9 - Connect to new instance


1 - Summary

This guide will show how to have multiple instances of MySQL running in Red Hat
Enterprise Linux. There are various ways of doing this like using mysqld_multi,
but this guide will go about about it by using a separate configuration file
and separate init script. This has been tested in Red Hat Enterprise Linux 6
(x86_64).


2 - Create new directories

Create new directories for the data, logs and pid of the new instance.
# grep -i mysql /etc/passwd /etc/group
/etc/passwd:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
/etc/group:mysql:x:27:
# sudo mkdir -p /data/databases/test
[sudo] password for testuser:
# sudo mkdir -p /data/databases/test/mysql/ib_data
[sudo] password for testuser:
# sudo mkdir -p /data/databases/test/mysql/ib_logs
[sudo] password for testuser:
# sudo chown -R mysql:mysql /data/databases/test/
[sudo] password for testuser:
# sudo mkdir -p /data/logs/mysqld2
[sudo] password for testuser:
# sudo chown -R mysql:mysql /data/logs/mysqld2/
[sudo] password for testuser:
# sudo chmod -R 777 /data/logs/mysqld2/
[sudo] password for testuser:
# sudo mkdir /var/run/mysqld2/
[sudo] password for testuser:
# sudo chown -R mysql:mysql /var/run/mysqld2/
[sudo] password for testuser:


3 - Create new configuration file

Create a new configuration file for the new instance.
  [client]
  port = 3307
  socket = /data/databases/test/mysql/mysql.sock
  [mysqld]
  bind-address = 0.0.0.0
  datadir = /data/databases/test/mysql
  default-storage-engine = InnoDB
  innodb_data_home_dir = /data/databases/test/mysql/ib_data
  innodb_file_per_table
  innodb_log_group_home_dir = /data/databases/test/mysql/ib_logs
  key_buffer_size = 256M
  log-error = /data/logs/mysqld2/errors.log
  port = 3307
  socket = /data/databases/test/mysql/mysql.sock
  user = mysql
  [mysql]
  no-auto-rehash
  socket = /data/databases/test/mysql/mysql.sock
  [mysqladmin]
  socket = /data/databases/test/mysql/mysql.sock
  [mysqldump]
  quick
  socket = /data/databases/test/mysql/mysql.sock
# sudo vi /etc/my2.cnf
[sudo] password for testuser:


4 - Create new init script

We will first also creat copies of the mysqld_safe script and mysqld binaries.
# rpm -qa | grep -i ^mysql | grep -i server
mysql*server*
# rpm -ql mysql*server* | grep -iE '(mysqld_safe|mysqld)'
/etc/logrotate.d/mysqld
/etc/rc.d/init.d/mysqld
/etc/sysconfig/mysqld
/usr/bin/mysqld_multi
/usr/bin/mysqld_safe
/usr/bin/mysqldumpslow
/usr/libexec/mysqld
/usr/share/man/man1/mysqld_multi.1.gz
/usr/share/man/man1/mysqld_safe.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man8/mysqld.8.gz
/var/log/mysqld.log
/var/run/mysqld
# sudo cp /usr/bin/mysqld_safe /usr/bin/mysqld2_safe
[sudo] password for testuser:
# sudo cp /usr/libexec/mysqld /usr/libexec/mysqld2
[sudo] password for testuser:

Make the following changes. The first section of code is from the original init
script, while the second section is from the new init script for the second
instance.
< # 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.cnf
< # pidfile: /var/run/mysqld/mysqld.pid
---
> # mysqld2       This shell script takes care of starting and stopping
> #               the MySQL subsystem (mysqld2).
> #
> # chkconfig: - 64 36
> # description:  MySQL database server.
> # processname: mysqld2
> # config: /etc/my2.cnf
> # pidfile: /var/run/mysqld2/mysqld2.pid

< exec="/usr/bin/mysqld_safe"
< prog="mysqld"

< # Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
---
> exec="/usr/bin/mysqld2_safe"
> prog="mysqld2"

> # Set timeouts here so they can be overridden from /etc/sysconfig/mysqld2

< get_mysql_option mysqld datadir "/var/lib/mysql"
< datadir="$result"
< get_mysql_option mysqld socket "$datadir/mysql.sock"
< socketfile="$result"
< get_mysql_option mysqld_safe log-error "/var/log/mysqld.log"
< errlogfile="$result"
< get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid"
< mypidfile="$result"
---
> get_mysql_option mysqld2 datadir "/data/databases/test/mysql"
> datadir="$result"
> get_mysql_option mysqld2 socket "$datadir/mysql.sock"
> socketfile="$result"
> get_mysql_option mysqld2_safe log-error "/data/logs/mysqld2/mysqld2.log"
> errlogfile="$result"
> get_mysql_option mysqld2_safe pid-file "/var/run/mysqld2/mysqld2.pid"
> mypidfile="$result"

<         chown mysql:mysql "$datadir"
<         chmod 0755 "$datadir"
<         # 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   --datadir="$datadir" --socket="$socketfile" \
<                 --pid-file="$mypidfile" \
<                 --basedir=/usr --user=mysql >/dev/null 2>&1 &
---
>         chown mysql:mysql "$datadir"
>         chmod 0755 "$datadir"
>         mysqld_safe_differences=`diff /usr/bin/mysqld_safe /usr/bin/mysqld2_safe | wc -l`
>         if [ $mysqld_safe_differences -ne 0 ]; then
>             cp -f /usr/bin/mysqld_safe /usr/bin/mysqld2_safe > /dev/null 2>&1
>         fi
>         mysqld_differences=`diff /usr/libexec/mysqld /usr/libexec/mysqld2 | wc -l`
>         if [ $mysqld_differences -ne 0 ]; then
>             cp -fp /usr/libexec/mysqld /usr/libexec/mysqld2 > /dev/null 2>&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/my2.cnf --mysqld=mysqld2 --datadir="$datadir" \
>         	--socket="$socketfile" --pid-file="$mypidfile" \
>         	--basedir=/usr --user=mysql >/dev/null 2>&1 &

<         return $ret
< }
< 
< restart(){
---
>         return $ret
> }
> 
> status(){
>         if [ -e "$mypidfile" ]; then
>             pid=`cat "$mypidfile"`
>             echo "mysqld2 (pid $pid) is running..."
>         else
>             echo "mysqld2 is stopped"
>         fi
> }
> 
> restart(){


<   status)
<     status $prog
---
<   status)
<     status

# rpm -ql mysql*server* | grep -i init.d
/etc/rc.d/init.d/mysqld
# sudo cp /etc/rc.d/init.d/mysqld /etc/rc.d/init.d/mysqld2
[sudo] password for testuser:
# sudo vi /etc/rc.d/init.d/mysqld2
[sudo] password for testuser:


5 - Enable the service

Enable the service so that it will start when the system starts up.
# sudo chkconfig --add mysqld2
[sudo] password for testuser:
# sudo chkconfig mysqld2 on
[sudo] password for testuser:


6 - Install system databases

Instal the system databases for the new instance.
# sudo mysql_install_db --defaults-file=/etc/my2.cnf --datadir=/data/databases/test --user=mysql
[sudo] password for testuser:


7 - Start new instance

Start the new instance.
# sudo service mysqld2 start
[sudo] password for testuser:
Starting mysqld2:  OK
# sudo service mysqld2 status
[sudo] password for testuser:
mysqld2 (pid 17833) is running...


8 - Set password

Set the root password for this new instance.
# mysqladmin -u root -h 127.0.0.1 -P 3307 password '********'


9 - Connect to new instance

There are a few ways to log in.
# mysql -u root -p -h 127.0.0.1 -P 3307
Enter password:

# mysql -u root -p -S /data/databases/test/mysql/mysql.sock
Enter password:

mysql> show global variables like '%port%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_support_xa   | ON    |
| large_files_support | ON    |
| port                | 3307  |
| report_host         |       |
| report_password     |       |
| report_port         | 3307  |
| report_user         |       |
+---------------------+-------+
7 rows in set (0.00 sec)

mysql> quit
Bye