EmergencyEMERGENCY? Get 24/7 Help Now!

mysqld_multi: How to run multiple instances of MySQL

 | August 26, 2014 |  Posted In: Insight for DBAs, MySQL, Percona MySQL Support, Percona Server, Percona XtraBackup

PREVIOUS POST
NEXT POST

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 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 look 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 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 they replace the ones defined in my.cnf.

Getting started with multiple instances

To have multiple instances of MySQL running we must replace the [mysqld] group in the 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 it’s 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 an 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 it’s 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’s 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 it’s 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 an 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 an 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 in 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 afterwards. 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, specially when mixing instances from different MySQL/Percona Server versions. Differently from MySQL Sandbox, where each instance relies on it’s 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.

PREVIOUS POST
NEXT POST
Fernando Laudares

Fernando Laudares joined Percona in early 2013 after working 8 years for a Canadian company specialized in offering services based in open source technologies. Fernando's work experience includes the architecture, deployment and maintenance of IT infrastructures based on Linux, open source software and a layer of server virtualization. From the basic services such as DHCP & DNS to identity management systems, but also including backup routines, configuration management tools and thin-clients. He's now focusing on the universe of MySQL with a particular interest in understanding the intricacies of database systems.

14 Comments

  • 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.

  • 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’;

  • 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?

  • 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

  • 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

  • @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

  • 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.”

    • 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.

  • 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.

  • 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.

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.