Configuration Considerations for MySQL

Below are some specific considerations you need to think about concerning your LifeKeeper MySQL environment.

To operate MySQL database services on the primary and backup servers, file systems and disk partitions must be accessible from each server. Before you can begin configuring the MySQL Recovery Kit, be sure you have completed the following preliminary steps and have tested/run the databases on each server. In the instructions below, the user “mysql” refers to the operating system user that will start the MySQL server.

1. Install the MySQL server and client components on all servers. Be sure that all of the servers are running the same version of the MySQL client and server components. The MySQL executables can be located on a local or shared drive.

Note: If you use Red Hat Software Collections and need to export the X_SCLS environment variable in order to run a specific version of MySQL with LifeKeeper, then set the X_SCLS environment variable via /etc/default/LifeKeeper by adding the line X_SCLS=VERSION to the file (i.e. X_SCLS=mysql55). This is typically only the case if you want to enable MySQL 5.5 which is included in RHEL 5.10 (MySQL 5.0 is enabled as the default).

2. If mysqld is running on any of the servers on the socket and/or port where you wish to run the LifeKeeper protected MySQL database server, stop each MySQL server using the mysqladmin command.

3. Move the contents of the MySQL data directory to a shared location. By default, the MySQL data directory is installed on a local drive. This location depends on the distribution mechanism. The binary RPM installs the data directory at /var/lib/mysql. (Be sure that only the contents are moved and the directory remains intact. This allows the MySQL database server to write logs in this directory, if necessary. Make sure that the “mysql” user described in step 4 has permissions to write the logs to this location.)

4. If the installation process did not create the Linux user “mysql”, create this user. For security reasons, the MySQL server should not be run as “root.” (Refer to the MySQL Administration Guide for a full discussion of the security issues.) Make sure that “mysql” is the only user with read/write permissions in the database directories. The “mysql” user and group should be created on all servers. The user ID and group ID must be the same on all servers.

5. IMPORTANT: A server started by an automatic OS startup cannot be under LifeKeeper protection. In addition, the server can not use the same port number or socket as a server under LifeKeeper protection.

6. It is recommended that the socket be written to the data directory on the shared disk. If the socket will be written to a local disk, make sure the path exists on all LifeKeeper servers where your hierarchy will exist. Make sure that the user “mysql” has permissions to write the socket to this location.

7. Start the MySQL server using the mysql daemon startup command appropriate for your configuration. For configurations defining a single instance in the my.cnf file, use the command:

<start command> —user=mysql —socket=<socket> —port=<port number>

—datadir=<path to the data directory> —log &

The <start command> for mysql versions 3.x is safe_mysqld, and the command for version 4.x is mysqld_safe.

For configurations using the mysqld Group feature in the my.cnf file, use the command:

mysqld_multi start <group number>

The <group number> represents the numerical instance defined in the my.cnf file for the mysqld Group. For more information on using mysqld groups with LifeKeeper, see: Using mysqld Groups with LifeKeeper.

sytemctl command must be utilized when MySQL (v5.7.6 or later) is set up to use Systemd in the distribution with Systemd. For the details, refer to “Consideration about the use in Systemd environment”.

8. Create a MySQL database user named “mysql”. Give this user a password and grant the user “shutdown” permissions. This only has to be done on one server. (Refer to the MySQL Administration Guide for details on creating users and granting permissions).

9. Copy the sample my.cnf configuration file to the desired location (/etc or /<datadir>). This file contains options for the database server and for client programs.

The file can be located in either the MySQL data directory or the /etc directory. The /etc/my.cnf file contains global options. Place the my.cnf file in /etc if only one database will run on the machine at any given time (i.e. an Active/Standby configuration) or if you are using the mysqld Group feature (see Using mysqld Groups with LifeKeeper). If the file is located in /etc, you must copy it to each LifeKeeper backup server. The my.cnf file in the data directory should contain server‑specific options. For multiple servers and Active/Active configurations, this file must be stored in the data directory for each resource instance unless you are using the mysqld Group feature (see Using mysqld Groups with LifeKeeper).

Note: The my.cnf file should not exist in both the /etc and / locations if both copies will contain server specific options. If a my.cnf file containing server specific options is located in /etc along with a protected my.cnf file installed in the / potential conflicts may result. Refer to the MySQL documentation on configuring global settings and server specific options.

Add or edit the following entries:

a. In the “client” section of the file, specify the user and the password that should be used for connections.

[client]
user =clientuser
password =password
.
.
.

b. In the “mysqld” section of the file, specify the socket and port that should be used for connections, as well as the pid-file location for the mysqld process. The user variable should specify the operating system user that will start the mysqld process.

[mysqld]
socket =/home1/test/mysql/mysql.sock
port =3307
pid-file =/home1/test/mysql/mysqld.pid
user =osuser

*Note: Make sure this file is properly protected and owned by the user “mysql.”

Note: Once the MySQL hierarchy is created, if you need to change any of the information in the my.cnf file, you must stop the mysql server instance by taking the hierarchy out-of-service (i.e. the OSU state) before making changes.

Note: The above example my.cnf configuration describes a single database instance mysqld. See Using mysqld Groups with LifeKeeper for configuration examples using mysqld groups.

Note: “include” directive is not supported. All the setups must be described in a single my.cnf file.