Enabling Multiple Mysql Instances on the Same Server

From Kyle's Wiki
Jump to: navigation, search

Using the mysqld_multi script, you can spawn multiple instances of mysql on the same server. This can be good for development, Mysql Replication, or backups.

Setting up your Config file

You can use the one my.cnf file in /etc/mysql/ to control both of your instances. Here is an example. Just put the number after [mysqld] like [mysqld1].

Don't forget to add server-id = X where x is a unique number. Also, you can't mix your files together, so make sure they are all storing to different places.

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin

[mysqld1]
port            = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 20
max_binlog_size         = 100M
log_error                = /var/log/mysql/error.log

[mysqld2]
port            = 3305
datadir=/var/lib/mysql-slave
socket=/var/lib/mysql/mysql-slave.sock
user=mysql
server-id               = 2
log_error                = /var/log/mysql/slave-error.log

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index

master-host = 127.0.0.1
master-port = 3306
master-user = replicant
master-password = replicator


Setting Up Init Scripts on Ubuntu with Upstart

You will need to files in /etc/init/ if you want 2 instances of mysql. Here is an example of each: (These are taken from Ubuntu Lucid 10.04)

# MySQL Service

description     "MySQL Server"
author          "Mario Limonciello <[email protected]>"

start on (net-device-up
          and local-filesystems
	  and runlevel [2345])
stop on runlevel [016]

respawn

env HOME=/etc/mysql
umask 007

pre-start script
    #Sanity checks
    [ -r $HOME/my.cnf ]
    [ -d /var/run/mysqld ] || install -m 755 -o mysql -g root -d /var/run/mysqld
    # Load AppArmor profile
    if aa-status --enabled 2>/dev/null; then
        apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld || true
    fi
    LC_ALL=C BLOCKSIZE= df --portability /var/lib/mysql/. | tail -n 1 | awk '{ exit ($4<4096) }'
end script

script
    if [ "$RUNLEVEL" = "0" -o "$RUNLEVEL" = "1" -o "$RUNLEVEL" = "6" ]; then
        exec /usr/bin/mysqld_multi stop 1
    else
        exec /usr/bin/mysqld_multi start 1
    fi
end script

post-start script
    for i in `seq 1 30` ; do
        /usr/bin/mysqladmin --defaults-file="${HOME}"/debian.cnf ping && {
            exec "${HOME}"/debian-start
            # should not reach this line
            exit 2
        }
        sleep 1
    done
    exit 1
end script

And Number 2 (the slave):

# MySQL Service slave

description     "MySQL Server"
author          "Mario Limonciello <[email protected]>"

start on (net-device-up
          and local-filesystems
	  and runlevel [2345])
stop on runlevel [016]

respawn

env HOME=/etc/mysql
umask 007


script
    if [ "$RUNLEVEL" = "0" -o "$RUNLEVEL" = "1" -o "$RUNLEVEL" = "6" ]; then
        exec /usr/bin/mysqld_multi stop 2
    else
        exec /usr/bin/mysqld_multi start 2
    fi
end script

So you have the /etc/init/mysql.conf and /etc/init/mysql-slave.conf, now you should be able to start them:

service mysql start
service mysql-slave start

Setting Up Init Scripts on Centos / Redhat

This is more complicated than upstart.

One way to do it is with a mysqld_multi script, that inidividual mysql_X scripts can call.

Then Run:

chkconfig mysqld off
chkconfig mysql_1 on
chkconfig mysql_2 on
Personal tools
Namespaces

Variants
Actions
Efforts
Toolbox
Meta