Thursday, May 9, 2013

MySQL export all databases structure with triggers

So, I had many issues exporting a dev db because of missing permissions and more.
I finally worked out a way that would finish on our development server...

    mysqldump -pmy1pass --no-data --allow-keywords --compact --triggers --single-transaction --all-databases | gzip -9 > backup/mysql/all_structure.zip

So this skips locking due to the --single-transaction
Allows keywords as column names with --allow-keywords
Compact export with --compact

Woot.