mysqldump -u root -p --single-transaction shop orders --result-file=orders.sql dumps one table out of a database, and mysql shop < orders.sql puts it back without touching any other table. Two things the short answer hides: the dump begins with DROP TABLE IF EXISTS, and --where lets you dump only the rows you care about.
1. Dump one table
$ clai dump the orders table from the shop database→ mysqldump -h my8 -uroot -p --single-transaction shop orders --result-file=orders.sqlThe database name comes first, then the tables, space separated. --single-transaction takes an InnoDB dump without locking — on a live database that flag is not optional. --result-file instead of > keeps line endings intact and stops error text ending up inside your dump.
2. What such a dump contains
$ clai show the meaningful lines of the dump→ grep -E 'DROP TABLE|CREATE TABLE|INSERT INTO' orders.sqlDROP TABLE IF EXISTS `orders`;CREATE TABLE `orders` (INSERT INTO `orders` VALUES (1,'acme',120.50,'2026-08-01'),(2,'globex'Here is the part nobody mentions. The very first statement is DROP TABLE IF EXISTS. Restoring this file doesn't merge data into the table — it replaces the table with its state at dump time.
3. Structure only, no rows
$ clai dump only the structure of the orders table→ mysqldump -h my8 -uroot -p --no-data shop orders --result-file=schema.sql--no-data keeps CREATE TABLE and drops every INSERT — verified, the file contains zero insert statements. Useful for diffing the schema on production against staging.
4. Dump only the rows you need
$ clai dump only the orders from august→ mysqldump -h my8 -uroot -p --single-transaction --where="created >= '2026-08-01'" shop orders --result-file=august.sqlINSERT INTO `orders` VALUES (1,'acme',120.50,'2026-08-01'),(3,'initech',45.20,'2026-08-10');Two rows of three made it in — the July order was filtered out. The line above is the INSERT from the resulting file. This is how you take surgical extracts: by date, by customer, by id range.
5. Restore one table into a live database
$ clai restore the orders table from the dump→ mysql -h my8 -uroot -p shop < orders.sqlVerified against a live database: orders had 2 rows before the restore and 3 after, while the neighbouring customers table stayed exactly as it was. That is how you roll back one table without disturbing the rest.
6. The same dump into a different database
$ clai load the table dump into the shop_copy database→ mysql -h my8 -uroot -p shop_copy < orders.sqlA single-table dump contains no CREATE DATABASE and no USE, so the target is chosen by the argument you give mysql. Verified: shop_copy ended up with an orders table holding all three rows.
Gotchas
- A single-table dump starts with DROP TABLE. Anything written to that table after the dump was taken disappears on restore. When you truly want to append, dump with
--no-create-infoinstead. --no-create-inforuns into the primary key. Verified: loading such a file over existing rows givesERROR 1062 (23000) at line 24: Duplicate entry '1' for key 'orders.PRIMARY'. Appending only works for new rows — clear the table first, or add--insert-ignoreor--replace.- Without
--single-transactiona dump locks the table. On MyISAM that's unavoidable; on InnoDB it's a self-inflicted outage in the middle of the day.
Related questions
How do I dump several tables? List them: mysqldump shop orders customers. The inverse is --ignore-table=shop.logs, which excludes one.
How do I compress the dump on the fly? mysqldump … | gzip > dump.sql.gz, and restore with zcat dump.sql.gz | mysql shop. This doesn't combine with --result-file, which writes the file itself.
How does mysqldump compare to mysqlpump and mydumper? Both competitors dump in parallel and win on large databases, but mysqldump is everywhere and its output is readable by a human.
See also
- pg_dump and pg_restore without the version trap
- Docker logs taking up disk space
- Verify a download: sha256sum -c, then the signature
CliAI writes the dump line with the flags the situation needs and shows it before it touches the database. Install it in one line.