How to automate downloading, unzipping and loading of IP2Locaion Geolocation data into Linux MySQL

75 %
25 %
Information about How to automate downloading, unzipping and loading of IP2Locaion...

Published on March 19, 2014

Author: hexahow



This is a guide for users to fully automate the updating of their IP2Location Geolocation database.
The whole shell script code is available at

Introduction  We will show how to download the IP2Location Geolocation csv data from the web server and then unzip it before loading the data into a MySQL server on a Linux platform  We will be using DB24 database in this guide.

Configure login details  Firstly, setup some login details for the IP2Location web server and MySQL server information as below. # Configuration LOGIN="IP2LOCATION_WEBSITE_LOGIN" PASS="IP2LOCATION_WEBSITE_PASSWORD" CODE="DB24CSV" DBHOST="YOUR_DATABASE_HOST" DBUSER="YOUR_DATABASE_USERNAME" DBPASS="YOUR_DATABASE_PASSWORD" DBNAME="YOUR_DATABASE_NAME"

Checking for pre-requisites  Linux packages like wget, unzip, mysql, wc, find and grep are needed for this script to run. 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  If the temporary folder does not exist, it will be created. if [ ! -d /tmp/ip2location ]; then echo -n "Create temporary directory.......................... " mkdir /tmp/ip2location if [ ! -d /tmp/ip2location ]; then error "Failed to create /tmp/ip2location" exit 0 fi success "[OK]" fi

Downloading the zipped data file from the web server wget -O -q$LOGIN&password=$PASS&productcode=$CODE 2>&1 if [ ! -f ]; then error "Download failed." exit 0 fi if [ ! -z "$(grep 'NO PERMISSION'" ]; then error "Permission denied." exit 0 fi if [ ! -z "$(grep '5 times'" ]; then error "Download quota exceed." exit 0 fi if [ $(wc -c < -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 if [ -z $(find . -name 'IP-COUNTRY*.CSV') ]; then echo "ERROR:" exit 0 fi NAME="$(find . -name 'IP-COUNTRY*.CSV')"

Creating a temporary table in MySQL  Drops the temporary table if it already exists and then creates the table. RESULT="$(mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME -e 'DROP TABLE IF EXISTS `ip2location_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 `ip2location_database_tmp` (`ip_from` INT(10) UNSIGNED ZEROFILL NOT NULL,`ip_to` INT(10) UNSIGNED ZEROFILL NOT NULL,`country_code` CHAR(2) NOT NULL,`country_name` VARCHAR(64) NOT NULL,`region_name` VARCHAR(128) NOT NULL,`city_name` VARCHAR(128) NOT NULL,`latitude` DOUBLE NULL DEFAULT NULL,`longitude` DOUBLE NULL DEFAULT NULL,`zip_code` VARCHAR(12) NULL DEFAULT NULL,`time_zone` VARCHAR(8) NULL DEFAULT NULL,`isp` VARCHAR(255) NOT NULL,`domain` VARCHAR(128) NOT NULL,`net_speed` VARCHAR(8) NOT NULL,`idd_code` VARCHAR(5) NOT NULL,`area_code` VARCHAR(30) NOT NULL,`weather_station_code` VARCHAR(10) NOT NULL,`weather_station_name` VARCHAR(128) NOT NULL,`mcc` VARCHAR(128) NULL DEFAULT NULL,`mnc` VARCHAR(128) NULL DEFAULT NULL,`mobile_brand` VARCHAR(128) NULL DEFAULT NULL,`elevation` INT(10) NOT NULL,`usage_type` VARCHAR(11) NOT NULL,INDEX `idx_ip_from` (`ip_from`),INDEX `idx_ip_to` (`ip_to`),INDEX `idx_isp` (`isp`)) 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. RESULT="$(mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME -e 'LOAD DATA LOCAL INFILE ''''$NAME'''' INTO TABLE `ip2location_database_tmp` FIELDS TERMINATED BY ''',''' ENCLOSED BY '''"''' LINES TERMINATED BY '''rn''';' 2>&1)“ if [ ! -z "$(echo $RESULT)" ]; then error "Failed." exit 0 fi

Dropping the existing data table  Drops the existing data table. RESULT="$(mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME -e 'DROP TABLE IF EXISTS `ip2location_database`;' 2>&1)“ if [ ! -z "$(echo $RESULT)" ]; then error "Failed to drop "ip2location_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. RESULT="$(mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME -e 'RENAME TABLE `ip2location_database_tmp` TO `ip2location_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/ip2location

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...