Use mysqldump to test for upgrade incompatibilities

The goal of this guide is to use mysqldump to test the table definitions of an existing Database against another Database version in order to see if it’s compatible with the desired version. The primary reason to do this is to ensure a Database is compatible with a higher version before upgrading without waiting for lengthy data-loading operations.

​

Preparation

​

Step 0: Install the necessary tools

Install the Aptible CLI and MySQL. This guide uses the mysqldump and mysql client tools.

​

Step 1: Configuration

Collect information on the Database you’d like to test and store it in the following environment variables for use later in the guide:

• SOURCE_HANDLE - The handle (i.e. name) of the Database.

• SOURCE_ENVIRONMENT - The handle of the environment the Database belongs to.

Example:

SOURCE_HANDLE='source-db'
SOURCE_ENVIRONMENT='test-environment'

Collect information on the target Database and store it in the following environment variables:

• TARGET_HANDLE - The handle (i.e., name) for the Database.

• TARGET_VERSION - The target MySQL version. Run aptible db:versions to see a full list of options. This must be within one General Availability version of the source Database.

• TARGET_ENVIRONMENT - The handle of the Environment to create the Database in.

Example:

TARGET_HANDLE='upgrade-test'
TARGET_VERSION='8.0'
TARGET_ENVIRONMENT='test-environment'

​

Step 2: Create the target Database

Create a new Database running the desired version. Assuming the environment variables above are set, this command can be copied and pasted as-is to create the Database.

aptible db:create "$TARGET_HANDLE" \
  --type mysql \
  --version "$TARGET_VERSION" \
  --environment "$TARGET_ENVIRONMENT"

By default, aptible db:create creates a Database with 1 GB of memory and 10 GB of disk space. This is typically sufficient for testing table definition compatibility, but if more memory or disk is required, the --container-size and --disk-size arguments can be used.

​

Execution

​

Step 1: Dump the table definition

In a terminal, create a Database Tunnel to the source Database using the Aptible CLI.

aptible db:tunnel "$SOURCE_HANDLE" --environment "$SOURCE_ENVIRONMENT" --port 5432

The tunnel will block the current terminal until it’s stopped. In another terminal, collect the tunnel’s Database Credentials, which are printed by aptible db:tunnel. Then dump the database and database object definitions into a file. defs.sql in this case.

MYSQL_PWD="$PASSWORD" mysqldump  --user root --host localhost.aptible.in --port 5432 --all-databases --no-data --routines --events  > defs.sql

The following error may come up when dumping the table definitions:

Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

This is due to a new flag that is enabled by default in mysqldump 8. You can disable this flag and resolve the error by adding --column-statistics=0 to the above command.

You now have a copy of your Database’s database object definitions in defs.sql! The Database Tunnel can be closed by following the instructions that aptible db:tunnel printed when the tunnel started.

​

Step 2: Restore the table definitions

Create a Database Tunnel to the target Database using the Aptible CLI.

aptible db:tunnel "$TARGET_HANDLE" --environment "$TARGET_ENVIRONMENT" --port 5432

Again, the tunnel will block the current terminal until it’s stopped. In another terminal, apply the table definitions to the target Database.

MYSQL_PWD="$PASSWORD" mysql --user aptible --host localhost.aptible.in --port 5432 < defs.sql

If there are any errors, they will need to be addressed in order to be able to upgrade the source Database to the desired version. Consult the MySQL Documentation for details about the errors you encounter.

Once you’ve updated the source Database’s table definitions, you can test the changes by deprovisioning the target Database, see the Cleanup section, and starting from the Create the target Database step.

​

Cleanup

​

Step 1: Deprovision the target Database

aptible db:deprovision "$TARGET_HANDLE" --environment "$TARGET_ENVIRONMENT"

​

Step 2: Delete Final Backups (Optional)

If the $TARGET_ENVIRONMENT is configured to retain final Database Backups, which is enabled by default, you may want to delete the final backups for all target Databases you created for this test.

You can obtain a list of final backups by running the following:

aptible backup:orphaned --environment "$TARGET_ENVIRONMENT"

Then, delete the backup(s) by ID using the aptible backup:purge command.