mysqlimport Command in Linux
mysqlimport is a command used in Linux for importing data into MySQL databases from text files. This command simplifies the process of loading data, making it a crucial component for database administrators and developers who frequently handle large datasets.
Table of Contents
Here is a comprehensive guide to the options available with the mysqlimport command −
Syntax of mysqlimport Command
The general syntax for using the Linux mysqlimport command is as follows −
mysqlimport [options] database_name textfile1 [textfile2 ...]
Where −
- [options] are parameters that modify the behavior of the command.
- database_name is the name of the MySQL database where the data will be imported.
- textfile1, textfile2, ... are the text files containing the data to be imported.
mysqlimport Command Options
The command mysqlimport offers a variety of options to customize the import process. Here are some key ones −
| Option | Description |
|---|---|
| --character-sets-dir=path | Specifies the directory where character sets are installed. |
| --columns=column_list, -c | Takes a comma-separated list of column names, matching data file columns to table columns. |
| --compress, -C | Compresses all data sent between the client and server if both support compression. |
| --debug[=debug_options], -# | Writes a debugging log with options, e.g., . Default is .d:t:o,file_named:t:o |
| --debug-check | Prints debugging info when the program exits. |
| --default-auth=plugin_name | Sets the default authentication plugin for the client side. |
| --default-character-set=name | Specifies the default character set to use. |
| --defaults-extra-file=filename | Reads default options from this file after the global defaults file. Must be the first option. |
| --defaults-file=filename | Reads default options from this file, overriding global defaults. Must be the first option. |
| --delete, -d | Empties the table before importing the text file. |
| --fields-terminated-by=... | Specifies field terminators for input files, same as LOAD DATA INFILE. |
| --force, -f | Ignores errors, continuing processing if a table doesn't exist. Without this, mysqlimport exits on error. |
| --help, -? | Displays a help message and exits. |
| --host=host_name, -h | Connects to the MariaDB server on the specified host (default is localhost). |
| --ignore, -i | Skips rows that duplicate existing rows on unique key values. |
| --ignore-foreign-keys, -k | Disables foreign key checks while importing data. |
| --ignore-lines=N | Ignores the first N lines of the data file. |
| --lines-terminated-by=... | Specifies line terminators for input files, similar to LOAD DATA INFILE. |
| --local, -L | Reads input files from the client host. |
| --lock-tables, -l | Locks all tables for writing before processing any text files. |
| --low-priority | Uses LOW_PRIORITY for loading the table, affecting only engines with table-level locking. |
| --no-defaults | Does not read default options from any option file. Must be the first argument. |
| --password[=password], -p | The password for connecting to the server. If omitted, mysqlimport prompts for it. |
| --pipe, -W | On Windows, connects to the server via a named pipe, if supported. |
Examples of mysqlimport Command in Linux
Check out the following examples of the mysqlimport command running on the Linux environment −
- Setting Character Sets Directory
- Importing Data into Specific Columns
- Compressing Data During Transfer
- Writing a Debugging Log
- Emptying Table Before Import
- Specifying Custom Field Terminators
- Continuing Despite Errors
- Connecting to a Remote Host
Setting Character Sets Directory
When working with text data that uses specific character sets, it's important to specify where these character sets are located. Here's how you can do it −
sudo mysqlimport --local --character-sets-dir=/usr/share/mysql/charsets --user=root --password=your_password example_db users.txt
This command ensures that the mysqlimport utility knows where to find the necessary character sets. This is crucial for correctly interpreting and importing text data.
Importing Data into Specific Columns
You might need to import data into specific columns of a table. This can be achieved using the --columns option −
sudo mysqlimport --local --columns='username,email' --user=root --password=your_password example_db users.txt
With this command, the data from users.txt is imported into the username and email columns of the specified table, ensuring data is placed accurately.
Compressing Data During Transfer
To optimize the data transfer process between the client and the server, you can compress the data −
sudo mysqlimport --local --compress --user=root --password=your_password example_db users.txt
This command reduces the amount of data transferred, potentially speeding up the import process and reducing bandwidth usage.
Writing a Debugging Log
If you need to troubleshoot issues during the import process, you can write a debugging log −
sudo mysqlimport --local --debug=d:t:o,debug.log --user=root --password=your_password example_db users.txt
This command logs detailed debugging information to debug.log, helping identify and resolve any issues that arise during the import.
Emptying Table Before Import
To ensure the table is empty before importing new data, you can use the --delete option −
sudo mysqlimport --local --delete --user=root --password=your_password example_db users.txt
This command clears all existing rows in the table, ensuring that only the new data from users.txt is present after the import.
Specifying Custom Field Terminators
When your data files use custom delimiters, you can specify these terminators for accurate parsing −
sudo mysqlimport --local --fields-terminated-by=';' --user=root --password=your_password example_db users.txt
This command sets the field terminator to a semicolon, allowing the import process to correctly interpret the data file's structure.
Continuing Despite Errors
In cases where you want the import process to continue even if some errors occur, use the --force option −
sudo mysqlimport --local --force --user=root --password=your_password example_db users.txt
This command skips any problematic rows and continues with the import, ensuring that as much data as possible is imported even if some issues arise.
Connecting to a Remote Host
To import data into a MySQL server located on a different host −
sudo mysqlimport --local --host=example.com --user=root --password=your_password example_db users.txt
This command connects to the MySQL server on example.com, allowing you to import data from a remote location, which is essential for distributed systems.
Conclusion
The mysqlimport command is a critical utility for database administrators and developers working with MySQL databases on Linux. It significantly simplifies the data import process, especially when handling large datasets.
By providing a variety of options, mysqlimport allows users to customize their imports to fit specific needs. Mastering this command enhances efficiency, ensures data integrity, and streamlines database operations.