CODEWITHSUNDEEP
phpdatabasemysqlsql-insert
Kellen Stuart
Kellen Stuart

Reputation: 8893

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

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

Upvotes: 4

Views: 4806

Answers (3)

Rizki Wahyu
Rizki Wahyu

Reputation: 118

u can try it

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

Upvotes: 2

Nick
Nick

Reputation: 1863

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

  1. Create new MYSQL Schema
  2. Import self-contained backup
  3. Set "Target Schema" to the new empty database (.sql Dump must not contain schema creation code)
  4. Restore backup
  5. Dump table to csv and import it in to the production live database
  6. 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

Upvotes: 0

useless
useless

Reputation: 1906

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.

Upvotes: 0

Related Questions