Export MySQL Database into Separate Files per Table

  • 3

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.


3 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!

Leave a Reply