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.


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 :

; Your account key is available in your customer section at https://db-ip.com/account/

; 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 (February 24th 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


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 !