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

Zak · Accepted Answer · 2014-02-11T11:11:10.520

340

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"

edited Feb 11 '14 at 11:11

answered Jun 01 '09 at 17:35

Zak

24,947
11
38
68

You can use the -B (--batch) switch to output as tab delimited, too. – jonstjohn Jun 01 '09 at 17:46
32

how to restore this dumped txt file? – Vivek S Jun 03 '11 at 03:51
3

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 – Richard Dev Mar 07 '12 at 15:04
17

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. – quano Apr 05 '12 at 23:08
1

for who wants to use mysqldump and restore the file have a look here: http://stackoverflow.com/a/2431936/411786 – Syco May 21 '13 at 13:05
5

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: http://stackoverflow.com/a/17071108/1676044 – Kevin Borders Mar 25 '14 at 15:05
If you want export the data with charset of utf8, please add this config `--default-character-set=utf8` – Ayano Jan 04 '18 at 07:35
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? – y2k-shubham Apr 13 '18 at 11:41
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 – droplet Nov 18 '21 at 14:50

Thomas Ahle · Answer 2 · 2012-01-14T23:42:34.870

280

This should work

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

edited Jan 14 '12 at 23:42

answered Feb 18 '10 at 14:57

Thomas Ahle

30,774
21
92
114

8

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 ;) – ijw Jan 04 '11 at 19:59
@ijw 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. – Thomas Ahle Feb 18 '11 at 14:53
3

@Sagotharan: Well, it's not a query. That's probably why. – Lightness Races in Orbit Aug 16 '11 at 10:33
91

!!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. – Tamlyn Sep 27 '12 at 18:34
42

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](http://stackoverflow.com/a/15058427/1054260) for an example. – Gary Dec 05 '13 at 18:28
Note that this is not exactly the same as the result of a similar SELECT query: it will not see any data in views. – Tgr Aug 03 '19 at 15:02

score 97 · Answer 3 · answered Feb 25 '13 at 00:29

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.

This is exactly what I was looking for. This answer should be higher up. — musicin3d, Mar 08 '21 at 21:59
This is what I always use for such use case when I need to dump only certain data - so it's very handy. — stamster, Oct 15 '21 at 08:54

score 79 · Answer 4 · edited Jan 01 '14 at 03:48

79

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'

edited Jan 01 '14 at 03:48

Eric Leschinski

146,994
96
417
335

answered Apr 28 '10 at 10:24

Guy

791
5
2

10

What if I want to dump multiple tables. – SIFE May 06 '11 at 15:01
14

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. – dknight Aug 13 '12 at 16:34
2

where do it save the file? – Techlord Aug 16 '13 at 06:47
1

@dknight I'm assuming you mean a remote console via a terminal console, in which case you can retrieve the dumped file via`scp` after the connection is terminated. here's an example. `scp user@remote-server.com:/tmp/querydump.csv ~/local.csv` – Luke Aug 02 '17 at 09:53
1

It seems to be insecure: #1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement – mikep Nov 30 '18 at 15:41

score 42 · Answer 5 · edited Jan 18 '22 at 17:09

42

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.

edited Jan 18 '22 at 17:09

GabrielOshiro

7,986
4
45
57

answered Aug 19 '11 at 18:06

Wagner Bianchi

421
4
2

score 14 · Answer 6 · answered Mar 03 '18 at 01:34

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.

score 10 · Answer 7 · answered Feb 07 '17 at 03:00

10

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.

answered Feb 07 '17 at 03:00

MPelletier

16,256
15
86
137

and you have 5gb in query select, does not work – Jeferson Guedes May 27 '23 at 01:07

score 3 · Answer 8 · answered Oct 20 '14 at 10:01

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

score 0 · Answer 9 · edited May 29 '17 at 12:26

0

mysql Export the query results command line：

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

edited May 29 '17 at 12:26

Kumar Rakesh

2,718
2
18
39

answered Nov 11 '16 at 08:50

lanni654321

1,019
11
7

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 – Ramesh Mar 05 '19 at 06:06

score 0 · Answer 10 · answered Oct 12 '21 at 12:33

0

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

answered Oct 12 '21 at 12:33

Ahmed Micky

11
3

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](/help/how-to-answer). – Community Oct 12 '21 at 13:17

score 0 · Answer 11 · answered Jan 09 '22 at 10:57

0

To dump a specific table,

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

answered Jan 09 '22 at 10:57

Praveen Kumar

849
8
8

score 0 · Answer 12 · answered Mar 18 '22 at 09:44

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 ;-)

MySQL dump by query

12 Answers12

Linked