Home > MySQL > MySQL multiple instances on Ubuntu

MySQL multiple instances on Ubuntu

Production systems must take care of security and tweaking not covered by this post

The post looks long, but you can get another instance of MySQL running in less than 5 mins


  1. OS: Ubuntu 12.04 LTS server edition – up to date
  2. Already has MySQL installed that comes default with 12.04 – you can easily install LAMP with the command tasksel
  3. MySQL Server version: 5.5.34-0ubuntu0.12.04.1 (Ubuntu)
  4. You have OS root privileges
  5. Default MySQL is running on port 3306

What will we do

  1. Set up 2 more MySQL instances on ports 3307 and 3308
  2. Each instance will have their own config files, data directories and log directories

Stopping default MySQL instance

sudo service mysql stop
sudo ps -A | grep mysql

Creating data directories

  • MySQL cannot share data directories, so we need to set up new ones
  • default basedir = /usr, this can be shared across instances
  • default instance port = 3306 and data dir = /var/lib/mysql
  • new instance       port = 3307 and data dir = /var/lib/mysql3307
  • new instance       port = 3308 and data dir = /var/lib/mysql3308
  • MySQL must own data dirs
  • we need to set rules in apparmor to let MySQL access the new dirs
sudo mkdir /var/lib/mysql3307
sudo mkdir /var/lib/mysql3308
sudo chown -R mysql /var/lib/mysql3307
sudo chown -R mysql /var/lib/mysql3308

Creating log directories

  • create separate log dirs for new MySQL instances
  • default log dir = /var/log/mysql
  • new log dir for 3307 = /var/log/mysql/mysql3307
  • new log dir for 3308 = /var/log/mysql/mysql3308
  • log dirs must be owned by MySQL
  • note that /var/log/mysql already has apparmor permissions for MySQL, so any dir under it also has access
sudo mkdir /var/log/mysql/mysql3307
sudo mkdir /var/log/mysql/mysql3308
sudo chown -R mysql /var/log/mysql/mysql3307
sudo chown -R mysql /var/log/mysql/mysql3308

Creating config files

  • create the config files for new instances by copying default file
  • default config file = /etc/mysql/my.cnf
  • config file for 3307 = /etc/mysql/my3307.cnf
  • config file for 3308 = /etc/mysql/my3308.cnf
  • see config files on github
  • /etc/mysql/my3307.cnf
  • /etc/mysql/my3308.cnf
  • special care has to be taken so that these values are different
  • datadir
  • server-id
  • all port entries
  • all socket entries
  • all pid-file entries
  • all log file entries, general, error, binary etc
sudo cp /etc/mysql/my.cnf /etc/mysql/my3307.cnf
sudo cp /etc/mysql/my.cnf /etc/mysql/my3308.cnf

Apparmor settings ( skip if you dont have this )

  • apparmor is like an application firewall – comes installed default with Ubuntu server
  • command aa-status will show you if it is loaded
  • default apparmor profile for MySQL = /etc/apparmor.d/usr.sbin.mysqld
  • put additional rules in /etc/apparmor.d/local/usr.sbin.mysqld
  • specify the correct data dirs, pid and socket files for each instance – see file on github
  • /etc/apparmor.d/local/usr.sbin.mysqld
  • after modifying, restart apparmor
sudo service apparmor reload

Installing new MySQL instances

  • install MySQL files into the new data dirs for port 3307 and port 3308
  • after this, under each new data dir, you will see the mysql, performance_schema and test dirs
  • this will install MySQL with default settings,  no root password
  • in the below commands, you can use the – -verbose flag to see more details
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3307 --defaults-file=/etc/mysql/my3307.cnf
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3308 --defaults-file=/etc/mysql/my3308.cnf

Starting the mysql instances

  • start the default instance on 3306
  • start instances on 3307 and 3308 in the background
sudo service mysql start
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql

Accessing the new instances

  • Note that the new instances on 3307 and 3308 will not have a root password
  • it is important to specify host and host=
  • if host is omitted, or localhost is given, then default instance is assumed ( on 3306 )
  • remember to explicitly specify host and port for all commands
mysql -h --port=3307 -u root
mysql -h --port=3308 -u root

Shutting down the MySQL instances

  • We will use mysqladmin to cleanly shutdown
  • it is important to specify host and and port
  • no password for now
mysqladmin -h --port=3307 -u root shutdown
mysqladmin -h --port=3308 -u root shutdown

Post installation set up ( for each instance )

  • update root password
  • drop all anonymous users – check for users with empty username
  • drop database test
  • flush privileges

update mysql.user set password=PASSWORD('myRootPassword') where User='root';
drop database test;
drop user ''@'localhost';
drop user ''@'%';
drop user ''@'ubuntu';
flush privileges;

Starting new instances on boot and reboot

  • Put commands in the file /etc/rc.local to start new instances on boot
  • the rc.local file will look like this
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql
exit 0


  • Most of the time, the problem is due to incorrect permissions, or incorrect config files or apparmor
  • Check error logs in /var/log/mysql for each instance
  • Make sure that each mysql config has different values for variables
  • Make sure that directory permissions are correct, mysql must own data and log dirs
  • remember to specify host and port explicitly when connecting
  • if connecting from a remote host, check the bind-address config variable in the config file for the instance
  • if connecting from remote host, make sure that ports 3307 and 3308 are open and no other applications are using them
  • Make sure that all dirs have the apparmor permissions and you have reloaded apparmor.
  • You can see enties like the below in /var/log/syslog if apparmor is blocking mysql
Nov 7 11:51:16 ubuntu kernel: [ 1080.756609] type=1400 audit(1383843076.476:32): apparmor="DENIED" operation="mknod"
parent=2749 profile="/usr/sbin/mysqld" name="/var/lib/mysql1/ibdata1" pid=3559 comm="mysqld" requested_mask="c" 
denied_mask="c" fsuid=102 ouid=102

Finally – bash aliases if you need

  • To make commands simpler to type, you can set up bash aliases
  • Put the below aliases or whatever you want, in a file called .bash_aliases in your home dir
  • If the file is not found, create it in ~/.bash_aliases
  • remember to open a new shell for these commands to take effect
########### mysql @ 3307 ##############################
alias mysql3307-start='sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql'
alias mysql3307-stop='mysqladmin -h --port=3307 -u root shutdown -p'
alias mysql3307-root='mysql -h --port=3307 -u root -p'
########### mysql @ 3308 ##############################
alias mysql3308-start='sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql'
alias mysql3308-stop='mysqladmin -h --port=3308 -u root shutdown -p'
alias mysql3308-root='mysql -h --port=3308 -u root -p'
Categories: MySQL
  1. June 15, 2014 at 6:59 PM

    I needed quick config, and none of the “official” sites where providing me with a tutorial such as yours. Thank you. Very much. Really. You rock. FYI – this is for a large multinational project. Thanks again.

  2. suraj
    October 29, 2014 at 10:04 AM

    Very Nice post. its simple and easy to use. Thanks a ton

  3. Balázs Ruda
    December 14, 2014 at 10:09 AM

    FATAL ERROR: Could not find my-default.cnf

    If you compiled from source, you need to run ‘make install’ to
    copy the software into the correct location ready for operation.

    If you are using a binary release, you must either be at the top
    level of the extracted archive, or pass the –basedir option
    pointing to that location.


    • December 14, 2014 at 5:21 PM

      what defaults file are you using ?
      have you given the correct defaults file in all the commands ?
      are your mysql instances running ? – is atleast 1 mysql instance running ?

  4. Jasbir
    January 5, 2015 at 11:40 PM

    excellent post

  5. Tyshan Shi
    February 14, 2015 at 8:39 AM

    December 14, 2014 at 10:09 AM Reply
    FATAL ERROR: Could not find my-default.cnf

    This happens in mysql5.6 version, copy /etc/mysql/my.cnf to /usr/share/mysql/my-default.cnf

    Then run the command to install db

  6. March 20, 2015 at 9:06 AM

    me too facing the same FATAL ERROR: Could not find my-default.cnf error!
    It didn’t help

    • March 20, 2015 at 4:09 PM

      @abhishek sharma
      make sure that you know where your mysql default config files are located and use them in the commands
      your mysql version might be different and files might be in different locations

  7. March 30, 2015 at 9:56 PM

    I am trying to start the service by using the command ,

    mysqld_safe –defaults-file=/etc/mysql/my3307.cnf –user=mysql

    Getting thr following error

    150330 17:53:01 mysqld_safe Can’t log to error log and syslog at the same time. Remove all –log-error configuration options for –syslog to take effect.
    150330 17:53:01 mysqld_safe Logging to ‘/var/log/mysql/mysql3307/error.log’.
    150330 17:53:01 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql3307
    150330 17:53:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld3307.pid ended

  8. Tariqul Islam
    April 3, 2015 at 9:10 AM

    mysqld_safe A mysqld process already exists occured

  9. April 13, 2015 at 9:17 AM

    150413 9:12:59 [Note] Server socket created on IP: ‘’.
    150413 9:12:59 [ERROR] Can’t start server : Bind on unix socket: Permission denied
    150413 9:12:59 [ERROR] Do you already have another mysqld server running on socket: /var/run/mysqld/mysqld.sock3307 ?
    150413 9:12:59 [ERROR] Aborting

    150413 9:12:59 InnoDB: Starting shutdown…
    150413 9:13:00 InnoDB: Shutdown completed; log sequence number 1595685
    150413 9:13:00 [Note] /usr/sbin/mysqld: Shutdown complete

    150413 09:13:00 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid3307 ended

    I did lot of googling about this , but bad luck.
    i changed permission of /var/run/mysqld to 777 with chown -R mysql:mysql , but didn’t work.

  10. April 16, 2015 at 6:31 AM

    i just change the path /var/run/mysqld to other path and its works

  11. southom
    July 2, 2015 at 1:34 AM

    Wow.Can you help me to fix some probroms like this:
    150702 01:13:40 mysqld_safe Starting mysqld daemon with databases from /www/.mysql/data3308
    150702 1:13:40 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
    150702 1:13:40 [Note] /usr/sbin/mysqld (mysqld 5.5.43-0ubuntu0.14.04.1) starting as process 27206 …
    150702 1:13:40 [Warning] Can’t create test file /www/.mysql/data3308/localhost.lower-test
    150702 1:13:40 [Warning] Can’t create test file /www/.mysql/data3308/localhost.lower-test
    150702 1:13:40 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
    150702 1:13:40 [Note] Plugin ‘FEDERATED’ is disabled.

    • July 2, 2015 at 2:37 PM

      looks like a permission issue – make sure that the user mysql is running as has write permissions to www/.mysql

      • southom
        July 5, 2015 at 4:38 AM

        Thinks for replied.As what you suggested

        1th,make sure the folder is writable.
        2nd,add some thing into /etc/apparmor.d/usr.sbin.mysqld like that:
        /www/.mysql/data3308/ r,
        /www/.mysql/data3308/** rwk,
        /var/run/mysqld/mysqld3308.pid rw,
        /var/run/mysqld/mysqld3308.sock w,

        Then restart /etc/init.d/apparmor,and try again,its works!
        Haha,Thanks again.

  12. Ventsi
    July 28, 2015 at 12:05 PM

    How I can reset the root password of some of the mysql instances NOT the main MySQL instalation, but some of it’s instances. Because everywhere in the net is explained only for the main Mysql installation not about some of it’s instnaces

    • July 28, 2015 at 5:18 PM


      just add skip-grant-tables to the config file and restart the mysql instance
      it should allow you yo login without a password
      then you update the password
      remove the skip grant option from the config file and restart mysql


      • Ventsi
        July 30, 2015 at 10:54 AM

        It works!!! Thank you very much!!!
        Just to explain in details what exactly I’ve made if somebody else needs more detailed Info. My conf file was named: my2.cnf and was placed in /etc/mysql/my2.cnf
        I added the lines you told me: skip-grant-tables in the section: [mysqld] so the section looks like that:
        # * Basic Settings
        user = mysql
        pid-file = /var/run/mysqld/mysqld2.pid
        socket = /var/run/mysqld/mysqld2.sock
        port = 3326
        basedir = /usr
        datadir = /var/lib/mysql2
        tmpdir = /tmp
        lc-messages-dir = /usr/share/mysql

        reset the mysql instance 2, and I was enabled to login without a pass🙂

        Thank you again🙂

  13. ajith
    February 22, 2016 at 5:36 AM

    can you help me to fix a problem ,
    when i run “sudo -b mysqld_safe –defaults-file=/etc/mysql/my3307.cnf –user=mysql”

    160222 11:04:20 mysqld_safe Logging to syslog.
    160222 11:04:20 mysqld_safe A mysqld process already exists

    • February 22, 2016 at 8:08 PM


      have you changed the port of the second instance correctly ? It seems that a mysql process is already running on the specified port

      check it with these commands and it should show you all mysql processes

      netstat -tunpl | grep mysql

      • April 15, 2016 at 5:44 PM

        netstat -tunpl | grep mysql it gives following reply only…

        tcp 0 0* LISTEN 5640/mysqld

        also checked

        netstat -tunpl | grep “:3307”

        nobody is using that port, then also this error is coming.

        160222 11:04:20 mysqld_safe Logging to syslog.
        160222 11:04:20 mysqld_safe A mysqld process already exists

      • April 15, 2016 at 5:58 PM

        what command are you using to start the second instance? – have you specified the correct defaults file in the startup command ?

  14. April 15, 2016 at 7:59 PM

    using the exactly same steps as you have mentioned in above doc. here is my history.

    1211 aa-status
    1212 cat /etc/apparmor.d/local/usr.sbin.mysqld
    1213 service mysql stop
    1214 ps -A | grep mysql
    1215 mkdir /var/lib/mysql3307
    1216 mkdir /var/lib/mysql3308
    1217 chown -R mysql /var/lib/mysql3307
    1218 ll /var/lib/mysql3308
    1219 ll /var/lib | grep mysql3308
    1220 chown -R mysql /var/lib/mysql3308
    1221 mkdir /var/log/mysql/mysql3307
    1222 mkdir /var/log/mysql/mysql3308
    1223 chown -R mysql /var/log/mysql/mysql3307
    1224 chown -R mysql /var/log/mysql/mysql3308
    1225 cp /etc/mysql/my.cnf /etc/mysql/my3307.cnf
    1226 cp /etc/mysql/my.cnf /etc/mysql/my3308.cnf
    1227 nano /etc/apparmor.d/usr.sbin.mysqld
    1228 nano /etc/apparmor.d/local/usr.sbin.mysqld
    1229 service apparmor reload
    1230 mysql_install_db –user=mysql –basedir=/usr –datadir=/var/lib/mysql3307 –defaults-file=/etc/mysql/my3307.cnf
    1231 mysql_install_db –user=mysql –basedir=/usr –datadir=/var/lib/mysql3308 –defaults-file=/etc/mysql/my3308.cnf
    1232 sudo service mysql start

    and after that if I run this command..
    sudo -b mysqld_safe –defaults-file=/etc/mysql/my3307.cnf –user=mysql

    and get the errors

    160416 01:29:37 mysqld_safe Logging to syslog.
    160416 01:29:37 mysqld_safe A mysqld process already exists

  15. April 15, 2016 at 8:03 PM

    have you tried with apparmor disabled ?
    what happens if you stop the default mysql instance and start the new one ?

  16. April 15, 2016 at 8:20 PM

    Looks like that worked!

    but with port 3306 only.

    do you think I should also modify /etc/mysql/my3307.cnf ????? to change some port settings …

    so that I can run all mysql simultaneously.

  17. April 15, 2016 at 8:24 PM

    was it apparmor ? if you got the files from my github – all port setting must be correct unless you have configured your default mysql instance differently

  18. April 15, 2016 at 8:29 PM

    no.. it was not apparmor.

    looks like once I have followed following commands and it worked…

    service mysql stop
    sudo -b mysqld_safe –defaults-file=/etc/mysql/my3307.cnf –user=mysql

    but right now running only one mysql on port 3306 so must be some other configuration file changes I need to follow.

  19. April 16, 2016 at 8:25 AM

    hey Naveen,

    some how , I was able to successfully install two instances using following link.

    I had to change configuration files


    Thanks you Naveen for you help on this.

  20. September 1, 2016 at 9:55 PM

    Naveen, I have come across various posts on different topics over the years but this has to be one of the cleanest, best explained and simplest posts ever. I love that final touch of creating aliases🙂 Loved it. And it worked like a charm! Kudos!

  1. No trackbacks yet.

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

%d bloggers like this: