How to use mysqldump to Test for Upgrade Incompatabilities
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.
Install the Aptible CLI and MySQL. This guide uses the
mysql client tools.
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 theDatabase belongs to.
Collect information on the target Database and store it in in the following environment variables:
TARGET_HANDLE- The handle (i.e. name) for the Database.
TARGET_VERSION- The target MySQL version. Run
aptible db:versionsto 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.
TARGET_HANDLE='upgrade-test' TARGET_VERSION='8.0' TARGET_ENVIRONMENT='test-environment'
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"
aptible db:create creates a Database with a 1 GB of memory and 10 GB of disk space. This is typically sufficient for testing table definition compatability, but if more memory or disk is required, the
--disk-size arguments can be used.
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 is 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.
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.
Deprovision the target Database
aptible db:deprovision "$TARGET_HANDLE" --environment "$TARGET_ENVIRONMENT"
$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:
aptible backup:orphaned --environment "$TARGET_ENVIRONMENT"
Then, delete the backup(s) by ID using the
aptible backup:purge command.
Updated 10 months ago