Creating a new instance of MySQL server in the same host

STEP I. create new MySQL datadir

mkdir /home/mysql2
cp -r /var/lib/mysql/mysql /home/mysql2
chown -R mysql:mysql /home/mysql2
cp /etc/my.cnf /etc/my2.cnf

Edit:

port        = 3307
datadir     = /home/mysql2
socket        = /var/run/mysqld/mysqld2.sock
pid-file    = /var/run/mysqld/mysqld2.pid

Configure apparmor at /etc/apparmor.d/local/usr.sbin.mysqld (in Ubuntu 12.04, after pulling my hair for 1.5 days, I found a blog post saying that I had to remove the /var… stupid change):

/var/run/mysqld/mysqld2.pid w,
/var/run/mysqld/mysqld2.sock w,
/home/mysql2/ r,
/home/mysql2/** rwk,

Restart apparmor:

/etc/init.d/apparmor restart

STEP II. start the new instance
For some reason, in Ubuntu Jaunty, I can’t start the new instance from the /etc/my2.cnf file. Instead, I have to do this:

/usr/sbin/mysqld –basedir=/usr –datadir=/home/mysql2 –user=mysql –pid-file=/var/run/mysqld/mysqld2.pid –skip-external-locking –port=3307 –socket=/var/run/mysqld/mysqld2.sock

STEP III. connecting to the new instance

mysql -p<password> -P 3307 -h <hostname>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s