How to backing up database by its table and restore them to new database

Submitted by admin on Thu, 02/15/2018 - 12:30

for creating separated files for each table:

for x in `mysql --skip-column-names -u <source_username> -p<source_password> <source_database_name> -e 'show tables;'`;
do
     echo "file=$x"
     mysqldump -u <source_username> -p<source_password> <source_database_name> $x > "$x.sql"
done

 

for restoring all .sql files that we have been created in the previous step to our new database

for x in *.sql
do
  echo "file=$x"
  mysql -u <target_username> --password=<target_password> <target_database_name> < $x
done