# Export MySQL database to CSV - Tables with no data will be exported
# as zero length files to show they have been processed and not just
# forgotten about.

# This needs to be run as root as mysql 'outfile' owned by MySQL

# Make sure MySQL binary in the path.
PATH=/usr/local/mysql/bin:$PATH ; export PATH

# Datebase to look at
DB="premvet"

# Extract a list of all the tables in the DataBase
mysql -B -D $DB --skip-column-names -e "show tables;" >/tmp/tables

# Loop on the table names exporting them
for FILE in `cat /tmp/tables` ; do

echo "Processing $FILE"

# The Select into OutFile does not include column names so use a single
# pass to get the headers, convert to CSV and dump to a file. 
# Note the 1st sed line has a <TAB> in it use ^v<tab> to get it in vi/vim

mysql -D $DB  -e "select * from $FILE limit 1;" | 
sed '
s/	/","/g
s/^/"/
s/$/"/
s/\n//g' | head -1 > /tmp/${FILE}.csv

rm -rf /tmp/dummy # Remove the tmp file in case it's been left.

mysql -D $DB <<!EOF!
SELECT *  INTO OUTFILE '/tmp/dummy'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM $FILE ;
!EOF!

# Add this to the end of the csv file with the column headers.
cat /tmp/dummy >> /tmp/${FILE}.csv

done

# Remove the tmp file
rm -rf /tmp/tables /tmp/dummy