Posts Tagged mysqldump

Extract a table from a mysqldump

Today I encountered a problem: I needed to restore a single table from a database mysqldump.
Usually you 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:

#!/bin/sh

extract_table(){
  TABLE=$1
  DUMPFILE=$2
  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 ! 🙂

Comments (1)

copying databases

Similarly to my last tip (copying directory with ssh and tar) , you can also copy databases. It’s pretty simple, here is my magic command:
mysqldump -ppassword db |ssh user@remote "cat - | mysql -u dbuser -ppassword db"

Here, you can also gzip or bzip2 the input, and it should be very efficient, because mysqldump output is pure ascii with sql, gzip and bzip2 will easily find good pattern for compression.

Also, as usual, using my.cnf files, you don’t need -ppassword parameters.

Comments (2)