Coleção de utilitários para dump de dados pelo mysql

mysqldumps.sh

# Realiza o backup completo com estrutura, dados views, procedures, functions e triggers
# utilizando insert completo pela option --complete-insert
# alem de converter os binários para hexadecimal com a option --hex-blob
# inclui CREATE BATABASE option -B
mysqldump -u username -p --opt --complete-insert --hex-blob --routines -B databasename > databasename.sql


# Realiza o backup da estrutura tanto tables quanto views
# exclui as triggers otion --skip-triggers
# ignora os dados --no-data
mysqldump -u username -p --opt --no-data --skip-triggers databasename > estrutura.sql


# Realiza o dump apenas dos dados com insert completo
# ingora a estrutura option --no-create-info
mysqldump -u username -p --no-create-info --skip-triggers --complete-insert databasename > estrutura.sql


# Realiza o dump apenas das Triggers
mysqldump -u username -p --no-create-info --no-data databasename > triggers.sql

# Realiza o dump apenas das procedures e functions
mysqldump -u username -p --routines --no-create-info --no-data --skip-triggers databasename > proc_fun.sql

# Realiza o dump apenas das tabelas sem as views
mysql -u username -pESCAPED_PASSWORD INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'BASE TABLE' and table_schema = 'databasename'" | xargs mysqldump -u username -pESCAPED_PASSWORD --opt --no-data --skip-triggers databasename > estrutura_whitout_views.sql

# Realiza o dump apenas das views
mysql -u username -pESCAPED_PASSWORD INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = 'databasename'" | xargs mysqldump -u username -pESCAPED_PASSWORD --opt --no-data --skip-triggers databasename > views_only.sql

# Realiza o backup full, remove DEFINER e AUTO_INCREMENT
mysqldump -h localhost -P 3306 -u username -pESCAPED_PASSWORD --opt --complete-insert --hex-blob --routines -B databasename | sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | sed -E 's/ AUTO_INCREMENT=[0-9]*//g' > database_full.sql

# Realiza backup completo e adiciona DROP DATABASE
mysqldump --column-statistics=0 -h localhost -P 3306 -u username -pESCAPED_PASSWORD --opt --complete-insert --hex-blob --routines -B databasename | sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | sed -E 's/ AUTO_INCREMENT=[0-9]*//g' | sed "22i DROP DATABASE IF EXISTS \`databasename\`;\\n" > databasename.sql