Home / server / Questions / 302205
Accepted
Chad Johnson
Asked: 2011-08-18 10:52:50 +0800 CST

How do I dump view schemas for a MySQL database?

  • 772

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.

mysql database dump schema

4 Answers

  1. Best Answer

    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` */;
    • 10

  2. Use --opt option while making the dump:

    mysqldump -hserver -uUser -ppasswd -no-data --opt export > export.sql
    • 5

  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

    • 3

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

    mysqldump --skip-comments --skip-opt --complete-insert --add-drop-table --database dbname -p
    • 0