Quick answer
- In 1bench
- Right-click a table in the sidebar, choose Import Data, browse to a .sql, .csv or .json file and click Import.
- In SQL
- sql
mysql -u demo -p saas_app < saas_app.sqlRun it in your shell, not at the mysql> prompt. For a CSV file, use LOAD DATA LOCAL INFILE, shown below.
Import visually in 1bench
1bench imports SQL, CSV, JSON and NDJSON files into MySQL from the sidebar. CSV and JSON columns are matched to the table by name, and each import into an InnoDB table runs in one transaction, so a bad row leaves the table as it was.
Prerequisite: download 1bench. Free 7-day trial, no sign-up.
- 1
Create the table first
CSV and JSON go into a table that already exists; 1bench does not create one from the file. A SQL file can create its own tables, but Import Data lives on a table's menu, so an empty database needs one table first (right-click the database and choose New Table).
- 2
Choose Import Data
Right-click the table in the sidebar and choose Import Data. If the table is already open, the Import button in the bar at the bottom of the window does the same. Views do not get the option, and on a read-only connection it is greyed out.
- 3
Pick the file
Click Browse and choose the file. The Format list (SQL, NDJSON, CSV, JSON) switches to match the extension, and .jsonl counts as NDJSON. Target Table shows the table you clicked.
- 4
Set the options
For CSV and JSON, tick Clear target table before importing to replace the current rows. For a SQL file, tick Disable foreign key constraints during import if it inserts child rows before their parents.
- 5
Import
Click Import. The dialog counts rows or statements as they run, and Stop import cancels and rolls back. A toast reports Imported 4 rows, or for a SQL file, SQL file imported with the number of rows changed.
- The first CSV line must be the header. Columns are matched by name in any order and any case, columns missing from the file get their DEFAULT, and a header the table does not have stops the import with MySQL's Unknown column 'internal_note' in 'field list'. JSON keys work the same way; an unknown key fails with Keys not in table products: colour.
- 1bench reads comma-separated, double-quoted UTF-8. An unquoted empty field becomes NULL, and a quoted empty field ("") stays an empty string in text columns. MySQL's \N marker is read as the text \N, so an INT column fails with Incorrect integer value: '\N'. Rows with the wrong number of fields are skipped, and the toast says how many.
- 1bench turns on strict mode for the import, so a bad value stops it instead of being stored as 0 with a warning, which is what LOAD DATA LOCAL does.
- Clear target table before importing deletes the rows inside the import's transaction. It refuses a table that another table references, such as orders in the demo database, with Cannot truncate a table referenced in a foreign key constraint.
- SQL files run as written, one statement at a time, in the database of the table you clicked. Disable foreign key constraints during import sets FOREIGN_KEY_CHECKS and UNIQUE_CHECKS to 0 for the run. DEFINER clauses are dropped so views and triggers belong to you. CREATE, DROP and LOCK TABLES commit on their own, so a failure after one cannot be fully rolled back.
- DELIMITER is a mysql client command, not SQL, so a dump with triggers or stored procedures (mysqldump writes them with DELIMITER ;;) fails in 1bench with a syntax error near 'DELIMITER'. Load those dumps with the mysql client below.
Import with the mysql client and LOAD DATA
A file, such as a mysqldump backup, is loaded by the command-line client, which reads the file and sends each statement to the server. A CSV file goes in with . Both need the target database to exist, and the CSV needs its table too.
mysql -u demo -p saas_app < saas_app.sql
The examples use the 1bench demo database, , and its table ( AUTO_INCREMENT, , , , , a unique , , , ). Every command was run against MySQL 8.4 LTS and 9.7 with both versions of the client. Where they differ, the section says so.
Import a SQL file from the command line
Run from your shell with the database name last and the file on standard input. Add and for a remote server. Without a database name, any statement that does not say which database to use fails with .
mysql -h db.example.com -P 3306 -u demo -p saas_app < saas_app.sql
To restore into a new database, create it first. A dump made with already contains and , so it needs no database name on the command line.
mysql -u root -p -e "CREATE DATABASE saas_app_copy"
mysql -u root -p saas_app_copy < saas_app.sql
The client stops at the first error and keeps everything that ran before it. Add to report each error and carry on with the next statement.
Import with SOURCE
At the prompt, (or ) runs a file from your own machine. Relative paths start from the folder you launched the client in.
USE saas_app;
SOURCE /home/me/backups/saas_app.sql;
The 9.7 client no longer accepts outside the interactive prompt. and a piped line both fail with , while the 8.4 client runs them. Add to turn client commands back on in a script, or use instead.
Import a compressed dump
Decompress on the fly and pipe the result into the client, so the uncompressed file never touches the disk.
gunzip -c saas_app.sql.gz | mysql -u demo -p saas_app
unzip -p saas_app.zip | mysql -u demo -p saas_app
Foreign key errors in a dump
mysqldump writes at the top of every dump, so its files load in any table order. A hand-written file or one from another tool can insert into before and stop with this:
ERROR 1452 (23000) at line 1: Cannot add or update a child row: a foreign key constraint fails (`saas_app`.`order_items`, CONSTRAINT `fk_oi_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`))
Wrap the file in the same setting. It only lasts for the session, so it never affects other connections.
(echo "SET FOREIGN_KEY_CHECKS = 0;"; cat orders.sql; echo "SET FOREIGN_KEY_CHECKS = 1;") | mysql -u demo -p saas_app
Import one table from a dump
mysqldump marks each table with a comment and ends its data with , so can cut one table out.
sed -n '/^-- Table structure for table `orders`/,/^UNLOCK TABLES;/p' saas_app.sql > orders.sql
The cut-out file loses the dump's header, including . If another table references this one, its fails with . Load it with the wrapper from the section above.
Large files and max_allowed_packet
mysqldump writes many rows into each INSERT. A statement bigger than the server's (64 MB by default on 8.4 and 9.7) fails with . Raise it as an admin and reconnect, since the new value only applies to new sessions.
SET GLOBAL max_allowed_packet = 268435456; -- 256 MB
Access denied for SUPER while importing a dump
MySQL 9.7 has on by default, and mysqldump then adds and to the file. Loading that as a normal user fails with . 8.4 has GTIDs off by default, so its dumps load fine. Make the dump without those lines:
mysqldump -u demo -p --no-tablespaces --set-gtid-purged=OFF saas_app > saas_app.sql
Import a CSV with LOAD DATA LOCAL INFILE
reads a file on your machine and streams it to the server. List the columns in the order they appear in the file, so the is generated for you.
Example: load a CSV whose header is .
LOAD DATA LOCAL INFILE 'products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, sku, price, category, stock_quantity);
The options go in this order:
| Clause | What it does | Example |
|---|---|---|
| Column separator (default tab) | or | |
| Quote character around values that have commas | ||
| Escape character (default backslash) | to turn it off | |
| Line ending (default ) | for Windows files | |
| Skip the header row | ||
| File encoding, after the table name | ||
| Map file columns to table columns or variables | ||
| Compute a column from variables |
Fix "Loading local data is disabled"
Both the server and the client have to allow , and the server's is off by default on 8.4 and 9.7. Until it is on, every client gets this, even one started with :
ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the client and server sides
Turn it on as an admin, then start the client with . With the server on but the client flag missing, the error changes to
SET GLOBAL local_infile = 1;
mysql --local-infile=1 -u demo -p saas_app
lasts until the server restarts. Use to keep it, or put under in my.cnf. Managed services such as RDS change it in the parameter group instead.
Server files and secure_file_priv
Without , the server opens the file from its own disk. That needs the FILE privilege, or you get , and the file has to sit in the folder ( in the official Docker image). Anywhere else fails with .
SHOW VARIABLES LIKE 'secure_file_priv';
LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
(name, sku, price, category, stock_quantity);
is read-only at runtime, so changing it means editing the server config and restarting. For a file on your own computer, is nearly always the easier fix.
Skip columns, set values and parse dates
Read a file column into a to skip it or transform it in . parses dates that are not in MySQL's format, and turns empty fields into NULL.
Example: a Windows CSV with the header and dates like .
LOAD DATA LOCAL INFILE 'dated.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(sku, name, price, @added_on, @category)
SET created_at = STR_TO_DATE(@added_on, '%d/%m/%Y'),
category = NULLIF(@category, '');
Without , the carriage return stays on the last field of every line. A last column of is stored as (hex ), which looks right in a grid but never matches .
NULL values and empty fields
LOAD DATA writes NULL only for , or for the bare word when you use ; a quoted stays text. An empty field is an empty string in a text column. In a number column, stores 0 with , while a server-side load in strict mode stops with that as an error. Run after every load, or map the column through as above.
SHOW WARNINGS;
Duplicates: IGNORE and REPLACE
A plain server-side load stops at the first duplicate key with . behaves as if you wrote : duplicates and bad values become warnings and the load carries on. Say which you want explicitly.
LOAD DATA LOCAL INFILE 'products.csv' IGNORE INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES
(name, sku, price, category, stock_quantity);
LOAD DATA LOCAL INFILE 'products.csv' REPLACE INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES
(name, sku, price, category, stock_quantity);
deletes the old row and inserts a new one, so the row gets a new AUTO_INCREMENT id and every replaced row counts twice in the affected rows. If another table references the row, the delete fails with , even with . To update rows in place, load into a staging table and run from it.
Import a CSV with mysqlimport
is a shell wrapper around . It takes the table name from the file name, so loads into and fails with .
mysqlimport --local --ignore-lines=1 \
--fields-terminated-by=, --fields-optionally-enclosed-by='"' \
--columns=name,sku,price,category,stock_quantity \
-u demo -p saas_app products.csv
It prints . The same rules apply. It also sets the session's before loading, so a user without SESSION_VARIABLES_ADMIN fails with on 8.4 and 9.7, even with . The official Docker images do not include mysqlimport; it comes with the full MySQL download.
Import a large CSV in parallel
MySQL Shell's splits a big file into chunks and loads them over several connections. It uses underneath, so must be on, or it stops with .
mysqlsh demo@localhost:3306 --js -e 'util.importTable("products.csv", {schema: "saas_app", table: "products", dialect: "csv-unix", skipRows: 1, columns: ["name", "sku", "price", "category", "stock_quantity"], threads: 8})'
Import JSON
LOAD DATA has no JSON format, so load each document into a column and pull fields out with . For NDJSON (one object per line), turn off the field separator and escaping. With the default backslash escaping, inside a JSON string is unescaped first and the load fails with
CREATE TABLE products_raw (doc JSON);
LOAD DATA LOCAL INFILE 'products.ndjson'
INTO TABLE products_raw
FIELDS TERMINATED BY '\0' ESCAPED BY ''
LINES TERMINATED BY '\n'
(doc);
INSERT INTO products (name, sku, price)
SELECT doc->>'$.name', doc->>'$.sku', doc->>'$.price'
FROM products_raw;
For a file holding one JSON array, set too, so the whole file lands in one row, then expand it with .
INSERT INTO products (name, sku, price, category)
SELECT j.name, j.sku, j.price, j.category
FROM products_raw,
JSON_TABLE(doc, '$[*]' COLUMNS (
name VARCHAR(200) PATH '$.name',
sku VARCHAR(30) PATH '$.sku',
price DECIMAL(10,2) PATH '$.price',
category VARCHAR(50) PATH '$.category'
)) AS j;
MySQL Shell's loads NDJSON into a document collection over the X Protocol (port 33060), creating a table with a JSON column and an . It reads one document after another, not an array: a file starting with fails with .
mysqlsh mysqlx://demo@localhost:33060/saas_app --js -e 'util.importJson("products.ndjson", {schema: "saas_app", collection: "product_docs"})'