Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a bash script to run migrations on our MySQL databases.

Is it possible to wrap migrations within a single transaction when running multiple migration scripts from the command line?

Occasionally we've had schema changes that require some data manipulation to migrate that into the new structure and maintain referential integrity.

I'd love to do something like: (assume all vars are set accordingly)

for i in $( ls $MIGRATIONS_FOLDER/migration-* ); do

    # check database for record of this file
    result=`mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e 
            "SELECT id,filename,ran FROM tbl_migrations WHERE filename='$i'" 
            --batch --raw | wc -l`

    # if there is a record in tbl_migrations for this file, skip it
    if (($result > 1)); then

    echo "Importing $i";

    result=`mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME < $i`

    if [[summatz screwed up]]
        mysql `mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e "ROLLBACK;"`

    mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e 
            "INSERT INTO tbl_migrations (filename, ran) VALUES ('$i', 1)"

    if [ "$i" = "$END" ]; then
        echo "Ending migration at user specified file $i"

mysql `mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e "COMMIT;"`
share|improve this question
Schema changes can't be rolled back, they're not "transactional". Most (all?) DDL will implicitly commit before it runs. – Mat Oct 2 '12 at 15:54
thanks @Mat - I think I'll look at an alternative. tyvm! – Ian Wood Oct 2 '12 at 16:01
Use PostgreSQL if you're looking for transactional DDL, works as it should. – Frank Heikens Oct 2 '12 at 18:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.