Chapter 5. Configuring an external MySQL database

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.

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>