How to open a SQL dump on a Mac
A SQL dump is a plain-text file of CREATE TABLE and INSERT statements, so the free tools already on your Mac can read it: less pages through it, grep finds the table you need, and if the goal is running queries, restoring into a local server is the real answer. The catch is the file's shape: mysqldump packs many rows into each INSERT by default, and single lines that run to megabytes are the shape many editors handle worst. Caxton was built for exactly that file.
Download Caxton for Free · 7 days, no card · 4 MB · macOS 13.0+
What is actually in a .sql file?
Text, and nothing but. A dump from mysqldump or pg_dump in plain format is a script: table definitions, then data as INSERT statements, in the order a server would replay them. PostgreSQL's documentation describes pg_dump's plain format as exactly that, a script of SQL commands. Nothing about the format needs a database installed to read it.
What makes the file awkward is a documented default. mysqldump writes extended INSERT statements, many rows per statement, which its manual documents as producing a smaller dump file and faster inserts on reload. Good for the server, hard on editors: a few thousand rows per statement means single lines measured in megabytes, and a tool that assumes lines are short stalls laying out the very first screen.
What can I do free, right now?
Terminal answers the first questions at any size:
head -c 2000 dump.sql # what dialect, what charset, what shape
grep -n 'CREATE TABLE' dump.sql # every table, with its line number
grep -c 'INSERT INTO' dump.sql # how much data follows
less dump.sql # page through it, freeAnd if the point is querying the data, do the real thing: restore it locally, free, with the vendor's own tools.
mysql -u root -p mydb < dump.sql # MySQL / MariaDB
psql -d mydb -f dump.sql # PostgreSQLThat is the honest free path, and for a clean dump it is the whole answer. It stops being the answer when the restore fails partway: a DEFINER clause naming an account that does not exist on your machine, a charset line from a different server, a table you never wanted in a file you cannot open to remove it. Now the job is reading and editing the dump itself, and less can only show it to you one screen at a time.
How do I open and edit the dump itself?
Caxton opens the file memory-mapped, so a multi-gigabyte dump opens immediately and size is bounded by your disk, not your RAM. Long lines are the reason this page exists, and they are a first-class case: lines are rendered in segments, so a megabyte-long INSERT scrolls like any other text, and on the published benchmark (measured on an M1 Max with 64 GB RAM; methodology on the benchmarks page) a literal find in a 500 MB single-line file returns in 0.5 s. Search at log scale is measured on the 10 GB log corpus: 1,613,344 matches in 1.3 s, counted exactly.
Search at dump scale: the 10 GB benchmark log with 1,613,344 matches counted in 1.3 s. The counter is exact, not an estimate.
The pre-restore edits are ordinary text edits here: search DEFINER and delete the clauses, fix the SET NAMES line, select a table's block and cut it. Find and replace shows the match count before anything is touched, and a whole mapping of renames can run as one Batch Replace pass. When you save, the write is atomic: the dump on disk is never left half-written, which matters for the file you are about to feed a database.
Megabyte-long INSERT lines, opened like any other file.
Download Caxton for Free7 days free, no credit card · 4 MB · macOS 13.0+ · notarized
Which approach fits which job?
| Approach | Good for | Breaks when |
|---|---|---|
head / grep / less | First look, finding tables, counting statements, free | You need to read or change a megabyte-long line, not just locate it |
Restore locally (mysql, psql) | Actually querying the data, with the vendor's own free tools | The restore fails partway and the fix is an edit to the dump itself |
| Caxton | Reading, searching, and editing the dump as a file, long lines included | You want to run SQL against the data (restore it; that is what a dump is for) |
Frequently asked questions
Can I open a .sql dump without installing MySQL?
Yes. A dump is plain SQL text, so any tool that can handle the file's size reads it: less pages through it free in Terminal, and Caxton opens it memory-mapped for searching and editing. A database server is only needed to run queries against the data, and for that a local restore is the right answer.
Why does my editor freeze on a SQL dump smaller than my RAM?
Usually the lines, not the megabytes. mysqldump packs many rows into each INSERT statement by default, which its manual documents as producing a smaller dump and faster reloads, so a modest dump can contain single lines that run to megabytes. Editors that assume short lines stall laying them out. Caxton renders long lines in segments; its published benchmark finds text in a 500 MB single-line file in 0.5 s.
Can I edit a SQL dump before restoring it?
Yes, it is text, and pre-restore edits are routine: removing a DEFINER clause that names an account your machine does not have, adjusting a charset or collation line, or cutting a table you do not want. Caxton edits the file directly and saves atomically, so the dump is never left half-written.
How do I find one table in a multi-gigabyte dump?
Free: grep -n 'CREATE TABLE' dump.sql prints every table with its line number, and less jumps to a line with the g command. In Caxton the same search is live in the editor: on the published 10 GB log benchmark, a literal search returns 1,613,344 matches in 1.3 s with an exact count, and each match is clickable in place.
Sources
- MySQL Reference Manual: mysqldump
- PostgreSQL documentation: pg_dump
- Greenwood Software: less home page and manual
- Caxton benchmark methodology and results
If the restore just failed at line four million, the fix is a text edit. Download Caxton for Free, make it, and run the restore again: 7 days free, no card.