3

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.?

Manny
  • 1,407
  • 2
  • 11
  • 15

3 Answers3

2

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.

Community
  • 1
  • 1
  • Thanks Thomas. I tried to restore the dump into a new database but it failed as the dumpfile doesn't contain create table statmet. I've found this thread http://stackoverflow.com/questions/16735344/mysql-import-database-but-ignore-specific-table and running sed except on the table I want to restore. The database has about 200 tables and sed is running for a couple of hours.. – Manny May 02 '15 at 16:17
  • I was unable to restore the dump into a new database. The mysql restore returned an error that the database table not found. I think it is becuase `--no-create-info` option used in the mysqldump command. The sed did not finish even after 12 hours. grep for the table name also didn't help. I had to split the large dump file into several small files to open in vi editor. The table doesn't seem to be in the dump. I see till the table `sd_fileuploads`(which is one table before) and the rest of the tables starting from `sd_images` are not found in the dump file. I wonder why they're not in the dump – Manny May 03 '15 at 23:24
0

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.

Manny
  • 1,407
  • 2
  • 11
  • 15
0

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)"
D.Y.
  • 149
  • 2
  • 7