CODEWITHSUNDEEP
mysqlsqldump
black-room-boy
black-room-boy

Reputation: 659

How to export mysql query result so I can import it back again

I want to dump data from my table by certain criteria, here is the sql query:

"SELECT * FROM document WHERE date BETWEEN 20160101 AND 20160131"

Table that I am selecting from is MyISAM Merge.

I have deleted rows that are matching that query, and I want to import them back from backup, but only those rows.

I have tried making dump like this:

mysql -uroot -proot mydb -e "SELECT * INTO OUTFILE '/tmp/doc.sql' from document WHERE date BETWEEN 20160101 AND 20160131".

But it will generate a file that can not be imported by phpmyadmin import tool. And yet again PMA does not want to do any export of this query result.

Any help ? I can not post structure of the real database/table because of NDA.

EDIT: for all that are having trouble dumping data from MyISAM MRG table, you need to dump from the subtable, not the main one.

Upvotes: 2

Views: 2111

Answers (2)

Maxi Schvindt
Maxi Schvindt

Reputation: 1462

mysqldump -uroot -proot mydb document --where="date BETWEEN 20160101 AND 20160131" > dump.sql

mysql -uroot -proot mydb < dump.sql

Upvotes: 4

cn0047
cn0047

Reputation: 17091

If be honest i don't use phpmyadmin at all and phpmyadmin import tool, but i know how it can be done in shell:

1st step - export dump:

mysqldump -uUSER -pPASS \
--databases DB_NAME --tables document \
--where 'date BETWEEN 20160101 AND 20160131' \
--no-create-info > document.dump.sql

2nd step - import dump:

mysql -uUSER -pPASS -DDB_NAME < document.dump.sql

Suppose you have access to shell where db is located and i hope it'll be helpful for you...

Upvotes: 5

Related Questions