Mysqldump - How to restore a single table from this file?

Question

I need to restore just a single table in my database.

I have a .sql file that has all the info I need for one table but the rest would overwrite important information for other tables.

Instead of using the solution here - using a tool I've never heard of, I figured it would be more sure fire to do it manually.

Unfortunately, the MySqlDump generated a GIANT insert line too long to paste into mysql command line...

What should I do?

Should I use sed like the link above describes?

Or could I copy paste the commands for that specific table from the mysqldump.sql into a new .sql file then call:

mysql -u root -p -h localhost < copyPasteFile.sql

@Matt On a windows computer can I use grep or sed? I was reading that they are UNIX based? — Kellen Stuart, Feb 25 '16 at 03:22
mysql -u root -p databasename -h localhost < copyPasteFile.sql — Rizki Wahyu, Feb 25 '16 at 03:28
@RizkiWahyu Post an answer with that and I'll accept it. This was by far the easiest way to do it. — Kellen Stuart, Feb 25 '16 at 03:50

score 2 · Accepted Answer · answered Feb 25 '16 at 09:34

2

u can try it

mysql -u root -p databasename -h localhost < copyPasteFile.sql

answered Feb 25 '16 at 09:34

Rizki Wahyu

118
9

Nick · Answer 2 · 2016-02-25T03:46:25.120

I am not sure if its the best way but I just spin up a new schema and restore the backup in to it, Dump the data I need and import that in to the production database.

I use the MYSQL work bench which makes it easier but the below steps can probably be reproduced at the command line

Create new MYSQL Schema
Import self-contained backup
Set "Target Schema" to the new empty database (.sql Dump must not contain schema creation code)
Restore backup
Dump table to csv and import it in to the production live database
Delete Restore Schema

To restore to a schema at the command line it looks like you use:

mysql -u <user name> -p <password> <database name> < sqlfilename.sql

Could you perhaps elaborate more on this? – Kellen Stuart Feb 25 '16 at 03:24 — Kellen Stuart, Feb 25 '16 at 03:24

useless · Answer 3 · 2016-02-25T03:37:47.093

mysql -uuser -ppassword -e "create database temporary"

mysql -uuser -ppassword temporary < copyPasteFile.sql

mysqldump -uuser -ppassword temporary yourtable > onlythattable.sql

mysql -uuser -ppassword therealdb < onlythattable.sql

mysql -uuser -ppassword -e "drop database temporary"

make sure that copyPasteFile.sql does not have a "use somedatabase;" if it was exported with phpmyadmin, it probably has that, if it was exported with mysqldump it wont.

Mysqldump - How to restore a single table from this file?

3 Answers3