How to export MySQL tables in to separate files and import back in to a single database

We can use the MySQLDump (mysqldump) command to export tables in to separate files as follow.

mysqldump --user=USERNAME --password --tab=~/output/dir DBNAME

Then how do we import it back, it is not that easy as we thought so use the following script to do the job.

 

#!/bin/bash

DBUSER=$1
DBPASS=$2
DBNAME=$3
DBHOST=$4
DBPORT=$5
DIRECTORY=$6

DIRECTORY=$DIRECTORY/*

echo "Importing files from '$DIRECTORY' into '$DB'"

for file in $DIRECTORY

do
    echo "Importing... File: $file"
    gunzip -c $file | mysql -u $DBUSER -p$DBPASS -h $DBHOST -P$DBPORT $DBNAME
done

echo "Importing to database '$DB' is done"

Example usage, first save the file as db-import.sh and run as follow.

$ sh db-import.sh DBUSER DBPASS DBNAME DBHOST DBPORT DIR