How to automate downloading, unzipping & loading of IP2Proxy CSV data into Linux MySQL (IPv6)

Here is a guide for users to fully automate the updating of their IP2Proxy PX11 IPv6 database. We will show how to download the IP2Proxy PX11 IPv6 CSV data from the web server and then unzip it before loading the data into a MySQL server on a Linux platform. We will explain what each part of the code does as we go through the whole process.

Configure login details

Firstly, we need to set up some login details for the IP2Proxy web server as well as the MySQL server information. Under the Configuration section of the script, you will need to key in all the relevant login information in the fields below:

# Configuration
TOKEN="DOWNLOAD_TOKEN"
CODE="PX11IPV6"
DBHOST="YOUR_DATABASE_HOST"
DBUSER="YOUR_DATABASE_USERNAME"
DBPASS="YOUR_DATABASE_PASSWORD"
DBNAME="YOUR_DATABASE_NAME"

For most cases, you shouldn’t have to modify anything else in the script but we will still explain what is going on in case you want to modify the code.

Checking for pre-requisites

Some Linux packages like wget, unzip, mysql, wc, find and grep are needed for this script to run. If any of them are not installed, an error message will be shown and the script will terminate itself.

for a in wget unzip mysql wc find grep; do
    if [ -z "$(which $a)" ]; then
        error "Command \"$a\" not found."
        exit 0
    fi
done

 

Creating the temporary folder for storing the downloaded zipped data file

If the temporary folder does not exist, it will be created. An error message will be shown if the folder cannot be created and the script will terminate itself.

if [ ! -d /tmp/ip2proxy ]; then
    echo -n "Create temporary directory.......................... "
    mkdir /tmp/ip2proxy
    if [ ! -d /tmp/ip2proxy ]; then
        error "Failed to create /tmp/ip2proxy"
        exit 0
    fi
    success "[OK]"
fi

 

Downloading the zipped data file from the web server

If download is not successful then an error message will be shown and the script will terminate itself.

wget -O database.zip -q https://www.ip2location.com/download?token=$TOKEN\&file=$CODE 2>&1
if [ ! -f database.zip ]; then
    error "Download failed."
    exit 0
fi
if [ ! -z "$(grep 'NO PERMISSION' database.zip)" ]; then
     error "Permission denied."
        exit 0
fi
if [ ! -z "$(grep '5 times' database.zip)" ]; then
         error "Download quota exceed."
        exit 0
fi
if [ $(wc -c < database.zip) -lt 102400 ]; then
    error "Download failed."
    exit 0
fi

 

Decompressing the zipped file

If decompression is not successful then an error message will be shown and the script will terminate itself.

unzip -q -o database.zip
if [ -z $(find . -name 'IP2PROXY-IPV6*.CSV') ]; then
    echo "ERROR:"
    exit 0
fi
NAME="$(find . -name 'IP2PROXY-IPV6*.CSV')"

 

Creating a temporary table in MySQL to load the data

Drops the temporary table if it already exists and then creates the table. If an error is encountered, an error message will be shown and the script will terminate itself.

RESULT="$(mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME -e 'DROP TABLE IF EXISTS `ip2proxy_database_tmp`;' 2>&1)"
if [ ! -z "$(echo $RESULT | grep 'connect')" ]; then
        error "Failed to connect MySQL host."
        exit 0
fi
if [ ! -z "$(echo $RESULT | grep 'Access denied')" ]; then
    error "MySQL authentication failed."
    exit 0
fi
RESULT="$(mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME -e 'CREATE TABLE `ip2proxy_database_tmp` (`ip_from` DECIMAL(39,0) NOT NULL, `ip_to` DECIMAL(39,0) NOT NULL, `proxy_type` VARCHAR(3), `country_code` CHAR(2), `country_name` VARCHAR(64), `region_name` VARCHAR(128), `city_name` VARCHAR(128), `isp` VARCHAR(256), `domain` VARCHAR(128), `usage_type` VARCHAR(11), `asn` VARCHAR(6), `as` VARCHAR(256), `last_seen` INT(10), `threat` VARCHAR(128), `provider` VARCHAR(256), PRIMARY KEY (`ip_from`, `ip_to`)) ENGINE=MyISAM;' 2>&1)"
if [ ! -z "$(echo $RESULT)" ]; then
    error "Unable to create temporary table."
    exit 0
fi

Loading the CSV data into the MySQL temporary table

Loads the CSV data into the temporary table. If an error is encountered, an error message will be shown and the script will terminate itself.

RESULT="$(mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME -e 'LOAD DATA LOCAL INFILE '\'''$NAME''\'' INTO TABLE `ip2proxy_database_tmp` FIELDS TERMINATED BY '\'','\'' ENCLOSED BY '\''\"'\'' LINES TERMINATED BY '\''\r\n'\'';' 2>&1)"
if [ ! -z "$(echo $RESULT)" ]; then
        error "Failed."
        exit 0
fi

 

Dropping the existing data table

Drops the existing data table. If an error is encountered, an error message will be shown and the script will terminate itself.

RESULT="$(mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME -e 'DROP TABLE IF EXISTS `ip2proxy_database`;' 2>&1)"
if [ ! -z "$(echo $RESULT)" ]; then
        error "Failed to drop \"ip2proxy_database\" table."
        exit 0
fi

Rename the temporary table to become the live data table

Renames the temporary table to become the live data table. If an error is encountered, an error message will be shown and the script will terminate itself.

RESULT="$(mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME -e 'RENAME TABLE `ip2proxy_database_tmp` TO `ip2proxy_database`;' 2>&1)"
if [ ! -z "$(echo $RESULT)" ]; then
        error "Failed to rename table."
        exit 0
fi

Remove temporary download folder and the downloaded data file

Remove the temporary download folder and all files in that folder.

rm -rf /tmp/ip2proxy

Was this article helpful?

Yes No

Related Articles