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.
Environment
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 https://downloads.mysql.com/docs/airport-db.tar.gz or https://downloads.mysql.com/docs/airport-db.zip
For example to pull an untar the file use wget and tar -zxvf
wget https://downloads.mysql.com/docs/airport-db.tar.gz
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
From the Storage -> Object Store select Account name, and click on the + to create a 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.
After the Access Policies are added, select Create a new key and click Create.
Copy the Access Key ID and download the associated Secret Access Key.
The JSON file will include both the access key and secret.
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 http://192.168.4.180 --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:
[mysqld]
local-infile=1
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/@.post.sql' -> 's3://airport-bucket/airport-db/@.post.sql' [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.
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/@.post.sql 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/@.post.sql | 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 |
+--------------------------------------------------------+-----------+
Summary
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.