How to Setup two MySql Instances on a Single Machine

MySql Installation:  On the off chance that you as of now have one instance of MySQL database introduced on your machine, you can avoid this progression. 

The most straightforward approach to introduce MySQL on the Ubuntu is to run the accompanying direction from a terminal incite:

sudo apt-get install mysql-server

Amid the establishment procedure, you will be provoked to set a password key for the MySQL root client.

mysqld_multi Setup: With the end goal to oversee two MySQL occurrences on a similar machine productively, we have to utilize mysqld_multi. 

The initial phase in setting up mysqld_multi is the making of two separate [mysqld] bunches in the current my.cnf record. Default area of my.cnf record on the Ubuntu is /etc/mysql/. Along these lines, open my.cnf record with your most loved content tool, and rename existing [mysqld] gathering to [mysqld1]. This renamed gathering will be utilized for the design of the first MySQL case and will be additionally arranged as an ace occurrence. As in MySQL ace slave replication, each instance must have it's very own exceptional,server-id include the accompanying line in [mysqld1] gathering: 

server-id = 1

Since we require a different [mysqld] assemble for the second MySQL instance, duplicate the [mysqld1] aggregate with every single current design, and glue it beneath in the equivalent my.cnf record. Presently, rename the replicated gathering to [mysqld2], and roll out the accompanying improvements in the arrangement for the slave:

server-id           = 2
port                = 3307
socket              = /var/run/mysqld/mysqld_slave.sock
pid-file            = /var/run/mysqld/mysqld_slave.pid
datadir             = /var/lib/mysql_slave
log_error           = /var/log/mysql_slave/error_slave.log
relay-log           = /var/log/mysql_slave/relay-bin
relay-log-index     = /var/log/mysql_slave/relay-bin.index
master-info-file    = /var/log/mysql_slave/master.info
relay-log-info-file = /var/log/mysql_slave/relay-log.info
read_only           = 1

Aside from the [mysqld1] and [mysqld2] gatherings, we likewise need to add another gathering [mysqld_multi] to the my.cnf document:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = multipass

When we introduce the second MySQL example, and we start up both, we will give fitting benefits to the multi_admin client with the end goal to have the capacity to close down MySQL occasions.

AppArmor Security Settings: In some Linux conditions, AppArmor security settings are required with the end goal to run the second MySQL case. In any event, they are required on Ubuntu. 

To legitimately set-up AppArmor, alter /etc/apparmor.d/usr.sbin.mysqld record with your most loved content manager, include the accompanying lines:

/var/lib/mysql_slave/ r,
/var/lib/mysql_slave/** rwk,
/var/log/mysql_slave/ r,
/var/log/mysql_slave/* rw,
/var/run/mysqld/mysqld_slave.pid rw,
/var/run/mysqld/mysqld_slave.sock w,
/run/mysqld/mysqld_slave.pid rw,
/run/mysqld/mysqld_slave.sock w,

After you spare the record, reboot the machine all together for these progressions to produce results.

Second MySql Instance Installation: Execute the accompanying order with the end goal to introduce new MySQL data directory:

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

Once MySQL information index is introduced, you can begin both MySQL occurrences utilizing the mysqld_multi benefit: 

mysqld_multi start

Set the root secret key for the second MySQL occurrence by utilizing the mysqladmin with the fitting host and port. Remember, if host and port are not determined, mysqladmin will associate with the first MySQL example by the default: 

mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd

In the model above I set the password to "rootpwd", yet utilizing a more secure password is suggested.

Hope this will be helpful for you

Thanks,