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

4 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 for such social platform which give us variety of idea to explore ourself technically .This exposure give benefits to everyone to fit or to survive in global market which is very essential in the global era.
    Time Attendance Software

    ReplyDelete
  3. 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
  4. 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