Back Up MySQL View Definitions

Back Up MySQL View Definitions –Skip to content

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.

Backup the view definition using MySQL Shell

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:

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:

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:

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:

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):

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:

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.