Quick answer
- In 1bench
- Right-click the table in the sidebar, choose Export Table, pick SQL, NDJSON, CSV or JSON, and save the file.
- In SQL
- sql
mysqldump -u root -p --single-transaction saas_app > saas_app.sqlFor a CSV of one table, mysql -B -e "SELECT * FROM users" saas_app > users.tsv writes a tab-separated file on your machine; SELECT ... INTO OUTFILE writes a real CSV, but on the server.
Export visually in 1bench
1bench exports one MySQL table or view at a time to SQL, NDJSON, CSV or JSON, and writes the file on your own computer. It needs no FILE privilege, no secure_file_priv folder and no mysqldump install.
Prerequisite: download 1bench. Free 7-day trial, no sign-up.
- 1
Connect and expand the database
Open your MySQL connection in 1bench and expand the database in the sidebar to see its tables and views.
- 2
Choose Export Table
Right-click the table or view and choose Export Table. If the table is already open, the Export button in the bar at the bottom of the window does the same.
- 3
Narrow it down (optional)
To export only some rows, open the table, filter it in the Query panel, sort by clicking a column header or hide columns, then use the Export button at the bottom (the right-click menu always exports the whole table). The dialog then offers Current view, with only those rows and the visible columns, or Whole table.
- 4
Pick a format
Choose SQL (.sql), NDJSON, CSV (.csv) or JSON (.json); SQL is selected first. For SQL, tick Include structure (CREATE TABLE statements), Include data, or both. For JSON the first box reads Include column definitions. CSV and NDJSON always carry the rows only.
- 5
Export and save
Click Export and choose where to save the file. A progress bar counts the rows written, Cancel stops a long export, and a message such as Exported 300 rows confirms it.
- There is no Export Database or Export Schema for MySQL, so 1bench exports one table per file. For a whole database, with routines, events and users, use the mysqldump commands below.
- The SQL file holds the table's SHOW CREATE TABLE output and one INSERT per row, with an unqualified table name so it loads into whichever database you run it in. It has no DROP TABLE and no triggers. Generated columns are left out of the INSERTs.
- Every export reads inside one START TRANSACTION WITH CONSISTENT SNAPSHOT, so the rows are a consistent snapshot even while others write, and it works on a read-only connection.
- A view exports its CREATE VIEW statement in SQL mode but no INSERT statements; CSV and JSON export its rows.
- CSV always has a header row, writes NULL as an empty field and an empty string as "", and puts an apostrophe in front of text that starts with = + or @ so Excel does not run it as a formula. BLOB and BINARY columns come out as 0x hex in CSV and JSON, and as X'...' literals in SQL.
Export with SQL, the mysql client and mysqldump
MySQL gives you three ways out. writes a CSV or text file, but on the database server's disk. The client runs any query and prints the result, so a redirect saves it on your own machine. writes SQL that rebuilds tables, views, triggers and routines, and is the tool for backups and moving a database.
mysqldump -u root -p --single-transaction saas_app > saas_app.sql
The examples use the 1bench demo database, , with , , and . Every command was run on MySQL 8.4.9 LTS and 9.7.4, and behaves the same on both unless a section says otherwise.
Export to CSV with SELECT INTO OUTFILE
Add and the field options at the end of any SELECT. quotes only text and date values; plain quotes numbers too.
Example: shipped orders as CSV.
SELECT id, status, total_amount, notes, created_at
FROM orders
WHERE status = 'shipped'
ORDER BY id
INTO OUTFILE '/var/lib/mysql-files/shipped.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
The file starts . NULL is written as , which reads back as NULL but a spreadsheet shows as text. Wrap a column in if you want empty fields instead.
Three errors stop most first attempts. A path outside the allowed folder fails, even for root:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Run and write inside that folder. An empty value allows any folder and disables file export altogether; both are set in the server config, not per session. A user without the global FILE privilege gets , and granting ALL on one database does not include it. And MySQL never overwrites: running the same export twice gives .
Export to CSV with headers
INTO OUTFILE writes no header row. The usual fix is a with the column names as the first row and the real query in a subquery after it.
SELECT 'id', 'name', 'email'
UNION ALL
SELECT id, name, email FROM (SELECT id, name, email FROM users ORDER BY id) u
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
The header row makes every column text, so the ids are quoted too (). CSV readers do not mind.
Export a query from the command line
The client writes results to standard output, so a redirect saves them wherever you run it, with no FILE privilege needed. When the output is not a terminal it switches to batch mode: tab-separated, with a header row. forces it, and drops the header.
mysql -u demo -p -B -e "SELECT id, name, email FROM users ORDER BY id" saas_app > users.tsv
Batch mode is not quite CSV. NULL comes out as the word , the same as a text value , and a newline or tab inside a value is written as or . turns that escaping off. There is also no flag; fails with .
Swapping tabs for commas with is the common shortcut, and it breaks on the first value that holds a comma: turns into two columns. For real CSV from the client, quote the text columns in SQL and print with :
mysql -u demo -p --raw -N -e "
SELECT CONCAT_WS(',', id, CONCAT('\"', REPLACE(IFNULL(name, ''), '\"', '\"\"'), '\"'), country)
FROM users ORDER BY id" saas_app > users.csv
Keep the . skips NULL arguments without a trace, so a row with a NULL loses a column and every value after it shifts left.
Export all tables to CSV
There is no single statement for it. Loop over the base tables from in your own shell and write one file per table. saves typing the password for every table:
export MYSQL_PWD='demo1bench'
for t in $(mysql -u demo -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'saas_app' AND TABLE_TYPE = 'BASE TABLE'"); do
mysql -u demo -B -e "SELECT * FROM \`$t\`" saas_app > "$t.tsv"
done
does the same on the server side: one file with the CREATE TABLE and one data file per table. The data files go through INTO OUTFILE, so the folder must be the secure_file_priv one and you need FILE, or it fails with .
mysqldump -u root -p --tab=/var/lib/mysql-files/export \
--fields-terminated-by=, --fields-optionally-enclosed-by='"' saas_app
Export a database with mysqldump
writes one database as SQL: a and for each table, multi-row INSERTs and the table's triggers, then the views at the end. reads InnoDB tables from one snapshot without locking them. Pipe through gzip for a smaller file; the demo database went from 107 KB to 36 KB.
mysqldump -u root -p --single-transaction --routines --events saas_app > saas_app.sql
mysqldump -u root -p --single-transaction saas_app | gzip > saas_app.sql.gz
Load a dump into an existing database with the mysql client:
mysql -u root -p saas_app_copy < saas_app.sql
gunzip < saas_app.sql.gz | mysql -u root -p saas_app_copy
dumps several databases and adds and lines, so the file restores without naming a target. dumps every database on the server, including the system schema.
Dump one table or some rows
List table names after the database. filters the rows, and works on every table you list.
mysqldump -u root -p saas_app orders order_items > orders.sql
mysqldump -u root -p --no-create-info --where="status = 'shipped'" saas_app orders > shipped.sql
A wrong name stops the dump with and exit status 6. A single table's dump includes its triggers but not the tables its foreign keys point to; the file switches off, so it loads anyway and leaves the references unchecked.
Schema only or data only
() writes the CREATE TABLE and CREATE VIEW statements and the triggers, with no rows. () writes only the INSERTs, for tables that already exist.
mysqldump -u root -p --no-data --routines --events saas_app > schema.sql
mysqldump -u root -p --no-create-info --skip-triggers saas_app > data.sql
Triggers are in the dump by default ( leaves them out). Stored procedures, functions and events are not: without and they are silently missing from the file, schema-only or not. Views are always included.
Export to JSON
builds one object per row, which gives you NDJSON. wraps the rows into one JSON array. Print with and :
mysql -u demo -p --raw -N -e "SELECT JSON_OBJECT('id', id, 'name', name, 'country', country) FROM users ORDER BY id" saas_app > users.ndjson
mysql -u demo -p --raw -N -e "SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) FROM (SELECT id, name FROM users ORDER BY id) u" saas_app > users.json
Leave out and the client escapes every backslash a second time: becomes and the file no longer parses. INTO OUTFILE does the same unless you add .
More traps from the test runs:
- is a syntax error (ERROR 1064) in MySQL. Feeding it from a sorted subquery, as above, kept the order in every test run, but MySQL does not promise it.
- Over zero rows, returns NULL, which the client prints as . Use to get .
- sorts the keys (shortest first, then alphabetically), so comes out as only by luck. Do not rely on key order.
- A column nests as an object, but a BIGINT such as 9223372036854775000 loses precision when JavaScript parses it. Cast it with when the file goes to a browser or Node.
Export users and privileges
skips user accounts unless you ask, and the two versions differ here. On MySQL 8.4, print each account's statements yourself:
SHOW CREATE USER 'demo'@'%';
SHOW GRANTS FOR 'demo'@'%';
Each returns a statement you can run on the new server, with the password as a hash. mysqldump 9.x adds , which writes and for every account, and to drop each one first. The 9.7 client also works against an 8.4 server.
mysqldump -u root -p --all-databases --users --single-transaction > full.sql
The list includes and the system accounts such as @, which already exist on the target. Delete those lines, or use knowing it replaces the target's root.
mysqldump errors and warnings
Most of these print while the dump still finishes with exit status 0, so check the file, not just the exit code.
- . Harmless for a normal dump; add to skip that step.
- . Only the 9.7 client prints it, against 8.4 and 9.7 servers alike, for any user without SELECT on the schema. The dump is complete.
- . A wrong password, or the account is not allowed from that host. Nothing is written.
- . The mysql:9 Docker image ran with ON, and the dump then contains . Loading it as a normal user fails with . Dump with unless you are building a replica.
One more shows up on restore. With binary logging on (the default in 8.4 and 9.x), a user without SUPER cannot create the triggers in a dump: . The tables before that point have loaded, the rest have not. Restore as an admin, set on the target, or dump with .
From a Docker container
Run mysqldump inside the container and redirect on your side. The container's client always matches the server, and keeps the password off the command line and its warning out of your terminal.
docker exec -e MYSQL_PWD=secret my-mysql mysqldump -u root --single-transaction saas_app > saas_app.sql
docker exec -i -e MYSQL_PWD=secret my-mysql mysql -u root saas_app_copy < saas_app.sql
Do not add . With a terminal attached, Docker sends the client's stderr into the same stream and ends every line with , so the file starts with and loading it fails with at line 1.