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