Running multiple mysql instances on same machine

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)