Posted By

Leech on 08/06/08

Tagged

mysql Bash tables files

Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

basicmagic

File per Table

/ Published in: Bash

URL: http://hashmysql.org/index.php?title=File_per_Table

Exports all tables in a given DB one per file.

Expand | Embed | Plain Text

#!/bin/bash
 
if [ "$1" = "" ]; then
   PASSWD=
else
   PASSWD="-p$1"
fi
 
#
# DB_IGNORE_LIST is for those schemas which we do not want dump the data
#
DB_IGNORE_LIST="^information_schema$"
USER=root
SOCKET="/tmp/mysql.sock"
 
#
# Really shouldn't need to do much editing below here.
#
DB_LIST=`mysql -u root -S $SOCKET -e "SHOW DATABASES" -B --skip-column-names | egrep -v "$DB_IGNORE_LIST"`
for db in $DB_LIST
do
   TABLE_LIST=`mysql -u root -S $SOCKET -B --skip-column-names -e "SHOW TABLES" $db`
   for table in $TABLE_LIST
   do
      mkdir -p $db
      mysqldump -S $SOCKET -u $USER $PASSWD $db $table> $db/$table.sql
   done
done

Report this snippet Tweet

Comments

Subscribe to comments

Posted By: Leech on August 6, 2008

My modification for only a DB passed as parameter: Tables to Files: -save as tables2files.sh -chmod a+x tables2files.sh -./tables2files.sh databasename

#!/bin/bash

USER=root
#SOCKET="/tmp/mysql.sock"
# from: http://hashmysql.org/index.php?title=File_per_Table
#
# Really shouldn't need to do much editing below here.
#
TABLE_LIST=`mysql -u root -S $SOCKET -B --skip-column-names -e "SHOW TABLES" $1`
for table in $TABLE_LIST
do
    mkdir -p $1
    mysqldump -S $SOCKET -u $USER $1 $table> $1/$table.sql
done

Comment:

You need to login to post a comment.