MySQL dump from cloudSQL then convert to load infile for local import

loop_load_from_infile.sh

#!/usr/bin/bash
set -e

# Loops through a hardcoded list of dbs
# The hard coded parts are the db names since they need to exist, and the create table command is taken manually from the mysqldump

transform_files () {
        if ! [ -f ${file_src} ]
        then
                echo "file ${file_src} not found"
                return 1;
        fi
        echo "grepping and sedding ${file_src} into file ${file_dest}..."
        time grep "INSERT INTO" ${file_src} | sed -e 's/^[^(]*[(]//' -e 's/),(/\n/g' -e 's/);$//' > ${file_dest}
}

import_into_mysql () {
        if ! [ -f ${file_dest} ]
        then
                echo "file ${file_dest} not found"
                return 2;
        fi

        echo "about to import a file, depending on the file size this could take a while..."
        du -h ${file_dest}
        echo "importing the file ${file_dest}..."
        time mysql --user=${db_user} --password=${db_pass} <<-EOF

        -- speed up import
        -- refs: https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-bulk-data-loading.html
        -- speed up import
        -- refs: https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-bulk-data-loading.html
        -- refs: https://dba.stackexchange.com/questions/98814/mysql-dump-import-incredibly-slow-on-my-developers-machine
        SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
        SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
        SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;
        SET SESSION SQL_LOG_BIN=0;
        SET SESSION BULK_INSERT_BUFFER_SIZE=268435456;

        USE ${db};

        DROP TABLE IF EXISTS ${table};
        /*!40101 SET @saved_cs_client     = @@character_set_client */;
        /*!50503 SET character_set_client = utf8mb4 */;
        CREATE TABLE ${table} (
          id char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
          thing1 varchar(1500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
          thing2 varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
          time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
        /*!40101 SET character_set_client = @saved_cs_client */;
        
        LOAD DATA
            INFILE '${file_dest}'
            INTO TABLE ${table}
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY "'"
            LINES TERMINATED BY '\n';

        -- speed up import
        -- refs: https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-bulk-data-loading.html
        -- speed up import
        -- refs: https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-bulk-data-loading.html
        COMMIT;
        SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
        SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
        SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
        EOF
}

# MAIN
db_pass=Super_Secure_Password1
db_user=root

echo "This file drops tables if they exist!"
echo "hit ctr+c to cancel if you want... 10sec"
sleep 10s


for db in db1 db2 db3 db4 db5 db6
do
        table=table_in_${db}
        file_src=/mnt/mysqldumps/${db}.${table}.sql
        file_dest=/var/lib/mysql-files/${db}.${table}.csv

        transform_files
        import_into_mysql

done

mysqldump_per_table_crom_cloudsql.sh

#!/usr/bin/env bash
[ $# -lt 4 ] && echo "Usage: $(basename $0) <DB_HOST> <DB_USER> <DB_pass> <File_destination_Path>" && exit 1

# I looped through dumping per table using the following shell script
# this script hard codes the database names (ex: db1 db2 db3...) because the was faster at the time

cloudSQL_host=$1
cloudSQL_user=$2
cloudSQL_password=$3
file_path=$4
echo "lets make a dump file per table..."
for db_name in db1 db2 db3 db4 db5 db6
do
  echo "working on ${db_name}..."
  for tbl_name in $(mysql --skip-column-names --batch --no-auto-rehash --host=${cloudSQL_host} --user=${cloudSQL_user} --password=${cloudSQL_password} --database=${db_name} --execute='show tables')
  do
    file_dest=${file_path}/${db_name}.${tbl_name}.sql
    echo "dumping to ${file_dest}..."
    time mysqldump --host=${cloudSQL_host} --user=${cloudSQL_user} --password=${cloudSQL_password} --hex-blob --single-transaction --set-gtid-purged=OFF --default-character-set=utf8mb4 --compression-algorithms=zstd --opt ${db_name} ${tbl_name} > ${file_dest}
  done
done