Extract one table from a mysqldump file
A full mysqldump of a database can be tens of gigabytes, and often you need one table from it: to restore it, to check its data or to send it to someone. BigText opens the dump at once without loading it into memory, lists its tables in one pass and writes the one you need to its own file, with what it needs to restore.
Step by step
Open the dump
Open the .sql file with File → Open or by dragging it onto the window. A .sql.gz dump opens directly too.
List the tables
File → SQL dump tables… reads the dump once, about a gigabyte a second, and lists its tables with their rows and sizes, in the dump's order or largest first. Type part of a name in the filter to find a table among hundreds.
Check the table
Click a table to go to its CREATE TABLE statement and see its columns.
Save it to a file
Right-click the table and choose Save this table as… The file gets the table's DROP and CREATE statements, its rows, keys, indexes and triggers, and the dump's opening and closing settings, so it restores on its own:
mysql shop < orders.sql.
Good to know
- Several tables: check them in the list and press Save the checked tables as one file… To look at a table without saving it, use Open this table in a new tab.
- Split into a file per table… writes a file for each table, one for what comes before the tables and one for the views, routines and foreign keys after them. In the order of their names they restore the whole dump:
for f in shop.*.sql; do mysql shop < "$f"; done. - pg_dump files in plain format and SQLite
.dumpfiles work the same way. In scripts:bigtext-cli sqldump shop.sql extract orders -o orders.sql.