mysqldump to csv then upload to azure storage

.settings

[db]
host = "localhost"
name = "database-name"
user = "user-name"
pass = null
port = 3306

[azure]
key = '<container-shared-key>'
account = '<azure-account-name>'
container = '<container-name>'

mysql-to-azure.php

<?php
    /**
     * For large size table, this script will be limited by PHP MEMORY LIMIT 
     * since it not using mysqldump instead using query to memory
     */
    class sqlExtractor
    {
        private $DB_NAME;
        private $DB_HOST;
        private $DB_USER;
        private $DB_PASS;
        private $DB_PORT;
        private $AZURE_KEY;
        private $AZURE_ACCOUNT;
        private $AZURE_CONTAINER;

        /**
         * Extract credentials details from .settings file
         */
        public function __construct()
        {
            $SETTINGS = parse_ini_file('./.settings', true);
            
            $this->DB_NAME         = $SETTINGS['db']['name'];
            $this->DB_HOST         = $SETTINGS['db']['host'];
            $this->DB_USER         = $SETTINGS['db']['user'];
            $this->DB_PASS         = $SETTINGS['db']['pass'];
            $this->DB_PORT         = $SETTINGS['db']['port'];
            $this->AZURE_KEY       = $SETTINGS['azure']['key'];
            $this->AZURE_ACCOUNT   = $SETTINGS['azure']['account'];
            $this->AZURE_CONTAINER = $SETTINGS['azure']['container'];
        }
        
        /**
         * Execute query
         */
        private function runQuery(string $query)
        {
            // establish database connection using PDO
            try {
                $pdo = new \PDO(
                    "mysql:hostname={$this->DB_HOST};dbname={$this->DB_NAME};port={$this->DB_PORT}",
                    $this->DB_USER,
                    $this->DB_PASS,
                    [
                        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                        \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4'"
                    ]
                );
            } catch (PDOException $e) {
                die("Database connection failed: " . $e->getMessage());
            }
        
            return $pdo->query($query);
        }

        /**
         * Get list table column headers
         */
        private function getTableHeaders(string $tableName): array
        {
            $columns = [];
            $stmt = $this->runQuery("SHOW COLUMNS FROM $tableName;");
            
            while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $columns[] = $row['Field'];                
            }

            return $columns;
        }

        /**
         * Get list of tables from the database
         */
        private function getListOfTablesInDB()
        {
            $tables = [];
            $stmt = $this->runQuery("SHOW TABLES");

            while ($row = $stmt->fetch(PDO::FETCH_COLUMN)) {
                $tables[] = $row;
            }

            return $tables;
        }
    
        /**
         * Convert executed query data into csv file
         */
        private function generateCSV(string $tableName)
        {
            // open a file handle for writing to the CSV file
            $file_handle = fopen($tableName . ".csv", 'w');
            
            // write the header row to the CSV file
            $headers = $this->getTableHeaders($tableName);
            fputcsv($file_handle, $headers);

            $stmt = $this->runQuery("SELECT * FROM $tableName");

            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                fputcsv($file_handle, $row);
            }
            
            // close the file handle
            fclose($file_handle);
        }

        /**
         * Delete generated csv file
         */
        private function removeCSV(string $tableName)
        {
            unlink($tableName . ".csv");
        }

        /**
         * Upload generated csv file to azure with datetime folder
         */
        private function uploadCsvBlobToAzure(string $tableName)
        {
            $contentType    = 'text/csv;';
            $apiVersion     = '2021-08-06';
            $currentDate    = gmdate("D, d M Y H:i:s T", time());
            $todayFolder    = gmdate("Ymd", time());
            $absFilePath    = realpath($tableName . ".csv");
            $fileContent    = fopen($absFilePath, "r");
            $fileLength     = filesize($absFilePath);
            $blobName       = $tableName . ".csv";
            $destinationURL = "https://" . $this->AZURE_ACCOUNT . ".blob.core.windows.net/" . $this->AZURE_CONTAINER . "/" . $todayFolder . "/" . $blobName;

            if(! $absFilePath){
                die("Cannot find file of " . $tableName . " in current directory");
            }

            $xmsHeaders = [
                'x-ms-blob-cache-control:max-age=3600',
                'x-ms-blob-type:BlockBlob',
                'x-ms-date:' . $currentDate,
                'x-ms-version:'. $apiVersion,
            ];

            $canonicalizedHeaders = implode("\n", $xmsHeaders);
            $canonicalizedResource = "/" . $this->AZURE_ACCOUNT . "/" . $this->AZURE_CONTAINER . "/" . $todayFolder . "/" . $blobName;

            $arraysign = array();
            $arraysign[] = 'PUT';                   /*HTTP Verb*/  
            $arraysign[] = '';                      /*Content-Encoding*/  
            $arraysign[] = '';                      /*Content-Language*/  
            $arraysign[] = $fileLength;             /*Content-Length (include value when zero)*/  
            $arraysign[] = '';                      /*Content-MD5*/  
            $arraysign[] = 'text/csv;';              /*Content-Type*/  
            $arraysign[] = '';                      /*Date*/  
            $arraysign[] = '';                      /*If-Modified-Since */  
            $arraysign[] = '';                      /*If-Match*/  
            $arraysign[] = '';                      /*If-None-Match*/  
            $arraysign[] = '';                      /*If-Unmodified-Since*/  
            $arraysign[] = '';                      /*Range*/  
            $arraysign[] = $canonicalizedHeaders;   /*CanonicalizedHeaders*/
            $arraysign[] = $canonicalizedResource;  /*CanonicalizedResource*/
            $str2sign = implode("\n", $arraysign);

            // generate Azure auth signature
            $sig = base64_encode(hash_hmac('sha256', urldecode(mb_convert_encoding($str2sign, "UTF-8")), base64_decode($this->AZURE_KEY), true));
            $authHeader = "SharedKey ". $this->AZURE_ACCOUNT . ":" . $sig;

            $headers = array_merge($xmsHeaders, [
                        'Authorization:' . $authHeader,
                        'Content-Type:'. $contentType,
                        'Content-Length:' . $fileLength,
                    ]);

            # Execute curl commands to create container
            $ch = curl_init($destinationURL);
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
            curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
            curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "PUT");
            curl_setopt($ch, CURLOPT_INFILE, $fileContent); 
            curl_setopt($ch, CURLOPT_INFILESIZE, $fileLength); 
            curl_setopt($ch, CURLOPT_UPLOAD, true); 
            
            $result = curl_exec($ch);

            if(! $result)
            {
                $log = fopen('curl_log.txt', 'w');
                fwrite($log, curl_error($ch));
            }
    
            curl_close($ch);
        }


        /**
         * Link and execute each function per table
         */
        public function run()
        {
            $tableList = $this->getListOfTablesInDB();

            foreach ($tableList as $table) {
                $this->generateCSV($table);
                $this->uploadCsvBlobToAzure($table);
                $this->removeCSV($table);
            }
        }
    }

    $clas = new sqlExtractor();
    $clas->run();