Creates an SQL structure dump of a MySQL database ordered so as not to create a view before the BASE TABLE exists.

ordered-mysqldump.sh

#!/bin/bash
 
db_user=username
db_pass=password
db_schema=schema
db_host=hostname
db_login="-h$db_host -u$db_user -p$db_pass "
 
# Dump BASE TABLES (not views)
echo "Dumping base tables..."
mysql ${db_login} \
    --skip-column-names --batch \
    -e "select table_name from tables where table_type = 'BASE TABLE' and table_schema = '${db_schema}'" INFORMATION_SCHEMA \
| xargs mysqldump ${db_login} \
    --no-data --skip-triggers \
    --skip-opt --no-create-db --create-options --add-drop-table --disable-keys \
    ${db_schema} \
> definitions.sql
 
# Dump triggers
echo "Dumping triggers..."
mysqldump ${db_login} \
    --no-create-info --no-data \
    --skip-opt --no-create-db \
    ${db_schema} \
>> definitions.sql
 
# Dump views
echo "Dumping views..."
mysql \
    -u${db_user} -p${db_pass} -h${db_host} \
    --skip-column-names --batch \
    -e "select table_name from tables where table_type = 'VIEW' and table_schema = '${db_schema}'" INFORMATION_SCHEMA \
| xargs mysqldump \
    -u${db_user} -p${db_pass} -h${db_host} \
    --no-data --skip-triggers \
    --skip-opt --no-create-db --add-drop-table \ 
    ${db_schema} \
>> definitions.sql
 
# Dump routines, events
echo "Dumping routines and events..."
mysqldump \
    -u${db_user} -p${db_pass} -h${db_host} \
    --routines --events --skip-triggers --no-create-info --no-data \
    --skip-opt --no-create-db ${db_schema} \
>> definitions.sql