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
Add a comment |
12 Answers
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" 
9 Comments
Vivek S
how to restore this dumped txt file?
Richard Dev
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
quano
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.
Syco
for who wants to use mysqldump and restore the file have a look here: stackoverflow.com/a/2431936/411786
Kevin Borders
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: stackoverflow.com/a/17071108/1676044
|
This should work
mysqldump --databases X --tables Y --where="1 limit 1000000" 6 Comments
ijw
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 ;)
Thomas Ahle
@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.
Lightness Races in Orbit
@Sagotharan: Well, it's not a query. That's probably why.
Tamlyn
!!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.
|
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,
mysqldumpwill includeDROP TABLEandCREATE TABLEstatements 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-infooption. - You may need to add the appropriate
-h,-u, and-poptions to the example commands above in order to specify your desired database host, user, and password, respectively.
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' 
5 Comments
SIFE
What if I want to dump multiple tables.
dknight
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.
Techlord
where do it save the file?
Luke
@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 [email protected]:/tmp/querydump.csv ~/local.csv
mikep
It seems to be insecure: #1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
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.
Comments
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.
Comments
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.
1 Comment
Jeferson Guedes
and you have 5gb in query select, does not work
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 
Comments
To dump a specific table,
mysqldump -u root -p dbname -t tablename --where="id<30" > post.sql 
Comments
mysql Export the query results command line：
mysql -h120.26.133.63 -umiyadb -proot123 miya -e "select * from user where id=1" > mydumpfile.txt 
1 Comment
Ramesh
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
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 
1 Comment
Community
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.
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 ;-)
