MySQL

You are viewing documentation for a release that is no longer supported. The latest supported version of version 3 is [3.11]. For the most recent version 4, see [4]

You are viewing documentation for a release that is no longer supported. The latest supported version of version 3 is [3.11]. For the most recent version 4, see [4]

Overview

OKD provides a container image for running MySQL. This image can provide database services based on username, password, and database name settings provided via configuration.

Versions

Currently, OKD provides versions 5.6 and 5.7 of MySQL.

Images

This image comes in two flavors, depending on your needs:

• RHEL 7

• CentOS 7

RHEL 7 Based Images

The RHEL 7 image is available through the Red Hat Registry:

$ docker pull registry.access.redhat.com/rhscl/mysql-56-rhel7
$ docker pull registry.access.redhat.com/rhscl/mysql-57-rhel7

CentOS 7 Based Images

CentOS images for MySQL 5.6 and 5.7 are available on Docker Hub:

$ docker pull centos/mysql-56-centos7
$ docker pull centos/mysql-57-centos7

To use these images, you can either access them directly from these registries or push them into your OKD Docker registry. Additionally, you can create an ImageStream that points to the image, either in your Docker registry or at the external location. Your OKD resources can then reference the ImageStream. You can find example ImageStream definitions for all the provided OKD images.

Configuration and Usage

Initializing the Database

The first time you use the shared volume, the database is created along with the database administrator user and the MySQL root user (if you specify the **MYSQL_ROOT_PASSWORD** environment variable). Afterwards, the MySQL daemon starts up. If you are re-attaching the volume to another container, then the database, database user, and the administrator user are not created, and the MySQL daemon starts.

The following command creates a new database pod with MySQL running in a container:

$ oc new-app \
    -e MYSQL_USER=<username> \
    -e MYSQL_PASSWORD=<password> \
    -e MYSQL_DATABASE=<database_name> \
    openshift/mysql-56-centos7

Running MySQL Commands in Containers

OKD uses Software Collections (SCLs) to install and launch MySQL. If you want to execute a MySQL command inside of a running container (for debugging), you must invoke it using bash.

To do so, first identify the name of the pod. For example, you can view the list of pods in your current project:

$ oc get pods

Then, open a remote shell session to the pod:

$ oc rsh <pod>

When you enter the container, the required SCL is automatically enabled.

You can now run the mysql command from the bash shell to start a MySQL interactive session and perform normal MySQL operations. For example, to authenticate as the database user:

bash-4.2$ mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -h $HOSTNAME $MYSQL_DATABASE
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.37 MySQL Community Server (GPL)
...
mysql>

When you are finished, enter quit or exit to leave the MySQL session.

Environment Variables

The MySQL user name, password, and database name must be configured with the following environment variables:

MySQL settings can be configured with the following environment variables:

Some of the memory-related parameters have two default values. The fixed value is used when a container does not have memory limits assigned. The other value is calculated dynamically during a container’s startup based on available memory.

Volume Mount Points

The MySQL image can be run with mounted volumes to enable persistent storage for the database:

• /var/lib/mysql/data - This is the data directory where MySQL stores database files.

Changing Passwords

Passwords are part of the image configuration, therefore the only supported method to change passwords for the database user (**MYSQL_USER**) and root user is by changing the environment variables **MYSQL_PASSWORD** and **MYSQL_ROOT_PASSWORD**, respectively.

You can view the current passwords by viewing the pod or deployment configuration in the web console or by listing the environment variables with the CLI:

$ oc set env pod <pod_name> --list

Whenever **MYSQL_ROOT_PASSWORD** is set, it enables remote access for the root user with the given password, and whenever it is unset, remote access for the root user is disabled. This does not affect the regular user **MYSQL_USER**, who always has remote access. This also does not affect local access by the root user, who can always log in without a password in localhost.

Changing database passwords through SQL statements or any way other than through the environment variables aforementioned causes a mismatch between the values stored in the variables and the actual passwords. Whenever a database container starts, it resets the passwords to the values stored in the environment variables.

To change these passwords, update one or both of the desired environment variables for the related deployment configuration(s) using the oc set env command. If multiple deployment configurations utilize these environment variables, for example in the case of an application created from a template, you must update the variables on each deployment configuration so that the passwords are in sync everywhere. This can be done all in the same command:

$ oc set env dc <dc_name> [<dc_name_2> ...] \
  MYSQL_PASSWORD=<new_password> \
  MYSQL_ROOT_PASSWORD=<new_root_password>

Updating the environment variables triggers the redeployment of the database server if you have a configuration change trigger. Otherwise, you must manually start a new deployment in order to apply the password changes.

To verify that new passwords are in effect, first open a remote shell session to the running MySQL pod:

$ oc rsh <pod>

From the bash shell, verify the database user’s new password:

bash-4.2$ mysql -u $MYSQL_USER -p<new_password> -h $HOSTNAME $MYSQL_DATABASE -te "SELECT * FROM (SELECT database()) db CROSS JOIN (SELECT user()) u"

If the password was changed correctly, you should see a table like this:

+------------+---------------------+
| database() | user()              |
+------------+---------------------+
| sampledb   | user0PG@172.17.42.1 |
+------------+---------------------+

To verify the root user’s new password:

bash-4.2$ mysql -u root -p<new_root_password> -h $HOSTNAME $MYSQL_DATABASE -te "SELECT * FROM (SELECT database()) db CROSS JOIN (SELECT user()) u"

If the password was changed correctly, you should see a table like this:

+------------+------------------+
| database() | user()           |
+------------+------------------+
| sampledb   | root@172.17.42.1 |
+------------+------------------+

Creating a Database Service from a Template

OKD provides a template to make creating a new database service easy. The template provides parameter fields to define all the mandatory environment variables (user, password, database name, etc) with predefined defaults including auto-generation of password values. It will also define both a deployment configuration and a service.

The MySQL templates should have been registered in the default openshift project by your cluster administrator during the initial cluster setup. See Loading the Default Image Streams and Templates for more details, if required.

There are two templates available:

• mysql-ephemeral is for development or testing purposes only because it uses ephemeral storage for the database content. This means that if the database pod is restarted for any reason, such as the pod being moved to another node or the deployment configuration being updated and triggering a redeploy, all data will be lost.

• mysql-persistent uses a persistent volume store for the database data which means the data will survive a pod restart. Using persistent volumes requires a persistent volume pool be defined in the OKD deployment. Cluster administrator instructions for setting up the pool are located in Persistent Storage Using NFS.

You can instantiate templates by following these instructions.

Once you have instantiated the service, you can copy the user name, password, and database name environment variables into a deployment configuration for another component that intends to access the database. That component can then access the database via the service that was defined.

Using MySQL Replication

Red Hat provides a proof-of-concept template for MySQL master-slave replication (clustering); you can obtain the example template from GitHub.

To upload the example template into the current project’s template library:

$ oc create -f \
    https://raw.githubusercontent.com/sclorg/mysql-container/master/examples/replica/mysql_replica.json

The following sections detail the objects defined in the example template and describe how they work together to start a cluster of MySQL servers implementing master-slave replication. This is the recommended replication strategy for MySQL.

Creating the Deployment Configuration for the MySQL Master

To set up MySQL replication, a deployment configuration is defined in the example template that defines a replication controller. For MySQL master-slave replication, two deployment configurations are needed. One deployment configuration defines the MySQL master server and second the MySQL slave servers.

To tell a MySQL server to act as the master, the **command** field in the container’s definition in the deployment configuration must be set to run-mysqld-master. This script acts as an alternative entrypoint for the MySQL image and configures the MySQL server to run as the master in replication.

MySQL replication requires a special user that relays data between the master and slaves. The following environment variables are defined in the template for this purpose:

Example 1. MySQL Master Deployment Configuration Object Definition in the Example Template

kind: "DeploymentConfig"
apiVersion: "v1"
metadata:
  name: "mysql-master"
spec:
  strategy:
    type: "Recreate"
  triggers:
    - type: "ConfigChange"
  replicas: 1
  selector:
    name: "mysql-master"
  template:
    metadata:
      labels:
        name: "mysql-master"
    spec:
      volumes:
        - name: "mysql-master-data"
          persistentVolumeClaim:
            claimName: "mysql-master"
      containers:
        - name: "server"
          image: "openshift/mysql-56-centos7"
          command:
            - "run-mysqld-master"
          ports:
            - containerPort: 3306
              protocol: "TCP"
          env:
            - name: "MYSQL_MASTER_USER"
              value: "${MYSQL_MASTER_USER}"
            - name: "MYSQL_MASTER_PASSWORD"
              value: "${MYSQL_MASTER_PASSWORD}"
            - name: "MYSQL_USER"
              value: "${MYSQL_USER}"
            - name: "MYSQL_PASSWORD"
              value: "${MYSQL_PASSWORD}"
            - name: "MYSQL_DATABASE"
              value: "${MYSQL_DATABASE}"
            - name: "MYSQL_ROOT_PASSWORD"
              value: "${MYSQL_ROOT_PASSWORD}"
          volumeMounts:
            - name: "mysql-master-data"
              mountPath: "/var/lib/mysql/data"
          resources: {}
          terminationMessagePath: "/dev/termination-log"
          imagePullPolicy: "IfNotPresent"
          securityContext:
            capabilities: {}
            privileged: false
      restartPolicy: "Always"
      dnsPolicy: "ClusterFirst"

Since we claimed a persistent volume in this deployment configuration to have all data persisted for the MySQL master server, you must ask your cluster administrator to create a persistent volume that you can claim the storage from.

After the deployment configuration is created and the pod with MySQL master server is started, it will create the database defined by **MYSQL_DATABASE** and configure the server to replicate this database to slaves.

The example provided defines only one replica of the MySQL master server. This causes OKD to start only one instance of the server. Multiple instances (multi-master) is not supported and therefore you can not scale this replication controller.

To replicate the database created by the MySQL master, a deployment configuration is defined in the template. This deployment configuration creates a replication controller that launches the MySQL image with the **command** field set to run-mysqld-slave. This alternative entrypoints skips the initialization of the database and configures the MySQL server to connect to the mysql-master service, which is also defined in example template.

Example 2. MySQL Slave Deployment Configuration Object Definition in the Example Template

kind: "DeploymentConfig"
apiVersion: "v1"
metadata:
  name: "mysql-slave"
spec:
  strategy:
    type: "Recreate"
  triggers:
    - type: "ConfigChange"
  replicas: 1
  selector:
    name: "mysql-slave"
  template:
    metadata:
      labels:
        name: "mysql-slave"
    spec:
      containers:
        - name: "server"
          image: "openshift/mysql-56-centos7"
          command:
            - "run-mysqld-slave"
          ports:
            - containerPort: 3306
              protocol: "TCP"
          env:
            - name: "MYSQL_MASTER_USER"
              value: "${MYSQL_MASTER_USER}"
            - name: "MYSQL_MASTER_PASSWORD"
              value: "${MYSQL_MASTER_PASSWORD}"
            - name: "MYSQL_DATABASE"
              value: "${MYSQL_DATABASE}"
          resources: {}
          terminationMessagePath: "/dev/termination-log"
          imagePullPolicy: "IfNotPresent"
          securityContext:
            capabilities: {}
            privileged: false
      restartPolicy: "Always"
      dnsPolicy: "ClusterFirst"

This example deployment configuration starts the replication controller with the initial number of replicas set to 1. You can scale this replication controller in both directions, up to the resources capacity of your account.

If either the master or any of the slaves goes down, OKD will bring them back up. The master will reuse the persistent volume, while any restarted slaves will replicate data from the master.

Creating a Headless Service

The pods created by the MySQL slave replication controller must reach the MySQL master server in order to register for replication. The example template defines a headless service named mysql-master for this purpose. This service is not used only for replication, but the clients can also send the queries to mysql-master:3306 as the MySQL host.

To have a headless service, the **clusterIP** parameter in the service definition is set to None. Then you can use a DNS query to get a list of the pod IP addresses that represents the current endpoints for this service.

Example 3. Headless Service Object Definition in the Example Template

kind: "Service"
apiVersion: "v1"
metadata:
  name: "mysql-master"
  labels:
    name: "mysql-master"
spec:
  ports:
    - protocol: "TCP"
      port: 3306
      targetPort: 3306
      nodePort: 0
  selector:
    name: "mysql-master"
  clusterIP: "None"
  type: "ClusterIP"
  sessionAffinity: "None"
status:
  loadBalancer: {}

Scaling the MySQL Slaves

To increase the number of members in the cluster:

$ oc scale rc mysql-slave-1 --replicas=<number>

This tells the replication controller to create a new MySQL slave pod. When a new slave is created, the slave entrypoint first attempts to contact the mysql-master service and register itself to the replication set. Once that is done, the MySQL master server sends the slave the replicated database.

When scaling down, the MySQL slave is shut down and, because the slave does not have any persistent storage defined, all data on the slave is lost. The MySQL master server then discovers that the slave is not reachable anymore, and it automatically removes it from the replication.

Troubleshooting

This section describes some troubles you might encounter and presents possible resolutions.

Linux Native AIO Failure

Symptom

The MySQL container fails to start and the logs show something like:

151113  5:06:56 InnoDB: Using Linux native AIO
151113  5:06:56  InnoDB: Warning: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
InnoDB: Warning: io_setup() attempt 1 failed.
InnoDB: Warning: io_setup() attempt 2 failed.
Waiting for MySQL to start ...
InnoDB: Warning: io_setup() attempt 3 failed.
InnoDB: Warning: io_setup() attempt 4 failed.
Waiting for MySQL to start ...
InnoDB: Warning: io_setup() attempt 5 failed.
151113  5:06:59  InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts.
InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
151113  5:06:59 InnoDB: Fatal error: cannot initialize AIO sub-system
151113  5:06:59 [ERROR] Plugin 'InnoDB' init function returned error.
151113  5:06:59 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
151113  5:06:59 [ERROR] Unknown/unsupported storage engine: InnoDB
151113  5:06:59 [ERROR] Aborting

Explanation

MySQL’s storage engine was unable to use the kernel’s AIO (Asynchronous I/O) facilities due to resource limits.

Resolution

Turn off AIO usage entirely by setting environment variable **MYSQL_AIO** to have value 0. On subsequent deployments, this arranges for the MySQL configuration variable **innodb_use_native_aio** to have value 0.

Alternatively, increase the aio-max-nr kernel resource. The following example examines the current value of aio-max-nr and doubles it.

$ sysctl fs.aio-max-nr
fs.aio-max-nr = 1048576
# sysctl -w fs.aio-max-nr=2097152

This is a per-node resolution and lasts until the next node reboot.