In the second and final part of our mysqldump best practices we will talk about how to handle the migration and import for stored program objects and views from your MySQL database. To read more about the prerequisites for a successful dump and restore operation for large MySQL databases, check out the first part of this 2-part blog series.

Importing your stored procedures, functions and triggers

By default, mysqldump imports views and triggers. However it does not import procedures, functions and events. To import procedures and functions, the --routines option should be specified, and to import events, the --events option should be specified.

1. Importing triggers

Mysqldump will attempt to dump all the triggers in your database by default. To be able to dump a table's triggers, you must have the TRIGGER privilege for the table. If the dump user does not have this privilege, triggers will be skipped and mysqldump will not throw any error. So don’t be surprised if you don't see any triggers imported to your destination database.

2. Importing events

To import events, you need to specify --events option while invoking the mysqldump utility. This option requires the EVENT privileges for those databases. Again, mysqldump will silently skip events if the dump user does not have these privileges, even if you have specified --event option when invoking mysqldump.

3. Importing functions and stored procedure

To import routines, you need to specify --routines option while invoking the mysqldump utility. This option requires the global select privileges. Even in this case, mysqldump will silently skip functions and procedures if the dump user does not have these privileges, even if you have specified --routines option when invoking mysqldump.

3.1 Importing non deterministic functions

A stored program that modifies data is called non deterministic if it does not produce repeatable results. Example rand() function. It is especially challenging to use such functions in replicated setups, as they can result in different data on source and replica. To control such possibilities, MySQL imposes certain restrictions on function creation if binary logs are enabled.

By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:

ERROR 1418 (HY000) at line 181: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_funable)

So if your function is not declared as deterministic on the source, and binary logging is enabled on your destination, you will see the above error during the restore of the dump. Hence it is important to understand the deterministic nature of your functions upfront. If you are sure that your functions are deterministic, you need to turn on the log_bin_trust_function_creators configuration on your destination before the restore operation. When enabled, MySQL allows creation of such functions even when binary logging is enabled.

4. SQL SECURITY characteristic of the stored routines and views.

MySQL allows a SQL SECURITY context to be specified while creating the store programs or views. The SQL SECURITY characteristic can be specified as DEFINER or INVOKER. If the SQL_SECURITY context is DEFINER, the routine executes using the privileges of the account named in the routine DEFINER clause. If the context is INVOKER, routine executes using the privileges of the user who invokes it. The default value is DEFINER.

If you are restoring stored routines or views, you need to ensure the definer user account exists on your destination database with appropriate grants. Otherwise you will encounter failures during restore.

Let's demonstrate this with an example related to views.

Let's suppose you have Views V1 and V2 defines as below:

CREATE definer=admin@'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE definer=admin@'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

Note that views are dumped by default by mysqldump and if you don’t have the user ‘admin’ on your destination, you will encounter the below error during the restore operation:

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Note that it is not just sufficient to ensure the user exists, but the user needs to have appropriate privileges to execute the views. For example if the user admin@'%' exists on the destination, but does not have SELECT privileges on the mydb database, you will see an error message:

'/mysql_data/mysqldump/sqldump_1582456858033.sql':View 'mydb.V2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them.

Summary

In this 2-part blog series, we covered important prerequisites you need to handle to ensure successful migration of your data and stored programs. ScaleGrid MySQL hosting handles these guidelines to provide a smooth experience while importing your data to ScaleGrid platform. Please share with us your experience and best practices you adopt for MySQL data migrations!