Back Up MySQL View Definitions

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

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.

MySQL Connect 2013: ps_tools

During my talk and hands on labs at MySQL Connect 2013 I used several views, functions, and procedures from ps_helper and ps_tools.

The slides from the two talks can be downloaded from Thanks For Attending MySQL Connect or from the MySQL Connect 2013 Content Catalogue.

You can read more about ps_helper on Mark Leith's blog and you can download ps_helper from github.

To get ps_tools, download ps_tools.tgz from here. Once unpacked there is a ps_tools_5x.sql for each of the versions supported. The tools presented at MySQL Connect were all based on MySQL 5.6 and 5.7. Note that several of the included tools are not particularly useful on their own but are more meant as utility functions for some of the other tools. The actual implementations are organised so they are in the subdirectory corresponding to the earliest version it applies to. For a few tools, such as ps_reset_setup(), there are multiple versions depending on the MySQL version it applies to.

Several of the tools have a help text at the top of the source file.

The main tools are:

Function ps_thread_id():

Returns the thread id for a given connection. Specify NULL to get the thread id for the current connection. For example:

mysql> SELECT ps_tools.ps_thread_id(8063);
+-----------------------------+
| ps_tools.ps_thread_id(8063) |
+-----------------------------+
|                        8113 |
+-----------------------------+
1 row in set (0.00 sec)

View ps_setup_consumers:

Similar to performance_schema.setup_consumers, but includes a column to display whether the consumer is effectively enabled. See also slide 10 from CON 5282.

Function substr_count():

See also A Couple of Substring Functions: substr_count() and substr_by_delim().

Function substr_by_delim():

See also A Couple of Substring Functions: substr_count() and substr_by_delim().

Function ps_account_enabled():

Check whether a given account is enabled according to performance_schema.setup_actors. For example:

mysql> SELECT ps_tools.ps_account_enabled('localhost', 'root');
+--------------------------------------------------+
| ps_tools.ps_account_enabled('localhost', 'root') |
+--------------------------------------------------+
| YES                                              |
+--------------------------------------------------+
1 row in set (0.03 sec)

View ps_accounts_enabled:

Lists each account and whether it is enabled according to performance_schema.setup_actors.

Procedure ps_setup_tree_instruments():

Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting for more details.

Procedure ps_setup_tree_actors_by_host():

Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting for more details.

Procedure ps_setup_tree_actors_by_user():

Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting for more details.

Procedure ps_setup_tree_consumers():

Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting and slide 11 from CON 5282 for more details.

Procedure ps_dump_thread_stack():

This is the same as in ps_helper with one bug fix. See also slides 30-31 from CON 5282.

Procedure ps_enable_all():

Enable all consumers and instruments.

Procedure ps_reset_setup():

Reset consumers, instruments, actors, and threads to the defaults (without taking my.cnf into consideration).

View is_innodb_lock_waits:

Show information about waiting locks for InnoDB.

View is_engine_data:

Summary of the amount of data and indexes grouped by storage engine.

View ps_memory_by_thread_by_event_raw:

The amount of memory used grouped by thread and event without any formatting and ordering.

View ps_memory_current_by_thread_by_event:

The same as above but formatted output and ordered by current usage.

View ps_memory_high_by_thread_by_event:

The same as above but formatted output and ordered by the high watermark.

Procedure schema_unused_routines():

Lists unused stored procedures and functions.

A Couple of Substring Functions: substr_count() and substr_by_delim()

A problem that sometimes when writing queries or stored routines is the need to use strings to do basic manipulation of the data. While from a performance perspective it is generally faster to do these manipulations inside the application, for various reasons it may be desirably to keep things inside MySQL.

This post lists two stored functions that can be used for simple manipulation of strings.

substr_count()

This is a port of the PHP function of the same name. It counts the number of times a given substring is encountered in a text. The signature is:

substr_count(
   in_haystack mediumtext,
   in_needle varchar(255),
   in_offset int unsigned,
   in_length int unsigned
) RETURNS int unsigned

The function as it stands here, has the following limitations and behaviours:

  • As the MySQL convention is to use 1 as the offset, so is the MySQL port of substr_count().
  • As MySQL stored functions do not support optional arguments, all arguments must be specified. For in_offset and in_length use NULL or 0 to use the default values.
  • The maximum length supported for the needle is 255 characters.
  • I am using the LOCATE() function as that returns the first occurrence of a substring after a given offset. This will particularly benefit the performance in cases where the needle is only sparsely present in the search string.

The definition of substr_count() is:

DELIMITER //
   CREATE
 FUNCTION substr_count(in_haystack mediumtext, in_needle varchar(255), in_offset int unsigned, in_length int unsigned) RETURNS int unsigned
 LANGUAGE SQL DETERMINISTIC NO SQL SQL SECURITY INVOKER
   BEGIN
      DECLARE v_count, v_haystack_len, v_needle_len, v_offset, v_endpos int unsigned DEFAULT 0;

      SET v_haystack_len = CHAR_LENGTH(in_haystack),
          v_needle_len   = CHAR_LENGTH(in_needle),
          v_offset       = IF(in_offset IS NOT NULL AND in_offset > 0, in_offset, 1),
          v_endpos       = IF(in_length IS NOT NULL AND in_length > 0, v_offset + in_length, v_haystack_len);

      -- The last offset to use with LOCATE is at v_endpos - v_needle_len.
      -- That also means that if v_needlen > v_endpos, the count is trivially 0
      IF (v_endpos > v_needle_len) THEN
         SET v_endpos = v_endpos - v_needle_len;
         WHILE (v_offset < v_endpos) DO
            SET v_offset = LOCATE(in_needle, in_haystack, v_offset);
            IF (v_offset > 0) THEN
               -- v_offset is now the position of the first letter in the needle.
               -- Skip the length of the needle to avoid double counting.
               SET v_count  = v_count  + 1,
                  v_offset = v_offset + v_needle_len;
            ELSE
               -- The needle was not found. Set v_offset = v_endpos to exit the loop.
               SET v_offset = v_endpos;
            END IF;
         END WHILE;
      END IF;

      RETURN v_count;
   END//

DELIMITER ;

An example of how to use substr_count() is:

mysql> SELECT substr_count('a/b/c/d/e', '/', 3, 5);
+--------------------------------------+
| substr_count('a/b/c/d/e', '/', 3, 5) |
+--------------------------------------+
|                                    2 |
+--------------------------------------+
1 row in set (0.00 sec)

substr_by_delim()

The substr_by_delim() function can be used to pick out one element of a delimited string; an example would be to determine the second element in the comma delimited string ‘a,b,c,d,e':

substr_by_delim(
   in_set mediumtext,
   in_delimiter varchar(255),
   in_pos int
) RETURNS mediumtext

The width of in_delimiter is set to match the in_needle in substr_count() as substr_count() is used to find the number of delimiters in the search text.

DELIMITER //
   CREATE
 FUNCTION substr_by_delim(in_set mediumtext, in_delimiter varchar(255), in_pos int) RETURNS mediumtext
  COMMENT 'Returns the Nth element from a delimited list.'
 LANGUAGE SQL DETERMINISTIC NO SQL SQL SECURITY INVOKER
    BEGIN
      DECLARE v_num_parts int unsigned DEFAULT 0;

      IF (in_pos < 0) THEN
         -- substr_count returns the number of delimiters, add 1 to get the number of parts
         SET v_num_parts = substr_count(in_set, in_delimiter, NULL, NULL) + 1;
         IF (v_num_parts >= ABS(in_pos)) THEN
            -- Add the requested position (which is negative, so is actually a subtraction)
            -- Add 1 as the position is 1 based.
            SET in_pos = v_num_parts + in_pos + 1;
         ELSE
            -- The requested position is out of range, so set in_pos to 0.
            SET in_pos = 0;
         END IF;
      END IF;
      IF (in_pos <= 0 OR in_pos IS NULL OR in_pos > substr_count(in_set, in_delimiter, 0, NULL)+1) THEN
         -- in_pos is not BETWEEN 1 AND #of elements.
         RETURN NULL;
      ELSE
         RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(in_set, in_delimiter, in_pos), in_delimiter, -1);
      END IF;
   END//
DELIMITER ;

Like with the built-in SUBSTRING_INDEX() function, it is supported both to use positive and negative positions where a negative position counts from the end of the search string.

Examples of how to used substr_by_delim() are:

mysql> SELECT substr_by_delim('a,b,c,d,e', ',', 2);
+--------------------------------------+
| substr_by_delim('a,b,c,d,e', ',', 2) |
+--------------------------------------+
| b                                    |
+--------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT substr_by_delim('a,b,c,d,e', ',', -2);
+---------------------------------------+
| substr_by_delim('a,b,c,d,e', ',', -2) |
+---------------------------------------+
| d                                     |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT substr_by_delim('a||b||c||d||e', '||', 2);
+-------------------------------------------+
| substr_by_delim('a||b||c||d||e', '||', 2) |
+-------------------------------------------+
| b                                         |
+-------------------------------------------+
1 row in set (0.00 sec)

Hope you will find the two functions useful.