sql – MySQL dump by query

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

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"

This should work

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

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'

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.

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

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.

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.

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

mysql Export the query results command line：

mysql -h120.26.133.63 -umiyadb -proot123 miya -e "select * from user where id=1" > mydumpfile.txt