Uploading Data using MySQLImport 101: Syntax & Usage Simplified

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?

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. 

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 Name	Usage
–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.
–compression-algorithms	–compression-algorithms=value 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.
–debug-check	–debug-check Print debugging information when a program exits
–debug-info	–debug-info Print debugging information, memory, and CPU statistics when program exits
–default-auth	–default-auth=plugin Authentication plugin to use
–default-character-set	–default-character-set=charset_name Specify default character set
–defaults-extra-file	–defaults-extra-file=file_name Read named option file in addition to usual file options.
–defaults-file	–defaults-file=file_name Read-only named option file
–defaults-group-suffix	–defaults-group-suffix=str Option group suffix value
–delete	–delete, -D Empty the table before importing the text file
–enable-cleartext-plugin	–enable-cleartext-plugin Enable cleartext authentication plugin
–fields-enclosed-by,–fields-escaped-by, –fields-optionally-enclosed-by,–fields-terminated-by	These options have the same meaning as the corresponding clause for LOAD DATA.
–force	–force, -f Continue even if an SQL error occurs
–get-server-public-key	–get-server-public-key Request RSA public key from the server
–help	Display 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-lines	–ignore-lines=N Ignore the first N lines of the data file
–lines-terminated-by	–lines-terminated-by=… 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
–login-path	–login-path=name Read login path options from .mylogin.cnf
–low-priority	–low-priority Use LOW_PRIORITY when loading the table
–no-defaults	–no-defaults Read no option files
–password	–password[=password], -p [password]Password to use when connecting to server
–password1	–password1[=pass_val] First multifactor authentication password to use when connecting to server
–password2	–password2[=pass_val] Second multifactor authentication password to use when connecting to server
–password3	–password3[=pass_val] Third multifactor authentication password to use when connecting to server
–pipe	–pipe, -W Connect to the server using named pipe (Windows only)
–plugin-dir	–plugin-dir=dir_name Directory where plugins are installed
–port	–port=port_num, -P port_num TCP/IP port number for the connection
–print-defaults	–print-defaults Print default options
–protocol	–protocol={TCP|SOCKET|PIPE|MEMORY} 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
–server-public-key-path	–server-public-key-path=file_name Pathname to file containing RSA public key
–shared-memory-base-name	–shared-memory-base-name=name 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-ca	The file that contains the list of trusted SSL Certificate Authorities
–ssl-capath	The directory that contains trusted SSL Certificate Authority certificate files
–ssl-cert	The file that contains X.509 certificate
–ssl-cipher	Permissible ciphers for connection encryption
–ssl-crl	The file that contains certificate revocation lists
–ssl-crlpath	The directory that contains certificate revocation-list files
–ssl-fips-mode	–ssl-fips-mode={OFF|ON|STRICT} Whether to enable FIPS mode on client-side
–ssl-key	The file that contains X.509 key
–ssl-mode	The desired security state of connection to the server
–tls-ciphersuites	–tls-ciphersuites=ciphersuite_list Permissible TLSv1.3 ciphersuites for encrypted connections
–tls-version	–tls-version=protocol_list Permissible TLS protocols for encrypted connections
–use-threads	–use-threads=N 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
–zstd-compression-level	–zstd-compression-level=level 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
a
231     Mike Rogers
232     Ryan Smith
.
w imptest.txt
32
q
$> 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
0000040
$> 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

Conclusion

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.