How to dump and restore MySQL
The goal of this guide is to dump the data from one MySQL Database and restore it to another. This is generally done to upgrade to a new MySQL version but can be used in any situation where data needs to be migrated to a new Database instance.
MySQL only supports upgrade between General Availability releases, so upgrading multiple versions (i.e. 5.6 => 8.0) requires going through the upgrade process multiple times.
The amount of time it takes to dump and restore a Database is directly related to the size of the Database and network bandwidth. If the Database being dumped is small (< 10 GB) and bandwidth is decent, then dumping locally is usually fine. Otherwise, consider dumping and restoring from a server with more bandwidth, such as an AWS EC2 Instance.
Another thing to consider is available disk space. There should be at least as much space locally available as the Database is currently taking up on disk. See the Database's metrics to determine the current amount of space it's taking up. If there isn't enough space locally, this would be another good indicator to dump and restore from a server with a large enough disk.
All of the following instructions should be completed on the selected machine.
Test the table definitions
If data is being transferred to a Database running a different MySQL version than the original, first check that the table definitions can be restored on the desired version by following the How to use mysqldump to Test for Upgrade Incompatabilities guide.
If the same MySQL version is being used, this is not necessary.
Test the upgrade
It's recommended to test the upgrade before performing it in production. The easiest way to do this is to restore the latest backup of the Database and performing the upgrade against the restored Database. The restored Database should have the same container size as the production Database.
aptible backup:restore 1234 --handle upgrade-test --container-size 4096
If you're performing the test to get an estimate of how much downtime is required to perform the upgrade, you'll need to dump the restored Database twice in order to get an accurate time estimate. The first time will ensure that all of the backup data has been synced to the disk. The second backup will take approximately the same amount of time as the production dump.
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"
Scale Services down
Scale all Services that use the Database down to zero containers. It's usually easiest to prepare a script that scales all Services down and another that scales them back up to their current values once the upgrade has been complete. Current container counts can be found in the Aptible Dashboard or by running
APTIBLE_OUTPUT_FORMAT=json aptible apps.
aptible apps:scale --app my-app cmd --container-count 0
While this step is not strictly required, it ensures that the Services don't write to the Database during the upgrade and that its HTTP(S) Endpoints will show the App's Maintenance Page if anyone tries to access them.
Dump the data
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.
dump.sql in this case.
MYSQL_PWD="$PASSWORD" mysqldump --user root --host localhost.aptible.in --port 5432 --all-databases --routines --events > dump.sql
The following error may come up when dumping:
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
dump.sql! The Database Tunnel can be closed by following the instructions that
aptible db:tunnel printed when the tunnel started.
Restore the data
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 root --host localhost.aptible.in --port 5432 < dump.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 you can try the dump again by deprovisioning the target Database and starting from the Create the target Database step.
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 backup for the target Database.
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.
Once the upgrade is complete, any Services that use the existing Database need to be updated to use the upgraded target Database. Assuming you're supplying the Database Credentials through the App's Configuration, this can usually be easily done with the
aptible config:set command.
aptible config:set --app my-app DB_URL='mysql://aptible:[email protected]:5432/db'
Scale Services back up
If Services were scaled down before performing the upgrade, they need to be scaled back up afterwards. This would be the time to run the scale-up script that was mentioned in Scale Services down
aptible apps:scale --app my-app cmd --container-count 2
Once the original Database is no longer necessary, it should be deprovisioned or it will continue to incur costs. Note that this will purge all automated Backups. If you'd like to retain the Backups, contact Aptible Support to update them.
aptible db:deprovision "$SOURCE_HANDLE" --environment "$SOURCE_ENVIRONMENT"
Updated 8 months ago