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.
#!/bin/bash
GIT_MYSQL=/[BACKUP_LOCATION]
for T in `mysql -u [USER] -p[PASSWORD] -N -B -e 'show tables from [DATABASE]'`;
do
    echo "Backing up $T"
    mysqldump --skip-comments --compact -u [USER] -p[PASSWORD] [DATABASE] $T > $GIT_MYSQL/$T.sql
done;


8 Comments

TQuang89

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.

TQuang89

18-Aug-2017 at 2:23 am

Dear James Coyle. Thank you very much!

Brian

25-Mar-2019 at 8:56 am

Hello,

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?

Gopal

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.

Vido

28-Nov-2020 at 3:49 am

What about this?

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

Why to create a script when this functionality already exists?

    Fallingsheep

    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.

Thanks

Leave a Reply

Visit our advertisers

Quick Poll

How often do you change the password for the computer(s) you use?

Visit our advertisers