6

I have a MySQL database for which I wish to dump schemas for views. How do I do this? I tried mysqldump with --no-data, but that only dumps table schemas.

shgnInc
  • 1,634
  • 3
  • 21
  • 29
Chad Johnson
  • 479
  • 2
  • 7
  • 14

4 Answers4

10

I think you might either be passing some other options to mysqldump, or using a version of mysqldump that doesn't understand views (perhaps it's too old). When I run mysqldump --no-data, it does dump out the view definitions. See the below:

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `t` AS select 1 AS `1` */;
  • 1
    maybe the questioner didn't have a look in the mysqldump? To dump the views commented out is a very bad style by MySQL. There should at least a hint in the docs, but there is none. – Thomas Berger Aug 17 '11 at 19:22
  • 1
    Nevermind, I was wrong. It did dump the views. Thank god. Cool. – Chad Johnson Aug 17 '11 at 19:33
  • 1
    These are conditional comments that execute depending on your version of MySQL, see: http://dev.mysql.com/doc/refman/5.6/en/comments.html – Gary Aug 15 '13 at 19:43
5

Use --opt option while making the dump:

mysqldump -hserver -uUser -ppasswd -no-data --opt export > export.sql
Michael Hampton
  • 237,123
  • 42
  • 477
  • 940
Gopal
  • 51
  • 1
  • 1
  • 2
    Although for security reasons, I'd not put the password on the command line, I'd let the program prompt for it. – mdpc Nov 29 '12 at 20:41
  • I think we need to clarify that `export` in the command is a specific schema/database name i.e. NOT a mysqldump syntax – Nam G VU Feb 06 '17 at 08:01
3

Mysqldump won't have any options to dump only on views.The below command will help you to take the backup of only views.

mysql -uroot -pPassword INFORMATION_SCHEMA --skip-column-names -e "select table_name from tables where table_type = 'VIEW' and table_schema = 'sakila'" | xargs mysqldump -u root -pPassword sakila > only_views.sql

Sreedhar D
  • 31
  • 1
0

The other answers did not but this worked for me...

mysqldump --skip-comments --skip-opt --complete-insert --add-drop-table --database dbname -p
kervin
  • 211
  • 2
  • 7