In this document we are going to see How to run multiple instances of MySQL through mysqld_multi command, even earlier i wrote another method to create multiple instance in MySQL, but this time we are going to cover another method which could make through mysqld_multi command and has greater flexibility as compared to previous method.

Though mysqld_multi command, we could mange MySQL instance like stopping, starting and report multiple instance without creating conflict on another instance. This helps in lot of case in which we need to do some maintenance on one instance without interference to another instance. Let’s took an example below.

We have four instance, we named them like

1. MySQL1    Port -- 3306   Datadir -- /var/lib/mysql
2. MySQL2    Port -- 3307   Datadir -- /var/lib/mysql2
3. MySQL3    Port -- 3308   Datadir -- /var/lib/mysql3
4. MySQL4    Port -- 3309   Datadir -- /var/lib/mysql4

This is not only regarding these options mentioned above, but we can mention all instance options independent to each other which create them complete separate to each other.

So let’s try to create some instance mentioned like above with some main option defined. So to create multiple instance we need to edit MySQL configuration file which contain various statement block relate to particular instance.

To create this setup , we are using CentOS6 Machine , latest as of now writing this post March 2019

[root@srv1 ~]# cat /etc/redhat-release 
CentOS release 6.10 (Final)

[root@srv1 ~]# uname -r
2.6.32-754.11.1.el6.x86_64

mysql> show variables where Variable_name = "version";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.1.73 |
+---------------+--------+

Although i am not using latest MySQL version, but it’s more or less same in latest version as well for creating multiple instance through mysqld_multi command.

So for creating I create my MySQL configuration file (/etc/my.cnf)like below, you can take backup of your old MySQL configuration file and create new file like below.

[root@srv1 ~]# cat /etc/my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld1]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
datadir = /var/lib/mysql
tmpdir = /tmp
log-error = /var/log/mysqld.log

[mysqld2]
user = mysql
pid-file = /var/run/mysqld/mysqld2.pid
socket = /var/run/mysqld/mysqld2.sock
port = 3307
datadir = /var/lib/mysql2
tmpdir = /tmp
log-error = /var/log/mysqld2.log

[mysqld3]
user = mysql
pid-file = /var/run/mysqld/mysqld3.pid
socket = /var/run/mysqld/mysqld3.sock
port = 3308
datadir = /var/lib/mysql3
tmpdir = /tmp
log-error = /var/log/mysqld3.log

[mysqld4]
user = mysql
pid-file = /var/run/mysqld/mysqld4.pid
socket = /var/run/mysqld/mysqld4.sock
port = 3309
datadir = /var/lib/mysql4
tmpdir = /tmp
log-error = /var/log/mysqld4.log

Let’s try to understand what all options used in mysq.cnf for various instance.

  • mysqldN — Every MySQL instance used to call through [mysqlN], this N number is used to identify and call through mysqld_multi command.
  • user — options is used to mentioned through which user process will start
  • pid-file — This denote pid file used for process to run, as every process has, it always be different for every instance. In some cases i saw some instance has same pid that cause issues while stopping and running instance independently
  • socket — This denote for Unix Socket file which need to connect while connecting on MySQL server.
  • port — this is MySQL instance port
  • datadir — this contain data directory which store MySQL database file
  • tmpdir — this denote temporary directory that used somtime through MySQL process while running.
  • log-error — This contain log file path which will log errors during MySQL process instance.

There are some other options used for Client which are basically used default port and socket used to connect MySQL, otherwise we have to mentioned while connecting MySQL servers.
Client options could also used to manage through User’s home directory as well, mentioned in another post.

Now we can manage MySQL instance through mysqld_multi command like below.

mysqld_multi start 1
mysqld_multi start 2-4

Through above command we can start MySQL instance and could check with below command

mysqld_multi report

There are chances only first MySQL instance is running and rest are not, because we haven’t created any default database for MySQL which is required to start MySQL instance. Let’s see how we could create MySQL instance.

[root@srv1 ~]# mysql_install_db --user=mysql --datadir=/var/lib/mysql3
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h srv1 password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

So like, in case i need to create MySQL database directory for MySQL3 instance that has datadir /var/lib/mysql3, we need to use command mysql_install_db like above.

So after creating it, we can start it like below.

[root@srv1 ~]# mysqld_multi report 3
Reporting MySQL servers
MySQL server from group: mysqld3 is not running

[root@srv1 ~]# mysqld_multi start 3

[root@srv1 ~]# mysqld_multi report 3
Reporting MySQL servers
MySQL server from group: mysqld3 is running

So now after creating this data directories , we can start instance like below.

[root@srv1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running

Ideally mysqld_multi should start, stop and report for MySQL instance. Now we saw how to start and what all procedure to create these instance.

Now we need to know, how we could stop instance in case we need to stop them. So let’s see in case we try to stop these MySQL instance processes.

[root@srv1 ~]# mysqld_multi stop 3
[root@srv1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running

So above we tried but it didn’t work, So while stopping MySQL instance mysqld_multi used to manage through mysqladmin command on individual basis, for same mysqladmin require MySQL user that has ShutDown privileges. I suggest to define same user and Password on all instance that should also mentioned in MySQL configuration file.

So two stop MYSQL instance through mysqld_multi command, we need to work on two things.

1. One user need to define which has ShutDown privileges

mysql>GRANT SHUTDOWN ON *.* TO 'shutdown_user'@'localhost' IDENTIFIED BY 'shutpass';

mysql> FLUSH PRIVILEGES;

2. MySQL configuration file (/etc/my.cnf) should mentioned that user information

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = shutdown_user
password   = shutpass

After these two changes, we can work indecently on all defined instance or even work together as well.

[root@srv1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running

[root@srv1 ~]# mysqld_multi start 1-4

[root@srv1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running

[root@srv1 ~]# mysqld_multi stop 1,3

[root@srv1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is running