Skip to content

Instantly share code, notes, and snippets.

@sarfraznawaz2005
Forked from bxt/csv-export.sh
Created May 1, 2016 09:53
Show Gist options
  • Save sarfraznawaz2005/13198b535b0e1946de502579e9285851 to your computer and use it in GitHub Desktop.
Save sarfraznawaz2005/13198b535b0e1946de502579e9285851 to your computer and use it in GitHub Desktop.
Exporting whole MySQL databse to CSV files
#!/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}'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment