The MySQL Application Recovery Kit supports my.cnf files using the mysqld group feature managed via mysqld_multi. This MySQL feature allows multiple MySQL instances to be easily configured via a single my.cnf file (typically stored in /etc.) The kit now detects a my.cnf file using the mysqld group format and prompts the administrator to select the number of the mysqld group to be protected. The choice list provided to the administrator is determined by the group numbers defined in the my.cnf file minus any group numbers already being protected by the kit.

In general, it is easier to set up and control multiple MySQL instances using the mysqld group feature, and SIOS recommends that this approach be used when setting up active/active or multiple instance configurations.

my.cnf File

When using the mysqld group feature, the following are imperative:

a. A single my.cnf file should be used for defining mysqld groups for the database instances.

b. The my.cnf file should NOT be placed on shared storage.

c. An exact copy of the my.cnf file needs to exist on each cluster node (/etc/my.cnf is ideal).

d. Any changes made to the my.cnf file must be propagated to every node in the LifeKeeper cluster.

The recovery kit uses mysqld_multi commands when it detects the my.cnf file is using mysqld groups. Based on this, you should be able to use mysqld_multi to test your MySQL instance before placing it under control of LifeKeeper.

The following is a relatively complex my.cnf file using mysqld groups that describes two database instances controlled by mysqld_multi. The mysqld_multi command (and the MySQL LifeKeeper recovery kit) gives the administrator a lot of options on how things get set up. In the example below, [mysqld1] defines a relatively simple MySQL instance that uses most of the default locations for various MySQL directives. The second example [mysqld55] moves things around more. The comments will help describe what each section is doing in terms of LifeKeeper’s interaction with MySQL.

#The following client section defines which username/password combination will be used for
#LifeKeeper  connections. The username/password combination needs to be defined in each MySQL
# Database instance that will be described in this my.cnf file.
[client]
user     =  steeleye
password =  password
# This next section describes the default version of mysqld and mysqldadmin that mysqld_multi
# will use when processing mysqld_multi commands. The username/password combo defines the
# MySQL account that mysqld_multi will use when working with the database instances. This
# username and password combo needs to be defined in each MySQL Database instance that will be
# controlled by mysqld_multi. See how to set up the multi_admin account in the MySQL Reference
# Manual, by issuing "mysqld_multi --example".
[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
<>password   = password
# The next section defines the first of two MySQL Database instances in this my.cnf file. Note
# that each section starts  with a [mysqldNN] where NN is the mysqld group number (or instance).
# Each group name must have a number. There are a  number of directives that the LifeKeeper MySQL
# Recovery Kit will be looking for in these sections.
[mysqld1]
datadir  = /s11/mysql-data5077       #Defines where the data files for the instance will live. For
                                     # LifeKeeper, this directory must be on LifeKeeper protected
                                     #   (shared or replicated) storage.
mysqld   = /usr/bin/mysqld_safe       # Defines specifically which mysqld command will be used for
                                      #   starting the instance. This one is using the
                                      #   default  mysqld_safe that came with the distribution.
socket=/s11/mysql-data5077/moe.socket # Defines the location of the socket for this instance.
                                      #   If the socket is not on LifeKeeper protected storage, it
                                      #   needs to be defined in exactly the same place on each
                                      #   node in the cluster and be owned by the "user" defined 
                                      #   below.<
port     =  3307                      # Each instance needs its own, unique TCP/IP port.
pid-file = /var/run/mysqld/mysqld.pid # The pid-file can be on LifeKeeper protected or
                                     #   non-LifeKeeper protected storage.
log-error= /var/log/mysqld.log        # Location of the MySQL error log for this instance. Can be
                                     #   on LifeKeeper protected or non-LifeKeeper protected
                                     #   storage.
user     = mysql                      # The Linux user name that will run the MySQL processes.
#The next section defines the more complicated of the two MySQL instances. Instance "55" is not
#using the default MySQL that came with the Linux distribution as it is using the 5.5.12 version
#of MySQL that was installed from source. The binaries for this version were installed onto shared
#storage, and the binary directory is LifeKeeper protected.
[mysqld55]
datadir = /s11/mysql-data5512           # Same as above; this instance uses a different data
                                        #   directory, and  this directory is on LifeKeeper
                                        #   protected storage.
mysqld  =/s11/mysql5512/bin/mysqld_safe # For this instance, a different version of mysqld_safe
                                        #   is used; the  one that is included with 5.5.12.
socket=/s11/mysql-misc5512/larry.socket # This instance has the socket on LifeKeeper protected
                                        #   storage, but not  in the default location (datadir).
port     = 3308                         # This instance has a unique TCP/IP port as well.
pid-file = /var/run/mysqld/mysqld55.pid # This instance's pid-file is not on LifeKeeper protected
                                        #   storage.
log-error = /var/log/mysqld55.log       # This instance's log-error (error log) is not on
                                        #   LifeKeeper protected  storage.
log-bin   = /s11/mysql-log5512/larry    # The log-bin directive specifies where the binary
                                        #   transaction logs are  located for this instance.
                                        #   These logs must be on LifeKeeper  protected storage
                                        #   (the recovery kit will enforce this). By default,
                                        #   these logs are in the datadir.
user      = mysql                       # The Linux user name that will run the MySQL processes.
 

When describing both sets up of [mysqld<N>] for multi instance and [mysqld] for single instance, the set up for single instance must be described at the last part.

Example:

[mysqld1]
(set up for mysqld1)

[mysqld2]
(set up for mysqld2)

[mysqld55]
(set up for mysqld55)

[mysqld]
(set up for mysqld for single instance )

mysqld_multi Commands

For this example, issuing the mysql command:
# mysqld_multi start 1
would start the mysqld group 1 instance defined in my.cnf as [mysqld1], assuming all of the LifeKeeper protected resources that it depends on were in service on one of the LifeKeeper nodes.

Issuing the mysql command:
# mysqld_multi report 1
would report on the status of this instance (e.g. running or not running). Once this instance is running, creating a resource for it in LifeKeeper should be easy.

To get more information on setting up a mysqld_multi style my.cnf file, issue the command:
# mysqld_multi —example

Feedback

Thanks for your feedback.

Post your comment on this topic.

Post Comment