Exporting and Importing Data
Use MySQL Shell to export data from MySQL Servers and use MySQL Shell or Console to import data to MySQL DB systems.
Exporting and Importing Overview
Use MySQL Shell to export to and import from Object Storage.
It is recommended to use the latest version of MySQL Shell.
The dump and load utilities of MySQL Shell are built to use with MySQL Database Service. You can use it for all types of exports and imports, and supports export to and import from Object Storage. The minimum supported source versions, per major version, of MySQL are as follows:
- MySQL 8.0.11: Fully supported by MySQL Shell.
- MySQL 5.7.9: Fully supported by MySQL Shell.
- MySQL 5.6.10: Fully supported by MySQL Shell 8.0.26, or higher. As of MySQL Shell 8.0.22, you can dump instances, schemas, and tables from a MySQL 5.6.10, or higher, instance, but you cannot dump user accounts. To dump user accounts, use MySQL Shell 8.0.26, or higher.
MySQL Shell provides the following utilities:
dumpInstance()
: MySQL instance export utility that exports all compatible schemas to an Object Storage bucket or to local files. By default, this utility exports users, events, routines, and triggers.dumpSchemas()
: Schema export utility that exports selected schemas to an Object Storage bucket or to local files. See MySQL Shell Instance and Schema Dump Utilities.loadDump()
: An import utility that imports schemas to a DB system. See MySQL Shell Dump Loading Utility.To import a schema to a MySQL DB system, install MySQL Shell on a machine with access to the DB system. This can be a local machine using a VPN connection to the VCN, or a compute instance. See Connecting to a DB System, Bastion Session, and VPN Connections.
The dump files are exported as DDL files specifying the schema structure and tab-separated value (.tsv) files containing the data. The .tsv files are compressed using zstd, by default, but gzip is also available as an option. You can also choose no compression but, if you are uploading to Object Storage, it is recommended to use the default.
To further improve performance, large tables are chunked by default. The default chunk size is 32MB. You can disable chunking, but this is not recommended for large databases. You can import the chunks by parallel threads, which can greatly improve import performance.
Compatibility Between On-Premise MySQL and MySQL Database Service
MySQL Database Service has several security-related restrictions that are not present in an on-premise instance of MySQL. The dump commands in MySQL Shell makes it easier to load existing databases into the Service.
The dump commands can detect potential issues and, in some cases, automatically adjust your schema definition to be compliant.
The ocimds
option, when set to true, performs
compatibility checks on the schemas for these issues and aborts the dump if any are
found, while producing a detailed list of those issues and suggests additional steps to
correct those issues. loadDump
command only allows import of dumps
created with the ocimds
option enabled.
Some issues found by the ocimds
option may require you to
manually edit your schema before it can be loaded into the MySQL Database Service. The compatibility options can be used to automatically
modify the dumped schemas, resolving some of these compatibility issues. You can pass
one or more of the following, comma-separated options to
compatibility
:
force_innodb
: MySQL Database Service supports the InnoDB storage engine, only. This option modifies theENGINE=
clause ofCREATE TABLE
statements that use incompatible storage engines and replaces them with InnoDB.strip_definers
: Strips the"DEFINER=account"
clause from views, routines, events, and triggers. MySQL Database Service requires special privileges to create these objects with a definer other than the user loading the schema. By stripping theDEFINER
clause, these objects will be created with that default definer. Views and Routines have theirSQL SECURITY
clause changed fromDEFINER
toINVOKER
. This ensures that the access permissions of the account querying or calling these are applied, instead of the user that created them. If your database security model requires views and routines have more privileges than their invoker, manually modify the schema before loading it. See DEFINER and SQL Security.strip_restricted_grants
: Certain privileges are restricted in the MySQL Database Service. Privileges such asRELOAD
,FILE
,SUPER
,BINLOG_ADMIN
, andSET_USER_ID
. You cannot create users granting these privileges. This option strips these privileges from dumpedGRANT
statements.skip_invalid_accounts
: You cannot export a user that has no password defined. This option skips any such users.strip_tablespaces
: Tablespaces have some restrictions in the MySQL Database Service. If you need tables created in their default tablespaces, this option strips theTABLESPACE=
option fromCREATE TABLE
statements.- Primary key flags:
create_invisible_pks
: Primary keys are required by high availability. If you intend to export data for use in a highly available DB system, add primary keys as they are not defined on the tables. This compatibility flag adds invisible primary keys to each table which requires them. See Prerequisites.ignore_missing_pks
: If you do not intend to use high availability on your DB system, this compatibility flag ignores missing primary keys in your dump.
Additionally, DATA DIRECTORY
, INDEX
DIRECTORY
, and ENCRYPTION
options in CREATE
TABLE
statements are always commented out in DDL scripts if the
ocimds
option is enabled.
If you intend to export an older version of MySQL, such as 5.7.9, the minimum supported source version, it is recommended to run the MySQL Shell Upgrade Checker Utility to generate a report of all potential issues with your migration. See MySQL Shell User Guide - Upgrade Checker Utility.
Exporting Data Using MySQL Shell
Use the MySQL Shell
dumpInstance
utility to export data from a supported MySQL Server source to
an Object Storage bucket .
- MySQL Shell 8.0.27, or higher. The commands in this task
use the
JS
execution mode of MySQL Shell.Note
Exports created by MySQL Shell 8.0.27, or higher, cannot be imported by earlier versions of MySQL Shell. The latest version of MySQL Shell is recommended. - Access to Object Storage and an existing bucket.
- A valid configuration file. If you have installed and configured the CLI in the default location, you have a valid configuration file. If you have not installed and configured the CLI, you must either install it, or create a configuration file manually. See SDK and CLI Configuration File.
- You have run the
dumpInstance
command with thedryRun
andocimds
parameters set totrue
. This performs a test run of the export, checking for compatibility issues, and listing those issues in the output. See MySQL Shell Instance and Schema Dump Utilities.
util.dumpInstance
, or specific
schemas using util.dumpSchemas
. The syntax for each command is:
util.dumpInstance(outputUrl[, options])
util.dumpSchemas(schemas, outputUrl[, options])
This task uses util.dumpInstance
with compatibility
options.
Importing Data
Use MySQL Shell or the Console to import data to the MySQL DB system.
Ensure your DB system has enough storage space for import.
- Using Console: Use the Console to import data using an existing PAR URL, or generate
one using the wizard provided. The wizard can only generate PAR URLs for buckets and
bucket prefix. It cannot generate PAR URLs for manifest files. See Importing Data Using Pre-Authenticated Requests.
Note
You can import to a DB system in the same region as the Object Storage bucket only. - Using MySQL Shell: You have the following ways to import data using MySQL Shell:
- Importing using PAR: Object Storage supports generating PARs on individual
files, buckets, and bucket prefixes. MySQL Shell supports import using the
following methods:
- Bucket PAR: (recommended) Enables you to define a bucket PAR as the source for the import. It is supported by MySQL Shell 8.0.27 or higher. See Importing Data Using Bucket Pre-Authenticated Requests and MySQL Shell.
- Bucket prefix PAR: Enables you to define a bucket
prefix PAR as the source for the import. Supported by MySQL Shell
8.0.27 or higher. See Importing Data Using Bucket Pre-Authenticated
Requests and MySQL Shell.
Note
MySQL Shell supports the folder-type of prefix, only. The filename-matching prefix type is not supported. - Manifest file (
@.manifest.json
): Enables you to import a dump by creating a PAR for a single object in the bucket, the manifest file. The manifest contains details of all files in the dump. See Importing Data Using Manifest File Pre-Authenticated Requests and MySQL Shell.Note
Importing using a manifest file (@.manifest.json
) is a deprecated functionality. Import from a bucket or bucket prefix PAR instead.
- Importing using Object Storage bucket or bucket prefix: Use Object Storage bucket or bucket prefix to import data. See Importing Data Using Object Storage Bucket and MySQL Shell.
- Importing using PAR: Object Storage supports generating PARs on individual
files, buckets, and bucket prefixes. MySQL Shell supports import using the
following methods:
Importing Data Using Pre-Authenticated Requests
Use the Console to import data from a MySQL Shell dump to the MySQL DB system using Pre-Authenticated Request (PAR).
Related Topics
Importing Data Using Bucket Pre-Authenticated Requests and MySQL Shell
Use MySQL Shell and Pre-Authenticated Requests (PAR) to import data from an Object Storage bucket or bucket prefix to a MySQL DB system.
- One of the following network access types to the target MySQL DB
system. See Networking Setup.
- A bridged connection using FastConnect or VPN, enabling you to run MySQL Shell locally.
- SSH access to a compute instance with access to the MySQL DB system, enabling you to run MySQL Shell on the compute instance
- MySQL Shell 8.0.27, or higher.
- Access to Object Storage and an existing bucket that contains the exported files.
- PAR URL for the bucket or prefix with the access types Permit object reads
and Enable Object Listing. See Working with Pre-Authenticated
Requests.
Note
MySQL Shell supports the folder-type of prefix, only. The filename-matching prefix type is not supported. - A valid CLI configuration file. If you have installed and configured the CLI in the default location, you have a valid configuration file. If you have not installed and configured the CLI, you must either install it, or create a configuration file manually. See SDK and CLI Configuration File.
- Enough storage space in the DB system for importing data.
Related Topics
Importing Data Using Manifest File Pre-Authenticated Requests and MySQL Shell
Use MySQL Shell and Pre-Authenticated Request (PAR) of a manifest file to import data from Object Storage to a MySQL DB system.
Importing using a manifest file (
@.manifest.json
) is
a deprecated functionality. Import from a bucket or bucket prefix PAR
instead.
- One of the following network access types to the target MySQL DB
system. See Networking Setup.
- A bridged connection using FastConnect or VPN, enabling you to run MySQL Shell locally.
- SSH access to a compute instance with access to the MySQL DB system, enabling you to run MySQL Shell on the compute instance
- MySQL Shell 8.0.27, or higher.
- Access to Object Storage and an existing bucket that contains the exported files.
- Generated read-only PAR URL for the manifest file
(
@.manifest.json
). - A valid CLI configuration file. If you have installed and configured the CLI in the default location, you have a valid configuration file. If you have not installed and configured the CLI, you must either install it, or create a configuration file manually. See SDK and CLI Configuration File.
- Enough storage space in the DB system for importing data.
- A MySQL command-line client such as MySQL Shell.
You can import data from Object Storage while export is uploading that data. See Simultaneously Importing and Exporting Data Using MySQL Shell.
Related Topics
Importing Data Using Object Storage Bucket and MySQL Shell
Use MySQL Shell to import a MySQL Shell dump from Object Storage bucket or bucket prefix to a MySQL DB system.
- One of the following network access types to the target MySQL DB
system. See Networking Setup.
- A bridged connection using FastConnect or VPN, enabling you to run MySQL Shell locally.
- SSH access to a compute instance with access to the MySQL DB system, enabling you to run MySQL Shell on the compute instance
- MySQL Shell 8.0.27, or higher.
Note
Exports created by MySQL Shell 8.0.27, or higher, cannot be imported by earlier versions of MySQL Shell. The latest version of MySQL Shell is recommended. - Access to Object Storage and an existing bucket that contains the exported files.
- A valid CLI configuration file. If you have installed and configured the CLI in the default location, you have a valid configuration file. If you have not installed and configured the CLI, you must either install it, or create a configuration file manually. See SDK and CLI Configuration File.
- Enough storage space in the DB system for importing data.
You can import data from Object Storage while export is uploading that data. See Simultaneously Importing and Exporting Data Using MySQL Shell.
MySQL Shell Progress File
The MySQL Shell progress file records the progress of your imports and exports and enables you to restart operations in the event of a network or service disruption.
The progress file is defined by the
progressFile
parameter.
progressfile.json
in the your home
directory:util.loadDump("bucketPrefix", {osBucketName: "mds-bucket", threads: 4, progressFile: "~/progressfile.json"})
If you specify the
progressFile:
parameter, but leave the value blank,
progressFile: ""
, no progress file is
written, and it is not possible to resume the import in the event
of a problem.
Simultaneously Importing and Exporting Data Using MySQL Shell
You can load a dump while it is still being created in the Object Storage bucket.
The loadDump
option enables you to load a dump while it is still
being created in the Object Storage bucket. When all uploaded chunks are processed, the
command either waits for more data, the dump is marked as completed, or the defined
timeout passes.
The following example specifies a 5 minute timeout:
util.loadDump("bucketPrefix", {osBucketName: "mds-bucket", threads: n, waitDumpTimeout: 300})
Migrating from an On-Premise MySQL to MySQL Database Service
Use the Console and MySQL Shell to migrate from an on-premise MySQL to MySQL Database Service.
- Permissions to access the Console, create DB system, view and create a VCN configuration.
- (Optional) Create a VCN with a public and private subnet. See Creating a Virtual Cloud Network.
- Create a MySQL DB system. See Creating a DB System.
- Create a VPN connection. See VPN Connections.
- Create an Object Storage bucket. See To create a bucket section in Using Console.
- Populate your DB system using MySQL Shell. See Populating the DB System Using MySQL Shell.
Setting gtid_purged
Set replication coordinates after a clean import.
These methods apply only to new, clean setups. If you are attempting to recover from replication error, apply a
gtidSet
delta. See Resolving Replica and Source Synchronization Issues.
- MySQL Shell's
updateGtidSet: append
: When importing your data dump from Object Storage to the DB System, using MySQL Shell'sloadDump
utility, specifyupdateGtidSet: append
in theloadDump
parameters. This applies thegtid_executed
GTID set from the source, as recorded in the dump metadata, to the gtid_purged GTID set on the replica. For more information, see MySQL Shell Dump Loading Utility. call sys.set_gtid_purged("+gtidSet")
: A stored procedure in the DB System's MySQL instance, which enables you to apply thegtid_executed
GTID set from the source, as recorded in the dump metadata, to thegtid_purged
GTID set on the replica DB System, after importing your data. Retrieve the gtidSet value from thegtidExecuted
field in the@.json
dump file. For more information on this command, see Populating the DB System Using MySQL Shell.