Concept
Sometime there are are situations when we require to run multiple MySql servers on same server for example, two group of developers working on separate projects where they may need to have database with different setting for e.g sql_mode or innodb-file-format or any others.
We can either install separate MySQL servers or they make use of mysqld_multi, which has advantage of installing a single version of MySQL package and running/managing them all with mysqld_multi utility.
Steps to setup
Lets check how we can run 3 instances of MySQL server, for this we create 3 groups of [mysqldN] where N is positive integer. The number N is used to identify each instance of MySQL server. Each should have mandatory and unique socket, datadir, port and pid-file so that they do not interfere with each other.
[mysqld1] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 datadir = /var/lib/mysql [mysqld1] user = mysql pid-file = /var/run/mysqld/mysqld1.pid socket = /var/run/mysqld/mysqld1.sock port = 3307 datadir = /data/mysql/mysql1 [mysqld2] user = mysql pid-file = /var/run/mysqld/mysqld2.pid socket = /var/run/mysqld/mysqld2.sock port = 3308 datadir = /data/mysql/mysql2
Make sure the datadir exist for all of them.
Next we initialize the datadir
mysql_install_db --user=mysql --datadir=/data/mysql/mysql1 mysql_install_db --user=mysql --datadir=/data/mysql/mysql2
Now that we have datadir ready, its time to start the servers. But wait..
The default init script shipped with the mysql package cannot manage multiple MySQL server. We need to make use of the mysqld_multi utility to manage them. mysqld_multi needs to know the which mysqladmin and mysqld binaries will be used to manage these server. So we add a new group in my.cnf
[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin
Here we specify common binaries for them in the [mysqld_multi] group.
Now we are ready to run the servers.
mysqld_multi start
Will start all the servers, we can also start a server individually like
mysqld_multi start 1
This will the start the server with suffix 1
Similarly we can stop them like
mysqld_multi stop 1
To know the status of the server we use the report option
mysqld_multi report
which will have output like
Reporting MariaDB servers MariaDB server from group: mysqld1 is not running MariaDB server from group: mysqld2 is running MariaDB server from group: mysqld3 is not running
Summary
We saw how we can multiple mysql servers, this is a very handy method when we have to quickly setup mysql servers on a single machine. I have tried to make this precise and complete. Hope this help you. Comment below if you have queries or if you feel something missing or incorrect.