Mysqldump examples

Categories: 

Tags: 

Dump structure only, without data

mysqldump -d -h localhost -u root -pmypassword databasename > dump_file.sql

*Note: "-d" option is the only difference between this command and the typical mysqldump with all data

Dump all with drop table and drop database if exists

mysqldump --user=root --password=mypassword -h localhost  databasename --add-drop-table --add-drop-database > dump_file.sql

Dump with UTF8 encode

mysqldump --user=root --password=mypassword -h localhost  databasename --default-character-set utf8 > dump_file.sql

Dump all but ignore a specific table

mysqldump --user=root --password=mypassword -h localhost  databasename --ignore-table=databasename.table_name_to_ignore > dump_ignore_one_table_file.sql

Dump only specific tables

mysqldump --user=root --password=mypassword -h localhost  databasename table_name_to_dump table_name_to_dump_2 > dump_only_two_tables_file.sql

Dump and alter one line to change owner of a view

mysqldump --user=root --password=mypassword -h localhost  databasename | sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=new_user_owner/g' > dump_with_owner_altered.sql