Dump tables from database in mysql?

Question

How can I dump each table in the database into a separate file with the name of this table?

Answer 1

You can check the shell script suggested in the following article:

• How to dump all tables in a database into separate files? from 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 

Answer 2

Here is the Linux command line for backing up all tables in YOURDATABASENAME to split files in a specific path:

You need to replace YOURDATABASENAME and YOURPATH with the 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 back up all tables daily.

NOTE. If you use this from the Linux command line, you will need to add a user with global privileges to the database for YOURUSERNAME@localhost without a password. Otherwise, you will need to add the user and password parameters 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