How can I dump each table in database in separate file with that table name?
1
- Also have a look at dagondesign.com/articles/automatic-mysql-backup-scriptmosg– mosg2010-06-22 06:12:37 +00:00Commented Jun 22, 2010 at 6:12
Add a comment |
2 Answers
You may want to check out the shell script suggested in the following article:
- How do I dump all tables in a database into separate files? by Sunny Walia
Script:
#!/bin/bash db=$1 if [ "$db" = "" ]; then echo "Usage: $0 db_name" exit 1 fi mkdir $$ cd $$ clear for table in `mysql $db -e 'show tables' | egrep -v 'Tables_in_' `; do echo "Dumping $table" mysqldump --opt -Q $db $table > $table.sql done if [ "$table" = "" ]; then echo "No tables found in db: $db" fi 
Comments
Here is a Linux command line to backup all tables in YOURDATABASENAME to separate files in a specific path:
You will have to replace YOURDATABASENAME and YOURPATH with appropriate values.
for I in $(mysql --database=YOURDATABASENAME -e 'show tables' -s --skip-column-names); do mysqldump YOURDATABASENAME $I | gzip > "/YOURPATH/YOURDATABASENAME/$I.sql.gz"; done I use this as a scheduled cron job to backup all tables daily.
NOTE: if you are using this from a Linux command line, you will have to have to add a user with global privileges to the database for YOURUSERNAME@localhost with no password. Otherwise, you will have to add user and password options to the script as follows, but this will require a password for each table!
for I in $(mysql -u MYSQLUSERNAME -p --database=YOURDATABASENAME -e 'show tables' -s --skip-column-names); do mysqldump -u MYSQLUSERNAME -p YOURDATABASENAME $I | gzip > "/YOURPATH/YOURDATABASENAME/$I.sql.gz"; done 
