MySQLMySQL 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

Assumptions

  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=127.0.0.1
  • 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 127.0.0.1 --port=3307 -u root
mysql -h 127.0.0.1 --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 127.0.0.1 --port=3307 -u root shutdown
mysqladmin -h 127.0.0.1 --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

Troubleshooting

  • 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 127.0.0.1 --port=3307 -u root shutdown -p'
alias mysql3307-root='mysql -h 127.0.0.1 --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 127.0.0.1 --port=3308 -u root shutdown -p'
alias mysql3308-root='mysql -h 127.0.0.1 --port=3308 -u root -p'

Categories: MySQL

Comments

  1. December 4, 2017 2:45 pm

    […] https://naveensnayak.com/2013/11/10/mysql-multiple-instances-on-ubuntu/ […]
  2. bridge

    June 1, 2017 1:29 am

    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: 2017-06-01T07:20:04.550756Z 0 [ERROR] Could not create unix socket lock file /var/run/mysqld/mysqld.sock3307.lock. 2017-06-01T07:20:04.550765Z 0 [ERROR] Unable to setup unix socket lock file. 2017-06-01T07:20:04.550767Z 0 [ERROR] Aborting please help.thank you
    1. June 1, 2017 8:48 am

      @bridge, you can check a couple of things is apparmor running and have you given the correct apparmor-mysql permissions for the directory where the lock file is located ? does the mysql user have the right permission to create or read the file ? is there already a mysql process that is using that file ?
  3. April 23, 2017 12:18 pm

    Thanks for the post. Excellent one
  4. purvez desai

    April 4, 2017 3:42 am

    Hi Naveen I'll echo everyone in saying that the way you present your information is AMAZINGLY GOOD!! Thanks. I have a slightly different requirement. I currently have mysql5.5 running on ubuntu14.04. I want to run a mysql 5.7 version on the same machine. Have you thought about doing that and would you consider writing a post on that please?
  5. eli

    February 20, 2017 1:29 am

    excellent article . thank you very much
  6. September 1, 2016 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!
  7. April 16, 2016 8:25 am

    hey Naveen, some how , I was able to successfully install two instances using following link. I had to change configuration files http://sharadchhetri.com/2013/12/04/how-to-configure-multiple-mysql-instance-in-ubuntu/ Thanks you Naveen for you help on this.
  8. April 15, 2016 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.
  9. April 15, 2016 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
  10. April 15, 2016 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.
  11. April 15, 2016 8:03 pm

    have you tried with apparmor disabled ? what happens if you stop the default mysql instance and start the new one ?
  12. April 15, 2016 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
  13. ajith

    February 22, 2016 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
    1. February 22, 2016 8:08 pm

      @ajith 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
      1. April 15, 2016 5:44 pm

        netstat -tunpl | grep mysql it gives following reply only... tcp 0 0 127.0.0.1:3306 0.0.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
        1. April 15, 2016 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. Ventsi

    July 28, 2015 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
    1. July 28, 2015 5:18 pm

      @Ventsi 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 http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
      1. Ventsi

        July 30, 2015 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: [mysqld] # # * 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 skip-external-locking skip-grant-tables reset the mysql instance 2, and I was enabled to login without a pass :) Thank you again :)
  15. southom

    July 2, 2015 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.
    1. July 2, 2015 2:37 pm

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

        July 5, 2015 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.
  16. April 16, 2015 6:31 am

    i just change the path /var/run/mysqld to other path and its works
  17. April 13, 2015 9:17 am

    150413 9:12:59 [Note] Server socket created on IP: '0.0.0.0'. 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.
  18. Tariqul Islam

    April 3, 2015 9:10 am

    mysqld_safe A mysqld process already exists occured
  19. March 30, 2015 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
    1. March 30, 2015 10:31 pm

      @Omkar mysql is trying to log at 2 places - which is causing the error So you either log to syslog or to the mysql log file Comment out the log_error in the my3307.cnf file and give it a try this post might help http://shinguz.blogspot.ca/2010/01/mysql-reporting-to-syslog.html
  20. March 20, 2015 9:06 am

    me too facing the same FATAL ERROR: Could not find my-default.cnf error! It didn't help
    1. March 20, 2015 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
  21. Tyshan Shi

    February 14, 2015 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
  22. Jasbir

    January 5, 2015 11:40 pm

    excellent post
  23. Balázs Ruda

    December 14, 2014 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. solution??
    1. December 14, 2014 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 ?
  24. suraj

    October 29, 2014 10:04 am

    Very Nice post. its simple and easy to use. Thanks a ton
  25. June 15, 2014 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.

Post a comment

Your email address will not be published. Required fields are marked *