In this tutorial we demonstrate how to import a geolocation CSV file into a local SQL database and setup automatic updates.
The first step is to create a new database table to hold the imported data.
You will find the MySQL table creation statements for all available CSV databases on the following pages :
In this example we will use the IP to Country database :
~# mysql myapp Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15635862 Server version: 10.3.8-MariaDB-1:10.3.8+maria~jessie mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [myapp]> CREATE TABLE `dbip_lookup` ( -> `addr_type` enum('ipv4','ipv6') NOT NULL, -> `ip_start` varbinary(16) NOT NULL, -> `ip_end` varbinary(16) NOT NULL, -> `continent` char(2) NOT NULL, -> `country` char(2) NOT NULL, -> PRIMARY KEY (`addr_type`,`ip_start`) -> ) DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.048 sec) MariaDB [myapp]>
dbip-update is the preferred way for loading and updating databases. It is bundled in the package below and is meant to be run on the command line.dbip-phpsrc-3.0.1.tgz
When run without arguments or configuration file, dbip-update outputs this help page :
~# ./dbip-update.php usage: ./dbip-update.php -k <accountKey> [-l] [-d <dbType>] [-f <format>] [-o <outputDir|outputFileName>] [-b <dataSourceName> [-u <dbUser>] [-p <dbPassword] [-t <dbTableName>]] [-c <configFile>] [-n] [-z|-Z] [-w] [-q] -l list available items and exit -n request new items only -z fetch uncompressed file (default for mmdb format) -Z fetch compressed file (default for csv format) -w overwrite destination file if it already exists -b PDO DSN for database update (ie. "mysql:host=localhost;dbname=dbip") -u database username (default 'root') -p database password (default '') -t name of database table (default 'dbip_lookup') -q be quiet ~#
If you plan to use it for updates on a regular basis, you may want to create a configuration file to hold your basic account and database configuration.
The configuration file is optional. When dbip-update finds a file named dbip-update.ini in its directory, it will attempt to load it and apply its settings.
Below is a sample dbip-update.ini :
[account] ; Your account key is available in your customer section at https://db-ip.com/account/ accountKey = INSERT_YOUR_ACCOUNT_KEY_HERE [database] ; This is the PDO Data Source Name for your database instance, see http://php.net/manual/pdo.construct.php dataSourceName = "mysql:host=localhost;dbname=myapp" ; dbUser and dbPassword are the database account credentials dbUser = myapp dbPassword = myapp123
Now that you have created a database table and configuration file, it is time to import the latest available database into your local SQL instance.
This is simply done by running dbip-update :
~# ./dbip-update.php Starting update for ip-to-country (November 20th 2019) Download completed: 4,164.4 KB Verify signature: [MD5] [SHA1] passed Database updated: 655,145 rows imported ~#
The process takes a few seconds to several minutes depending on the data size and local database performance
dbip-update has the ability to start an update only if there are new database releases that you have not downloaded yet.
This is done by adding a
-n parameter to the commande line :
~# ./dbip-update.php -n there are no new downloads available ~#
If your operating systems supports it, you should then add a crontab entry to periodically check for new releases and update your local database.
When running it from a crontab, you should prevent dbip-update from sending unnecessary details to the console, the
-q argument will silent all but critical errors.
In the sample crontab entry below, dbip-update is run daily at midnight and will update the local database if it finds a new release :
# m h dom mon dow user command 0 0 * * * root /path/to/myapp/dbip/dbip-update.php -n -q
|Free||Basic||Core||Extended||IP to Country||IP to City||IP to Location||IP to ISP||IP to Location+ISP|
Was this article helpful ? Share it with others by clicking the social media buttons !