In Part 1 of this blog series I shared how to setup a Pure Storage FlashBlade S3 compliant Object Storage account, user and bucket. I also demonstrated how to download the MySQL airportdb database, and upload objects into the Object Store S3 bucket.
In this blog post I will show how we can use the MySQL Shell to install the aiportdb database dump into a MySQL 8 database using the S3 Object Storage bucket previously created.
List S3 Bucket
Use the s3cmd ls command or aws s3api list-buckets command to confirm the bucket access and name, for example.
$ ENDPOINT=http://192.168.4.180
$ PROFILE=fbstaines03
$ aws s3api list-buckets --query "Buckets[].Name" --endpoint-url ${ENDPOINT} --profile ${PROFILE}
--------------------
| ListBuckets |
+------------------+
| airport-bucket |
+------------------+
$ s3cmd ls
2023-07-05 13:17 s3://airport-bucket
MySQL Shell
The MySQL Shell supports loading database dumps stored in Oracle Cloud Infrastructure (OCI) Object Storage, Azure Blob Storage, AWS S3 Object Storage and ther S3 compatible Object Storage solutions, such as a Pure Storage FlashBlade Object Storage.
Options for Load Control
The MySQL Shell provides the following parameters to configure the load control:
| Argument | Description |
|---|---|
| dryRun: [true | false] | Display information about what actions would be performed given the specified options and dump files, including any errors that would be returned based on the dump contents, but do not proceed with the import. The default is false |
| waitDumpTimeout: number | Setting this option to a value greater than 0 activates concurrent loading of the dump while it is still being produced. |
| schema: “string” | The target schema into which a dump produced by MySQL Shell’s dump utilities must be loaded. |
| threads: number | The number of parallel threads to use to upload chunks of data to the target MySQL instance. Each thread has its own connection to the MySQL instance. The default is 4. |
| backgroudThreads: number | The number of threads in the pool of background threads used to fetch the contents of files. The default is the value of the threads option for a dump loaded from the local server, or four times the value of the thread soption for a dump loaded from a non-local server. |
| progressFile: “string” | Specifies the path to a local progress state file for tracking load progress. When loading a dump from local storage the progressFile option may be omitted. In this case, a progress state file named load-progress-server-uuid.json is automatically created in the dump directory. |
| showProgress: [true | false] | Display (true) or hide (false) progress information for the import. The default is true for the MySQL Shell is in interactive mode, and false otherwise. |
| resetProgress: [true | false] | Setting this option to true resets the progress state and starts the import again from the beginning. The default is false. |
| skipBinlog: [true | false] | Skips binary logging on the target MySQL instance for the sessions used by the utility during the course of the import, by issuing a SET sql_log_bin=0 statement. The default is false, so binary logging is active by default. |
| ignoreVersion: [true | false] | Import the dump even if the major version number of the MySQL instance from which the data was dumped is different to the major version number of the MySQL instance to which the data will be uploaded. The default is false, meaning that an error is issued and the import does not proceed if the major version number is different. |
| ignoreExistingObjects: [true | false] | Import the dump even if it contains objects that already exist in the target schema in the MySQL instance. The default is false, meaning that an error is issued and the import stops when a duplicate object is found, unless the import is being resumed from a previous attempt using a progress state file, in which case the check is skipped. |
| characterSet: “string” | The character set to be used for the import to the target MySQL instance, for example in the CHARACTER SET option of the LOAD DATA statement. |
| maxBytesPerTransaction: number | The maximum number of bytes that can be loaded from a data file in a single LOAD DATA statement. |
| sessionInitSql: list of strings | A list of SQL statements to run at the start of each client session used for loading data into the target MySQL instance. You can use this option to change session variables. |
Options for Load Content
The MySQL Shell provides the following parameters to manage the loading of data into the database:
| Argument | Description |
|---|---|
| LoadIndexes: [true | false] | Create (true) or do not create (false) secondary indexes for tables. The default is true. When this option is set to false, secondary indexes are not created during the import, and you must create them afterwards. |
| deferTableIndexes: [off | fulltext | all] | Defer the creation of secondary indexes until after the table data is loaded. This can reduce loading times. off means all indexes are created during the table load. The default setting fulltext defers full-text indexes only. all defers all secondary indexes and only creates primary indexes during the table load |
| analyzeTables: [off | on | histogram] | Execute ANALYZE TABLE for tables when they have been loaded. on analyzes all tables, and histogram analyzes only tables that have histogram information stored in the dump. The default is off. |
| showMetadata: [true | false] | Prints the gtid_executed GTID set and the binary log file name and position from the source instance, taken from the dump metadata included with dumps produced by MySQL Shell’s instance dump utility, schema dump utility, or table dump utility. The metadata is printed in YAML format. |
| UpdateGtidSet: [off | append | replace] | Apply the gtid_executed GTID set from the source MySQL instance, as recorded in the dump metadata, to the gtid_purged GTID set on the target MySQL instance. |
| CreateInvisiblePKs: [true | false] | Add primary keys in invisible columns for each table in the dump that does not contain a primary key. |
S3 Compatible Services
The MySQL Shell provides the following parameters for s3 compatible services:
| Argument | Description |
|---|---|
| s3BucketName: “string“ | The name of the S3 bucket where the dump files are located. By default, the default profile in the Amazon Web Services (AWS) CLI config and credentials files located at ~/.aws/ are used to establish a connection to the S3 bucket. |
| s3CredentialsFile: “string“ | A credentials file that contains the user’s credentials to use for the connection, instead of the one in the default location, ~/.aws/credentials. Typically, the credentials file contains the aws_access_key_id and aws_secret_access_key to use for the connection. |
| s3ConfigFile: “string” | An AWS CLI configuration file that contains the profile to use for the connection, instead of the one in the default location ~/.aws/config. Typically, the config file contains the region and output type to use for the connection. |
| s3Profile: “string“ | The profile name of the s3 CLI profile to use for the connection, instead of the default profile in the AWS CLI configuration file used for the connection. |
| s3Region: “string” | The name of the region to use for the connection. |
| s3EndpointOverride: “string“ | The URL of the endpoint to use instead of the default. |
For a full details of Load Control, Load Content and S3 Compatible Services arguments consult the MySQL Dump Loading Utility documentation.
Loading Data
To load data from the FlashBlade S3 compatible Object Storage bucket airport-bucket with util.loadDump we can provide the same details we use with the aws s3api command, for example:
util.loadDump("airport-db", {s3BucketName: "airport-bucket", s3EndpointOverride: "http://192.168.4.180", s3Profile: "fbstaines03", threads: 16, deferTableIndexes: "all", ignoreVersion: true})
In the example above we can see the first argument provided, airport_dump, this is the folder’s name that was created within the airport-bucket s3 bucket.
We can also see that the s3BucketName, s3EndPointOrverride and s3Profile arguments are set to the same values as the aws s3api command.
The s3CredentialsFile, s3ConfigFile and s3Region arguments have been left unset, using default values.
The number of parallel threads has set to 16, overriding the default value of 4.
The deferTableIndexes argument is set to all to defer all secondary indexes, only creating primary indexes during the data load.
The ignoreVersion argument is set to true to allow the import to proceed with a warning generated on an error.
$ mysqlsh --mysqlx -u root -h localhost -P 33060
MySQL Shell 8.0.33
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating an X protocol session to 'root@localhost:33060'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 14 (X protocol)
Server version: 8.0.33 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:33060+ ssl JS >
MySQL localhost:33060+ ssl JS > util.loadDump("airport-db", {s3BucketName: "airport-bucket", s3EndpointOverride: "http://192.168.4.180", s3Profile: "fbstaines03", threads: 16, deferTableIndexes: "all", ignoreVersion: true})
Loading DDL and Data from AWS S3 bucket=airport-bucket, prefix='airport-db' using 16 threads.
Opening dump...
NOTE: Dump format has version 1.0.2 and was created by an older version of MySQL Shell. If you experience problems loading it, please recreate the dump using the current version of MySQL Shell and try again.
Target is MySQL 8.0.33. Dump was produced from MySQL 8.0.26-cloud
Fetching dump data from remote location...
Listing files - done
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
9 thds loading - 100% (2.03 GB / 2.03 GB), 14.48 MB/s, 14 / 14 tables done
Recreating indexes - done
Executing common postamble SQL
39 chunks (59.50M rows, 2.03 GB) for 14 tables in 1 schemas were loaded in 9 min 49 sec (avg throughput 16.53 MB/s)
0 warnings were reported during the load.
MySQL localhost:33060+ ssl JS >
MySQL Airportdb
We can now ready to use the airportdb, logon the MySQL 8 database using either mysql or myssqlsh, for this example I will use the MySQL Shell.
Start by changing to SQL, using \sql.
Now use show schemas to see the database schemas and the new airportdb schema.
Finally change to the airportdb using use airportdb and then use show tables to confirm the 14 tables have been created.
$ mysqlsh --mysqlx -u root -h localhost -P 33060 MySQL Shell 8.0.33 Copyright (c) 2016, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. Creating an X protocol session to 'root@localhost:33060' Fetching schema names for auto-completion... Press ^C to stop. Your MySQL connection id is 32 (X protocol) Server version: 8.0.33 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. MySQL localhost:33060+ ssl JS > \sql Switching to SQL mode... Commands end with ; Fetching global names for auto-completion... Press ^C to stop. MySQL localhost:33060+ ssl SQL > show schemas; +--------------------+ | Database | +--------------------+ | airportdb | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.0017 sec) MySQL localhost:33060+ ssl SQL > use airportdb; Default schema set to `airportdb`. Fetching global names, object names from `airportdb` for auto-completion... Press ^C to stop. MySQL localhost:33060+ ssl airportdb SQL > MySQL localhost:33060+ ssl airportdb SQL > show tables; +---------------------+ | Tables_in_airportdb | +---------------------+ | airline | | airplane | | airplane_type | | airport | | airport_geo | | airport_reachable | | booking | | employee | | flight | | flight_log | | flightschedule | | passenger | | passengerdetails | | weatherdata | +---------------------+ 14 rows in set (0.0022 sec) MySQL localhost:33060+ ssl airportdb SQL >
Summary
In this post I have shared how to use the MySQL Shell loadDump function to access data stored a FlashBlade s3 Object Storage Bucket, and load the data into a MySQL 8 database.