Tag: dump

LinuxSQL

Working with GB Large mysql dump files -splitting insert statements

Recently I had to restore a huge database from a huge MySQL dump file.
Since the dump file was had all of the create statements mixed with insert statements, I found the recreation of the database to take a very long time with the possibility that it might error out and rollback all of the transactions.

So I came up with the following script which processes the single MySQL dump file and splits it out so we can run the different parts separately.

This creates files that can be run individually called

• mysql.tblname.beforeinsert
• mysql.tblname.insert
• mysql.tblname.afterinsert

cat mysql.dump.sql| awk 'BEGIN{ TABLE="table_not_set"}
{
	if($1=="CREATE" && $2=="TABLE")
	{
		TABLE=$3
		gsub("`","",TABLE)
		inserted=false
	}
	if($1!="INSERT")
	{
		if(!inserted)
		{
			print $0 > "mysql."TABLE".beforeinsert";
		}
		else
		{
			print $0 > "mysql."TABLE".afterinsert";
		}
	} else {
		print $0 > "mysql."TABLE".insert";
		inserted=true
	}
}
'