Migrating to Internal Percona MySQL

This topic describes the procedure for migrating Pivotal Application Service (PAS) internal MySQL databases from the less secure MariaDB infrastructure to a TLS-encrypted Percona stack.

In PAS v2.1 and earlier, internal system MySQL databases ran MariaDB with cluster nodes communicating over plaintext. PAS v2.2 offers a second option for internal databases: a Percona Server that uses TLS to encrypt communication between server cluster nodes.

WARNING: This procedure causes temporary downtime of PAS system functions, as described in Effects of MySQL Downtime and MySQL Migration Downtime. It does not interrupt apps hosted by PAS, unless they use TCP routing. Applications using TCP routes will not be routable during the migration.

Effects of MySQL Downtime

During internal MySQL server downtime, the Cloud Controller and other platform components that use system databases become unavailable. This prevents users from pushing apps, scaling apps, and running other cf CLI commands.

If the PAS UAA is configured to use an internal database, then the UAA also becomes unavailable. This outage temporarily prevents users from signing into PAS orgs and spaces, Apps Manager, and other PAS interfaces.

PAS UAA downtime caused by internal MySQL migration also temporarily prevents users from logging into any PAS apps that use the Single Sign-On (SSO) service.

Procedure

To migrate your internal MySQL databases from MariaDB to Percona, do the following:

  1. Run mysql-diag to check the state of your MySQL cluster and determine whether it is safe to migrate. For more information, see Running mysql-diag.

  2. If your internal databases MySQL cluster runs three or more nodes, scale it down horizontally to a single node before you migrate. In the PAS tile > Resource Config > MySQL Server settings, set Instances to 1. Click Save.

    Note: Do not click Review Pending Changes and Apply Changes yet.

  3. Migrating the database can demand up to 3 times the normal disk space on the MySQL server. To ensure that the single remaining MySQL server node currently uses at most 30% of its persistent disk, do the following:

    1. Check the persistent disk usage of the MySQL Server job in the Status tab of the PAS tile.
    2. If the MySQL Server job disk usage exceeds 30%, scale it up vertically by increasing its persistent disk.
  4. In the PAS tile > Databases pane, under Choose the location of your system databases, select to Internal Databases - MySQL - Percona XtraDB Cluster. Click Save. At the top of the image is the text 'Choose the location of your system databases. Please consult the documentation for migrating existing installations from MariaDB to Percona.' Below this text are three options: one selected radio button labeled 'Internal Databases - MySQL - Percona XtraDB Cluster', one radio button labeled 'Internal Databases - MySQL - MariaDB Galera Cluster (deprecated - planned to be removed in PAS 2.4', and one radio button labeled 'External Database - (e.g. AWS RDS)'. At the bottom of the image is a blue, rectangular button labeled 'Save'.

  5. Return to the Installation Dashboard, click Review Pending Changes, and click Apply Changes to re-deploy PAS with the new internal database. Consider the following when performing this step:

    • You do not need to run any errands for this step.
    • Internal system databases become temporarily unavailable to PAS components during this migration step, but PAS apps continue to run. See MySQL Migration Downtime for details.
    • You can see that the change is being applied when you see this in Ops Manager’s output after clicking Apply Changes:
      instance_groups:
        name: database
        jobs:
          name: pxc-mysql
          properties:
             pxc_enabled: "PXC-CLUSTER-NAME"
             pxc_enabled: "PXC-CLUSTER-NAME"
          name: mysql
          properties:
             cf_mysql_enabled: "CF-MYSQL-CLUSTER-NAME"
             cf_mysql_enabled: "CF-MYSQL-CLUSTER-NAME"
      
    • You can measure the disk space used in the /var/vcap/store/pxc-mysql directory as the migration is running by SSHing into the database node and running the following command: watch -n 5 du -sh /var/vcap/store/pxc-mysql As the migration is running, disk usage increases:
      2.9G    pxc-mysql
      
      For more information on SSH, see Accessing Apps with SSH.
  6. In the /var/vcap/store/pxc-mysql directory, open galera-init.log to see that Percona has started normally: {"timestamp":"1555115911.754334211","source":"/var/vcap/packages/galera-init/bin/galera-init","message":"/var/vcap/packages/galera-init/bin/galera-init.galera-init started","log_level":1,"data":{}}{"timestamp":"1555115911.754334211","source":"/var/vcap/packages/galera-init/bin/galera-init","message":"/var/vcap/packages/galera-init/bin/galera-init.galera-init started","log_level":1,"data":{}}

    Note: The file /var/vcap/store/migrated-successfully is created upon successful migration. Do not delete this file.

    Additionally, running monit shows that the mariadb_ctrl job has been replaced by galera-init and the galera-healthcheck job has been replaced by galera-agent.

  7. If you want to scale the database cluster back up to multiple server nodes, increase the instance count under Resource Config > MySQL Server. Then click Save to save the change and Review Pending Changes and Apply Changes in the Installation Dashboard to re-deploy.

  8. Run mysql-diag again to check the status of your new Percona cluster.

MySQL Migration Downtime

Upgrading existing internal system databases to the new option of Internal Databases - MySQL - Percona XtraDB Cluster causes temporary downtime of PAS system functions. It does not interrupt apps hosted by PAS.

The length of downtime depends on multiple factors, including database size, persistent disk type, CPU and disk capacity of VMs, and PAS configuration. There are two downtime events:

  • When you scale down and migrate the database: During the migration, PAS updates the single node and migrates the data.
  • When you scale back up: PAS updates the first node to make it part of a three-node cluster.

During migration, BOSH orphans the two disks used by the scaled-down nodes. This does not increase the risk of losing data written before the migration, and these orphaned disks can act as a backup, should the migration fail. If the migration fails, contact Pivotal Support.

Migration Length

To estimate how long a database migration takes, perform a manual backup without saving the backup file.

To perform a manual backup, run the command: time /var/vcap/packages/mariadb/bin/mysqldump --defaults-file=/var/vcap/jobs/mysql/config/mylogin.cnf --all-databases > /dev/null

BOSH runs additional processes before and after a MySQL migration, but the total time the migration takes is not much longer than the manual backup.

Delete MariaDB Migration Artifact

When you migrate your database from MariaDB to Percona, Percona preserves a backup of the MariaDB database as a migration artifact, so you can return to using the MariaDB database if something goes wrong during migration. Upon successful migration, the migration artifact is no longer needed and only takes up disk space.

To delete the MariaDB migration artifact, do the following:

  1. In the /var/vcap/store/pxc-mysql directory, open galera-init.log to ensure that Percona has started normally: {"timestamp":"1555115911.754334211","source":"/var/vcap/packages/galera-init/bin/galera-init","message":"/var/vcap/packages/galera-init/bin/galera-init.galera-init started","log_level":1,"data":{}}{"timestamp":"1555115911.754334211","source":"/var/vcap/packages/galera-init/bin/galera-init","message":"/var/vcap/packages/galera-init/bin/galera-init.galera-init started","log_level":1,"data":{}} You can see that the /var/vcap/store/migrated-successfully file is present in that directory.

  2. Go to the /var/vcap/store/mysql directory.

  3. Delete the migration artifact in /var/vcap/store/mysql.

    Note: Do not delete the new copy of the data in /var/vcap/store/pxc-mysql.

Create a pull request or raise an issue on the source for this page in GitHub