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.

  1. #!/bin/bash
  2.  
  3. if [ "$1" = "" ]; then
  4. PASSWD=
  5. else
  6. PASSWD="-p$1"
  7. fi
  8.  
  9. #
  10. # DB_IGNORE_LIST is for those schemas which we do not want dump the data
  11. #
  12. DB_IGNORE_LIST="^information_schema$"
  13. USER=root
  14. SOCKET="/tmp/mysql.sock"
  15.  
  16. #
  17. # Really shouldn't need to do much editing below here.
  18. #
  19. DB_LIST=`mysql -u root -S $SOCKET -e "SHOW DATABASES" -B --skip-column-names | egrep -v "$DB_IGNORE_LIST"`
  20. for db in $DB_LIST
  21. do
  22. TABLE_LIST=`mysql -u root -S $SOCKET -B --skip-column-names -e "SHOW TABLES" $db`
  23. for table in $TABLE_LIST
  24. do
  25. mkdir -p $db
  26. mysqldump -S $SOCKET -u $USER $PASSWD $db $table> $db/$table.sql
  27. done
  28. done

Report this snippet  

Comments

RSS Icon 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

You need to login to post a comment.