How to install multiple MySQL instances of the same version on a single server

Overview

This will show you how to install multi MySQL instances of the same version on a single server

How to steps

• Make sure you created the user and group mysql:mysql 
• Install the MySQL software of your choice without running the mysql_install_db since we'll be doing it at a later time. On my side I used a tarball instead of rpm or yum so I have better control and also I can install multiple version of MySQL on the same server.
• make sure you have all your filesystems created for your instances. Let say we want 2 instances mysql1 and mysql2. here the folders I created for my exemple
• /mysql/product/5.5.36 ($MYSQL_HOME)
• /mysql/product/5.5.36/config ($MYSQL_CONFIG)
• /mysql/admin/mysql1/noslog
• /mysql/admin/mysql1/nossql
• /mysql/admin/mysql1/pid
• /mysql/admin/mysql1socket
• /mysql/admin/mysql1backup
• /mysql/admin/mysql2/noslog
• /mysql/admin/mysql2/nossql
• /mysql/admin/mysql2/pid
• /mysql/admin/mysql2socket
• /mysql/admin/mysql2backup
• /mysql1/data
• /mysql1/logs
• /mysql2/data
• /mysql2/logs
• Make sure the user and group mysql are owner of all the folders  for mysql and the instances
• chown -R mysql:mysql /mysql /mysql1 /mysql2
• chmod -R 775  /mysql /mysql1 /mysql2
• now create a .cnf file onto $MYSQL__CONFIG and call it mysql_5.5.36.cnf and make sure mysql:mysql have access to it
• here is a simple exemple

[mysqld_safe]
basedir=/mysql/product/5.5.36
ledir=/mysql/product/5.5.36/bin

[mysqld_multi]
mysqld=mysqld_safe
mysqladmin=/mysql/product/5.5.36/bin/mysqladmin
log=/mysql/admin/mysql_multi/logs/mysql_multi.log

[mysqld1]
basedir=/mysql/product/5.5.36datadir=/mysql1/data
log-bin=/mysql1/logs/log-bin
socket=/mysql/admin/mysql1/socket/mysql1.socket
pid-file=/mysql/admin/mysql1/pid/mysql1.pid
log-error=/mysql/admin/mysql1/noslog/mysql1-error.log
max_binlog_size=100M
binlog-format=MIXED
port=3307
expire_logs_days=5
default-storage-engine=innodb
server-id=5401
skip-slave-start

[mysqld2]
basedir=/mysql/product/5.5.36datadir=/mysql2/data
log-bin=/mysql2/logs/log-bin
socket=/mysql/admin/mysql2/socket/mysql2.socket
pid-file=/mysql/admin/mysql2/pid/mysql1.pid
log-error=/mysql/admin/mysql2/noslog/mysql1-error.log
max_binlog_size=100M
binlog-format=MIXED
port=3308
expire_logs_days=5
default-storage-engine=innodb
server-id=5402
skip-slave-start

•  Now you have to create your instances
• cd $MYSQL_HOME
• ./scripts/mysql_install_db --defaults-file=$MYSQL_HOME/config/mysql_5.5.36.cnf --basedir=$MYSQL_HOME --datadir=/mysql1/data --user=mysql
• ./scripts/mysql_install_db --defaults-file=$MYSQL_HOME/config/mysql_5.5.36.cnf --basedir=$MYSQL_HOME --datadir=/mysql2/data --user=mysql
• Now you can start the 2 instances using mysqld_multi
• mysqld_multi --defaults-file=$MYSQL_CONFIG/mysql_5.5.36.cnf start 1
• mysqld_multi --defaults-file=$MYSQL_CONFIG/mysql_5.5.36.cnf start 2

• From that point
  your instances are up and running ad you can secure them
• cd $MYSQL_HOME
• ./bin/mysql_secure_installation defaults-file=$MYSQL_HOME/config/mysql_5.5.36.cnf --port=yourinstanceport --socket=yourinstancesocket

How to run mysql and other commands for each instance

To run mysql you have to run the mysql and other command with the --defaults-file, --port and --socket options so you indicate to mysql on wich instance you want to connect

Note

• I had few issues with some of the command where even if I specified the location of the .cnf that contains the location of the socket. it was not reading it. this is a know issue ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'.In order to fix it  create a soft link then remove it after

Ex: ln -fs /mysql/admin/mysql1/socket/mysql1.socket /tmp/mysql.sock
once done remove it
rm -f /tmp/mysql.sock