#!/bin/bash
# USAGE: Will create a .tar.gz with CSVs of all tables in schema.
# Configure below and run as root (i.e. the user mysql runs as)
#
# The script will (or should) SELECT * INTO OUTFILE your tables
# and save them into csv files under /tmp dir first, then name them
# like the tables and move them thogether into a directory. Then
# it will tar everything together and chown you the tarball.

# Schema to export:
DB=schemaname

# Directory to export files into: (before tar-ing)
DIR=csvs-$DB-$(date +%Y-%m-%d-%H-%M-%S)/

# Final tarball's location:
TARBALL=csvs-$DB-$(date +%Y-%m-%d-%H-%M-%S).tar.gz

# Config file for mysql password: (you would have to enter it many times)
CONFIG=/home/burny/.my.cnf
# This file looks sth like this (my.cnf-style):
# > [mysql]
# > user = sampleuser
# > password = samplepasswd

# Set owner to this user: (lets you access the export)
OWNER=bxt:bxtsgroup

# Name of file to save tables names to
SCHEMAFILE=schema.txt

# ---------------------------------------------------------------------
# Now following: the script. You shuldn't have to change
# somthing after this line, but you might save you some
# trouble if look at stuff before running it as root ;)

echo Saving to $DIR;
mkdir "$DIR";

for table in $(mysql --defaults-extra-file=$CONFIG $DB -B -e "show tables;");
do
  echo Processing $table
  mysql --defaults-extra-file=$CONFIG $DB -B -e "
SELECT * INTO OUTFILE '/tmp/tabledump.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM \`$table\`;"
  mv /tmp/tabledump.csv "$DIR$table.csv"
  cat <(echo -n "$table	") <(mysql $DB -B -e "DESCRIBE $table;" | awk 'BEGIN {ORS=","; getline} { print $1}' ) <(echo) >> "$DIR$SCHEMAFILE"
done

echo Zipping

tar -czvf "$TARBALL" "$DIR"

echo Changing permissions
chown $OWNER "$TARBALL"

echo Removing uncompressed
rm -R "$DIR"

echo -n "Done, filesize: "
ls -l "$TARBALL" | awk '{print $5}'