Coder Perfect

Query by query, MySQL dump


Is it possible to perform mysqldump with just one SQL query?

I’m referring to a full database dump, similar to what phpmyadmin generates when you export to SQL.

Asked by Jakub Arnold

Solution #1

mysql cli, not mysqldump…

mysql -e "select * from myTable" -u myuser -pxxxxxxxxx mydatabase

If you want, you can route it to a file:

mysql -e "select * from myTable" -u myuser -pxxxxxxxx mydatabase > mydumpfile.txt

Update: The original post requested if he could query the database and dump data from it. What he requested and what he meant were two very different things. He was adamant about mysqldumping all tables.

mysqldump --tables myTable --where="id < 1000"

Answered by Zak

Solution #2

This should work

mysqldump --databases X --tables Y --where="1 limit 1000000"

Answered by Thomas Ahle

Solution #3

Use a where query to dump a table:

mysqldump mydatabase mytable --where="mycolumn = myvalue" --no-create-info > data.sql

Dump a table in its entirety:

mysqldump mydatabase mytable > data.sql


Answered by Gary

Solution #4

You can save a query as a csv file in the following format:

SELECT * from myTable
INTO OUTFILE '/tmp/querydump.csv'

Answered by Guy

Solution #5

You might use the —where option on mysqldump to generate the output you want:

mysqldump -u root -p test t1 --where="1=1 limit 100" > arquivo.sql

A maximum of 100 rows from the exam. The database table t1 will be emptied.

Answered by Wagner Bianchi

Post is based on