Site icon Ron Ekins' – Oracle Technology, DevOps and Kubernetes Blog

How to utilize S3 Object Storage for common MySQL 8 database DBA activities – Part 1 (Setup)

In this blog post series I and going to share how a MySQL 8 DBA can utilise S3 Object Storage for common database admin activities.

If you don’t already have an up and running MySQL 8 database and want to follow this blog, you could check out my post How to Install MySQL 8 on Oracle Linux 8,

In this post I walkthrough how to install and configure MySQL 8 Community Server on Oracle Linux 8.


For this post I will be using S3 Object Storage provided by an on-premises Pure Storage FlashBlade which I have available to me in a lab.

To demonstrate the use of S3 Object Storage we will need a dataset, for this post I be using the Oracle provided MySQL airportdb database.

MySQL Airportdb

The airportdb database is a large data set based upon the Flughafen DB developed for use on Oracle Cloud Infrastructure (OCI) with MySQL HeatWave, but can also be used as a test dataset for on-premises MySQL databases.

The airportdb data files were produced using the MySQL Shell Schema Dump Utility.

Data files produced by the MySQL Shell Schema Dump Utility include DDL files for creating the database schema structure, compressed .tsv files that contain the data, and .json metadata files.

The airportdb database is approximately 2GB in size and consists of 14 tables containing a total of 55,983,205 records, and is available for download as zip or tar file or

For example to pull an untar the file use wget and tar -zxvf

tar -zxvf airport-db.tar.gz

Create Object Store Account

Now we have our data, we need somewhere to store it, so let’s create a FlashBlade s3 Object Store Account, User and S3 Bucket.

Logon to the Pure Storage FlashBlade and navigate to Storage -> Object Store

In the Account panel Click on the + and enter Name

Create Object Store Account

From the Storage -> Object Store select Account name, and click on the + to create a User.

Step 1: Create User

Select the required permissions.

For this post I will grant the full-access policy to get all current and future permissions, and click Add.

Step 2: Add Policies to user

After the Access Policies are added, select Create a new key and click Create.

Step 3: Add Access Key to user

Copy the Access Key ID and download the associated Secret Access Key.

The JSON file will include both the access key and secret.

Access Key
Create Bucket

Create Object Store S3 Bucket

From the Object Store Account, navigate to the Bucket area and click Create Bucket.

We have now completed the FlashBlade configuration, so return to the Linux server.

List Object Store Buckets

Install and configure AWS cli, if not already available.

Create a profile for the FlashBlade credentials in the local ~/.aws/credentials file.

The AWS cli provides a rich set of options which you read-up on here,

We can use the aws s3api list-buckets command, to confirm we have read access to the bucket, for example.

$ aws s3api list-buckets --query "Buckets[].Name" --endpoint-url --profile fbstaines03
|    ListBuckets   |
|  airport-bucket  |

Alternatively, we can perform the same with the s3cmd cli utility, thus.

$ s3cmd ls
2023-07-05 13:17  s3://airport-bucket

Before we attempt to load any objects, let’s we confirm we are using an on-premise Object Store, by checking the location of the bucket with s3cmd info.

$ s3cmd info s3://airport-bucket/
s3://airport-bucket/ (bucket):
   Location:  on-prem
   Payer:     none
   Expiration Rule: none
   Policy:    none
   CORS:      none
   ACL:       DefaultDisplayName: FULL_CONTROL

Upload Objects

To load the multiple files created from un-tarred airportdb from a local directory I will use the s3cmd utility.

You may need to update your /etc/my.cnf file to include local-infile=1 to allow local filesystem access.

For example:


The s3cmd put –recursive command provides a simple method of uploading a directory containing multiple files to an S3 object storage bucket, we can use this to upload the airportdb database to our S3 bucket.

$ s3cmd put --recursive airport-db s3://airport-bucket/
upload: 'airport-db/@.done.json' -> 's3://airport-bucket/airport-db/@.done.json' [1 of 114]
2586 of 2586 100% in 0s 743.86 KB/s done
upload: 'airport-db/@.json' -> 's3://airport-bucket/airport-db/@.json' [2 of 114]
859 of 859 100% in 0s 284.84 KB/s done
upload: 'airport-db/@.manifest.json' -> 's3://airport-bucket/airport-db/@.manifest.json' [3 of 114]
46349 of 46349 100% in 0s 12.30 MB/s done
upload: 'airport-db/' -> 's3://airport-bucket/airport-db/' [4 of 114]
240 of 240 100% in 0s 77.38 KB/s done
upload: 'airport-db/@.sql' -> 's3://airport-bucket/airport-db/@.sql' [5 of 114]
240 of 240 100% in 0s 80.82 KB/s done
upload: 'airport-db/README.txt' -> 's3://airport-bucket/airport-db/README.txt' [6 of 114]
4884 of 4884 100% in 0s 1467.10 KB/s done
upload: 'airport-db/airportdb.json' -> 's3://airport-bucket/airport-db/airportdb.json' [7 of 114]
1199 of 1199 100% in 0s 374.45 KB/s done
upload: 'airport-db/airportdb.sql' -> 's3://airport-bucket/airport-db/airportdb.sql' [8 of 114]
583 of 583 100% in 0s 195.51 KB/s done
upload: 'airport-db/airportdb@airline.json' -> 's3://airport-bucket/airport-db/airportdb@airline.json' [9 of 114]
627 of 627 100% in 0s 224.12 KB/s done
upload: 'airport-db/airportdb@airline.sql' -> 's3://airport-bucket/airport-db/airportdb@airline.sql' [10 of 114]
1259 of 1259 100% in 0s 423.67 KB/s done 
upload: 'airport-db/airportdb@weatherdata@0.tsv.zst.idx' -> 's3://airport-bucket/airport-db/airportdb@weatherdata@0.tsv.zst.idx'  [112 of 114]
 1752 of 1752   100% in    0s   527.90 KB/s  done
upload: 'airport-db/airportdb@weatherdata@@1.tsv.zst' -> 's3://airport-bucket/airport-db/airportdb@weatherdata@@1.tsv.zst'  [113 of 114]
 4733492 of 4733492   100% in    0s    98.28 MB/s  done
upload: 'airport-db/airportdb@weatherdata@@1.tsv.zst.idx' -> 's3://airport-bucket/airport-db/airportdb@weatherdata@@1.tsv.zst.idx'  [114 of 114]
 240 of 240   100% in    0s    65.47 KB/s  done

From the FlashBlade Object Store we can confirm the expected Object Count of 114.

FlashBlade Object Store

And with the s3cmd utilty.

$ s3cmd du s3://airport-bucket
   656687575     114 objects s3://airport-bucket/

List Objects

Again, using the s3cmd ls command we can list the s3 objects within the bucket

$ s3cmd ls s3://airport-bucket/airport-db/
2023-07-06 10:09         2586  s3://airport-bucket/airport-db/@.done.json
2023-07-06 10:09          859  s3://airport-bucket/airport-db/@.json
2023-07-06 10:09        46349  s3://airport-bucket/airport-db/@.manifest.json
2023-07-06 10:09          240  s3://airport-bucket/airport-db/
2023-07-06 10:09          240  s3://airport-bucket/airport-db/@.sql
2023-07-06 10:09         4884  s3://airport-bucket/airport-db/README.txt
2023-07-06 10:09         1199  s3://airport-bucket/airport-db/airportdb.json
2023-07-06 10:09          583  s3://airport-bucket/airport-db/airportdb.sql
2023-07-06 10:09     35538282  s3://airport-bucket/airport-db/airportdb@weatherdata@0.tsv.zst
2023-07-06 10:09         1752  s3://airport-bucket/airport-db/airportdb@weatherdata@0.tsv.zst.idx
2023-07-06 10:09      4733492  s3://airport-bucket/airport-db/airportdb@weatherdata@@1.tsv.zst
2023-07-06 10:09          240  s3://airport-bucket/airport-db/airportdb@weatherdata@@1.tsv.zst.idx

The s3api list-objects command can also be used to produce a table formatted output of objects, for example.

aws s3api list-objects --bucket ${BUCKET} --query 'Contents[].{Key: Key, Size: Size}' --endpoint-url ${ENDPOINT} --profile ${PROFILE}
$ aws s3api list-objects --bucket ${BUCKET} --query 'Contents[].{Key: Key, Size: Size}' --endpoint-url ${ENDPOINT} --profile ${PROFILE}
|                             ListObjects                            |
|                           Key                          |   Size    |
|  airport-db/@.done.json                                |  2586     |
|  airport-db/@.json                                     |  859      |
|  airport-db/@.manifest.json                            |  46349    |
|  airport-db/                                 |  240      |
|  airport-db/@.sql                                      |  240      |
|  airport-db/README.txt                                 |  4884     |
|  airport-db/airportdb.json                             |  1199     |
|  airport-db/airportdb.sql                              |  583      |
|  airport-db/airportdb@weatherdata@0.tsv.zst            |  35538282 |
|  airport-db/airportdb@weatherdata@0.tsv.zst.idx        |  1752     |
|  airport-db/airportdb@weatherdata@@1.tsv.zst           |  4733492  |
|  airport-db/airportdb@weatherdata@@1.tsv.zst.idx       |  240      |


In this post I have shared how to create a Pure Storage FlashBlade Object Store Account, User and S3 bucket, download the MySQL airportdb database, and how to use the s3cmd to upload the airportdb database into an S3 bucket.

In Part 2 I will show how we can use the MySQL Shell to create the airportdb within our running MySQL 8 database.

Exit mobile version