mysqldump to csv then upload to azure storage

mysql-to-azure.sh

#!/bin/bash

### Full Extraction
# This will dump all tables to .csv and upload to azure blob storage in a datetime folder

# Database Credentials
db_name='<database name>'
db_user='<db username>'
db_pass='<db password>'
db_host='localhost'
db_port=3306

# Azure Container Credentials
account='<account name>'
container='<container name>'
shared_key='<container shared key>'
content_type='text/csv;'
api_version='2021-08-06'
current_date=$(TZ=GMT date "+%a, %d %h %Y %H:%M:%S %Z")

# Use as folder name such as 20210101_1000
date=$(date +%Y%m%d_%H%M%S)

# Where to store generated temporary csv
dir="$(pwd)/$date"

# Create a datetime folder if not exist
if [ ! -d "$dir" ]; then
    mkdir -p "$dir"
fi

# Extract list of tables names and loop
for T in `mysql -u ${db_user} -h ${db_host} -p${db_pass} -P ${db_port} -D ${db_name} -N -B -e 'show tables;'`
do
    # For each table, T dump and convert to .csv
    mysql -u ${db_user} -h ${db_host} -p${db_pass} -P ${db_port} -D ${db_name} -e "SELECT * FROM ${T}" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > $dir/$T.csv
done;

canonicalized_headers="x-ms-blob-cache-control:max-age=3600\nx-ms-blob-type:BlockBlob\nx-ms-date:${current_date}\nx-ms-version:${api_version}"
decoded_hex_key="$(echo -n $shared_key | base64 -d -w0 | xxd -p -c256)"

# Upload each csv to Azure Blob Storage using shared key
for D in `ls $dir`
do
    file_path="$dir/$D"
    content_length=$(wc -c < $file_path)
    destination_url="https://$account.blob.core.windows.net/$container/$date/$D"
    canonicalized_resource="/$account/$container/$date/$D"

    # For zero size content, need to replace content length to empty quote as per Azure doc
    if [ $content_length -eq 0 ]
    then
        string_to_sign="PUT\n\n\n\n\n$content_type\n\n\n\n\n\n\n$canonicalized_headers\n$canonicalized_resource"
    else
        string_to_sign="PUT\n\n\n$content_length\n\n$content_type\n\n\n\n\n\n\n$canonicalized_headers\n$canonicalized_resource"
    fi

    # Generate Azure auth signature
    signature=$(printf "$string_to_sign" | openssl dgst -sha256 -mac HMAC -macopt "hexkey:$decoded_hex_key" -binary |  base64 -w0)

    # Upload the file using cURL
    curl -X PUT -T $file_path \
        -H "Authorization: SharedKey $account:$signature" \
        -H "Content-Type: $content_type" \
        -H "Content-Length: $content_length" \
        -H 'x-ms-blob-cache-control: max-age=3600' \
        -H 'x-ms-blob-type: BlockBlob' \
        -H "x-ms-date: $current_date" \
        -H "x-ms-version: $api_version" \
        $destination_url
done;

# remove created folder
rm -rf "$dir/"

echo "Finish as $(TZ=GMT date '+%a, %d %h %Y %H:%M:%S %Z')"