If you want to back up your table and views, stored procedures, or stored function definitions, you can use mysqldump or mysqlpump to export the schema without the data. However, if you just want the views you need to look for another option. This blog shows how MySQL Shell comes to the rescue.

There are a couple of approaches to get the view definitions. One option is to consider the information_schema.VIEWS view which has the following columns:

mysql> SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type
         FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'information_schema'
              AND TABLE_NAME = 'VIEWS'
        ORDER BY ORDINAL_POSITION;
+----------------------+---------------------------------+
| Field                | Type                            |
+----------------------+---------------------------------+
| TABLE_CATALOG        | varchar(64)                     |
| TABLE_SCHEMA         | varchar(64)                     |
| TABLE_NAME           | varchar(64)                     |
| VIEW_DEFINITION      | longtext                        |
| CHECK_OPTION         | enum('NONE','LOCAL','CASCADED') |
| IS_UPDATABLE         | enum('NO','YES')                |
| DEFINER              | varchar(288)                    |
| SECURITY_TYPE        | varchar(7)                      |
| CHARACTER_SET_CLIENT | varchar(64)                     |
| COLLATION_CONNECTION | varchar(64)                     |
+----------------------+---------------------------------+
10 rows in set (0.0011 sec)

This looks good, but there are two flaws. First of all, the algorithm of the view is not included among the information. Granted, most view definitions do not explicitly define the algorithm, but from time to time it is important. The other limitation is not visible from the column list but becomes clear if you look at an example of a view:

mysql> SELECT *
         FROM information_schema.VIEWS
        ORDER BY LENGTH(VIEW_DEFINITION) LIMIT 1\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sys
          TABLE_NAME: version
     VIEW_DEFINITION: select '2.1.1' AS `sys_version`,version() AS `mysql_version`
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: mysql.sys@localhost
       SECURITY_TYPE: INVOKER
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
1 row in set (0.0017 sec)

This just selects the information for the view with the shortest view definition as the definition is not important, so there is no reason to include more data than necessary. You may have a different view returned.

The important point in the output is the value of DEFINER. You may have to quote the username or hostname, but that is not simple to do because the full account name is listed.

An alternative is to export the view definition using the SHOW CREATE VIEW statement. For example for the sakila.staff_list view:

mysql> SHOW CREATE VIEW sakila.staff_list\G
*************************** 1. row ***************************
                View: staff_list
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`staff_list` AS select `s`.`staff_id` AS `ID`,concat(`s`.`first_name`,_utf8mb3' ',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`city`.`city` AS `city`,`sakila`.`country`.`country` AS `country`,`s`.`store_id` AS `SID` from (((`sakila`.`staff` `s` join `sakila`.`address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `sakila`.`city` on((`a`.`city_id` = `sakila`.`city`.`city_id`))) join `sakila`.`country` on((`sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`)))
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set, 1 warning (0.0013 sec)

This has all the required information with the username and hostname properly quoted. It is fine to use SHOW CREATE VIEW like this for a few views, but it is not practical to back up all view definitions and automatically pick up new definitions. This is where the scripting modes of MySQL Shell are useful.

This example uses Python, but you can also choose to implement a solution in JavaScript. In MySQL 8 you can use the X DevApi to easily query the information_schema.VIEWS view and add filters as required. An example of exporting all views except those in the system databases (mysql, information_schema, sys, and performance_schema) is:

\py

i_s = session.get_schema('information_schema')
views = i_s.get_table('VIEWS')
stmt = views.select('TABLE_SCHEMA', 'TABLE_NAME')
stmt = stmt.where("TABLE_SCHEMA NOT IN " +
       "('mysql', 'information_schema', 'sys', 'performance_schema')")

result = stmt.execute()
for view in result.fetch_all():
    sql = 'SHOW CREATE VIEW `{0}`.`{1}`'.format(*view)
    v_result = session.sql(sql).execute()
    v_def = v_result.fetch_one()
    print('DROP TABLE IF EXISTS `{0}`.`{1}`;'.format(*view))
    print('DROP VIEW IF EXISTS `{0}`.`{1}`;'.format(*view))
    print(v_def[1] + ';')
    print('')

You need an empty line after the stmt = stmt.where(...) statement and at the end to tell MySQL Shell that you have completed multi-line statements. The example assume that you already have a connection to MySQL.

First the schema object for information_schema schema and table object for the VIEWS view are fetched. Then a select statement is created with a WHERE clause specifying which schemas that we want the view definitions for. Change this as required. You can chain the two stmt and the result assignments to a single line (in the above example it was split out to improve the readability in the blog):

result = views.select('TABLE_SCHEMA', 'TABLE_NAME').where("TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema')").execute()

The result object can be used to loop over the views and execute SHOW CREATE VIEW for each view. In this example, a DROP TABLE and DROP VIEW are added, but that can optionally be removed. Then the second column of the SHOW CREATE VIEW output is printed.

Note that in the example, when the SHOW CREATE VIEW statement is put together, the schema and table names are quoted using backticks:

    sql = 'SHOW CREATE VIEW `{0}`.`{1}`'.format(*view)

For this to be valid, it assumes you have no view names with backticks in the name (if you have – please don’t! – you need to escape the backticks by duplicating it). If you have the ANSI_QUOTES SQL mode enabled, you should change the backticks with double quotes.

You can also use the character and collation information from information_schema.VIEWS view to set the client character set and collation like mysqldump does. This is left as an exercise for the reader.