Today I encountered a problem: I needed to restore a single table from a database mysqldump.
cat the-mysqldump.sql |mysql the_database so you’re only able to restore the full database. I didn’t find any mysqldump option to extract a single table from a full database dump, so I’ve come up with this (minimal) shell script:
grepstr="/*!40000 ALTER TABLE \`$TABLE\`"
lines=`grep -n "$grepstr" $DUMPFILE |cut -d":" -f1`
lines=`echo $lines|sed 's/\ /,/' `
echo "LOCK TABLES \`$TABLE\` WRITE;"
sed -n "$lines p" $DUMPFILE
echo "UNLOCK TABLES;"
extract_table $1 $2
Use it like this:
./this-script.sh table-to-extract dumfile-for-extract |mysql the_database (use the
|mysql after you have checked the content).
Be carefull, this script is minimalistic:
- It doesn’t check if the file exist and is really a mysqldump file
- It doesn’t check if the table to extract exists
- It doesn’t work if disable-keys is set to false in mysqldump
- It doesn’t have a usage() function
If some people request it, I’ll write all these features, but as usual, I wanted to come up with a solution I could already use one hour a ago, and I’m spending time to write this script, let’s do it the faster I can ! 🙂