Tutorials

How to import and update SQL databases

In this tutorial we demonstrate how to import a geolocation CSV file into a local SQL database and setup automatic updates.


Create a database table


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]>



Install dbip-update


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


Create configuration file

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



Initial import


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 (May 11th 2024)
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



Setup automatic updates


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
~#

Add a crontab entry

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



Compatibility

API DB
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 !