The need to have multiple instances of MySQL (the well-known mysqld process) running in the same server concurrently in a transparent way, instead of having them executed in separate containers/virtual machines, is not very common. Yet from time to time the Percona Support team receives a request from a customer to assist in the configuration of such an environment. MySQL provides a tool to facilitate the execution of multiple instances called mysqld_multi:

“mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status.”

For tests and development purposes, MySQL Sandbox might be more practical and I personally prefer to use it for my own tests. Both tools work around launching and managing multiple mysqld processes but Sandbox has, as the name suggests, a “sandbox” approach, making it easy to both create and dispose of a new instance (including all data inside it). It is more usual to see mysqld_multi being used in production servers: It’s provided with the server package and uses the same single configuration file that people are used to looking for when setting up MySQL. So, how does it work? How do we configure and manage the instances? And as importantly, how do we backup all the instances we create?

Understanding the concept of groups in my.cnf

You may have noticed already that MySQL’s main configuration file (or “option file“), my.cnf, is arranged under what is called group structures: Sections defining configuration options specific to a given program or purpose. Usually, the program itself gives a name to the group, which appears enclosed by brackets. Here’s a basic my.cnf showing three such groups:

The options defined in the group [client] above are used by the mysql command-line tool. As such, if you don’t specify any other option when executing mysql it will attempt to connect to the local MySQL server through the socket in /var/run/mysqld/mysqld.sock and using the credentials stated in that group. Similarly, mysqld will look for the options defined under its section at startup, and the same happens with Percona XtraBackup when you run a backup with that tool. However, the operating parameters defined by the above groups may also be stated as command-line options during the execution of the program, in which case they replace the ones defined in my.cnf.

Getting started with multiple instances of MySQL

To have multiple instances of MySQL running we must replace the [mysqld] group in my.cnf configuration file by as many [mysqlN] groups as we want instances running, with “N” being a positive integer, also called option group number. This number is used by mysqld_multi to identify each instance, so it must be unique across the server. Apart from the distinct group name, the same options that are valid for [mysqld] applies on [mysqldN] groups, the difference being that while stating them is optional for [mysqld] (it’s possible to start MySQL with an empty my.cnf as default values are used if not explicitly provided) some of them (like socket, port, pid-file, and datadir) are mandatory when defining multiple instances – so they don’t step on each other’s feet. Here’s a simple modified my.cnf showing the original [mysqld] group plus two other instances:

Besides using different pid files, ports and sockets for the new instances I’ve also defined a different datadir for each – it’s very important that the instances do not share the same datadir. Chances are you’re importing the data from a backup but if that’s not the case you can simply use mysql_install_db to create each additional datadir (but make sure the parent directory exists and that the mysql user has write access on it):

Note that if /data/mysql/mysql7 doesn’t exist and you start this instance anyway then myqld_multi will call mysqld_install_db itself to have the datadir created and the system tables installed inside it. Alternatively, from restoring a backup or having a new datadir created you can make a physical copy of the existing one from the main instance – just make sure to stop it first with a clean shutdown, so any pending changes are flushed to disk first.

Now, you may have noted I wrote above that you need to replace your original MySQL instance group ([mysqld]) by one with an option group number ([mysqlN]). That’s not entirely true, as they can co-exist in harmony. However, the usual start/stop script used to manage MySQL won’t work with the additional instances, nor mysqld_multi really manages [mysqld]. The simple solution here is to have the group [mysqld] renamed with a suffix integer, say [mysqld0] (you don’t need to make any changes to its current options though), and let mysqld_multi manage all instances.

Two commands you might find useful when configuring multiple instances are:

…which provides an example of a my.cnf file configured with multiple instances and showing the use of different options, and:

…which shows how a given group (“mysqld7” in the example above) was defined within my.cnf.

Managing multiple instances

mysqld_multi allows you to start, stop, reload (which is effectively a restart) and report the current status of a given instance, all instances or a subset of them. The most important observation here is that the “stop” action is managed through mysqladmin – and internally that happens on an individual basis, with one “mysqladmin … stop” call per instance, even if you have mysqld_multi stop all of them. For this to work properly you need to setup a MySQL account with the SHUTDOWN privilege and defined with the same user name and password in all instances. Yes, it will work out of the box if you run mysqld_multi as root in a freshly installed server where the root user can access MySQL passwordless in all instances. But as the manual suggests, it’s better to have a specific account created for this purpose:

If you plan on replicating the datadir of the main server across your other instances you can have that account created before you make copies of it, otherwise you just need to connect to each instance and create a similar account (remember, the privileged account is only needed by mysqld_multi to stop the instances, not to start them). There’s a special group that can be used on my.cnf to define options for mysqld_multi, which should be used to store these credentials. You might also indicate in there the path for the mysqladmin and mysqld (or mysqld_safe) binaries to use, though you might have a specific mysqld binary defined for each instance inside its respective group. Here’s one example:

You can use mysqld_multi to start, stop, restart or report the status of a particular instance, all instances or a subset of them. Here are a few examples that speak for themselves:

Managing the MySQL daemon

What is missing here is an init script to automate the start/stop of all instances upon server initialization/shutdown; now that we use mysqld_multi to control the instances, the usual /etc/init.d/mysql won’t work anymore. But a similar startup script (though much simpler and less robust) relying on mysqld_multi is provided alongside MySQL/Percona Server, which can be found in /usr/share/<mysql|percona-server>/mysqld_multi.server. You can simply copy it over as /etc/init.d/mysql, effectively replacing the original script while maintaining its name. Please note: You may need to edit it first and modify the first two lines defining “basedir” and “bindir” as this script was not designed to find out the good working values for these variables itself, which the original single-instance /etc/init.d/mysql does. Considering you probably have mysqld_multi installed in /usr/bin, setting these variables as follows is enough:

Configuring an instance with a different version of MySQL

If you’re planning to have multiple instances of MySQL running concurrently chances are you want to use a mix of different versions for each of them, such as during a development cycle to test application compatibility. This is a common use for mysqld_multi, and simple enough to achieve. To showcase its use I downloaded the latest version of MySQL 5.6 available and extracted the TAR file in /opt:

Then I made a cold copy of the datadir from one of the existing instances to /data/mysql/mysqld574:

and added a new group to my.cnf as follows:

Note the use of basedir, pointing to the path were the binaries for MySQL 5.6.20 were extracted, as well as a specific mysqld to be used with this instance. If you have made a copy of the datadir from an instance running a previous version of MySQL/Percona Server you will need to consider the same approach use when upgrading and run mysql_upgrade.

* I did try to use the latest experimental release of MySQL 5.7 (mysql-5.7.4-m14-linux-glibc2.5-x86_64.tar.gz) but it crashed with:

Using the conventional tools to start and stop an instance

Even though mysqld_multi makes things easier to control in general let’s not forget it is a wrapper; you can still rely (though not always, as shown below) on the conventional tools directly to start and stop an instance: mysqld* and mysqladmin. Just make sure to use the parameter –defaults-group-suffix to identify which instance you want to start:

and –socket to indicate the one you want to stop:

* However, mysqld won’t work to start an instance if you have redefined the option ‘mysqld’ on the configuration group, as I did for [mysqld5620] above, stating:

I’ve tested using “ledir” to indicate the path to the directory containing the binaries for MySQL 5.6.20 instead of “mysqld” but it also failed with a similar error. If nothing else, that shows you need to stick with mysqld_multi when starting instances in a mixed-version environment.

Backups

The backup of multiple instances must be done on an individual basis like you would if each instance was located in a different server. You just need to provide the appropriate parameters to identify the instance you’re targeting. For example, we can simply use socket with mysqldump when running it locally:

In Percona XtraBackup there’s an option named  –defaults-group that should be used in environments running multiple instances to indicate which one you want to backup :

Yes, you also need to provide a path to the socket (when running the command locally), even though that information is already available in “–defaults-group=mysqld7”; as it turns out, only the Percona XtraBackup tool (which is called by innobackupex during the backup process) makes use of the information available in the group option. You may need to provide credentials as well (“–user” & “–password”), and don’t forget you’ll need to prepare the backup afterward. The option “defaults-group” is not available in all versions of Percona XtraBackup so make sure to use the latest one.

Summary

Running multiple instances of MySQL concurrently in the same server transparently and without any contextualization or a virtualization layer is possible with both mysqld_multi and MySQL Sandbox. We have been using the later at Percona Support to quickly spin on new disposable instances (though you might as easily keep them running indefinitely). In this post though, I’ve looked at mysqld_multi which is provided with MySQL server and remains the official solution for providing an environment with multiple instances.

The key aspect when configuring multiple instances in my.cnf is the notion of group name option, as you replace a single [mysqld] section by as many [mysqldN] sections as you want instances running. It’s important though to pay attention to certain details when defining the options for each one of these groups, especially when mixing instances from different MySQL/Percona Server versions. Different from MySQL Sandbox, where each instance relies on its own configuration file, you should be careful each time you edit the shared my.cnf file as a syntax error when configuring a single group option will prevent all instances from starting upon the server’s (re)initialization.

I hope to have covered the major points about mysqld_multi here but feel free to leave us a note below if you have something else to add or any comment to contribute.

18 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jose

Great post Fernando!

Is it posible/good to set up a Percona Cluster using 3 instances of Percona in the same server?

0
Jeff

I am curious, how to get these multi servers to start and stop on boot/shutdown without having to use they mysqldN number? It doesn’t seem to work when I reboot a machine, it scrambles the db unless I explicitly shutdown each server.

0
Nicolas

Hi,

As of MySQL 5.6.25, my_print_defaults masks passwords by defaults.

mysqld_multi use ‘my_print_defaults’ to get args form mysqladmin, so now it can’t connect cause password is replace with ‘*’.

Have to patch mysqld_admin (v2.16) script to make it work (from line 101):

# add -s to uncrypt passwd
push @defaults_options, ‘-s’;

0
RR

Hi,

First of all: great article and very useful.
I am trying to create my own multi-mysql setup, but I ran into one problem.

I disabled mysql service from autostarting via ‘update-rc.d -f mysql disable’ so it wouldn’t run with default group [mysqld]. Then I configured two groups [mysqld0] and [mysqld1].

Everything works fine, but when I restart debian server then /var/run/mysqld folder is not automatically created (since service is no longer starting) and (since I use this folder for pid and socket files) my mysqld_multi instances won’t start.

Is there any way to make mysql create this folder automatically or do I have to do it after each restart via own scripts?

0
Till Brinkmann

Hi there,

the user multi_admin can not shudown the database in my configuration.

Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘multi_admin’@’localhost’ (using password: YES)’

But I can connect true the mysql client
#> mysql -u multi_admin -p -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.26 MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

I try a restart still same problem.
Any advice ?

Greeting

0
beshoy abdelmaseh

same problem like Till Brinkmann
i can not stop mysql instances using mysqld_multi stop
but i resolved this by editing in mysqld_multi script but this is not normal ??

0
gilad

Hi ,

Great article , but I have a question
What will be more flexible : use linux container or mysqld_multi ? ( for more then 5 mysql instance )
and when you will use container and not mysqld_multi ?

Tnx

0
beshoy abdelmaseh

@Till Brinkmann
# first to stop mysql instance
in MySQL 5.6 you must put password to stop as follow
$ mysqld_multi stop 1 –password=multipass

# second to start mysqld_multi on startup
you must set SELinux Context on MySQL directories as follow ( this is my solution may yours is different but the same concept nad all directories in below are owned by mysql user to )
1. Install semanage package to manage SELinux
$ sudo yum -y install policycoreutils-python
2. Set an equivalence label to current MySQL data directory as default directory
$ sudo semanage fcontext -a -e /var/lib/mysql /home/mysql
3. Set SELinux file label of MySQL log file
$ sudo semanage fcontext -a -t mysqld_log_t “/var/log/mysql(/.*)?”
4. Set SELinux file label of MySQL process id
$ sudo semanage fcontext -a -t mysqld_var_run_t “/var/run/mysql(/.*)?”
5. Set SELinux file label of socket file
$ sudo semanage fcontext -a -t mysqld_var_run_t “/tmp/mysql(/.*)?”
6. Apply the SELinux Contexts type
$ sudo restorecon -Rv /home/mysql; sudo restorecon -Rv /var/log/mysql; sudo restorecon -Rv /var/run/mysql; sudo restorecon -Rv /tmp/mysql
restorecon reset /home/mysql/data/instance-1/mysql/time_zone_leap_second.MYD context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:mysqld_db_t:s0
restorecon reset /home/mysql/data/instance-1/mysql/general_log.frm context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:mysqld_db_t:s0
………………………………………………………………………………………………..
………………………………………………………………………………………………..
restorecon reset /var/log/mysql/instance-2/mysql-instance-2.log context unconfined_u:object_r:var_log_t:s0->unconfined_u:object_r:mysqld_db_t:s0
restorecon reset /var/log/mysql/instance-2/mysql-error-instance-2.log context unconfined_u:object_r:var_log_t:s0->unconfined_u:object_r:mysqld_db_t:s0
7. Verify SELinux context is changed
$ ls -lZd /var/lib/mysql /home/mysql /var/log/mysql /var/run/mysql /tmp/mysql /etc/my.cnf
-rw-r–r–. root root system_u:object_r:mysqld_etc_t:s0 /etc/my.cnf
drwxr-xr-x. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 /home/mysql
drwxr-xr-x. mysql mysql unconfined_u:object_r:mysqld_var_run_t:s0 /tmp/mysql
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 /var/lib/mysql
drwxr-xr-x. mysql mysql unconfined_u:object_r:mysqld_log_t:s0 /var/log/mysql
drwxr-xr-x. mysql mysql unconfined_u:object_r:mysqld_var_run_t:s0 /var/run/mysql
8. Configure SELinux to enable MySQL’s use of port 3307
$ sudo semanage port -a -t mysqld_port_t -p tcp 3307
9. List defined ports for MySQL by SELinux
$ sudo semanage port -l | grep mysql
mysqld_port_t tcp 3307, 1186, 3306, 63132-63164
mysqlmanagerd_port_t tcp 2273

0
travel surabaya

thaankkkks fernando . thats i need .i’m so confused before and always get error messages .
but after try your methode succes

0
Drew Borell

I appreciate this write-up and all your efforts to make this information available. That said, this makes no sense for a cluster IMO. Why would you bind different ports to a single address instead of binding unique addresses (multiple adapters) to a single instance and use the same port? You dont even need vm to do this with most servers. Any respectable server should have at least adapters.
e.g.)
eth0 172.16.16.10:3306
eth1 172.16.16.11:3306

https://dev.mysql.com/doc/refman/5.5/en/ipv6-server-config.html

“If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address.”

0
Drew Borell

Well I answered my own question, so no need to reply. After further reading I see the MySQL documentation states the socket and port must be unique. The bind-address parameter only exists to specify adapters but you cannot bind the same port to different IP addresses as I would have expected.

0
John Stilley

Hello, I loved your original post. But I am installing today with the latest version of mysqld_multi and things have change. In particular, I get these two messages when I try to install using the above instructions now:

FATAL ERROR: Tried to start mysqld under group [mysqld2], but no mysqld_safe binary was found.

[WARNING] mysql_install_db is deprecated. Please consider switching to mysqld –initialize
[ERROR] The data directory ‘/aa/var/lib/mysql_53307’ already exist and is not empty.

0
steenb

Hi Fernando
Thanks, article used with rh6.5 🙂
any pros and cons on using systemctl instead when running rh7 ?
greeting

0
ambiente1

Having some troubles using [mysqld] and [mysqldN].

When i launch [mysqld] with port 3306 and user “mysql”, i can’t start any other [mysqldN] using mysqld_multi, because it tries to use user “mysql” as well, even when [mysqldN] define a user “xyz”.

The datadir is owned by user “xyz” and group “mysql”. chmod 2750.

How to solve this issue?

Because on Gentoo, it needs the service “mysql” to run like it should.

0
TranDuongNgocTuan

I want to create multi server mysql. I used to multi server to perform synchronous insert/detele data on each server. This is my config /etc/my.cnf (Mysql 5.7)

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

[mysqld1]
user = mysql1
pid-file = /var/run/mysqld/mysqld1.pid
socket = /var/run/mysqld/mysqld1.sock
port=3307
datadir=/var/lib/mysqld1
server_id=4
log-bin=mysql-bin
binlog_format=mixed

[mysqld2]
user = mysql2
pid-file = /var/run/mysqld/mysqld2.pid
socket = /var/run/mysqld/mysqld2.sock
port=3308
datadir=/var/lib/mysqld2
server_id=3
skip-log-bin
msqld_multi report

Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
But I can’t not login mysql

$ mysql -u root –socket=/var/run/mysqld/mysqld2.sock -p
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
I want to login data mysqld2 to perform instert/delete data!

Please, help me!!!

0
Macdev

Does somebody have a performance comparison of multiple innodb instances vs. a big single innodb instance? We are currently running 40+ Drupal DBs within one instance an think about spliting it on the same hardware.
At the moment there are 3 mysqld running there after splitting there would be 4.

0
Hasan Ovuc

mysql_install_db part gives error in below on Ubuntu Xenial. I solved it as disable apparmor for mysql.

Error:

error 1005 can’t create table ‘db’ (errno 13)

0
Tu Nguyen

Great post.

Can I create 2 instance, and sync data between 2 instance?

0