Search

Chapter 5. Configuring an external MySQL database

download PDF
Important

When you externalize databases from a Red Hat 3scale API Management deployment, this means to provide isolation from the application and resilience against service disruptions at the database level. The resilience to service disruptions depends on the service level agreements (SLAs) provided by the infrastructure or platform provider where you host the databases. This is not offered by 3scale. For more details on externalizing of databases offered by your chosen deployment, see the associated documentation.

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

This guide provides information for externalizing the MySQL database. This is useful where there are several infrastructure issues, such as network or filesystem, using the default system-mysql pod.

Prerequisites

  • Access to an OpenShift Container Platform 4.x cluster using an account with administrator privileges.
  • A 3scale instance installation on the OpenShift cluster. See Installing 3scale on OpenShift.

To configure an external MySQL database, perform the steps outlined in the following sections:

5.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

The URL must be in the following format:

<database_scheme>://<admin_user>:<admin_password>@<database_host>/<database_name>

An <admin_user> must be an existing user in the external database with full permissions on the <database_name> logical database. The <database_name> must be an already existing logical database in the external database.

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.

5.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 pods -n <3scale_namespace>

    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 8.0, for Linux (x86_64)
      --
      -- Host: localhost    Database:
      -- ------------------------------------------------------
      -- Server version   8.0
      
      /*!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.

5.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>

5.4. Additional information

Red Hat logoGithubRedditYoutubeTwitter

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

We help Red Hat users innovate and achieve their goals with our products and services with content they can trust.

Making open source more inclusive

Red Hat is committed to replacing problematic language in our code, documentation, and web properties. For more details, see the Red Hat Blog.

About Red Hat

We deliver hardened solutions that make it easier for enterprises to work across platforms and environments, from the core datacenter to the network edge.

© 2024 Red Hat, Inc.