As soon as your business starts to grow, data associated with customers, products, and services start generating at an exponential rate. This calls for an effective, reliable, scalable, and secure Database Management System to store your rapidly growing data. MySQL is often the popular choice as an Open Source Relational Database Management System. With Standard SQL support, you can query, manipulate & add data to your MySQL Tables.

One of the essential commands used for uploading data to your Tables is MySQLImport. By providing the Database Name and the Necessary Filenames, you can easily upload data to your MySQL Tables.

In this article, you will learn how to effectively use the MySQLImport Command to seamlessly upload data to your MySQL Database.

What is MySQL?

MySQLImport - MySQL Logo

MySQL is a popular and widely used Open-Source Relational Database Management System. Similar to other RDMS, MySQL stores your data in separate tables consisting of columns and rows. Initially released in 1995, MySQL is now developed, distributed, and supported by Oracle Corporation. From a Technical Perspective, MySQL is written in C and C++. You can use SQL (Standard Query Language) to Interact with the MySQL Database. MySQL is an important part of the Modern LAMP stack that includes a Linux-based Operating System, the Apache Web Server, a MySQL Database, and PHP for Processing.

Using SQL commands you can retrieve, modify, or add data to your MySQL Tables. One of the popular approaches to upload files to MySQL Tables is via MySQLImport Command. SQL can also be used to define Table Schema or even configure the relationship between tables in the database. You can use Client Side GUIs such as MySQL WorkBench, SequelPro, DBVisualizer, etc., to type in SQL commands and the server will respond with requested information accordingly. Due to MySQL’s brilliant set of features and constant support, it is extensively used by organizations such as Facebook, Flickr, Twitter, Wikipedia, and YouTube.

Key Features of MySQL

MySQL is among the RDMS Market leaders due to the following eye-catching features it offers:

  • Ease-of-Use & Flexibility: MySQL is compatible with various Operating Systems such as UNIX, Linux, Windows, etc. It also supports a variety of programming languages such as PHP, PERL, C, C++, JAVA, etc. The MySQL environment also offers a collection of tools to ease tasks such as Server Management, Reporting, and Data Analysis. Allowing you to work with broader datasets, MySQL provides full support for several data structures, JSON & GeoSpatial Data, as well as logical, numeric, alphanumeric, date, and time data types.
  • Top-Notch Performance: You can be rest assured about the best-in-class Query Performance with the wide array of Cluster Servers that backs MySQL. It effectively handles your fluctuating workloads and processes massive volumes of data with optimal speed. It allows you to store data in 50 million rows or more in a table with a default file size limit for a table of 4GB. However, you can raise it to a theoretical limit of 8 Million Terabytes(TB) depending on your operating system configurations.
  • Open-Source: MySQL comes under the GNU General Public License (GPL) meaning it will always be freely available. You can customize it on Oracle’s Open-Source MySQL Codebase according to your requirements. Since it is Open-Source software, a large public community has evolved that regularly enriches MySQL’s documentation and Online Support Culture.
  • Localization: Making it user-friendly for everyone, MySQL supports several different character sets, including latin1 (cp1252), german, big5, ujis, etc. You can also set the language for the error messages the server provides to the clients. The MySQL Server Time can also be modified dynamically with the ability to set specific time zones for individual clients.
  • Secure: Using SQL you can configure your Data Access Control settings. With the Access Privilege System and User Account Management, you can decide who can view or use your MySQL data. MySQL sets the bar high with Host-Based Verification and Password Encryption
Simplify MySQL ETL and Analysis Using Hevo’s No-code Data Pipeline

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Start for free now!

Get Started with Hevo for Free

How to Upload Data to MySQL using MySQLImport?

SysAdmins and DBAs (Database Administrator) who manage MySQL Databases are often required to upload data from Text, Csv, or Excel files into MySQL Tables. To perform this you can use the MySQLImport Command. The MySQLImport Client supplies a Command-Line Interface to the LOAD DATA SQL statement. You will find that most of the MySQLImport options refer directly to the LOAD DATA SQL syntax.

A) MySQLImport Syntax

To upload data via MySQLIMport Command, you can follow the syntax given below:

mysqlimport [options] db_name textfile1 [textfile2 ...]

Here, db_name refers to the name of the database that contains the tables in which you want to upload the data from files textfile1, textfile2, etc. From all the files mentioned in the command line above, MySQL will take each file individually and strip the extension from the file name. The remaining filename is used to determine the name of the table into which the data needs to be uploaded. For instance, data from files such as Employee.txt, Employee.text, and Employee will be imported into a table named Employee. 

Below is the list of the options that MySQL Import supports. You can mention them on the command line or in the [mysqlimport] and [client] groups of an option file.  

Option NameUsage
–bind-address– – bind-address=ip_address
Use this to specify a network interface to connect to MySQL Server.
–columns–columns=column_list, -c column_list
It takes a comma-separated list of column names as its input.
–compress–compress, -C
Compress all information sent between client and server. Note that this option has been deprecated as of MySQL 8.0.18.
Use to configure permitted compression algorithms for connections to server.
–debug–debug[=debug_options], -# [debug_options]Write Debugging Log. A typical debug_options string is d:t:o,file_name where the default is d:t:o.
Print debugging information when a program exits
Print debugging information, memory, and CPU statistics when program exits

Authentication plugin to use
Specify default character set
Read named option file in addition to usual file options.
Read-only named option file
Option group suffix value
–delete–delete, -D
Empty the table before importing the text file
Enable cleartext authentication plugin
–fields-enclosed-by,–fields-escaped-by, –fields-optionally-enclosed-by,–fields-terminated-byThese options have the same meaning as the corresponding clause for LOAD DATA.
–force–force, -f
Continue even if an SQL error occurs
Request RSA public key from the server
–helpDisplay help message and exit
–host–host=host_name, -h host_name
Host on which MySQL server is located
–ignore–ignore, -i
See the description for the –replace option
Ignore the first N lines of the data file
This option has the same meaning as the corresponding clause for LOAD DATA
–local–local, -L
Read input files locally from the client host
–lock-tables–lock-tables, -l
Lock all tables for writing before processing any text files
Read login path options from .mylogin.cnf
Use LOW_PRIORITY when loading the table
Read no option files
–password–password[=password], -p
[password]Password to use when connecting to server
First multifactor authentication password to use when connecting to server
Second multifactor authentication password to use when connecting to server
Third multifactor authentication password to use when connecting to server
–pipe–pipe, -W
Connect to the server using named pipe (Windows only)
Directory where plugins are installed
–port–port=port_num, -P port_num
TCP/IP port number for the connection
Print default options
Transport protocol to use
–replace–replace, -r
The –replace and –ignore options control handling of input rows that duplicate existing rows on unique key values
Pathname to file containing RSA public key
Shared-memory name for shared-memory connections (Windows only)
–silent–silent, -s
Produce output only when errors occur
–socket–socket=path, -S path
Unix socket file or Windows named pipe to use
–ssl-caThe file that contains the list of trusted SSL Certificate Authorities
–ssl-capathThe directory that contains trusted SSL Certificate Authority certificate files
–ssl-certThe file that contains X.509 certificate
–ssl-cipherPermissible ciphers for connection encryption
–ssl-crlThe file that contains certificate revocation lists
–ssl-crlpathThe directory that contains certificate revocation-list files
Whether to enable FIPS mode on client-side
–ssl-keyThe file that contains X.509 key
–ssl-modeThe desired security state of connection to the server
Permissible TLSv1.3 ciphersuites for encrypted connections
Permissible TLS protocols for encrypted connections
Number of threads for parallel file-loading
–user–user=user_name, -u user_name
MySQL user name to use when connecting to server
–verbose–verbose, -v
Verbose mode
–version–version, -V
Display version information and exit
Compression level for connections to the server that use zstd compression

B) MySQLImport Example Queries

To understand MySQLImport, let’s go through the following example:

  • Creating a student Table in a Database called Test and importing Student data

mysql -e 'CREATE TABLE student(id INT, n VARCHAR(30))' test
$> ed
231     Mike Rogers
232     Ryan Smith
w imptest.txt
$> od -c imptest.txt
0000000   1   0   0  t   M   i   k	e       R   o   g   e   r   s  n   1   0
0000020   1  t   R   a   y   n       S   m   i   t   h  n
$> mysqlimport --local test student.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
$> mysql -e 'SELECT * FROM student' test
| id   | n          |
|  231 | Mike Rogers   |
|  232 | Ryan Smith |

Note that the file name should be exactly the same as the Table name you want the data imported. If not then you will get an error message.

For, example, here the “stu” table doesn’t exist in the test database. Hence, the filename student.txt should be selected to match the Student Table Name.

$> mysqlimport --local test stu.txt
mysqlimport: Error: Table 'test.stu' doesn't exist, when using table: stu


In this article, you have learned how to effectively use the MySQLImport command to upload data to your MySQL Database. MySQL allows you to retrieve, add or change data via SQL commands. The Standard MySQLImport command seamlessly imports data from the files you mention to your tables. By naming the files exactly the same as the Table name, you can avoid any errors and effortlessly import data to your tables. MySQLImport command also provides a list of options to enter columns list, compression settings, passwords, etc.

After you have uploaded your data into the MySQL Database, you can start processing the data to harness important business insights. To get a complete picture of your Business health & performance you would require to consolidate data from MySQL and all other applications used across your business for Marketing, Customer Relationship Management, Accounting, Sales, etc. To achieve this you would need to assign a portion of your Engineering Bandwidth to Integrate Data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse or a destination of your choice for further Business Analytics. All of these challenges can be comfortably solved by a Cloud-Based ETL tool such as HevoData.  

Visit our Website to Explore Hevo

If you are using MySQL as your Database Management System and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 150+ sources, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of working with the MySQLImport command! Share your thoughts with us in the comments section below.

Sanchit Agarwal
Research Analyst, Hevo Data

Sanchit Agarwal is an Engineer turned Data Analyst with a passion for data, software architecture and AI. He leverages his diverse technical background and 2+ years of experience to write content. He has penned over 200 articles on data integration and infrastructures, driven by a desire to empower data practitioners with practical solutions for their everyday challenges.

All your customer data in one place.