MySQL dump by query

Question

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

Hellman

Answer 1

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"

Minne

You can use the -B (--batch) switch to output as tab delimited, too. – Smith

how to restore this dumped txt file? – Quickfreeze

for those trying the mysql -e approach. I had to customize the script a bit to work for me. This one requires you to enter the sql password when run. mysql -e "select * from table WHERE query = 'asdasd'" -u root -p --database=DBNAME > text.txt – Spent

you could just create a new table for the query (CREATE TABLE SELECT), and then dump that table with mysqldump. That way you can easily restore it later. – Playwright

for who wants to use mysqldump and restore the file have a look here: https://mcmap.net/q/110904/-mysqldump-from-a-query – Ivonne

To import data that is exported with mysql -e with the -B option to output as tab-delimited, run mysqlimport --ignore-lines=1 --fields-terminated-by='\t'. See: https://mcmap.net/q/49169/-how-do-i-import-csv-file-into-a-mysql-table – Vernitavernoleninsk

If you want export the data with charset of utf8, please add this config --default-character-set=utf8 – Thermostatics

If i'm dumping only a subset of rows of table (using --where option of mysqldump and corresponding SQL query in mysql -e..) to csv file, should i expect a difference in performance between the two commands? – Sapless

Anyone know how to use backslashes \ in the where clause? need a --where="type LIKE 'Something\Something\Something' ", but doesn't produce inserts on the dump file – Diesis

Answer 2

This should work

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

Confectionary

A better example might be something that actually looks like a where clause, such as --where="myColumn < 1000" - the first million rows of every table seems like a strange thing to request ;) – Policyholder

@Policyholder If what you want to do is to take an easily reinsert-able backup of your table, you probably don't need a where clause for anything else than limits. – Confectionary

@Sagotharan: Well, it's not a query. That's probably why. – Pagepageant

!!WARNING!! mysqldump adds a 'DROP TABLE' command at the top of the exported file. That means if you do a partial export then reimport it, you'll lose everything else. Well, I did. – Imhoff

Yes, in order to not delete all the data in your table when restoring from the saved data file, make sure you add in the --no-create-info option. See my answer for an example. – Scamp

Note that this is not exactly the same as the result of a similar SELECT query: it will not see any data in views. – Rendition

Answer 3

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.

Scamp

This is exactly what I was looking for. This answer should be higher up. – Plication

This is what I always use for such use case when I need to dump only certain data - so it's very handy. – Maomaoism

Answer 4

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'

Fleabane

What if I want to dump multiple tables. – Undertook

This creates a file on the machine on which MySQL database is running. So, if you are querying from a remote console this method fails. If there is a way of doing it from a remote console as well, please let me know about it. – Fugazy

where do it save the file? – Owensby

@Fugazy I'm assuming you mean a remote console via a terminal console, in which case you can retrieve the dumped file viascp after the connection is terminated. here's an example. scp [email protected]:/tmp/querydump.csv ~/local.csv – Cartesian

It seems to be insecure: #1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement – Herisau

Answer 5

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.

Overthrow

Answer 6

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.

Lands

Answer 7

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.

Wassyngton

and you have 5gb in query select, does not work – Yahrzeit

Answer 8

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

Susannesusceptibility

Answer 9

To dump a specific table,

mysqldump -u root -p dbname -t tablename --where="id<30" > post.sql

Tripody

Answer 10

mysql Export the query results command line：

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

Saari

it is working for me in ubuntu , command: mysql -h localhost -u root -p dbname -e "select * from users where id=1;select * from staffs where id=1" --xml > /var/www/html/project/backups/db.xml – Celik

Answer 11

If you want to dump specific fields from a table this can be handy

1/ create temporary table with your query.

create table tmptable select field1, field2, field3 from mytable where filter1 and fileter2 ;

2/ dump the whole temporary table. then you have your dump file with your specific fields.

mysqldump -u user -p mydatabase tmptable > my-quick-dump.sql

Lobito

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Demarco

Answer 12

here is my mysqldump to select the same relation from different tables:

 mysqldump --defaults-file=~/.mysql/datenbank.rc -Q -t -c --hex-blob \
 --default-character-set=utf8 --where="`cat where-relation-ids-in.sql`" \
 datenbank table01 table02 table03 table04 > recovered-data.sql

where-relation-ids-in.sql:

relation_id IN (6384291, 6384068, 6383414)

~/.mysql/datenbank.rc

[client]
user=db_user
password=db_password
host=127.0.0.1

Remark: If your relation_id file is huge, the comment of the where clause will be cut in the dump file, but all data is selected correct ;-)

I hope it helps someone ;-)

Mauretania