How to create database in MySQL for import Maxmind GeoLite2 city csv

Question

How to create database in MySQL for import Maxmind GeoLite2 city csv?

I need to know what types of fields I have to use and how properly connect the tables in the database to import from a csv-file.

This is my SQL-code example:

DROP TABLE IF EXISTS location;

CREATE TABLE IF NOT EXISTS `location` (
`geoname_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`locale_code` varchar(2) DEFAULT NULL, 
`continent_code` varchar(2) DEFAULT NULL,
`continent_name` varchar(20) DEFAULT NULL,
`country_iso_code` varchar(2) DEFAULT NULL,
`country_name` varchar(50) DEFAULT NULL,
`subdivision_1_iso_code` varchar(20) DEFAULT NULL, 
`subdivision_1_name` varchar(70) DEFAULT NULL,
`subdivision_2_iso_code` varchar(20) DEFAULT NULL, 
`subdivision_2_name` varchar(70) DEFAULT NULL,
`city_name` varchar(100) DEFAULT NULL,
`metro_code` int(11) DEFAULT NULL,
`time_zone` varchar(100) DEFAULT NULL,
PRIMARY KEY (`geoname_id`)
)   ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS blocks_IPv4;

CREATE TABLE `blocks_IPv4` (
`network` varchar(30) DEFAULT NULL,
`geoname_id` int(11) DEFAULT NULL,
`registered_country_geoname_id` int(11) DEFAULT NULL,
`represented_country_geoname_id` int(11) DEFAULT NULL,
`is_anonymous_proxy` tinyint(1) DEFAULT '0',
`is_satellite_provider` tinyint(1) DEFAULT '0',
`postal_code` varchar(45) DEFAULT NULL,
`latitude` float DEFAULT NULL,
`longitude` float DEFAULT NULL,
`accuracy_radius` INT(5)
)   ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS blocks_IPv6;

CREATE TABLE `blocks_IPv6` (
`network` varchar(255) DEFAULT NULL, 
`geoname_id` int(11) DEFAULT NULL,
`registered_country_geoname_id` int(11) DEFAULT NULL,
`represented_country_geoname_id` int(11) DEFAULT NULL,
`is_anonymous_proxy` tinyint(1) DEFAULT '0',
`is_satellite_provider` tinyint(1) DEFAULT '0',
`postal_code` varchar(45) DEFAULT NULL,
`latitude` float DEFAULT NULL,
`longitude` float DEFAULT NULL,
`accuracy_radius` INT(5)
)    ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

You can use my tool for converting MaxMind GeoLite2 country/city CSV to MySQL/PostgreSQL/Microsoft SQL Server 2019 from GitHub https://github.com/mbto/maxmind-geoip2-csv2sql-converter

You can read examples, or follow this steps:

1. Take a free license key to MaxMind API (if you don't have) at https://support.maxmind.com/account-faq/license-keys/how-do-i-generate-a-license-key/
2. Install Java 11 (if not installed) at adoptopenjdk.net or github.com/raphw/raphw.github.io or oracle.com/java
3. Download a tool from releases (.zip or .tar)
4. Unpack to yours directory
5. Copy/Paste .ini template bin/GeoLite2-City-CSV.mysql.default.ini with your profile name, example bin/GeoLite2-City-CSV.mysql.Your Project Name.ini or use default.
6. Open .ini template with Notepad and change [windows_loader] or [unix_loader] section (set MySQL host:port, user and password).
7. For unix: Execute chmod +x maxmind-geoip2-csv2sql-converter
8. Run converting: maxmind-geoip2-csv2sql-converter.bat -c "GeoLite2-City-CSV.mysql.Your Project Name.ini" -k Your_License_Key -i 4,6
9. After converting, the scripts bin/converted/loader.bat and bin/converted/loader.sh will be available.
10. For unix: Execute chmod +x loader.sh
11. Execute loader.bat or loader.sh for importing schemas.

Done