Running multiple instances of MySQL on the same machine

Main Steps
 
Considering already there is an instance of MySQL on this machine.

1) Create separate data and log directories and assign permissions

     mkdir /var/lib/mysql2

     chown -R mysql.mysql /var/lib/mysql2/

     mkdir /var/log/mysql2

     chown -R mysql.mysql /var/log/mysql2

2) Create a new mysql configuration file (Copy existing one and edit it)

     Edit this new configuration file and at least update the mysql port (default to 3306), the pid      and socket to be different than the default ones, and also point the data and log folders to the ones created before

3) Initializing and starting mysql

    mysql_install_db --user=mysql --datadir=/var/lib/mysql2/

    mysqld_safe --defaults-file=/etc/mysql2/my.cnf &

4) Connect to the new instance

     mysql -h 127.0.0.1 -P 3307

5) Stop mysql

     mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown

30 comments:

  1. I tried but there is lot of negotiations in the info given...hope we need more info regarding this ...and also thanks for giving the basic idea over mysql replication

    ReplyDelete
  2. thanks.

    I found that if i set
    datadir=/usr/local/mysql/var2

    then error_log and pid_file are automatically relative to it.

    basically I changed only these:

    datadir=/usr/local/mysql/var2
    port=3307
    socket=/tmp/mysql2.sock

    ReplyDelete
  3. Hi, take a note that on Ubuntu you need to setup apparmor for new mysqld instance.

    For more info check https://blogs.oracle.com/jsmyth/entry/apparmor_and_mysql

    ReplyDelete
  4. how many instances can be run simultaneously in windows 7 ??

    ReplyDelete
    Replies
    1. I hope that the number of instances have not restricted in Windows 7.

      Delete
  5. If anyone is looking at these comments, SPAM! ^

    ReplyDelete
  6. If anyone is looking at these comments, SPAM! ^

    ReplyDelete
  7. How to have MariaDB and MySQL in one same server?

    ReplyDelete
  8. Hi, Am new bie to mysql server. Can you give me any idea or link for mysql server 5.7.10 with multiple instances and different port on centos 7. Thanks in advance

    ReplyDelete
    Replies
    1. This test was carried out on oraclelinux-release-7.2-1.0.5.el7.x86_64

      Here is where you get mysql for download :

      http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz

      Logon to the linux box - and go to the folder where you want mysql to be installed - in my case its /home/revathi/work/


      (1) [root@localhost work]# tar -zxvf /media/sf_softwares/mysql/5.7/mysql-advanced-5.7.10-linux-glibc2.5-x86_64.tar.gz
      (2) [root@localhost work]# ln -s mysql-advanced-5.7.10-linux-glibc2.5-x86_64/ mysql5710

      (3) [root@localhost etc]# cp /home/revathi/work/mysql5710/support-files/my-default.cnf node1.cnf

      (4) [root@localhost mysql5710]# bin/mysqld --initialize --basedir=/home/revathi/work/mysql5710/ --datadir=/home/revathi/work/mysql5710/node1/
      /** Do make a note of the password generated during --initialize, you will need this for initial logging onto the server **/

      (5) [root@localhost mysql5710]# cd /home/revathi/
      (6) [root@localhost revathi]# chown revathi:revathi work/ -Rf

      /** make sure we have all the folders that are mentioned in node1.cnf **/

      (7) [revathi@localhost mysql5710]$ mkdir log
      (8) [revathi@localhost mysql5710]$ mkdir run
      (9) [revathi@localhost mysql5710]$ mkdir node1

      (10)[revathi@localhost mysql5710]$ bin/mysqld_safe --defaults-file=/etc/node1.cnf

      (11)[revathi@localhost mysql5710]$ bin/mysql -u root --socket=/home/revathi/work/mysql5710/run/node1.sock -p
      Enter password: /** Enter the password noted above **/

      (12) mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';


      Contents of /etc/node1.cnf
      ==========================

      [mysqld]

      basedir = /home/revathi/work/mysql5710
      datadir = /home/revathi/work/mysql5710/node1
      port = 3307
      server_id = 1
      socket = /home/revathi/work/mysql5710/run/node1.sock

      log_error=/home/revathi/work/mysql5710/log/node1_log.err
      pid_file=/home/revathi/work/mysql5710/run/node1.pid
      log_bin =/home/revathi/work/mysql5710/log/node1-bin
      gtid_mode=ON
      log_slave_updates = ON
      enforce_gtid_consistency=TRUE

      sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

      Delete
    2. If this works fine, copy the contents of /etc/node1.cnf to /etc/node2.cnf
      Change the folder names from node1 to node 2 and repeat steps (4), (10) (11) and (12)

      Happy installation times !!!

      Delete
    3. /etc/node2.cnf shud look like this :

      [mysqld]

      basedir = /home/revathi/work/mysql5710
      datadir = /home/revathi/work/mysql5710/node2
      port = 3308
      server_id = 2
      socket = /home/revathi/work/mysql5710/run/node2.sock

      log_error=/home/revathi/work/mysql5710/log/node2_log.err
      pid_file=/home/revathi/work/mysql5710/run/node2.pid
      log_bin =/home/revathi/work/mysql5710/log/node2-bin
      gtid_mode=ON
      log_slave_updates = ON
      enforce_gtid_consistency=TRUE

      Delete
  9. Thanks for providing this informative information. it is very useful you may also refer-http://www.s4techno.com/blog/2016/07/17/mysql-connection-command/

    ReplyDelete
  10. Is there any way to run multiply mysql instances under different users. I'm asking this because I want to limit resources which can be used by each instance and that can be done trough limiting resources per user on OS (CentOS 7)

    ReplyDelete
  11. Thanks for providing this installation. Can anyone please provide me any proper documents regarding this . If anyone have please send to me jatin.srivastava@epsilon.com

    ReplyDelete
  12. Dear Lasantha,

    Thank you very much for this post. This has saved me a lot of trouble and money to my company. Keep up the good work.

    - Madhusanka

    ReplyDelete