Problem
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
Notes:
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'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
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 https://stackoverflow.com/questions/935556/mysql-dump-by-query