Import a Delimited Table File Safely with mysqlimport

mysqlimport turns a plain delimited text file into rows in an existing MariaDB table, with the row count to prove what landed. You will load a local delimited text file into an existing MariaDB table, select the file's columns explicitly, and verify the row count. The installed command is MariaDB 10.11.14 from package mariadb-client 1:10.11.14-0ubuntu0.24.04.1; mysqlimport is a symlink to mariadb-import. Allow about fifteen minutes if the database and table already exist.

You need the client package, a MariaDB account with permission to insert into the destination table, an input file, and a table whose name matches the file's base name. This guide changes database rows. Review the file and destination before running the import. No command here needs sudo; use elevated privileges only to solve a separately verified file-permission problem.

1. Check the installed client

Start with read-only checks. They do not connect to the server or change data:

$ command -v mysqlimport
/usr/bin/mysqlimport
$ readlink -f /usr/bin/mysqlimport
/usr/bin/mariadb-import
$ mysqlimport --version
mysqlimport  Ver 3.7 Distrib 10.11.14-MariaDB, for debian-linux-gnu (x86_64)

The same program is available as mariadb-import. The manual describes mysqlimport as a command-line interface to SQL LOAD DATA INFILE. Most import options therefore describe how fields and lines are separated rather than how a table is created.

Checkpoint: run mysqlimport --help if your installed version differs from the one above. Keep the output with your deployment notes when scripting against a particular client release.

2. Prepare a file and match it to a table

For each input file, the client removes the file extension and uses what remains as the table name. A file called orders.tsv therefore targets a table called orders. The table must already exist; mysqlimport does not infer or create its schema.

For a controlled test, create a small file in a working directory. The first line below is a header, and the two data fields are separated by a tab:

$ mkdir -p /tmp/mysqlimport-demo
$ printf 'order_id\tcustomer\n1001\tAda Lovelace\n1002\tGrace Hopper\n' > /tmp/mysqlimport-demo/orders.tsv
$ sed -n '1,3p' /tmp/mysqlimport-demo/orders.tsv
order_id	customer
1001	Ada Lovelace
1002	Grace Hopper

Do not copy the file into a server directory just to make it visible. The --local option tells the client to read the input from the client host. This is usually the clearest choice when the file is beside your shell. Without it, the server may be asked to open the file itself, subject to server-side file access rules.

3. Confirm the table before importing

Use an account that can inspect the target database. Replace the placeholders with values from your environment:

$ mysql --host=DB_HOST --user=DB_USER --password DB_NAME \\
    -e 'SHOW CREATE TABLE orders\G'
Enter password:

Check that the table has columns corresponding to the file. In this example it might contain order_id INT and customer VARCHAR(200). The --columns option lets you state the mapping instead of relying on the table's complete column order.

Do not put the password directly after --password= in a shared shell command or script. The manual warns that command-line passwords are insecure because they can leak through history or process inspection. Omitting the value, as above, prompts interactively. For unattended work, put connection settings in a permissions-controlled option file and inspect the effective arguments with mysqlimport --print-defaults without displaying the file contents.

4. Run a header-aware local import

Checkpoint before the write: importing rows is a state-changing operation. Confirm that orders.tsv contains the intended data, that DB_NAME is not a production database by accident, and that the destination table is correct.

The following command reads the file locally, skips its header, and maps the two fields to named columns:

$ mysqlimport --local \\
    --host=DB_HOST \\
    --user=DB_USER \\
    --password \\
    --ignore-lines=1 \\
    --columns=order_id,customer \\
    --fields-terminated-by='\t' \\
    DB_NAME /tmp/mysqlimport-demo/orders.tsv
Enter password:
DB_NAME.orders: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

The exact spacing can vary, but a successful report identifies the database and table and gives record, delete, skip and warning counts. A non-zero exit status or an error line means you should investigate before repeating the command. Repeating an import is not automatically harmless.

The field and line options are passed through to LOAD DATA INFILE. For comma-separated files, use --fields-terminated-by=','; if values may be quoted, also choose the appropriate --fields-enclosed-by and escape settings for the file you actually received. Do not guess these settings from the file extension.

5. Verify what reached the table

Use a query that checks both the number and the values of the imported rows:

$ mysql --host=DB_HOST --user=DB_USER --password DB_NAME \\
    -e "SELECT order_id, customer FROM orders WHERE order_id IN (1001,1002) ORDER BY order_id;"
Enter password:
+----------+----------------+
| order_id | customer       |
+----------+----------------+
|     1001 | Ada Lovelace   |
|     1002 | Grace Hopper   |
+----------+----------------+

Also compare the reported record count with the number of data lines you expected. A warning count is not proof that every value is correct. Investigate warnings, truncation and character-set problems before declaring the load complete.

This example is reversible only if you can identify the rows uniquely and have approval to remove them. If the test data must be removed, inspect the matching rows first, then issue a narrowly scoped delete:

$ mysql --host=DB_HOST --user=DB_USER --password DB_NAME \\
    -e "DELETE FROM orders WHERE order_id IN (1001,1002);"
Enter password:

That delete is destructive. Do not adapt it to real orders without a backup, a transaction plan and an explicit review of the key predicate.

6. Choose duplicate and failure behaviour deliberately

When an input row conflicts with an existing unique key, the default behaviour is an error and the rest of that text file is ignored. Choose one of these options only after deciding what the source file means:

--force is different: it continues after SQL errors, such as a missing destination table, and can leave a batch only partly loaded. Do not add it merely to make a scheduled job look successful. If several files are supplied, remember that the file stem selects each table; review every name before using a batch command.

7. Diagnose the common traps

If the client cannot connect, check the host, user, socket, port and protocol. The default host is localhost; --host=DB_HOST selects another host, and --port=DB_PORT selects TCP port use. --socket=SOCKET_PATH selects a Unix socket for a local connection. Keep these connection checks separate from import retries.

If the table is not found, inspect the file name and database name. orders.tsv targets orders, not a table named orders.tsv. If the input has a header, forgetting --ignore-lines=1 commonly produces a conversion error or a bad first row. If columns are in a different order, use --columns and verify the mapping.

If duplicate rows stop the load, do not jump straight to --replace. First determine whether the file is a new batch, a retry, or a correction set. If the file must be loaded again, keep the original import output and query results so the decision is auditable.

Done means