Importing and Exporting Databases
This section describes how to export data from MySQL Servers and import to MySQL DB Systems.
- MySQL Shell: (Recommended) MySQL Shell's dump/load utilities are purpose-built for use with MySQL Database Service. Useful for all types of exports and imports. Supports export to, and import from, Object Storage.
The minimum supported source version of MySQL is 5.7.9.
MySQL Shell
This section describes importing and exporting data using MySQL Shell.
dumpInstance()
: MySQL instance export utility which 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 which exports selected schemas to an Object Storage bucket or to local files. For more information, see MySQL Shell Instance and Schema Dump UtilitiesloadDump()
: an import utility which imports schemas to a DB System. For more information, see MySQL Shell Dump Loading UtilityTo import a schema to a MySQL DB System, MySQL Shell must be installed on a Compute instance configured to access the target MySQL DB System. For more information, see Connecting to a DB System.
These utilities were introduced in MySQL Shell 8.0.21.
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. Chunking can be disabled, but this is not recommended for large databases. During import, the chunks can be imported 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. To make it easier to load existing databases into the Service, the dump commands in MySQL Shell 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. 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 make changes to your database schema before it can be loaded into the MySQL Database Service. However, the compatibility option 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, you must manually modify the schema before loading it. For more information, 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
. It is not possible to create users granting these privileges. This option strips these privileges from dumpedGRANT
statements.-
strip_role_admin
:ROLE_ADMIN
privilege is restricted in the MySQL Database Service. It is not possible to create users granting this privilege. This option strips this privilege from dumpedGRANT
statements. 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.
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. For more information, see MySQL Shell User Guide - Upgrade Checker Utility.
Exporting Data to Object Storage with MySQL Shell
This task describes how to export data from a supported MySQL Server source to an Object Storage bucket using the MySQL Shell dumpInstance
utility.
- MySQL Shell 8.0.21, or higher.
- MySQL Server 5.7.9, or higher.
- Access to Object Storage and an existing bucket.
- The name of your Object Storage namespace. See Understanding Object Storage Namespaces for more information.
- A valid configuration file. See SDK and CLI Configuration File. If you have installed and configured the Command Line Interface 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.
- 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. For more information on compatibility issues, and how to correct them, 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 to MySQL DB System from Object Storage Using MySQL Shell
This task describes how to import data from Object Storage to a MySQL DB System.
- A Compute instance with access to the target MySQL DB System. This example assumes you are using a Compute running Oracle Linux.
- MySQL Shell 8.0.21, or higher, installed on the Compute instance.
- Access to Object Storage and an existing bucket which contains the exported files.
- The name of your Object Storage namespace. See Understanding Object Storage Namespaces for more information.
- A valid Command Line Interface (CLI) configuration file. See SDK and 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.
- Ensure your DB System has enough storage space for the import.
It is also possible to import data from Object Storage while the export is uploading that data. For more information, see Simultaneous Import and Export with MySQL Shell.
MySQL Shell Progress File
Describes the use of a local progress file, instead of using the default progress file stored in Object Storage bucket.
If your database export is extremely large, with many thousands of chunks, it is recommended to set the progressFile: "/path/to/filename"
parameter. This creates the progress file on the local file system of the compute, instead of in the same Object Storage bucket as the data, and can improve the performance of the import.
progressfile.json
in the opc
user's home directory on the Compute instance:util.loadDump("bucketPrefix", {osBucketName: "mds-bucket", osNamespace: "NamespaceID",
threads: 4, progressFile: "/home/opc/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.
Simultaneous Import and Export with MySQL Shell
Enabling simultaneous export and import of data.
This option enables loadDump
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:
>MySQL>JS> util.loadDump("bucketPrefix", {osBucketName: "mds-bucket",
osNamespace: "NamespaceID", threads: n, waitDumpTimeout: 300})