Here is a guide for users to fully automate the updating of their IP2Location DB24 database. We will show how to download the IP2Location DB24 csv data from the web server and then unzip it before loading the data into a MySQL server on a Windows platform. We will explain what each part of the code does as we go through the whole process. The scripts will work for Windows 7 or later versions of Windows.

Download the scripts

Please download the zip file containing the scripts. Extract the 3 files inside to a folder. The ip2location-update.bat is a batch file which you will run later to automate the downloading, unzipping and loading of the DB24 data. The download.ps1 and unzip.ps1 are Powershell scripts which will be used by the batch file. Make sure all 3 files are in the same folder.

Configure login details

Before you run the batch file, you will need to setup the login details for the IP2Location web server as well as the MySQL server information. Open the ip2location-update.bat file in a text editor like Notepad. Do NOT double click on the batch file as this will run the code. Instead, right click on it and click Edit. Then look for the below and replace with your actual details then save the file:

SET LOGIN=IP2LOCATION_WEBSITE_LOGIN
SET PASS=IP2LOCATION_WEBSITE_PASSWORD
SET DBHOST=DATABASE_HOSTNAME
SET DBUSER=DATABASE_USERNAME
SET DBPASS=DATABASE_PASSWORD
SET DBNAME=DATABASE_NAME

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

Creating the temporary folder for storing the downloaded zipped data file

If the temporary folder does not exist, it will be created.

SET ROOT=%~dp0
IF NOT EXIST "%ROOT%tmp" MKDIR "%ROOT%tmp"
SET DOWNLOADFOLDER=%ROOT%tmp

Checking for pre-requisites

As mentioned earlier, the batch file will require both download.ps1 and unzip.ps1 to be in the same folder. If any of these 2 files are missing, an error will be shown and the batch file will terminate itself.

IF NOT EXIST "%ROOT%download.ps1" GOTO DOWNLOADERMISSINGERROR
IF NOT EXIST "%ROOT%unzip.ps1" GOTO UNZIPMISSINGERROR

Granting permission for the Powershell scripts to run

Before we can run the Powershell scripts, permission must be granted with the following:

powershell -Command "set-executionpolicy unrestricted"

Downloading the DB24 data from the IP2Location

Download the DB24 data by connecting to IP2Location website and passing all the login parameters to it. Save the zip file to the specified download folder. An error will be shown if the download is not successful.

powershell -File download.ps1 %LOGIN% %PASS% %CODE% %OUTPUTNAME% %DOWNLOADFOLDER%

Unzipping the downloaded zip file

Decompress the zip file to get the CSV data file. An error will be shown if the decompression is not successful.

powershell -File unzip.ps1 %OUTPUTNAME% %DOWNLOADFOLDER%

Creating a temporary table in MySQL to load the data

Drop 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.

mysql -h %DBHOST% -u %DBUSER% -p%DBPASS% %DBNAME% -e "DROP TABLE IF EXISTS `ip2location_db24_tmp`;" 2>&1 | FINDSTR /m "ERROR" > NUL
mysql -h %DBHOST% -u %DBUSER% -p%DBPASS% %DBNAME% -e "CREATE TABLE `ip2location_db24_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 | FINDSTR /m "ERROR" > NUL

Loading the CSV data into the MySQL temporary table

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

mysql -h %DBHOST% -u %DBUSER% -p%DBPASS% %DBNAME% -e "LOAD DATA LOCAL INFILE '%NAME%' INTO TABLE `ip2location_db24_tmp` FIELDS TERMINATED BY ',' ENCLOSED BY '""' LINES TERMINATED BY '\r\n';" 2>&1 | FINDSTR /m "ERROR" > NUL
    

Note: Please make sure the local_inline option has been enabled in MySQL.

Dropping the existing data table

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

mysql -h %DBHOST% -u %DBUSER% -p%DBPASS% %DBNAME% -e "DROP TABLE IF EXISTS `ip2location_db24`;" 2>&1 | FINDSTR /m "ERROR" > NUL
    

Rename the temporary table to become the live data table

Rename 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.

mysql -h %DBHOST% -u %DBUSER% -p%DBPASS% %DBNAME% -e "RENAME TABLE `ip2location_db24_tmp` TO `ip2location_db24`;" 2>&1 | FINDSTR /m "ERROR" > NUL
    

Remove temporary download folder and the downloaded data file

Perform final clean up by removing the download folder and all files in it.

CD %ROOT%
RMDIR /s /q %DOWNLOADFOLDER%

The complete script can be downloaded below