Dump MySQL Database Tables into Separate Files

Need to dump your mysql database tables so that each table is in a separate file? And, the tables are too big for phpMyAdmin? This script will help.

#!/bin/bash

# dump-tables-mysql.sh
# Descr: Dump MySQL table data into separate SQL files for a specified database.
# Usage: Run without args for usage info.
# Notes:
#  * Script will prompt for password for db access.
#  * Output files are compressed and saved in the current working dir, unless DIR is
#    specified on command-line.

[ $# -lt 4 ] && echo "Usage: $(basename $0) <DB_HOST> <DB_USER> <DB_NAME> <DIR> [<PORT>]" && exit 1

DB_host=$1
DB_user=$2
DB=$3
DIR=$4
PORT=$5

[ -n "$DIR" ] || DIR=.
test -d $DIR || mkdir -p $DIR

[ -n "$PORT" ] || PORT=3306

echo -n "DB password: "
read -s DB_pass
echo
echo "Dumping tables into separate SQL command files for database '$DB' into dir=$DIR"

tbl_count=0

for t in $(mysql -NBA -h $DB_host --port=$PORT -u $DB_user -p=$DB_pass -p$DB_pass -D $DB -e 'show tables') 
do 
    echo "DUMPING TABLE: $DB.$t"
    mysqldump --host=$DB_host --port=$PORT --user=$DB_user --password=$DB_pass $DB $t --result-file=./$DIR/$t.sql
    tbl_count=$(( tbl_count + 1 ))
done

echo "$tbl_count tables dumped from database '$DB' into dir=$DIR"

Example

$ mysqldump.sh localhost root database_table ./mysql_table_dump

Leave a Reply

Your email address will not be published. Required fields are marked *