Red Hat Summit Red Hat SummitSupportConsoleDevelopersStart a trialContact

Chapter 4. Configuring an external MySQL database

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

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

4.1. External MySQL database configuration
Copy link

When creating an external MySQL database, you need to configure it as explained below.

MySQL database user

The connection string that is used to configure the database connection (see System database secret to learn where to configure the connection string) for the external MySQL database must be in the following format: 

mysql2://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}
Copy to Clipboard Toggle word wrap

{DB_PASSWORD} and {DB_PORT} are optional.

The user with username {DB_USER} must be created and granted all privileges to the database indicated as {DB_NAME}. Example commands for creating a user: 

CREATE USER 'exampleuser'@'%' IDENTIFIED BY 'examplepass';
GRANT ALL PRIVILEGES ON exampledb.* to 'exampleuser'@'%';
Copy to Clipboard Toggle word wrap

In case of a new installation of 3scale, if the database {DB_NAME} does not exist, it will be created by the installation scripts.

Binary logging configuration

In case binary logging is enabled on the MySQL server, and the database user doesn’t have the SUPER privilege, the global system variable log_bin_trust_function_creators must be set to 1. This is required because 3scale uses stored procedures and triggers.

Alternatively, if you choose to set SUPER privilege for the database user, note that it is deprecated as of MySQL 8.0 and will be removed in a future version of MySQL. See MySQL documentation for more information.

Configuring secure connection (SSL/TLS)

If the MySQL server requires a secure connection (for example, when the system variable require_secure_transport is set to ON), you need to configure the MySQL client to use SSL/TLS. To do so, add the parameter ?ssl_mode=required to the connection string set in the System database secret. Example value: 

mysql2://some-user:secure-password@db.example.com/system_database?ssl_mode=required
Copy to Clipboard Toggle word wrap

Refer to the MySQL documentation on client-side configuration for encrypted connections for more information about the possible values of the ssl_mode parameter.

4.2. Externalizing the MySQL database
Copy link

Use the following steps to fully externalize the MySQL database.

Warning

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

Prerequisites

  • You have a running and working 3scale instance with the MySQL default database. The 3scale instance installation supports your MySQL database version.

  • You have a MySQL instance with the following characteristics:

    • Its version is supported for the current 3scale On-premises installation. See the supported configurations page.
    • The current built-in MySQL database and the external one have the same version.
    • The database must be accessible from the pods in the 3scale namespace. The database hostname or IP address is referred to as <ext_mysql_hostname>.
    • The database is configured according to the requirements specified in External MySQL database configuration.
    • An empty database exists. The database name is referred to as <ext_mysql_dbname>.
    • A user with credentials referred to as <ext_mysql_username> and <ext_mysql_password> has full access to the <ext_mysql_dbname> database. root user can also be used to access it, but it is not recommended. Refer to External MySQL database configuration for an example on granting the required privileges to a user.
  • You have the sed utility for manipulating text files.

Procedure

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

    $ oc login <url> <authentication-parameters>
$ oc project <3scale-project>
    Copy to Clipboard Toggle word wrap

    Replace <url>, <authentication-parameters> and <3scale-project> with your own OpenShift server URL, authentication parameters and the project name where 3scale is installed. Authentication parameters can be either -u <username> or --token=<token>.

  2. Back up the existing APIManager custom resource. To confirm the name of your APIManager resource, use the following command: 

    $ oc get apimanager
NAME         AGE
apimanager   151d
    Copy to Clipboard Toggle word wrap

    In the example above the name of the APIManager custom resource is apimanager.

    Export the resource for future reference using the following command (change <apimanager-resource-name> with your resource name): 

    $ oc get apimanager <apimanager-resource-name> -o yaml > apimanager.backup.yml
    Copy to Clipboard Toggle word wrap

  3. Ensure that you are able to log in to the external MySQL database through the system-mysql pod before proceeding with the next steps: 

    $ oc rsh <system_mysql_pod_name> mysql -u <ext_mysql_username> -p -h <ext_mysql_hostname> <ext_mysql_dbname>
    Copy to Clipboard Toggle word wrap
    • <system_mysql_pod_name>: The name of the system-mysql pod (starting with system-mysql-). You can get the pod name using the command oc get pods --selector=deployment=system-mysql -o name.
    • When prompted, enter the <ext_mysql_username> user password (<ext_mysql_password>).
    • The CLI will now display mysql>. Type exit, then press return.
  4. Stop the 3scale pods in the order described below.

Stop 3scale On-premises

  1. Scale down the deployment of the 3scale operator controller to prevent it from interfering with the scaling down of other pods. 

    $ oc scale deployment/threescale-operator-controller-manager-v2 --replicas=0
    Copy to Clipboard Toggle word wrap

  2. Scale down the following pods to 0 replicas. It is recommended to run these commands one by one and wait for each step to be completed before proceeding to the next one. Use the command oc get deployment/<deployment-name> to check the status of the pods in the deployment. You should expect the READY column to show 0/0 for each deployment before proceeding to the next one. 

    $ oc scale deployment/{apicast-production,apicast-staging} --replicas=0
$ oc get deployment/{apicast-production,apicast-staging}
    Copy to Clipboard Toggle word wrap 
    $ oc scale deployment/{system-app,system-sidekiq} --replicas=0
$ oc get deployment/{system-app,system-sidekiq}
    Copy to Clipboard Toggle word wrap 
    $ oc scale deployment/{backend-listener,backend-worker,backend-cron,system-memcache,system-redis,system-searchd,zync,zync-que} --replicas=0
$ oc get deployment/{backend-listener,backend-worker,backend-cron,system-memcache,system-redis,system-searchd,zync,zync-que}
    Copy to Clipboard Toggle word wrap 
    $ oc scale deployment/{backend-redis,zync-database} --replicas=0
$ oc get deployment/{backend-redis,zync-database}
    Copy to Clipboard Toggle word wrap

  3. Ensure that all deployments except system-mysql are scaled down to 0 pods using the following command. The READY column should show 0/0 for all deployments except system-mysql. 

    $ oc get deployments
    Copy to Clipboard Toggle word wrap

    1. Perform a full MySQL dump using the following command: 

      $ oc rsh <system_mysql_pod_name> /bin/bash -c "mysqldump -u root --single-transaction --routines --triggers system" > system-mysql-dump-temp.sql
      Copy to Clipboard Toggle word wrap
      • Replace <system_mysql_pod_name> with your unique system-mysql pod name.

      • Validate that the file system-mysql-dump-temp.sql contains a valid MySQL level dump as in the following example: 

        $ head -n 10 system-mysql-dump-temp.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 */;
        Copy to Clipboard Toggle word wrap

    2. Remove the DEFINER from the dump using the following command, if the user on the external database is not root: 

      $ sed 's/DEFINER=`root`@`%`//g' system-mysql-dump-temp.sql > system-mysql-dump.sql
      Copy to Clipboard Toggle word wrap

    3. Scale down the system-mysql pod and leave it with 0 (zero) replicas: 

      $ oc scale deployment/system-mysql --replicas=0
      Copy to Clipboard Toggle word wrap

    4. Import the dump into the external server using the following command: 

      $ mysql -u <ext_mysql_username> -p -h <ext_mysql_hostname> <ext_mysql_dbname> < system-mysql-dump.sql
      Copy to Clipboard Toggle word wrap
      • When prompted, enter the <ext_mysql_username> user password (<ext_mysql_password>) on the external server.

      • Ensure that the <ext_mysql_dbname> database has been populated with the data from the dump: 

        $ mysql -u <ext_mysql_username> -p -h <ext_mysql_hostname> <ext_mysql_dbname> -e 'show tables;'
        Copy to Clipboard Toggle word wrap

        Example output for the database named system_app would be the following (only the first 15 lines are shown): 

        +--------------------------------+
| Tables_in_system_app           |
+--------------------------------+
| access_tokens                  |
| accounts                       |
| alerts                         |
| api_docs_services              |
| application_keys               |
| ar_internal_metadata           |
| audits                         |
| authentication_providers       |
| backend_api_configs            |
| backend_apis                   |
| backend_events                 |
| billing_strategies             |
        Copy to Clipboard Toggle word wrap

    5. Perform a backup of the existing OpenShift secret system-database: 

      $ oc get secret system-database -o yaml > system-database.backup.yml
      Copy to Clipboard Toggle word wrap

    6. Update the connection string in the system-database secret to point to the external MySQL database. Replace the <ext_mysql_username>, <ext_mysql_password>, <ext_mysql_dbname> and <ext_mysql_hostname> with your own values: 

      $ oc create secret generic system-database --from-literal=URL=mysql2://<ext_mysql_username>:<ext_mysql_password>@<ext_mysql_hostname>/<ext_mysql_dbname> --dry-run=client -o yaml | oc replace -f -
      Copy to Clipboard Toggle word wrap

    7. Update the APIManager custom resource to indicate that the database of the system component is external. This will detach the system-mysql deployment and the related PersistentVolumeClaim and ConfigMap resources from the 3scale operator. Run the following command (change <apimanager-resource-name> with your APIManager resource name): 

      $ oc patch apimanager apimanager --type=merge -p '{"spec": {"externalComponents": {"system": {"database": true}}}}'
      Copy to Clipboard Toggle word wrap
    8. Use the following instructions to Start 3scale On-premises, which scales up all the pods in the correct order.

  4. Scale up the following pods to 1 replica. It is recommended to run these commands one by one and wait for each step to be completed before proceeding to the next one. Use the command oc get deployment/<deployment-name> to check the status of the pods in the deployment. You should expect the READY column to show 1/1 for each deployment before proceeding to the next one. 

    $ oc scale deployment/{backend-redis,zync-database} --replicas=1
$ oc get deployment/{backend-redis,zync-database}
    Copy to Clipboard Toggle word wrap 
    $ oc scale deployment/{backend-listener,backend-worker,backend-cron,system-memcache,system-redis,system-searchd,zync,zync-que} --replicas=1
$ oc get deployment/{backend-listener,backend-worker,backend-cron,system-memcache,system-redis,system-searchd,zync,zync-que}
    Copy to Clipboard Toggle word wrap 
    $ oc scale deployment/{system-app,system-sidekiq} --replicas=1
$ oc get deployment/{system-app,system-sidekiq}
    Copy to Clipboard Toggle word wrap

    If the external MySQL database integration has been configured properly, the system-app and system-sidekiq pods should start without any errors. At this point you can try to access the admin and developer portals to verify they work as expected. Proceed to the next commands after confirming that both portals are accessible and contain the expected data. In case you observe any errors in the system-app or system-sidekiq pods, you can follow the instructions in Rolling back to revert the changes and restore the system to use the internal MySQL database. 

    $ oc scale deployment/{apicast-production,apicast-staging} --replicas=1
$ oc get deployment/{apicast-production,apicast-staging}
    Copy to Clipboard Toggle word wrap

  5. Scale up the deployment of the 3scale operator controller back to 1 replica. 

    $ oc scale deployment/threescale-operator-controller-manager-v2 --replicas=1
    Copy to Clipboard Toggle word wrap

    Once the threescale-operator-controller-manager-v2 pod is up and running, the 3scale operator will reconcile the resources to ensure that the state of the cluster matches the desired state defined in the APIManager custom resource. For example, if any replicas number is specified for any of the components in the APIManager custom resource, the operator will scale the corresponding deployment to match that number.

  6. Verify that all the pods are up and running with the following command: 

    $ oc get deployments
    Copy to Clipboard Toggle word wrap

    All deployments should show matching numbers of ready and desired replicas in the READY column, for example, 1/1 or 2/2, except system-mysql.

    1. Verify that the everything is working properly by logging in to the Admin Portal and the Developer Portal and checking that the APIs are working as expected.
    2. Back up the system-mysql Deployment object. You may delete after a few days once you are sure everything is running properly. Deleting system-mysql Deployment avoids any future confusion if this procedure is done again in the future.

4.3. Rolling back
Copy link

Perform a rollback procedure if some issue occurs when scaling up the pods and it cannot be fixed.

Prerequisites

Procedure

Run the following steps to roll back the changes made to externalize the MySQL database.

  1. Scale down all the pods following the instructions in Stop 3scale On-premises.

  2. Restore the system-database secret to its original contents, pointing the system to the internal MySQL database. Run the following command: 

    $ cat system-database.backup.yml \
  | yq 'del(.metadata.annotations, .metadata.creationTimestamp, .metadata.resourceVersion, .metadata.uid)' \
  | oc replace -f -
    Copy to Clipboard Toggle word wrap

  3. Update the APIManager custom resource to indicate that the database of the system component is internal. Run the following command (change <apimanager-resource-name> with your APIManager resource name): 

    $ oc patch apimanager apimanager --type=merge -p '{"spec": {"externalComponents": {"system": {"database": false}}}}'
    Copy to Clipboard Toggle word wrap
  4. Scale all the pods up again following the instructions in Start 3scale On-premises.

4.4. Additional information
Copy link

For more information about 3scale and MySQL database support, see Red Hat 3scale API Management Supported Configurations.

After you have confirmed that the external MySQL database is working correctly with your 3scale installation, you can optionally delete the internal MySQL database resources to free up resources in your OpenShift cluster. Follow the steps below to delete the resources.

Warning

The data stored in the internal MySQL database will be permanently lost after performing these steps. If you think you might need to restore to the internal MySQL database in the future, you will need to restore the data from the dump system-mysql-dump.sql you created as part of Externalizing the MySQL database.

  1. (Optional) Back up the OpenShift resources related to the internal MySQL database. This is useful only for reference, in case you applied any custom configuration to them. It is not recommended to restore these resources from the backup files. If you need to roll back to using the internal MySQL database, set spec.externalComponents.system.database field of the APIManager CR to false and let the 3scale operator recreate the resources. 

    $ oc get deployment system-mysql -o yaml > system-mysql-deployment.backup.yml
$ oc get pvc mysql-storage -o yaml > mysql-storage.backup.yml
$ oc get cm mysql-extra-conf -o yaml > mysql-extra-conf.backup.yml
$ oc get cm mysql-main-conf -o yaml > mysql-main-conf.backup.yml
$ oc get service system-mysql -o yaml > system-mysql-service.backup.yml
    Copy to Clipboard Toggle word wrap

  2. Delete the OpenShift resources related to the internal MySQL database: 

    $ oc delete deployment system-mysql
$ oc delete pvc mysql-storage
$ oc delete cm mysql-extra-conf
$ oc delete cm mysql-main-conf
$ oc delete service system-mysql
    Copy to Clipboard Toggle word wrap
Back to top
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. Explore our recent updates.

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.

Theme

© 2025 Red Hat