7

From a very big backup file (created by mysqldump) I need to bring back only one (or a few) specific table(s), without loading all tables.

Are there any fast alternatives?

1
  • multiple databases !
    – user218202
    Commented Dec 18, 2020 at 18:33

1 Answer 1

10

You could achieve that by two steps :

Step 1 : Locate position of your table(or tables) and it (them) in a file :

 grep -n "Table structure" your_dump_file > list_tables_NUM.TXT

This will create a list of all tables ranked by position, example :

cat list_tables_NUM.TXT
...
41743:-- Table structure for table `table_X`
41770:-- Table structure for table `table_Y`    
42780:-- Table structure for table `table_Z`
...

Based-on that result you will create a secondary file containing that specific table, let's assume that you need to restore only table_X and table_Y:

Step 2 : Extract from position 41743 to 42780 :

sed -n '41743,42780 p' your_dump_file > dump_table_XY.sql

At the final, you may find your tables (structure+data) in the dump_table_XY.sql

Tested for MySQL/Mariadb in Centos7

2
  • it works well if i look for one table only. So when I search to extract a few tables in one shot, it seems to be tedious task especially if the tables are not successives !
    – user218202
    Commented Dec 22, 2020 at 16:14
  • 1
    You can make your own .sh script to achieve that by giving tables names as arguments and then execute the same steps to put each one of them in one dump file. I'll will provide an update of my answer later if it's not obvious for you.
    – Yassine LD
    Commented Dec 22, 2020 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.