mysqldump Best Practices: Part 2 - Migrations Guide
Tags: mysql, database, cloud, tutorial
Published: 2021-04-12 22:18:25

---
title: mysqldump Best Practices: Part 2 - Migrations Guide
published: true
description: In this MySQL blog series we discuss common aspects you should handle upfront to ensure a successful dump & restore activity. In part 2, we focus on migration.
tags: mysql, database, cloud, tutorial
cover_image: https://scalegrid.io/blog/wp-content/uploads/2020/12/mysqldump-Best-Practices-Part-2-Migration-Guide-ScaleGrid-Blog.jpg
---
<link rel="canonical" href="https://scalegrid.io/blog/mysqldump-best-practices-part-2/" />
<p style="text-align: justify;">In the second and final part of our mysqldump best practices we will talk about <a style="color: #2da964;" href="https://scalegrid.io/blog/mysqldump-best-practices-part-2/">how to handle the migration and import for stored program objects and views from your MySQL database</a>. 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.</p>
<table style="border: solid 20px #def5fe;" width="100%">
<tbody>
<tr>
<th style="padding: 20px 30px 20px 30px;">
<h2 style="padding-bottom: 10px;">mysqldump Best Practices</h2>
<ul>
	<li style="padding-bottom: 10px;"><a style="color: #2da964;" href="https://scalegrid.io/blog/mysqldump-best-practices-part-1/">Part 1 - MySQL Prerequisites</a></li>
	<li style="padding-bottom: 10px;"><a style="color: #2da964;" href="#intro">Part 2 - Migrations Guide</a></li>
</ul>
</th>
</tr>
</tbody>
</table>
<h2 style="padding-top: 15px;"><a id="intro"></a>Importing your stored procedures, functions and triggers</h2>
<p style="text-align: justify;">By default, mysqldump imports views and triggers. However it does not import procedures, functions and events. To import procedures and functions, the <code>--routines</code> option should be specified, and to import events, the <code>--events</code> option should be specified.</p>
<h2 style="padding-top: 15px;"><a id="1"></a>1. Importing triggers</h2>
<p style="text-align: justify;">Mysqldump will attempt to dump all the triggers in your database by default. To be able to dump a table's <code>triggers</code>, you must have the <a href="https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_trigger"><code>TRIGGER</code></a> 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.</p>
<h2 style="padding-top: 15px;"><a id="2"></a>2. Importing events</h2>
<p style="text-align: justify;">To import events, you need to specify <code>--events</code> option while invoking the mysqldump utility. This option requires the <a href="https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_event"><code>EVENT</code></a> 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.</p>
<h2 style="padding-top: 15px;"><a id="3"></a>3. Importing functions and stored procedure</h2>
<p style="text-align: justify;">To import routines, you need to specify <code>--routines</code> option while invoking the mysqldump utility. This option requires the <code>global select</code> 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 <code>--routines</code> option when invoking mysqldump.</p>
<h3 style="padding-top: 15px;"><a id="3-1"></a>3.1 Importing non deterministic functions</h3>
<p style="text-align: justify;">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.</p>
<p style="text-align: justify;">By default, for a <a href="https://dev.mysql.com/doc/refman/5.7/en/create-function.html"><code>CREATE FUNCTION</code></a> statement to be accepted, at least one of <code>DETERMINISTIC</code>, <code>NO SQL</code>, or <code>READS SQL DATA</code> must be specified explicitly. Otherwise an error occurs:</p>

<code>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)</code>

<p style="text-align: justify;">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 <a href="https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_log_bin_trust_function_creators"><code>log_bin_trust_function_creators</code></a> configuration on your destination before the restore operation. When enabled, MySQL allows creation of such functions even when binary logging is enabled.</p>
<h2 style="padding-top: 15px;"><a id="4"></a>4. SQL SECURITY characteristic of the stored routines and views.</h2>
<p style="text-align: justify;">MySQL allows a <code>SQL SECURITY</code> context to be specified while creating the store programs or views. The <code>SQL SECURITY</code> characteristic can be specified as <code>DEFINER</code> or <code>INVOKER</code>. If the <code>SQL_SECURITY</code> context is <code>DEFINER</code>, the routine executes using the privileges of the account named in the routine <code>DEFINER</code> clause. If the context is <code>INVOKER</code>, routine executes using the privileges of the user who invokes it. The default value is <code>DEFINER</code>.</p>
<p style="text-align: justify;">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.</p>
<p style="text-align: justify;">Let's demonstrate this with an example related to views.</p>
<p style="text-align: justify;">Let's suppose you have Views V1 and V2 defines as below:</p>
<code>CREATE definer=admin@'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE definer=admin@'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;</code>
<p style="text-align: justify;">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:</p>
<code>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.</code>
<p style="text-align: justify;">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 <code>admin@'%'</code> exists on the destination, but does not have <code>SELECT</code> privileges on the mydb database, you will see an error message:</p>
<code>'/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.</code>
<h2 style="padding-top: 15px;"><a id="5"></a>Summary</h2>
In this 2-part blog series, we covered important prerequisites you need to handle to ensure successful migration of your data and stored programs. <a style="color: #2da964;" href="https://scalegrid.io/mysql.html">ScaleGrid MySQL</a> 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!</p>