If you like to keep your ddl backed up in some source management tool like svn or cvs and want to do it individually for stored procedures, events, triggers, tables and such rather than having a single file you can easily do so using the below. You could even include the –skip-dump-date or –skip-comments and use the below to compare ddl daily checking for alterations thus making sure you are aware of any ddl changes done on the database.

user=backup_user
password=`cat ~/.backup_password`
hostname=127.0.0.1
port=3306
dbname=test_db
path=/home/mysql/ddl
date=`date +%Y%m%d`

mysqldump -u$user -p$password -h$hostname -P$port --no-create-info --no-data --no-create-db --skip-opt $dbname > "$path"/"$dbname"_triggers_"$date".sql
mysqldump -u$user -p$password -h$hostname -P$port --routines --skip-triggers --no-create-info --no-data --no-create-db --skip-opt $dbname > "$path"/"$dbname"_routines_"$date".sql
mysqldump -u$user -p$password -h$hostname -P$port --events --skip-triggers --no-create-info --no-data --no-create-db --skip-opt $dbname > "$path"/"$dbname"_events_"$date".sql
mysqldump -u$user -p$password -h$hostname -P$port --skip-triggers --no-data --no-create-db --skip-opt $dbname > "$path"/"$dbname"_tables_"$date".sql
VN:F [1.9.22_1171]
Rating: 10.0/10 (3 votes cast)
VN:F [1.9.22_1171]
Rating: +5 (from 5 votes)

Dumping DDL – mysqldump tables, stored procedures, events, triggers (separately), 10.0 out of 10 based on 3 ratings

  1. MarkR says:

    Note that if you actually have to dump your database schema to load it into your SCM system (e.g. svn) you are doing it all wrong.

    The schema should be created by files FROM your source code, not the other way around.

    Granted, it may be possible to end up in this situation if you’ve been following an inappropriate development system, or if you have legacy stuff from previous developers.

    In that case, your focus should be on persuading the development team to follow a proper process when making schema changes in the future.

  2. Hi Mark,

    Good point for setups where ddl is generated by hibernate and such but here I’m talking more from a dba perspective. I’m talking about a developer giving you a stored procedure to be deployed on a database in change management not generated by the application. For the former I personally rather keep a record for myself not just through development svn. Plus not all databases ddl are autogenerated either. For those databases and stored procedures the above makes sense too.
    Another instance where you might use the above is when you only require say events in your dump or triggers etc.

    Thanks for your input,
    Darren

  3. Be aware that this will could cause a security issue as the password will show up in the processlist when this job is running.

  4. Hi Daniel,
    That is correct but only if its the same user who is executing the backup to run show processlist OR if the user issuing `show processlist;` has the “process” privilege which should be granted lightly. Even if a user has all kind of privileges on the information_schema database apart from the process privileges, it’ll still not be able to see connections from any other user apart from itself.

*