Running Multiple MySQL Server Instances in Parallel on a Linux Server

Exclusive offer: get 50% off this eBook here
MySQL Admin Cookbook

MySQL Admin Cookbook — Save 50%

99 great recipes for mastering MySQL configuration and administration

$29.99    $15.00
by Daniel Schneller Udo Schwedt | September 2010 | Cookbooks MySQL Open Source

In the previous article, MySQL Admin: Configuring InnoDB and Installing MySQL as a Windows Service, we covered the configuring InnoDB and installing MySQL as a windows service.

In this article, by Daniel Schneller & Udo Schwedt, authors of MySQL Admin Cookbook, we will cover:

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

 

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

Introduction

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
    [mysqld1]
    server-id=10001
    socket=/var/run/mysqld/mysqld1.sock
    port=23306
    pid-file=/var/run/mysqld/mysqld1.pid
    datadir=/var/lib/mysql1
    log_bin=/var/log/mysql1/mysql1-bin.log

    # mysqld_multi test, instance 2
    [mysqld2]
    server-id=10002
    socket=/var/run/mysqld/mysqld2.sock
    port=33306
    pid-file=/var/run/mysqld/mysqld2.pid
    datadir=/var/lib/mysql2
    log_bin=/var/log/mysql2/mysql2-bin.log

  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.

Windows

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 http://mysqlsandbox.net

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

MySQL Admin Cookbook 99 great recipes for mastering MySQL configuration and administration
Published: March 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

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:

    [mysqld]
    ...
    sql-mode=STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE
    ...

  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 http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html 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.

Summary

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:


MySQL Admin Cookbook 99 great recipes for mastering MySQL configuration and administration
Published: March 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Daniel Schneller

Daniel Schneller works as a software developer, database administrator, and general IT professional for an independent software vendor in the retail sector. After successfully graduating from the University of Cooperative Education in Heidenheim, Germany with a degree in Business Computer Science, he started his career as a professional software developer, focused on the Microsoft technology stack. In 2002 he started focusing on enterprise-level Java development and has since gained extensive knowledge and experience implementing large scale systems based on Java EE and relational databases, especially MySQL since version 4.0.

Currently he is mostly involved with the ongoing development of framework-level functionality, including customization and extension of an ORM-based persistence layer. He is involved in different open source projects such as FindBugs, Eclipse, and Checkstyle and infrequently blogs about Java, MySQL, Windows, Linux and other insanities at http://www.danielschneller.com.

Udo Schwedt

Udo Schwedt is head of the Java architecture team and deputy head of the Java development department at the IT service provider for Germany's market leader in the Do-It-Yourself sector. After finishing his studies at RWTH Aachen, Germany with a degree in Computer Science, he started his career as a professional Java developer in a leading software company in the banking sector. In 2003, he joined his current company as a framework developer, subsequently taking the role of a lead architect.

Both authors are involved in a large scale international software project, which encompasses development of a Java-based merchandise management software solution. This decentralized multi-platform environment is based on more than 500 decentralized MySQL server instances with more than 5,500 replication slaves. Daniel and Udo are responsible for configuration and management of this infrastructure.

Books From Packt

WordPress 3.0 jQuery
WordPress 3.0 jQuery

Drupal 7
Drupal 7

Drupal 7 First look
Drupal 7 First look

Mastering phpMyAdmin 3.1 for Effective MySQL Management
Mastering phpMyAdmin 3.1 for Effective MySQL Management

MySQL for Python
MySQL for Python

ChronoForms 1.3 for Joomla! site Cookbook
ChronoForms 1.3 for Joomla! site Cookbook

High Availability MySQL Cookbook
High Availability MySQL Cookbook

Moodle 1.9 Theme Design: Beginner's Guide
Moodle 1.9 Theme Design: Beginner's Guide

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
8
G
x
w
b
Y
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software