How to restore a specific table from mysqldump

Question

I have a mysqldump file which was taken using the following command. mysqldump -u root --password=<passwd> 'gss-app' table1 table2 ... tableN --skip-triggers --skip-add-drop-table --skip-lock-tables --compact --no-create-info --quick | bzip2 -c > /var/backups/gss-app.sql.bz2

I have decompressed the dump and want to restore a table named sd_images. As the create table and drop table statements are skipped in the command used the dump file starts INSERT INTO table_name for each table. Could anyone please help me to restore only sd_images table into my database.?

Answer 1

To restore only sd_images column please use execute parameter with INSERT

mysql --user=root --password="<passwd>" --database=database --execute="INSERT INTO table_name SELECT * FROM table_name ON DUPLICATE KEY UPDATE sd_images = VALUES(sd_images)"

Answer 2

I took a backup of the database structure only using

mysqldump -u root -p<password> --no-data gss-app > gss-app-structureonly.sql

and then restored the structure to a new database 'restore'.

After that I restored the huge mysql dump on the new database 'restore' and I could see the data in the new database. But still the old dump is missing few tables. I ran a full backup again by removing all the --skip options. Hope it will be fine hereafter. Thanks.

Answer 3

There is already a stackoverflow answer for this Can I restore a single table from a full mysql mysqldump file?

My preferred approach would be to load the entire dumpfile into a new database, then copy just the table you want into your target database rather than use sed on the dumpfile. But that's just me.