IBM Cloud Docs
Understanding data portability for Databases for MySQL

Understanding data portability for Databases for MySQL

Data PortabilityThe ability of a service or workload to recover from rare, major incidents and wide-scale failures, such as service disruption. This includes a physical disaster that affects an entire region, corruption of a database, or the loss of a service contributing to a workload. The impact exceeds the ability of the high availability design to handle it. involves a set of tools, and procedures that enable customers to export the digital artifacts that would be needed to implement similar workload and data processing on different service providers or on-prem software. It includes procedures for copying and storing the service customer's content, including the related configuration used by the service to store and process the data, on customer's own location.

Responsibilities

IBM Cloud services provide interfaces and instructions to guide the customer to copy and store the service customer content, including the related configuration, on their own selected location.

You are responsible for the use of the exported data and configuration for the purpose of data portability to other infrastructures. This can involve the following:

  • Planning and execution for setting up alternate infrastructure on on different cloud providers or on-prem software that provide similar capabilities to the IBM services.
  • Planning and execution for the porting of the required application code on the alternate infrastructure, including the adaptation of customer's application code, and deployment automation.
  • Conversion of the exported data and configuration to format required by the alternate infrastructure and adapted applications.

For more information about your responsibilities when using IBM Cloud® Databases for MySQL, see Shared responsibilities for Databases for MySQL.

Data export procedures

Databases for MySQL provides mechanisms to export your content that has been uploaded, stored, and processed using the service.

Exporting data from Databases for MySQL

Two options exist to migrate data from IBM Cloud® Databases for MySQL. We recommend mysqldump and mydumper. The best tool for you depends on certain conditions, including network connection, the size of your data set, and intermediate schema needs.

Before you begin

Before you start your data migration, install MySQL locally, so you have the mysql and mysqldump tools.

MySQL Workbench also provides a graphical tool for working with MySQL servers and databases.

mysqldump

This native MySQL client utility installs by default and can perform logical backups, reproducing table structure and data, without copying the actual data files. mysqldump dumps one or more MySQL databases for backup or transfer to another MySQL server. For more information, see the mysqldump documentation.

Use mysqldump under the following conditions:

  • The data set is smaller than 10 GB.
  • Migration time is not critical, and the cost of retrying the migration is low.
  • You don’t need to do any intermediate schema or data transformations.

Don't use mysqldump if any of the following conditions are met:

  • Your data set is larger than 10 GB.
  • The network connection between the source and target databases is unstable or slow.

Follow these steps::

Run mysqldump on your source database to create an SQL file, which can be used to re-create the database. At a minimum, migrating mysql using the CLI requires the following arguments:

  • Hostname (-h flag)
  • Port number (-P flag)
  • Username (-u flag)
  • --ssl-mode=VERIFY_IDENTITY (clients require an encrypted connection and perform verification against the server CA certificate and against the server hostname in its certificate).
  • --ssl-ca (the path name of the Certificate Authority (CA) file, which can be found within the Endpoints CLI tab of the Overview page in the UI).
  • database name
  • result file (-r flag)

Your CLI command looks like this:

mysqldump -h <host_name> -P <port_number> -u <user_name> --ssl-mode=VERIFY_IDENTITY --ssl-ca=mysql.crt --set-gtid-purged=OFF -p <database_name> -r dump.sql

To generate a log file of the mysqldump job that tracks errors while it's running, use a command like this:

mysqldump -h <host_name> -P <port_number> -u <user_name> --log-error=error.log --ssl-mode=VERIFY_IDENTITY --ssl-ca=mysql.crt --set-gtid-purged=OFF -p ibmclouddb -r dump.sql

You can do the same while importing, for example:

mysql -h <host_name> -P <port_number> -u admin --ssl-mode=VERIFY_IDENTITY --ssl-ca=mysql.crt -p ibmclouddb < dump.sql > import_logfile.log

The mysql command has many options. For more information, see the mysqldump documentation and command reference.

mydumper

mydumper and its paired logical backup tool myloader use multithreading capabilities to perform data migration similarly to mysqldump. However, mydumper provides many improvements, such as parallel backups, consistent reads, and easier to manage output. Parallelism allows for better performance during both the import and export process, while output can be easier to manage because individual tables get dumped into separate files.

Use mydumper under the following conditions:

  • The data set is larger than 10 GB.
  • The network connection between source and target databases is fast and stable.
  • You need to do intermediate schema or data transformations.

Don't use mydumper if any of the following conditions are met:

  • Your data set is smaller than 10 GB.
  • The network connection between the source and target databases is unstable or very slow.

Before you begin exporting your data with mydumper, see the mydumper project for details and step-by-step instructions on installation and necessary developer environment.

Next, refer to How to use mydumper page for information about using the mydumper and myloader tools to perform full data migration.

Exported data formats

The exported data is in SQL format and can be imported into any other MySQL instance using the mysql command. Perform imports with the admin user. For more information, see the mysqldump documentation.

Data ownership

All exported data are classified as Customer content and therefore apply to them the full customer ownership and licensing rights, as stated in IBM Cloud Service Agreement.