How to import CSV files into MySQL or MariaDB

Bulk-importing a .csv file into MySQL or MariaDB turns spreadsheet-shaped data into queryable rows with minimal manual work. The approach is useful for migrations, one-off backfills, analytics staging, and feeding applications from exports generated by other systems.

LOAD DATA [LOCAL] INFILE parses input as a stream of lines, splits each line into fields, and assigns those fields to columns in an existing table. With LOCAL, the client reads the file from the machine running the mysql client and sends it over the connection; without LOCAL, the server reads from its own filesystem, typically subject to secure_file_priv restrictions.

Imports succeed only when the CSV layout matches the target schema, including column order, quoting, character set, and newline conventions. Many installations disable local_infile by default for security, so enabling it temporarily (and disabling it again afterwards) keeps risk contained. When data types do not match—dates, decimals, enums—values may be coerced or rejected, making a warning review an important post-import check.

1. Connect to the server using the mysql client with LOCAL INFILE enabled.

   $ mysql --local-infile=1 -u db_user -p
   Enter password:
   mysql>

   LOCAL reads the file from the client host running mysql, while non-LOCAL reads from the database server filesystem.

2. Select the destination database.

   mysql> USE your_database_name;
   Database changed

3. Check the server setting for local_infile.

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

4. Enable local_infile temporarily when it is OFF.

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

   Leaving local_infile enabled longer than necessary increases risk during connections to untrusted servers or intermediaries.

5. Check whether server-side imports are restricted by secure_file_priv.

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

   A non-empty value restricts non-LOCAL LOAD DATA INFILE reads to that directory on the server.

6. Inspect the target table schema for column order and types.

   mysql> DESCRIBE your_table_name;
   +------------+--------------+------+-----+---------+----------------+
   | Field      | Type         | Null | Key | Default | Extra          |
   +------------+--------------+------+-----+---------+----------------+
   | id         | int          | NO   | PRI | NULL    | auto_increment |
   | name       | varchar(255) | NO   |     | NULL    |                |
   | created_at | date         | YES  |     | NULL    |                |
   +------------+--------------+------+-----+---------+----------------+
   3 rows in set (0.00 sec)

   List columns in the LOAD DATA statement when the CSV omits table columns (for example, an AUTO_INCREMENT id).

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

   mysql> LOAD DATA LOCAL INFILE '/path/to/your/file.csv'
       -> INTO TABLE your_table_name
       -> CHARACTER SET utf8mb4
       -> FIELDS TERMINATED BY ','
       -> OPTIONALLY ENCLOSED BY '"'
       -> LINES TERMINATED BY '\n'
       -> IGNORE 1 LINES;
   Query OK, 1000 rows affected (0.14 sec)
   Records: 1000  Deleted: 0  Skipped: 1  Warnings: 0

   Use IGNORE 1 LINES only when the first row is a header, and switch LINES TERMINATED BY '\r\n' for Windows-style newlines.

8. Review warnings produced by the import.

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

   Warnings often indicate truncation, invalid dates, or character set mismatches even when rows load successfully.

9. Verify the imported row count.

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

10. Preview a few rows to confirm columns are aligned.

    mysql> SELECT * FROM your_table_name LIMIT 5;
    +----+-------------+------------+
    | id | name        | created_at |
    +----+-------------+------------+
    |  1 | Alpha       | 2025-01-02 |
    |  2 | Beta        | 2025-01-03 |
    |  3 | Gamma       | NULL       |
    |  4 | Delta, Inc. | 2025-01-05 |
    |  5 | Epsilon     | 2025-01-06 |
    +----+-------------+------------+
    5 rows in set (0.00 sec)

11. Disable local_infile again when it was enabled for the import.

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

    Some environments apply the global value to new connections, so existing sessions may require reconnecting to inherit the change.

12. Exit the mysql shell.

    mysql> EXIT;
    Bye