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 PX12 IPv6 database. We will show how to download the IP2Proxy PX12 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. The full script will also be shown at the end of the article.

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="PX12IPV6"
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."
	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
	mkdir /tmp/ip2proxy
	if [ ! -d /tmp/ip2proxy ]; then
		error "Unable to create temp directory."
	fi
fi

cd /tmp/ip2proxy

 

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."
fi

if [ ! -z "$(grep 'NO PERMISSION' database.zip)" ]; then
	error "Permission denied."
fi

if [ ! -z "$(grep '5 times' database.zip)" ]; then
	error "Download quota exceed."
fi

if [ $(wc -c < database.zip) -lt 102400 ]; then
	error "Download failed."
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
	error "CSV file not found."
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 "Unable to connect to the MySQL host."
fi

if [ ! -z "$(echo $RESULT | grep 'Access denied')" ]; then
	error "MySQL authentication failed."
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), `fraud_score` INT(10), PRIMARY KEY (`ip_from`, `ip_to`)) ENGINE=MyISAM;' 2>&1)"
if [ ! -z "$(echo $RESULT)" ]; then
	error "Unable to create temporary table."
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 "Unable to import data."
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 "Unable to drop existing table."
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 "Unable to rename the table."
fi

Remove temporary download folder and the downloaded data file

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

cd /tmp
rm -rf /tmp/ip2proxy

Save the full script below

You can save the full code below for your own use.

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

function error { echo -e "\e[00;91m$1\e[00m"; exit 0; }

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

if [ ! -d /tmp/ip2proxy ]; then
	mkdir /tmp/ip2proxy
	if [ ! -d /tmp/ip2proxy ]; then
		error "Unable to create temp directory."
	fi
fi

cd /tmp/ip2proxy

wget -O database.zip -q https://www.ip2location.com/download?token=$TOKEN\&file=$CODE 2>&1
if [ ! -f database.zip ]; then
	error "Download failed."
fi

if [ ! -z "$(grep 'NO PERMISSION' database.zip)" ]; then
	error "Permission denied."
fi

if [ ! -z "$(grep '5 times' database.zip)" ]; then
	error "Download quota exceed."
fi

if [ $(wc -c < database.zip) -lt 102400 ]; then
	error "Download failed."
fi

unzip -q -o database.zip
if [ -z $(find . -name 'IP2PROXY-IPV6*.CSV') ]; then
	error "CSV file not found."
fi

NAME="$(find . -name 'IP2PROXY-IPV6*.CSV')"

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 "Unable to connect to the MySQL host."
fi

if [ ! -z "$(echo $RESULT | grep 'Access denied')" ]; then
	error "MySQL authentication failed."
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), `fraud_score` INT(10), PRIMARY KEY (`ip_from`, `ip_to`)) ENGINE=MyISAM;' 2>&1)"
if [ ! -z "$(echo $RESULT)" ]; then
	error "Unable to create temporary table."
fi

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 "Unable to import data."
fi

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

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 "Unable to rename the table."
fi

cd /tmp
rm -rf /tmp/ip2proxy

Was this article helpful?

Yes No

Related Articles