Export MySQL Database into Separate Files per Table

I 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;