sql – MySQL dump by query

The Question :

232 people think this question is useful

Is it possible to do mysqldump by single SQL query?

I mean to dump the whole database, like phpmyadmin does when you do export to SQL

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

Update: 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

Notes:

  • Replace mydatabase, 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 --no-create-info option.
  • You may need to add the appropriate -h, -u, and -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.

Cheers, WB

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:

enter image description here

Then choose “SQL INSERT statements (*.sql)” in the list.

enter image description here

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 -h120.26.133.63 -umiyadb -proot123 miya -e "select * from user where id=1" > mydumpfile.txt

Add a Comment