The Question :
232 people think this question is useful
Is it possible to do
mysqldump by single
I mean to dump the whole database, like
phpmyadmin does when you do export to
The Question Comments :
The Answer 1
303 people think this answer is useful
not mysqldump, but mysql cli…
mysql -e "select * from myTable" -u myuser -pxxxxxxxxx mydatabase
you can redirect it out to a file if you want :
mysql -e "select * from myTable" -u myuser -pxxxxxxxx mydatabase > mydumpfile.txt
Original post asked if he could dump from the database by query. What he asked and what he meant were different. He really wanted to just mysqldump all tables.
mysqldump --tables myTable --where="id < 1000"
The Answer 2
266 people think this answer is useful
This should work
mysqldump --databases X --tables Y --where="1 limit 1000000"
The Answer 3
76 people think this answer is useful
You can dump a query as csv like this:
SELECT * from myTable
INTO OUTFILE '/tmp/querydump.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
The Answer 4
71 people think this answer is useful
Dump a table using a where query:
mysqldump mydatabase mytable --where="mycolumn = myvalue" --no-create-info > data.sql
Dump an entire table:
mysqldump mydatabase mytable > data.sql
mytable, and the where statement with your desired values.
- By default,
mysqldump will include
DROP TABLE and
CREATE TABLE statements in its output. Therefore, if you wish to not delete all the data in your table when restoring from the saved data file, make sure you use the
- You may need to add the appropriate
-p options to the example commands above in order to specify your desired database host, user, and password, respectively.
The Answer 5
39 people think this answer is useful
You could use –where option on mysqldump to produce an output that you are waiting for:
mysqldump -u root -p test t1 --where="1=1 limit 100" > arquivo.sql
At most 100 rows from test.t1 will be dumped from database table.
The Answer 6
10 people think this answer is useful
If you want to export your last n amount of records into a file, you can run the following:
mysqldump -u user -p -h localhost --where "1=1 ORDER BY id DESC LIMIT 100" database table > export_file.sql
The above will save the last 100 records into export_file.sql, assuming the table you’re exporting from has an auto-incremented id column.
You will need to alter the user, localhost, database and table values. You may optionally alter the id column and export file name.
The Answer 7
6 people think this answer is useful
MySQL Workbench also has this feature neatly in the GUI. Simply run a query, click the save icon next to Export/Import:
Then choose “SQL INSERT statements (*.sql)” in the list.
Enter a name, click save, confirm the table name and you will have your dump file.
The Answer 8
3 people think this answer is useful
Combining much of above here is my real practical example, selecting records based on both meterid & timestamp. I have needed this command for years. Executes really quickly.
mysqldump -uuser -ppassword main_dbo trHourly --where="MeterID =5406 AND TIMESTAMP<'2014-10-13 05:00:00'" --no-create-info --skip-extended-insert | grep '^INSERT' > 5406.sql
The Answer 9
0 people think this answer is useful
mysql Export the query results command line：
mysql -h18.104.22.168 -umiyadb -proot123 miya -e "select * from user where id=1" > mydumpfile.txt