Export MySQL Database into Separate Files per Table

Export MySQL Database into Separate Files per Table

Category : How-to

Get Social!

mysql-logoI have recently been using git to check in an applications database. The database has many tables, some of which are populated with test data and created a fairly large file when exported. I noticed a few issues issues when checking these into git, namely that the large file was uploaded and saved in git as a single large file containing my changes and the other stuff which had not changed.

Instead of using this large file as one and checking it into git, breaking the file into several smaller files means that only the table which changed would be added to the git commit resulting in much smaller uploads.

The below code is a bash script which let’s you export, using mysqldump, all tables in a MySQL database to individual files. This will result in one file per MySQL table in the database. You will need to modify the following attributes:

  • [USER] – the username to use when connecting to the MySQL instance.
  • [PASSWORD] – the password for the above MySQL user.
  • [DATABASE] – the name of the MySQL database to export.
  • [BACKUP_LOCATION] – the location on the MySQL server where the SQL files will be created.
for T in `mysql -u [USER] -p[PASSWORD] -N -B -e 'show tables from [DATABASE]'`;
    echo "Backing up $T"
    mysqldump --skip-comments --compact -u [USER] -p[PASSWORD] [DATABASE] $T > $GIT_MYSQL/$T.sql



6-Jul-2017 at 5:52 pm

Hi, thanks for your article. So, in case I need to restore them into a new database, how can I do it? Just create new database, then import all of tables by command:
for i in `ls /BACKUP-DIR`;do mysql -u USER -p PASS NEW_DB < $i;done

    James Coyle

    6-Jul-2017 at 7:57 pm

    Yes, but you’ll need to recreate your users and grants too.


18-Aug-2017 at 2:23 am

Dear James Coyle. Thank you very much!


25-Mar-2019 at 8:56 am


Thank you! Is this for a localhost db? I tried entering a remote server, but I get

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

How can I connect to a remote db?


12-Mar-2020 at 11:24 am

Awesome script it’s make my work so easy.
This script i made changes as per my requirement like all multiple table dump file create in zip format.
Thanks for the sharing.


28-Nov-2020 at 3:49 am

What about this?

mysqldump -u [USER] -p[PASSWORD] -y –tab=[BACKUP_LOCATION] [DATABASE]
with data
mysqldump -u [USER] -p[PASSWORD] -d -y –tab=[BACKUP_LOCATION] [DATABASE]
without data

Why to create a script when this functionality already exists?


    13-Jan-2021 at 5:42 am

    clearly didnt check the time stamp on the post did ya?

Manish Kumar Peddi

15-Jun-2022 at 6:41 pm

Hi James,

Can you share script to import the tables as well.


Leave a Reply

Visit our advertisers

Quick Poll

Do you use ZFS on Linux?

Visit our advertisers