mysqldump Best Practices (Part 2): Migrations Guide
Let's get everything where you need it to be.
Join the DZone community and get the full member experience.Join For Free
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.
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.
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.
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.
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.
A stored program that modifies data is called nondeterministic 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
NO SQL, or
READS SQL DATA must be specified explicitly. Otherwise an error occurs:
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 the creation of such functions even when binary logging is enabled.
MySQL allows a
SQL SECURITY context to be specified while creating the store programs or views. The
SQL SECURITY characteristic can be specified as
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, the routine executes using the privileges of the user who invokes it. The default value is
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:
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:
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:
In this 2-part blog series, we covered important prerequisites you need to handle to ensure successful migration of your data and stored programs.
Published at DZone with permission of Prasad Nagaraj, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.