Running more than one MySQL instance on same machine can be done in few methods, the easiest (dirty) method can be using different port,socket & data directory configuration.

On linux machine, simply copy /etc/my.cnf file to /etc/my2.cnf

vi /etc/my2.cnf

Change MySQL port/socket, data & log directory to run secondary mysql instance.

[mysqld]
socket=/var/lib/mysql/mysql2.sock
port=3308
user=mysql
log-error=/data1/mysql/mysqld.log
..
..

[mysqld_safe]
log-error=/data1/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld2.pid

Start secondary MySQL instance with my2.cnf default file

bash-4.1$ mysqld_safe --defaults-file=/etc/my2.cnf &

There is a mysql utility to handle mutiple MySQL instances called “mysqld_multi”

I’ve used Ubuntu 14 to demonstrate  [mysqld_multi] configuration.

Install MySQL server, I set password for “root” as “admin”

apt-get install mysql-server
service mysql stop

Duplicate MySQL data directory for  secondary MySQL and change owner.

cp -R /var/lib/mysql /var/lib/mysql2
chown -R mysql:mysql /var/lib/mysql2

Typlical mysqld_multi configuration is given below, I’ve used  root user credentials within [mysqld_multi] to manage both MySQL instances.

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root
password   = admin



[mysqld1]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
skip-external-locking
bind-address        = 127.0.0.1
key_buffer        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit    = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/mysql1-error.log
expire_logs_days    = 10
max_binlog_size         = 100M


[mysqld2]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld2.pid
socket        = /var/run/mysqld/mysqld2.sock
port        = 3308
basedir        = /usr
datadir        = /var/lib/mysql2
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
skip-external-locking
bind-address        = 127.0.0.1
key_buffer        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit    = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/mysql2-error.log
expire_logs_days    = 10
max_binlog_size         = 100M



[mysqldump]
quick
quote-names
max_allowed_packet    = 16M

[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition

[isamchk]
key_buffer        = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Now, it’s time to to start using mysqld_multi

root@test:/var/lib# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running

root@test:/var/lib# mysqld_multi start

root@test:/var/lib# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

If secondary MySQL instance not starting then look at the error log, mostly it will be permission issue. I had the problem when I tried this first time.

Error:/ “Can’t create test file /var/lib/mysql/test.lower-test”

You may need to configure APPARMOR to allow MySql to read/write new data directory (/var/lib/mysql2) , otherwise remove apparmor at your own risk.

/etc/init.d/apparmor stop
/etc/init.d/apparmor teardown
update-rc.d -f apparmor remove
apt-get purge apparmor
reboot

Login into mysql instance, make sure to specify correct port number

root@test:/var/lib# mysql --protocol=TCP --port=3306 -u root -padmin 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye
root@test:/var/lib# mysql --protocol=TCP --port=3308 -u root -padmin 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)