Aliyun RDS Export Database structure finishing Tools __ Database

This article uses the shell to implement a small tool, can organize Aliyun RDS Export database structure ZIP file, organized into the SQL file that can be used directly.

Aliyun RDS Exported database structure SQL needs to be sorted out

1. After decompression, the SQL filename is missing the database name identification

Aliyun RDS Exported database structure SQL, compressed into a zip file with the extracted SQL file name without the database name.

For example
Database name is Test_user

Exported zip file name: Test_user_1616950_all_sql.zip
Extracted SQL file name: 1616950_all.sql

If you have multiple database export files at the same time, this is more difficult to distinguish, so you need to rename the extracted files, with the database name.

2. With no need to use the set of data

Because only the structure is exported, no data is required, so we do not need to obtain the auto_increment settings of the database table before, and we need to remove it for similar settings.

3. After decompression of the SQL file for UTF8 BOM

If we execute this SQL Create data table is used for example, PHP code read after execution, SQL file with UTF8 BOM will cause error, so the SQL file UTF8 BOM header need to be removed.

Finishing Steps

1. Extract Files

2. After decompression file renamed by database name

3. Delete auto_increment and Row_format set parameters

4. UTF8 BOM Header to remove file


The code is as follows

#!/bin/bash

# Extract Aliyun exported database zip file, execute rename, turn UTF8 not BOM encoding, delete useless statements, etc. processing
# 
Date:     2017-12-18
# Author:   fdipzone
# Ver:      1.0
#

Save the renamed SQL file after decompression
sqlfile= ()

# traverse all zip files in the current directory for
file in $ (find.-name "*.zip"-type f); Do  

    # defines the renamed file name after decompression
    tmp_sqlfile=$ (echo $file |sed ' s/_[0-9]*_all_sql.zip/.sql/')
    Tmp_sqlfile=${tmp_ Sqlfile:2}

    # Save to Array
    sqlfile+= ($tmp _sqlfile)

    # Perform decompression
    unzip_file=$ (unzip $file |grep ' inflating ' | Awk-f ' {print $} ')

    # rename
    $ (mv $unzip _file $tmp _sqlfile) done

# traverse all SQL files, delete useless statements, and convert to UTF8 not BOM Encoding for 
F in ${sqlfile[*]}, do

    # delete row_format=xxx
    $ (sed-i ' s/row_format=[a-z]*//g ' $f)

    # Delete Auto_ Increment=xxx
    $ (sed-i ' s/auto_increment=[0-9]*//g ' $f)

    # Delete UTF8 BOM
    $ (sed-i ' s/^\xef\xbb\xbf//g ' $f) C32/>done

Exit 0