GoAdmio

table

Extract a single table from a SQL dump file into its own .sql file, optionally under a new name

On this page: Overview · Quick start · Syntax · Options · Output · Examples · Troubleshooting · Details · See also


Overview

When you only need one table from a large MySQL/MariaDB dump, /go/table cuts it out. It reads the dump file, copies the statements for the table you name into a new .sql file, and can rename the table on the way.

The command works on the text of the file only. It does not connect to a database, and it does not import anything. To load the result, import the new file yourself (see Examples).

It is meant for dumps made by mysqldump, where each table starts with a DROP TABLE IF EXISTS statement.


Quick start

/go/table -f /root/backup.sql -t wp_users

This creates /root/wp_users.sql.


Syntax

/go/table -f <dump-file> -t <table> [-o <output-file>] [-r <new-table-name>]

The options can be given in any order.


Options

Option Required Default Notes
-f <file> Yes — The SQL dump to read. It must be a plain .sql file, not compressed.
-t <table> Yes — The table to extract. The name is case-sensitive and must match the dump.
-o <file> No /root/<table>.sql The file to create. An existing file is overwritten without a warning.
-r <name> No — Renames the table in the new file.

If -f or -t is missing, the command prints the usage line and exits with status 1.


Output

File /root/wp_users.sql created successfully.
Outcome What you get
File created The message above. Exit status 0.
-f or -t missing Usage: table [-f] SQL File Name [-t] Table Name [-o] Output File Name [-r] Rename Table (Optional). Exit status 1.
Dump file not found File <file> does not exist. Exit status 1.
Unknown option The usage line is printed, but the command carries on.

[!WARNING] The "created successfully" message does not mean the table was found. If the table name isn't in the dump, the command creates an empty file and still prints the message. Check the size of the file.

Every run is logged to /var/log/go/table.log with a timestamp and the arguments used.


Examples

# Extract wp_users to /root/wp_users.sql
/go/table -f /root/backup.sql -t wp_users
# Choose the output file
/go/table -f /root/backup.sql -t wp_users -o /tmp/wp_users.sql
# Extract the table and rename it
/go/table -f /root/backup.sql -t wp_users -r wp_users_copy
# Check that the file isn't empty, then import it into a database
ls -lh /root/wp_users.sql
mysql -u dbuser -p dbname < /root/wp_users.sql

Troubleshooting

Symptom Likely cause Fix
The usage line is shown -f or -t is missing. Pass both.
File <file> does not exist. Wrong path. Check the path.
The new file is empty The table name isn't in the dump (a typo, or a different case), or the dump has neither DROP TABLE nor TRUNCATE TABLE statements. Find the real name with grep -n "CREATE TABLE" <dump-file>.
The new file is empty when you run as a normal user The dump can only be read by root, so the check for DROP TABLE fails. Run the command as root or with sudo.
The command can't read the dump The file is compressed (.sql.gz). Decompress it first with gunzip.
Syntax error near ADD PRIMARY KEY or similar when importing The dump has multi-line ALTER TABLE statements (phpMyAdmin dumps often do). The command deletes only the first line of each. Export that table again with mysqldump, or use copytable.
Table doesn't exist when importing The dump was in TRUNCATE style, so the new file has data only. Create the table first. See Dump styles.
Foreign keys, triggers, or views still use the old name -r renames only the table's own DROP, CREATE, and INSERT statements. Edit those references by hand.
The new file has extra lines at the end For the last table in the dump, the command copies to the end of the file. Remove the trailing lines if you don't need them.

Details

Dump styles

The command decides how to work by looking at the dump:

Dump contains Mode The new file holds
DROP TABLE anywhere Drop mode DROP TABLE IF EXISTS, CREATE TABLE, and INSERT statements. You can import it into an empty database.
No DROP TABLE Truncate mode TRUNCATE TABLE and INSERT statements only. The table must already exist.

How it works

flowchart TD
    A["/go/table -f dump -t table"] --> B{"-f and -t given, file exists?"}
    B -- "No" --> X["Usage or error, exit 1"]
    B -- "Yes" --> C{"Dump contains DROP TABLE?"}
    C -- "Yes" --> D["Copy from DROP TABLE IF EXISTS table to the next DROP TABLE"]
    C -- "No" --> E["Copy from TRUNCATE TABLE table to the next TRUNCATE TABLE"]
    D --> F["Remove the next table's line, ALTER TABLE, LOCK TABLES, and 'Table structure' lines"]
    E --> F
    F --> G{"-r given?"}
    G -- "Yes" --> H["Rename the table in the statements"]
    G -- "No" --> I["Done"]
    H --> I
  1. Copies every line from the table's DROP TABLE IF EXISTS (or TRUNCATE TABLE) line up to the next table's matching line.
  2. Removes the next table's line, so only your table remains. Its own DROP or TRUNCATE line is kept.
  3. Removes all lines containing ALTER TABLE (which includes the DISABLE KEYS and ENABLE KEYS lines mysqldump writes), LOCK TABLES, and UNLOCK TABLES, and the Table structure comment.
  4. With -r, renames the table in the DROP TABLE, CREATE TABLE, TRUNCATE TABLE, and INSERT INTO statements and in the table comments.

Removing the lock lines means the import runs without table locks and without disabled keys, so it can be slower on large tables.

Requirements

Item Used for
sed, grep Reading and editing the dump.
Root access Reading dumps that only root can read.
/var/log/go/table.log The command log.

No database access is needed.


See also

Command Description
import Import a SQL dump into a MySQL/MariaDB database
sql Create a MySQL database and import an SQL file into it
copytable Copy a MySQL/MariaDB table from one server to another
csv Export a MySQL/MariaDB table to a CSV file
dbls List all databases and their sizes
repair Repair a MySQL database