How to import CSV files into MySQL or MariaDB

Importing a CSV file into MySQL or MariaDB is one of the fastest ways to load exported application data, reporting extracts, or staged migration rows into a queryable table. Bulk loading keeps large datasets practical and avoids repetitive manual INSERT statements.

LOAD DATA LOCAL INFILE makes the command-line client read the file from its own filesystem and stream the contents to the database server. That keeps the workflow simple when the CSV already lives on the workstation or shell host running the client. Server-side LOAD DATA INFILE is different because the database server reads the file itself, so server-side file-access rules and directory restrictions can apply.

Imports work only when the CSV layout matches the destination table, including column order, header handling, quoting, character set, and newline style. Current MySQL 8.4 servers report local_infile as OFF until an administrator enables it, while MariaDB deployments can report either ON or OFF depending on packaging and service policy, so checking the live setting before loading keeps the import path predictable.

1. Open the mysql or mariadb client with LOCAL enabled.

   $ mysql --local-infile=1 -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   ##### snipped #####
   mysql>

   The mariadb client accepts the same --local-infile=1 option. The LOCAL keyword makes the client read the file from the machine running the client.

2. Select the target database.

   mysql> USE appdb;
   Database changed

3. Check whether the server allows LOAD DATA LOCAL INFILE.

   mysql> SHOW VARIABLES LIKE 'local_infile';
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | local_infile  | OFF   |
   +---------------+-------+
   1 row in set (0.04 sec)

   MySQL 8.4 reports OFF in the official server build. Some MariaDB deployments report ON instead, so the live value matters more than assumptions.

4. Enable local_infile temporarily when the server reports OFF.

   mysql> SET GLOBAL local_infile = 1;
   Query OK, 0 rows affected (0.00 sec)

   Changing the global value requires administrative privileges. Leave it enabled only for the import window when the surrounding environment does not need ongoing client-side file loads.

5. Check secure_file_priv before switching to server-side LOAD DATA INFILE.

   mysql> SHOW VARIABLES LIKE 'secure_file_priv';
   +------------------+-----------------------+
   | Variable_name    | Value                 |
   +------------------+-----------------------+
   | secure_file_priv | /var/lib/mysql-files/ |
   +------------------+-----------------------+
   1 row in set (0.00 sec)

   secure_file_priv does not restrict LOAD DATA LOCAL INFILE because the client reads the file. It matters only when the server must read the file itself without LOCAL. A blank value removes the directory restriction, and NULL disables server-side file operations.

6. Inspect the target table so the column list and CSV layout match.

   mysql> DESCRIBE import_orders;
   +-------------+---------------+------+-----+---------+----------------+
   | Field       | Type          | Null | Key | Default | Extra          |
   +-------------+---------------+------+-----+---------+----------------+
   | id          | int           | NO   | PRI | NULL    | auto_increment |
   | customer_id | int           | NO   |     | NULL    |                |
   | total       | decimal(10,2) | NO   |     | NULL    |                |
   | created_at  | datetime      | NO   |     | NULL    |                |
   +-------------+---------------+------+-----+---------+----------------+
   4 rows in set (0.03 sec)

   List the destination columns explicitly when the CSV omits table columns such as an AUTO_INCREMENT id.

7. Import the CSV file with LOAD DATA LOCAL INFILE.

   mysql> LOAD DATA LOCAL INFILE '/home/dbops/imports/orders-import.csv'
       ->   INTO TABLE import_orders
       ->   CHARACTER SET utf8mb4
       ->   FIELDS TERMINATED BY ','
       ->   OPTIONALLY ENCLOSED BY '"'
       ->   LINES TERMINATED BY '\n'
       ->   IGNORE 1 LINES
       ->   (customer_id,total,created_at);
   Query OK, 5 rows affected (0.00 sec)
   Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

   Use IGNORE 1 LINES only when the first row is a header. Switch to '\r\n' for LINES TERMINATED BY when the CSV uses Windows-style newlines.

8. Review import warnings before treating the load as complete.

   mysql> SHOW WARNINGS LIMIT 10;
   Empty set (0.00 sec)

   Warnings usually point to truncated values, invalid dates, or character-set mismatches. Review them even when the row count looks correct.

9. Verify the imported row count.

   mysql> SELECT COUNT(*) AS imported_rows FROM import_orders;
   +---------------+
   | imported_rows |
   +---------------+
   |             5 |
   +---------------+
   1 row in set (0.00 sec)

10. Preview imported rows to confirm the columns landed in the expected order.

    mysql> SELECT * FROM import_orders ORDER BY id LIMIT 5;
    +----+-------------+--------+---------------------+
    | id | customer_id | total  | created_at          |
    +----+-------------+--------+---------------------+
    |  1 |           1 | 249.99 | 2025-12-24 14:12:40 |
    |  2 |           2 |  89.50 | 2025-12-24 14:12:40 |
    |  3 |           3 | 149.00 | 2025-12-24 14:12:40 |
    |  4 |           1 | 249.99 | 2025-12-24 14:13:03 |
    |  5 |           2 |  89.50 | 2025-12-24 14:13:03 |
    +----+-------------+--------+---------------------+
    5 rows in set (0.00 sec)

11. Disable local_infile again when it was enabled only for this import.

    mysql> SET GLOBAL local_infile = 0;
    Query OK, 0 rows affected (0.00 sec)

    Skip this step when the server already uses a deliberate persistent local_infile policy for trusted bulk-load workflows.

12. Exit the SQL shell.

    mysql> EXIT;
    Bye