mysql database dump restore – awk script create one script per table

mysql database dump restore – awk script create one script per table

I have used the following scripts to take a huge mysql dump scripts,  with multiple databases and multiple tables,  and use it to create one single script file for creating each table and for inserting the records into that table.

 

This script allows one to create a single large dump file for all databases on a server,  yet when it comes time that some incremental restore is needed,  the large dump file can be quickly stripped into files that can be used to restore a smaller incremental change.

 

Current Database: `49erstrivia`
awk 'BEGIN{ TABLE="table_not_set"}
{
 if($1=="--" && /Current Database:/)
 { 
     CURRENTDB=$NF;
     gsub("`","",CURRENTDB);
     inserted=false;
     print CURRENTDB;
 }
 if($1=="CREATE" && $2=="TABLE")
 { 
     TABLE=$3
     gsub("`","",TABLE)
     inserted=false
 }
 if($1!="INSERT") 
 {
     if(!inserted)
     {
         print $0 > "mysql."CURRENTDB"."TABLE".beforeinsert";
     }
     else
     {
         print $0 > "mysql."CURRENTDB"."TABLE".afterinsert";
     }
 } 
 else 
 {
     print $0 > "mysql."CURRENTDB"."TABLE".insert"; 
     inserted=true
 }
}
'

Leave a Reply

Your email address will not be published. Required fields are marked *