1

I am doing regular backups of our whole database, the file has about 200MB. From this big file, I need to import only one table to our database. Because I think it wouldn't be necessary to truncate all tables and then import data to all of the tables, I am looking for a way how to import data into only one database table.

I found this topic, where is recommended to fetch the data of the respective table this way:

$ sed -n -e '/CREATE TABLE.*mytable/,/CREATE TABLE/p' mysql.dump > mytable.dump

But when I opened the file with the database backup, the data there are in this format:

1f8b 0808 d107 9854 0203 3230 3134 3132
3232 3037 3532 3231 2d32 3031 342d 3132
...

So I cannot use the method mentioned above.

How could I import data from only one to the database?

Thank you in advance.

EDIT: How the backups are made

`mysqldump -u #{db_config['username']} -p#{db_config['password']} -i -c -q #{db_config['database']} > tmp/#{backup_filename}`
`gzip -9 tmp/#{backup_filename}`

Example of the file name: 20141220212448-2014-12-21_12-00-06.sql

Community
  • 1
  • 1
user984621
  • 46,344
  • 73
  • 224
  • 412
  • 2
    why not just dump that db separately? `mysqldump yourdb > yourdb.sql`? – Marc B Dec 22 '14 at 16:09
  • Which command have you used to create dump file? As Marc said you can backup single table not whole DB. Also there settings in MySQL to create separate DB files for particular tables(but this is binary DB files). – Boris Ivanov Dec 22 '14 at 16:10
  • @MarcB because the table was truncated by accident. – user984621 Dec 22 '14 at 16:10
  • Check whether you are trying to open a compressed dump file directly without extracting it. you are supposed view the plain sql queries when you open the file in a text editor. – Slowcoder Dec 22 '14 at 16:11
  • if it's only 200meg, just load the dump into an editor and trim the irrelevant sql from before/after your db's tables. that'd be faster than trying to get sed/awk to NOT mangle the sql. – Marc B Dec 22 '14 at 16:12
  • @BorisIvanov this way: `mysqldump -u #{db_config['username']} -p#{db_config['password']} -i -c -q #{db_config['database']} > tmp/#{backup_filename}` `gzip -9 tmp/#{backup_filename}` – user984621 Dec 22 '14 at 16:14
  • 1
    mysqldump [options] db_name [tbl_name ...] [4.5.4 mysqldump — A Database Backup Program](http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html). – wchiquito Dec 22 '14 at 16:16
  • Then your problem that you using SED on ZIP archive. But as others advising - backup single table, do not ZIP it. Then just recover. – Boris Ivanov Dec 23 '14 at 10:03

0 Answers0