Chapter 10. Configuring an external MySQL database

This guide provides information for externalizing the MySQL database for Chapter 8, 3scale High Availability and Evaluation templates. This can be done by using the default amp.yml file. This is useful where there are several infrastructure issues, such as network or filesystem, using the default system-mysql pod.

The difference between this approach and the one in Chapter 8, 3scale High Availability and Evaluation templates is that this provides a way for externalizing a MySQL database in case Red Hat 3scale API Management was initially using the default amp.yml template.

Note

Red Hat supports 3scale configurations that use an external MySQL database. However, the database itself is not within the scope of support.

Prerequisites

To configure an external MySQL database for High Availability (HA), perform the steps outlined in the following sections:

10.1. External MySQL database limitations

There are limitations with the process of externalizing your MySQL database:

3scale On-premises versions

It has only been tested and verified on the 2.5 On-premises and 2.6 On-premises versions from 3scale.

MySQL database user

With the mysql2:// formatted URL, you must use 'root'@'%' or the connection to the database will fail. Using any combination of username and password is not supported since 3scale uses 'root'@'%'.

MySQL host

Use the IP address from the external MySQL database instead of the hostname or it will not resolve. For example, use 1.1.1.1 instead of mysql.mydomain.com.

10.2. Externalizing the MySQL database

Use the following steps to fully externalize the MySQL database.

Warning

This will cause downtime in the environment while the process is ongoing.

Procedure

  1. Login to the OpenShift node where your 3scale On-premises instance is hosted and change to its project:

    oc login -u <user> <url>
    oc project <3scale-project>

    Replace <user>, <url>, and <3scale-project> with your own credentials and the project name.

  2. Follow the steps below in the order shown to scale down all the pods. This will avoid loss of data.

    Stop 3scale On-premises

    From the OpenShift web console or from the command line interface (CLI), scale down all the deployment configurations to zero replicas in the following order:

    • apicast-wildcard-router and zync for versions before 3scale 2.6 or zync-que and zync for 3scale 2.6 and above.
    • apicast-staging and apicast-production.
    • system-sidekiq, backend-cron, and system-sphinx.

      • 3scale 2.3 includes system-resque.
    • system-app.
    • backend-listener and backend-worker.
    • backend-redis, system-memcache, system-mysql, system-redis, and zync-database.

      The following example shows how to perform this in the CLI for apicast-wildcard-router and zync:

      oc scale dc/apicast-wildcard-router --replicas=0
      oc scale dc/zync --replicas=0
      Note

      The deployment configuration for each step can be scaled down at the same time. For example, you could scale down apicast-wildcard-router and zync together. However, it is better to wait for the pods from each step to terminate before scaling down the ones that follow. The 3scale instance will be completely inaccessible until it is fully started again.

  3. To confirm that no pods are running on the 3scale project use the following command:

    oc get pod

    The command should return No resources found.

  4. Scale up the database level pods again using the following command:

    oc scale dc/{backend-redis,system-memcache,system-mysql,system-redis,zync-database} --replicas=1
  5. Ensure that you are able to login to the external MySQL database through the system-mysql pod before proceeding with the next steps:

    oc rsh system-mysql-<system_mysql_pod_id>
    mysql -u root -p -h <host>
    • <system_mysql_pod_id>: The identifier of the system-mysql pod.
    • The user should always be root. For more information see External MySQL database limitations.

      1. The CLI will now display mysql>. Type exit, then press return. Type exit again at the next prompt to go back to the OpenShift node console.
  6. Perform a full MySQL dump using the following command:

    oc rsh system-mysql-<system_mysql_pod_id> /bin/bash -c "mysqldump -u root --single-transaction --routines --triggers --all-databases" > system-mysql-dump.sql
    • Replace <system_mysql_pod_id> with your unique system-mysql pod ID .
    • Validate that the file system-mysql-dump.sql contains a valid MySQL level dump as in the following example:

      $ head -n 10 system-mysql-dump.sql
      -- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
      --
      -- Host: localhost    Database:
      -- ------------------------------------------------------
      -- Server version   5.7.24
      
      /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
      /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
      /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
      /*!40101 SET NAMES utf8 */;
  7. Scale down the system-mysql pod and leave it with 0 (zero) replicas:

    oc scale dc/system-mysql --replicas=0
  8. Find the base64 equivalent of the URL mysql2://root:<password>@<host>/system, replacing <password> and <host> accordingly:

    echo "mysql2://root:<password>@<host>/system" | base64
  9. Create a default 'user'@'%' on the remote MySQL database. It only needs to have SELECT privileges. Also find its base64 equivalents:

    echo "user" | base64
    echo "<password>" | base64
    • Replace <password> with the password for 'user'@'%'.
  10. Perform a backup and edit the OpenShift secret system-database:

    oc get secret system-database -o yaml > system-database-orig.bkp.yml
    oc edit secret system-database
    • URL: Replace it with the value from [step-8].
    • DB_USER and DB_PASSWORD: Use the values from the previous step for both.
  11. Send system-mysql-dump.sql to the remote database server and import the dump into it. Use the command to import it:
  12. Use the command below to send system-mysql-dump.sql to the remote database server and import the dump into the server:

    mysql -u root -p < system-mysql-dump.sql
  13. Ensure that a new database called system was created:

    mysql -u root -p -se "SHOW DATABASES"
  14. Use the following instructions to Start 3scale On-premises, which scales up all the pods in the correct order.

    Start 3scale On-premises

    • backend-redis, system-memcache, system-mysql, system-redis, and zync-database.
    • backend-listener and backend-worker.
    • system-app.
    • system-sidekiq, backend-cron, and system-sphinx

      • 3scale 2.3 includes system-resque.
    • apicast-staging and apicast-production.
    • apicast-wildcard-router and zync for versions before 3scale 2.6 or zync-que and zync for 3scale 2.6 and above.

      The following example shows how to perform this in the CLI for backend-redis, system-memcache, system-mysql, system-redis, and zync-database:

      oc scale dc/backend-redis --replicas=1
      oc scale dc/system-memcache --replicas=1
      oc scale dc/system-mysql --replicas=1
      oc scale dc/system-redis --replicas=1
      oc scale dc/zync-database --replicas=1

      The system-app pod should now be up and running without any issues.

  15. After validation, scale back up the other pods in the order shown.
  16. Backup the system-mysql DeploymentConfig object. You may delete after a few days once you are sure everything is running properly. Deleting system-mysql DeploymentConfig avoids any future confusion if this procedure is done again in the future.

10.3. Rolling back

Perform a rollback procedure if the system-app pod is not fully back online and the root cause for it could not be determined or addressed after following step 14.

  1. Edit the secret system-database using the original values from system-database-orig.bkp.yml. See [step-10]:

    oc edit secret system-database

    Replace URL, DB_USER, and DB_PASSWORD with their original values.

  2. Scale down all the pods and then scale them back up again, including system-mysql. The system-app pod and the other pods to be started after it should be up and running again. Run the following command to confirm all pods are back up and running:

    oc get pods -n <3scale-project>

10.4. Additional information