Posts Tagged sql

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)

mysql2csv

Sometimes, people ask me for some data in a database so I create the SQL request, but I don’t know how to give the output to that people. I think that most people have Excel, doens’t matter if that’s evil or not, they like to use it, and they find it usefull. That’s why I was searching for a way to output CSV from MySQL or from any SQL client that output result on the term and I found that solution that seems to work pretty well:

mysql the_database -B -e "select some,field from mytable where my_condition = something ;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > thefile.csv

The file thefile.csv is formatted in csv with correct newlines that excel can read, and that’s it 🙂

Comments (1)