Running Multiple MySQL Server Instances in Parallel on a Linux Server

MySQL Admin Cookbook

March 2010


99 great recipes for mastering MySQL configuration and administration


MySQL Admin Cookbook

MySQL Admin Cookbook

99 great recipes for mastering MySQL configuration and administration

  • Set up MySQL to perform administrative tasks such as efficiently managing data and database schema, improving the performance of MySQL servers, and managing user credentials
  • Deal with typical performance bottlenecks and lock-contention problems
  • Restrict access sensibly and regain access to your database in case of loss of administrative user credentials
  • Part of Packt's Cookbook series: Each recipe is a carefully organized sequence of instructions to complete the task as efficiently as possible

Read more about this book

(For more resources on MySQL, see here.)


On most Linux setups, MySQL comes as a readymade installation package, making it easy to get started. It is, however, a little more complicated to run multiple instances in parallel, often a setup handy for development. This is because in contrast to Windows, MySQL is usually not installed in a self-contained directory, but most Linux distribution packages spread it across the appropriate system folders for programs, configuration files, and so on. You can, however, also install MySQL in its own directory, for example, if you need to use a version not available as a prepared package for your Linux distribution. While this gives you the greatest flexibility, as a downside you will have to take care of wiring up your MySQL server with the operating system manually. For example, you will need to hook up the startup and shutdown scripts with the appropriate facilities of your distribution.

In more recent distributions, you can make use of a tool called mysqld_multi, a solution that lets you set up multiple instances of MySQL daemons with varying configurations. In this recipe, we will show you how to set up two parallel MySQL servers, listening on different TCP ports and using separate data directories for their respective databases.

Getting ready

This recipe is based on an Ubuntu Linux machine with the 8.04 LTS version. mysqld_multi comes with the MySQL packages for that operating system. If you are using other distributions, you need to make sure you have mysqld_multi installed to be able to follow along. Refer to your distribution's package repositories for information on which packages you need to install.

You will also need an operating system user with sufficient privileges to edit the MySQL configuration file—typically /etc/mysql/my.cnf on Ubuntu—and restart services. As for AppArmor or SELinux, we assume these have been disabled before you start to simplify the process.

How to do it...

  1. Locate and open the my.cnf configuration file in a text editor.
  2. Create the following two sections in the file:

    # mysqld_multi test, instance 1

    # mysqld_multi test, instance 2

  3. Save the configuration file.
  4. Issue the following command to verify the two sections are found by mysqld_multi:

    $ sudo mysqld_multi report

  5. Initialize the data directories:

    $ sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql1
    $ sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql2

  6. Start both instances and verify they have been started:

    $ sudo mysqld_multi start 1
    $ sudo mysqld_multi report

  7. Connect to both instances and verify their settings:

    $ mysql -S /var/run/mysqld/mysql1.sock
    mysql> SHOW VARIABLES LIKE 'server_id';

How it works...

mysqld_multi uses a single configuration file for all MySQL server instances, but inside that file each instance has its individual [mysqld] section with its specific options. mysqld_multi then takes care of launching the MySQL executable with the correct options to use the options from its corresponding section.

The sections are distinguished by a positive number directly appended to the word mysqld in the section header. You can specify all the usual MySQL configuration file options in these sections, just as you would for a single instance. Make sure, however, to specify the minimum set of options as in the recipe steps previously stated, as these are required to be unique for every single instance.

There's more...

Some special preparation might be needed, depending on the particular operating system you are using.

Turning off AppArmor / SELinux for Linux distributions

If your system uses the AppArmor or SELinux security features, you will need to make sure these are either turned off while you try this out, or configured (for permanent use once your configuration has been finished) to allow access to the newly defined directories and files. See the documentation for your respective Linux distribution for more details on how to do this.


On Windows, running multiple server instances is usually more straightforward. MySQL is normally installed in a separate, self-contained folder. To run two or more independent server instances, you only need to install a Windows service for each of them and point them to an individual configuration file.

Considering the alternative MySQL Sandbox project

As an alternative to mysqld_multi you might want to have a look at MySQL Sandbox, which offers a different approach to hosting multiple independent MySQL installations on a single operating system. While mysqld_multi manages multiple configurations in a single file, MySQL Sandbox aims at completely separating MySQL installations from each other, easily allowing even several MySQL releases to run side by side. For more details, visit the project's website at

Preventing invalid date values from being stored in DATE or DATETIME columns

In this recipe, we will show you how to configure MySQL in a way such that invalid dates are rejected when a client attempts to store them in a DATE or DATETIME column using a combination of flags for the SQL mode setting.

See the There's more... section of this recipe for some more detailed information on the server mode setting in general and on how to use it on a per-session basis.

Getting ready

Read more about this book

(For more resources on MySQL, see here.)

The configuration options shown in this recipe can be applied to individual sessions or as server-wide defaults. For production systems, we recommend specifying them in the MySQL configuration file. You will need the necessary operating system level privileges to edit it, and then restart the service to activate the settings.

The final step in the recipe is the attempt to insert some invalid dates. You can safely skip this step. If you want to try it, you will need a table set up like this in the test database:

CREATE TABLE table_a (
test_date DATE NOT NULL

How to do it...

  1. Locate the MySQL configuration file, typically my.cnf or my.ini (on Windows), and open it in a text editor.
  2. In the [mysqld] section make sure the following line is present, adding it if needed:


  3. Save the file.
  4. Restart the MySQL server
  5. Verify whether the setting was applied using this statement from a MySQL client:

    mysql> SELECT @@GLOBAL.sql_mode;

  6. Optionally try to insert some false values:

    mysql> INSERT INTO table_a VALUES ('2009-02-31');
    mysql> INSERT INTO table_a VALUES ('2009-00-31');
    mysql> INSERT INTO table_a VALUES ('0000-00-00');

Getting ready

Setting the SQL mode to STRICT_ALL_TABLES will enable validation on all tables (as opposed to only those in transactional storage engines if you were to use STRICT_TRANS_TABLES). While setting up the SQL mode like this would already prohibit the insertion of values like Feb 31st, one could still insert all zero dates or dates with zero fields in them. This is what the other two options NO_ZERO_DATE and NO_ZERO_IN_DATE take care of.

There's more...

Starting with MySQL 5.0, the concept of SQL modes was introduced to provide granular control over the degree of leniency the server will apply for invalid values. MySQL has traditionally been very forgiving when receiving invalid values to be inserted into its tables. There are truncation and approximation rules on what will happen when, for example, you try to insert a value that exceeds the maximum length of a column's definition.

For enterprise systems, this clearly is unwanted behavior. Whenever an application tries to store values in the database that do not meet the previously defined criteria of length and value ranges, an error must be thrown to prevent silent data corruption. One might argue that data validation must be done at the application level and invalid data never be stored to the database anyway. But we are strong believers in the database being the "last line of defense". Of course, any decent application will reject invalid inputs, but in reality there can be bugs or an administrator accessing the database independently might just make a mistake. Setting up MySQL to verify incoming data (again) can be invaluable in these situations. Even though you take a slight performance hit, data integrity should be considered as an even higher priority.

The so-called strict mode enables the general use of MySQL server-side data validation. The remaining options described in the MySQL online manual section 5.1.8 at allow a somewhat granular control over what exactly get validated and how.

We recommend going through all the SQL mode options to see if there are any that you would like to enable on your servers.

Configuring SQL mode for the current session only

For experimenting with the different SQL modes, it is often easier to configure them for your current session only. The following statement disables the global settings configured above for the current session only:

mysql> SET @@session.sql_mode='';

This can also come in handy for maintenance scripts that need to temporarily disable certain restrictions that are set up for normal operations.


In the above article we have covered:

  • Running multiple MySQL server instances in parallel on a Linux server
  • Preventing invalid date values from being stored in DATE or DATETIME columns

Further resources on this subject:

Books to Consider

MySQL Admin Cookbook LITE: Configuration, Server Monitoring, Managing Users
$ 9.99
Microsoft SQL Server 2008 R2 Administration Cookbook
$ 32.99
MySQL Admin Cookbook
$ 29.99
comments powered by Disqus

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free