How to put MySQL code into source control?

Question

I know I can copy all my MySQL code manually to files and then put those files into source control. But is there any way to do this automatically?

I would like to do this to stored procedures, but also to table/event/trigger creation scripts.

Answer 1

You can create triggers on data change, which would store the change automatically to some source control. However there is no automatic way to track structure changes (tables, stored procedures and so on) this way. So probably the best way is to dump database and store these dumps in source control. You can do this periodically to automate the things.

Answer 2

Based on Michal answer, the solution I am using so far is:

#!/bin/bash
BACKUP_PATH=/root/database_name
DATABASE=database_name
PASSWORD=Password
rm -f "$BACKUP_PATH/*.sql"
mysqldump -p$PASSWORD --routines --skip-dump-date --no-create-info --no-data --skip-opt $DATABASE > $BACKUP_PATH/$DATABASE.sql
mysqldump -p$PASSWORD --tab=$BACKUP_PATH --skip-dump-date --no-data --skip-opt $DATABASE
hg commit -Am "automatic commit" $BACKUP_PATH