Importing and Indexing First Watch Malicious Domains Data Feed into MySQL | WhoisXML API
Products APIs Data Feeds Web Tools Domain Research & Monitoring Enterprise Packages
WHOIS / WHOIS History
WHOIS / WHOIS History

Provide current and historical ownership information on domains / IPs. Identify all connections between domains, registrants, registrars, and DNS servers.

DNS / DNS History
DNS / DNS History

Look into all current and historical DNS / IP connections between domains and A, MX, NS, and other records. Monitor suspicious changes to DNS records.

IP Geolocation / IP Netblocks
IP Geolocation / IP Netblocks

Get detailed context on an IP address, including its user’s geolocation, time zone, connected domains, connection type, IP range, ASN, and other network ownership details.

Domain Research Suite (DRS)
Domain Research Suite (DRS)

Access our web-based solution to dig into and monitor all domain events of interest.

Domain Research Suite (DRS)
Domain Research Suite (DRS)

Get access to a web-based enterprise-grade solution to search and monitor domain registrations and ownership details for branded terms, fuzzy matches, registrants of interest, and more.

Research
Monitoring
White-Label
WhoisXML API Predictive Threat Intelligence
Predictive Threat Intelligence

Detect and block access to and from dangerous domain names before malicious actors can weaponize them. Contact us today for more information.

WhoisXML API Internet Infrastructure
Internet Infrastructure

Unlock integrated intelligence on Internet properties and their ownership, infrastructure, and other attributes.

Enterprise API Packages
Enterprise API Packages

Our complete set of domain, IP, and DNS intelligence available via API calls as an annual subscription with predictable pricing.

Security Intelligence (SI) Suite
Security Intelligence (SI) Suite

Offers complete access to WHOIS, IP, DNS, and subdomain data for product enrichment, threat hunting and more.

Multi-Level API User Administration Now Available - Manage individual API keys for team members in your organization.

Learn More
×
Contact Us

WhoisXML API Blog

Read other articles Download PDF

Importing and Indexing First Watch Malicious Domains Data Feed into MySQL

Abstract

This white paper provides a comprehensive guide for importing First Watch Malicious Domain data from a CSV file into a MySQL database and indexing it on the `domainName` column. The steps outlined here cover the creation of a database, the table structure design, and data import using MySQL. By following this approach, users can efficiently handle domain-related datasets for querying and analysis purposes.

1. Introduction

The growth of the internet and the expansion of domain names necessitate effective methods for storing, indexing, and analyzing domain-related data. In many cybersecurity and domain analysis use cases, data is typically collected in a CSV or JSON format, which needs to be imported into a database for processing. This white paper provides a step-by-step guide for importing domain data specifically from a CSV file into MySQL, creating a robust and efficient data storage solution for domain analysis.

2. Prerequisites

Before proceeding with the steps described in this paper, ensure the following:

  • MySQL is installed and running on your Linux system. The commands will vary slightly on Microsoft Windows.
  • You have sufficient privileges to create databases and tables.
  • The CSV file containing domain data is available and accessible from the MySQL server.

3. Creating the MySQL Database

The first step involves creating a MySQL database to store the domain information:

Log in to MySQL

To begin, log in to the MySQL server using the following command:

mysql -u root -p

Enter the password for the root user when prompted.

Create the Database

Once logged in, create a new database named `domain_data_db`:

CREATE DATABASE domain_data_db;

Use the Database

Switch to the newly created database to prepare for table creation:

USE domain_data_db;

4. Designing and Creating the Table

The next step is to create a table to store the domain data from the CSV file. The table structure should reflect the fields in the CSV file to facilitate accurate data import.

Define the Table Structure

Create a table named `domains` that matches the fields from the CSV file:

CREATE TABLE domains (
 	id INT AUTO_INCREMENT PRIMARY KEY,
 	reason VARCHAR(255),
 	domainName VARCHAR(255) UNIQUE,
 	registrarName VARCHAR(255),
 	registrarIANAID INT,
 	whoisServer VARCHAR(255),
 	nameServers TEXT,
 	createdDateRaw DATETIME,
 	updatedDateRaw DATETIME,
 	expiresDateRaw DATETIME,
 	createdDateParsed DATETIME,
 	-- (add remaining columns as needed)
 	INDEX (domainName)
 );

5. Importing the CSV File into MySQL

After creating the table, the next step is importing the CSV data into the table.

Using the LOAD DATA Command

To import the data from the CSV file, use the `LOAD DATA INFILE` command. Ensure that the CSV file is accessible from the MySQL server and execute the following command:

LOAD DATA INFILE '/path/fwmd.YYYY-MM-DD.enterprise.daily.data.csv'
 INTO TABLE domains
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 ROWS
 (reason, domainName, registrarName, registrarIANAID, whoisServer, nameServers, createdDateRaw, updatedDateRaw, expiresDateRaw, createdDateParsed);

Adjust the file path (`/path/) to point to the location of the CSV file on the MySQL server. The command will read the data from the CSV and insert it into the `domains` table.  Also, depending on your MySQL configuration, you may need to use the “--local-infile=1” command-line option to use a local file.  After this executes, you should see something similar to the following:

Query OK, 66272 rows affected, 65535 warnings (1.76 sec)
Records: 66272  Deleted: 0  Skipped: 0  Warnings: 361653

6. Verification

Once the import is complete, verify that the data has been correctly imported by running a simple query:

SELECT * FROM domains LIMIT 5;

This command will return the first five rows of the table, allowing you to verify that the data matches the expected format.  For example:

mysql> select * from domains limit 5;
+----+--------+----------------------------+-------------------------------+-----------------+----------------------+---------------------------------------------------+---------------------+---------------------+---------------------+---------------------+
| id | reason | domainName                 | registrarName                 | registrarIANAID | whoisServer          | nameServers                                       | createdDateRaw      | updatedDateRaw      | expiresDateRaw      | createdDateParsed   |
+----+--------+----------------------------+-------------------------------+-----------------+----------------------+---------------------------------------------------+---------------------+---------------------+---------------------+---------------------+
|  1 | added  | trialparticipantforall.com | GoDaddy.com, LLC              |             146 | whois.godaddy.com    | NS41.DOMAINCONTROL.COM|NS42.DOMAINCONTROL.COM     | 2024-11-02 22:11:58 | 2024-11-02 22:11:59 | 2027-11-02 22:11:58 | 2024-11-02 22:11:58 |
|  2 | added  | trkbj.com                  | DYNADOT12 LLC                 |            1867 | whois.dynadot12.com  | sp1.gname-dns.com|sp2.gname-dns.com               | 2024-11-02 18:13:07 | 2024-11-03 02:15:48 | 2025-11-02 18:13:07 | 2024-11-02 18:13:07 |
|  3 | added  | truthslieswhatif.com       | GoDaddy.com, LLC              |             146 | whois.godaddy.com    | NS03.DOMAINCONTROL.COM|NS04.DOMAINCONTROL.COM     | 2024-11-02 04:41:00 | 2024-11-02 04:41:01 | 2025-11-02 04:41:00 | 2024-11-02 04:41:00 |
|  4 | added  | tzsnmj.com                 | West263 International Limited |            1915 | whois.hkdns.hk       | grapdns1.myhostadmin.net|grapdns2.myhostadmin.net | 2024-11-02 04:01:36 | 2024-11-02 06:07:00 | 2025-11-02 04:01:36 | 2024-11-02 04:01:36 |
|  5 | added  | uyehjwqzza.com             | Dominet (HK) Limited          |            3775 | grs-whois.aliyun.com | VIP7.ALIDNS.COM|VIP8.ALIDNS.COM                   | 2024-11-02 06:04:11 | 2024-11-02 06:06:33 | 2025-11-02 06:04:11 | 2024-11-02 06:04:11 |
+----+--------+----------------------------+-------------------------------+-----------------+----------------------+---------------------------------------------------+---------------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

To search for a specific domainName:

mysql> SELECT * FROM domains WHERE domainName = 'trialparticipantforall.com';
+----+--------+----------------------------+------------------+-----------------+-------------------+-----------------------------------------------+---------------------+---------------------+---------------------+---------------------+
| id | reason | domainName                 | registrarName    | registrarIANAID | whoisServer       | nameServers                                   | createdDateRaw      | updatedDateRaw      | expiresDateRaw      | createdDateParsed   |
+----+--------+----------------------------+------------------+-----------------+-------------------+-----------------------------------------------+---------------------+---------------------+---------------------+---------------------+
|  1 | added  | trialparticipantforall.com | GoDaddy.com, LLC |             146 | whois.godaddy.com | NS41.DOMAINCONTROL.COM|NS42.DOMAINCONTROL.COM | 2024-11-02 22:11:58 | 2024-11-02 22:11:59 | 2027-11-02 22:11:58 | 2024-11-02 22:11:58 |
+----+--------+----------------------------+------------------+-----------------+-------------------+-----------------------------------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

To search for a keyword in the domainName field:

mysql> SELECT * FROM domains WHERE domainName like '%paypal%';
+-------+--------+----------------------------------+-----------------------------------------+-----------------+--------------------+--------------------------------------------------+---------------------+---------------------+---------------------+---------------------+
| id    | reason | domainName                       | registrarName                           | registrarIANAID | whoisServer        | nameServers                                      | createdDateRaw      | updatedDateRaw      | expiresDateRaw      | createdDateParsed   |
+-------+--------+----------------------------------+-----------------------------------------+-----------------+--------------------+--------------------------------------------------+---------------------+---------------------+---------------------+---------------------+
|  5128 | added  | paypalpayments.com.mx            | PDR Ltd. d/b/a PublicDomainRegistry.com |             303 | whois.mx           | ns71.hostgator.mx|ns70.hostgator.mx              | 2024-11-01 00:00:00 | 2024-11-01 00:00:00 | 2025-11-01 00:00:00 | 2024-11-01 00:00:00 |
| 20769 | added  | paypalsolution.net               | Wix.Com Ltd.                            |            3817 | whois.wix.com      | ns0.wixdns.net|ns1.wixdns.net                    | 2024-11-02 13:59:50 | 2024-11-02 13:59:50 | 2025-11-02 13:59:50 | 2024-11-02 13:59:50 |
| 26406 | added  | paypal-pay.com                   | NameSilo, LLC                           |            1479 | whois.namesilo.com | ARIA.NS.CLOUDFLARE.COM|ROMMY.NS.CLOUDFLARE.COM   | 2024-11-02 11:52:50 | 2024-11-02 12:52:54 | 2025-11-02 11:52:50 | 2024-11-02 11:52:50 |
| 47177 | added  | kunden-paypal-aktualisierung.xyz | NameSilo, LLC                           |            1479 | whois.namesilo.com | NITIN.NS.CLOUDFLARE.COM|ARUSHI.NS.CLOUDFLARE.COM | 2024-11-03 09:57:28 | 2024-11-03 09:57:30 | 2025-11-03 23:59:59 | 2024-11-03 09:57:28 |
+-------+--------+----------------------------------+-----------------------------------------+-----------------+--------------------+--------------------------------------------------+---------------------+---------------------+---------------------+---------------------+
4 rows in set (0.04 sec)

Python Example

# python findDomain.py trialparticipantforall.com

import mysql.connector
from mysql.connector import Error
import sys

def search_domain(domain_name):
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='domain_data_db',
            user='root',
            password='<YOUR_PASSWORD>'
        )
        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)

            query = "SELECT * FROM domains WHERE domainName = %s"
            cursor.execute(query, (domain_name,))
            result = cursor.fetchall()

            if result:
                for row in result:
                    print(row)
            else:
                print("No domain found with the name:", domain_name)
    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

search_domain(sys.argv[1])

7. Conclusion

This white paper has outlined a method for importing and indexing domain data from a CSV file into a MySQL database. The guide provided covers the creation of a database, designing a table structure, and using the `LOAD DATA INFILE` command to facilitate the data import. With the `domainName` column indexed, subsequent queries can be executed more efficiently, improving the overall performance of the system.

Appendix

Sample Data

10 record sample plus header line:

reason,domainName,registrarName,registrarIANAID,whoisServer,nameServers,createdDateRaw,updatedDateRaw,expiresDateRaw,createdDateParsed,updatedDateParsed,expiresDateParsed,status,registryDataRawText,whoisRecordRawText,auditUpdatedDate,contactEmail,registrant_rawText,registrant_email,registrant_name,registrant_organization,registrant_street1,registrant_street2,registrant_street3,registrant_street4,registrant_city,registrant_state,registrant_postalCode,registrant_country,registrant_fax,registrant_faxExt,registrant_telephone,registrant_telephoneExt,administrativeContact_rawText,administrativeContact_email,administrativeContact_name,administrativeContact_organization,administrativeContact_street1,administrativeContact_street2,administrativeContact_street3,administrativeContact_street4,administrativeContact_city,administrativeContact_state,administrativeContact_postalCode,administrativeContact_country,administrativeContact_fax,administrativeContact_faxExt,administrativeContact_telephone,administrativeContact_telephoneExt,billingContact_rawText,billingContact_email,billingContact_name,billingContact_organization,billingContact_street1,billingContact_street2,billingContact_street3,billingContact_street4,billingContact_city,billingContact_state,billingContact_postalCode,billingContact_country,billingContact_fax,billingContact_faxExt,billingContact_telephone,billingContact_telephoneExt,technicalContact_rawText,technicalContact_email,technicalContact_name,technicalContact_organization,technicalContact_street1,technicalContact_street2,technicalContact_street3,technicalContact_street4,technicalContact_city,technicalContact_state,technicalContact_postalCode,technicalContact_country,technicalContact_fax,technicalContact_faxExt,technicalContact_telephone,technicalContact_telephoneExt,zoneContact_rawText,zoneContact_email,zoneContact_name,zoneContact_organization,zoneContact_street1,zoneContact_street2,zoneContact_street3,zoneContact_street4,zoneContact_city,zoneContact_state,zoneContact_postalCode,zoneContact_country,zoneContact_fax,zoneContact_faxExt,zoneContact_telephone,zoneContact_telephoneExt
added,trialparticipantforall.com,"GoDaddy.com, LLC",146,whois.godaddy.com,NS41.DOMAINCONTROL.COM|NS42.DOMAINCONTROL.COM,2024-11-02T22:11:58Z,2024-11-02T22:11:59Z,2027-11-02T22:11:58Z,2024-11-02 22:11:58 UTC,2024-11-02 22:11:59 UTC,2027-11-02 22:11:58 UTC,clientTransferProhibited clientUpdateProhibited clientRenewProhibited clientDeleteProhibited,,,2024-11-03 12:28:48 UTC,[email protected],,,Registration Private,"Domains By Proxy, LLC","DomainsByProxy.com|100 S. Mill Ave, Suite 1600",,,,Tempe,Arizona,85281,UNITED STATES,,,14806242599,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Registration Private,"Domains By Proxy, LLC","DomainsByProxy.com|100 S. Mill Ave, Suite 1600",,,,Tempe,Arizona,85281,UNITED STATES,,,14806242599,,,,,,,,,,,,,,,,,
added,trkbj.com,DYNADOT12 LLC,1867,whois.dynadot12.com,sp1.gname-dns.com|sp2.gname-dns.com,2024-11-02T18:13:07Z,2024-11-03T02:15:48Z,2025-11-02T18:13:07Z,2024-11-02 18:13:07 UTC,2024-11-03 02:15:48 UTC,2025-11-02 18:13:07 UTC,clientTransferProhibited,,,2024-11-03 12:28:48 UTC,[email protected],,,REDACTED FOR PRIVACY,Super Privacy Service LTD c/o Dynadot,PO Box 701,,,,San Mateo,California,94401,UNITED STATES,,,16505854708,,,,REDACTED FOR PRIVACY,Super Privacy Service LTD c/o Dynadot,PO Box 701,,,,San Mateo,California,94401,UNITED STATES,,,16505854708,,,,,,,,,,,,,,,,,,,,REDACTED FOR PRIVACY,Super Privacy Service LTD c/o Dynadot,PO Box 701,,,,San Mateo,California,94401,UNITED STATES,,,16505854708,,,,,,,,,,,,,,,,,
added,truthslieswhatif.com,"GoDaddy.com, LLC",146,whois.godaddy.com,NS03.DOMAINCONTROL.COM|NS04.DOMAINCONTROL.COM,2024-11-02T04:41:00Z,2024-11-02T04:41:01Z,2025-11-02T04:41:00Z,2024-11-02 04:41:00 UTC,2024-11-02 04:41:01 UTC,2025-11-02 04:41:00 UTC,clientTransferProhibited clientUpdateProhibited clientRenewProhibited clientDeleteProhibited,,,2024-11-03 12:28:48 UTC,[email protected],,,Registration Private,"Domains By Proxy, LLC","DomainsByProxy.com|100 S. Mill Ave, Suite 1600",,,,Tempe,Arizona,85281,UNITED STATES,,,14806242599,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Registration Private,"Domains By Proxy, LLC","DomainsByProxy.com|100 S. Mill Ave, Suite 1600",,,,Tempe,Arizona,85281,UNITED STATES,,,14806242599,,,,,,,,,,,,,,,,,
added,tzsnmj.com,West263 International Limited,1915,whois.hkdns.hk,grapdns1.myhostadmin.net|grapdns2.myhostadmin.net,2024-11-02T04:01:36Z,2024-11-02T06:07:00Z,2025-11-02T04:01:36Z,2024-11-02 04:01:36 UTC,2024-11-02 06:07:00 UTC,2025-11-02 04:01:36 UTC,ok,,,2024-11-03 12:28:49 UTC,[email protected],,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,Shan Dong Sheng,REDACTED FOR PRIVACY,CHINA,,,,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,,,,,,,,,,,,,,,,,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,,,,,,,,,,,,,,,,,
added,uyehjwqzza.com,Dominet (HK) Limited,3775,grs-whois.aliyun.com,VIP7.ALIDNS.COM|VIP8.ALIDNS.COM,2024-11-02T06:04:11Z,2024-11-02T06:06:33Z,2025-11-02T06:04:11Z,2024-11-02 06:04:11 UTC,2024-11-02 06:06:33 UTC,2025-11-02 06:04:11 UTC,ok,,,2024-11-03 12:28:50 UTC,[email protected],,,Registry Registrant ID: Not Available From Registry,,,,,,,JDASKDHASJ,,EQUATORIAL GUINEA,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
added,vegannewsstand.com,"ENOM, INC.",48,WHOIS.ENOM.COM,NS-NOT-CONFIGURED-1.SURESERVER.COM|NS-NOT-CONFIGURED-2.SURESERVER.COM,2024-11-02T15:45:21Z,2024-11-02T15:45:22Z,2025-11-02T15:45:21Z,2024-11-02 15:45:21 UTC,2024-11-02 15:45:22 UTC,2025-11-02 15:45:21 UTC,addPeriod clientTransferProhibited,,,2024-11-03 12:28:50 UTC,[email protected],,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,MI,REDACTED FOR PRIVACY,UNITED STATES,,,,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,,,,,,,,,,,,,,,,,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,,,,,,,,,,,,,,,,,
added,vhfamilyenterprises.com,Squarespace Domains II LLC,895,whois.squarespace.domains,ns-cloud-b4.googledomains.com|ns-cloud-b3.googledomains.com|ns-cloud-b2.googledomains.com|ns-cloud-b1.googledomains.com,2024-11-02T21:01:10Z,2024-11-02T21:01:11Z,2025-11-02T21:01:10Z,2024-11-02 21:01:10 UTC,2024-11-02 21:01:11 UTC,2025-11-02 21:01:10 UTC,clientTransferProhibited clientDeleteProhibited,,,2024-11-03 12:28:51 UTC,[email protected],,,REDACTED FOR PRIVACY,vhfamilyenterprises.com,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,MN,REDACTED FOR PRIVACY,UNITED STATES,,,,,,,REDACTED FOR PRIVACY,vhfamilyenterprises.com,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,MN,REDACTED FOR PRIVACY,UNITED STATES,,,,,,,,,,,,,,,,,,,,,,,REDACTED FOR PRIVACY,vhfamilyenterprises.com,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,MN,REDACTED FOR PRIVACY,UNITED STATES,,,,,,,,,,,,,,,,,,,,
added,vjty824.com,REALTIME REGISTER B.V.,839,whois.yoursrs.com,a.share-dns.com|b.share-dns.net,2024-11-02T11:48:07Z,2024-11-02T11:52:31Z,2025-11-02T11:48:07Z,2024-11-02 11:48:07 UTC,2024-11-02 11:52:31 UTC,2025-11-02 11:48:07 UTC,clientTransferProhibited,,,2024-11-03 12:28:51 UTC,[email protected],,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,NETHERLANDS,,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,,,,,,,,,,,,,,,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,,,,,,,,,,,,,,,
added,vqif074.com,Dnsgulf Pte. Ltd.,4149,whois.dnsgulf.com,NS1.DNS.COM|NS2.DNS.COM,2024-11-02T15:52:41Z,2024-11-02T15:52:41Z,2025-11-02T15:52:41Z,2024-11-02 15:52:41 UTC,2024-11-02 15:52:41 UTC,2025-11-02 15:52:41 UTC,ok,,,2024-11-03 12:28:51 UTC,[email protected],,,REDACTED FOR PRIVACY,Fu Yong Chun,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,shanxi,REDACTED FOR PRIVACY,CHINA,,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,,,,,,,,,,,,,,,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,,,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,REDACTED FOR PRIVACY,,,,,,,,,,,,,,,,

Mass Import

#!/bin/bash

# MySQL credentials
DB_HOST="localhost"
DB_USER="root"
DB_PASS="your_password"  # Replace with your MySQL root password
DB_NAME="domain_data_db"

# Directory containing CSV files
CSV_DIR="/path/csv_directory"  # Replace with the directory containing your CSV files

# Loop through all fwmd*.csv files in the directory
for csv_file in "$CSV_DIR"/fwmd*.csv; do
    if [[ -f "$csv_file" ]]; then
        echo "Importing $csv_file into MySQL..."
        
        # Import the CSV file into MySQL
        mysql --local-infile=1 -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "
        LOAD DATA LOCAL INFILE '$csv_file'
        INTO TABLE domains
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        IGNORE 1 ROWS
        (reason, domainName, registrarName, registrarIANAID, whoisServer, nameServers, createdDateRaw, updatedDateRaw, expiresDateRaw, createdDateParsed);
        "
        
        echo "$csv_file imported successfully."
    else
        echo "No fwmd*.csv files found in the directory."
    fi

done

echo "All CSV files have been processed."

Downloading Multiple Files

To download the entire collection using wget command:

wget --user="<YOUR_API_KEY" --password="<YOUR_API_KEY>" -r -np -nH --cut-dirs=6 -A "fwmd*.tar.gz" https://download.whoisxmlapi.com/datafeeds/First_Watch_Malicious_Domains_Data_Feed/enterprise/2024/

You can adjust the -A parameter to match your objectives.

References

First Watch Specifications: https://firstwatch.whoisxmlapi.com/specifications/datafeed-files 
mySQL Documentation: https://dev.mysql.com/doc/
CSV File Handling in MySQL: https://dev.mysql.com/doc/refman/8.0/en/load-data.html 

Contact Information

WHOIS, Inc. Sales: [email protected]
Support: [email protected]
Website: www.whoisxmlapi.com

Related posts
Read other articles Download PDF
Try our WhoisXML API for free
Get started