Yesterday my friend and ex-colleague Sveta published a blog post on how to migrate ownership of stored routines, views and triggers in MySQL.

I usually agree a lot with her and even if I agree with what she wrote, there is one single point I would like to comment: use MySQL Shell !

In the blog post, Sveta recommends the use of mysqldump which I don’t. For any logical dump & load operations, I really recommend the use of MySQL Shell’s Dump & Load Utility ! It’s much faster, it has many nice options like hidden Primary Key creation, and is compatible with OCI.

And of course, MySQL Shell also provides a solution for the problem exposed in the blog post.

Let’s see MySQL Shell in action with the exact same example:

First, using my own user (fred), I create a database and a view:

mysql> create database definers; mysql> use definers; mysql> CREATE VIEW large_tables AS SELECT * FROM information_schema.tables WHERE DATA_LENGTH > 100000000; mysql> SELECT DEFINER, TABLE_SCHEMA, TABLE_NAME FROM information_schema.views WHERE TABLE_NAME='large_tables'; +---------+--------------+--------------+ | DEFINER | TABLE_SCHEMA | TABLE_NAME | +---------+--------------+--------------+ | fred@% | definers | large_tables | +---------+--------------+--------------+

Now, I create the production user, for obvious security reasons, I assign a password to it:

mysql> CREATE USER production identified by '********'; mysql> GRANT ALL ON definers.* TO production@'%'; mysql> GRANT SESSION_VARIABLES_ADMIN ON <em>.</em> TO production@'%';

We can now dump the definers schema using MySQL Shell where I will use fred to connect:

[fred@imac ~] $ mysqlsh fred@localhost MySQL  localhost:33060+   2021-07-08 07:41:47  JS  util.dumpSchemas(['definers'], '/tmp/dump') Acquiring global read lock Global read lock acquired Gathering information - done All transactions have been started Locking instance for backup Global read lock has been released Writing global DDL files Running data dump using 4 threads. NOTE: Progress information uses estimated values and may not be accurate. Writing DDL for schema definers Writing DDL for view definers.large_tables ?% (0 rows / ?), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed Duration: 00:00:00s Schemas dumped: 1 Tables dumped: 0 Uncompressed data size: 0 bytes Compressed data size: 0 bytes Compression ratio: 0.0 Rows written: 0 Bytes written: 0 bytes Average uncompressed throughput: 0.00 B/s Average compressed throughput: 0.00 B/s

And now I will load it (the view needs to be removed first if you load it on the same server) using the production user:

[fred@imac ~] $ mysqlsh production@localhost MySQL  localhost:33060+   2021-07-08 07:42:53  JS  util.loadDump('/tmp/dump') Loading DDL and Data from '/tmp/dump' using 4 threads. Opening dump… Target is MySQL 8.0.25. Dump was produced from MySQL 8.0.25 Checking for pre-existing objects… Executing common preamble SQL Executing DDL script for schema definers [Worker002] Executing DDL script for definers.large_tables (placeholder for view) Executing DDL script for view definers.large_tables ERROR: Error executing DDL script for view definers.large_tables: MySQL Error 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation: /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=fred@% SQL SECURITY DEFINER VIEW large_tables AS select information_schema.tables.TABLE_CATALOG AS TABLE_CATALOG,information_schema.tables.TABLE_SCHEMA AS TABLE_SCHEMA,information_schema.tables.TABLE_NAME AS TABLE_NAME,information_schema.tables.TABLE_TYPE AS TABLE_TYPE,information_schema.tables.ENGINE AS ENGINE,information_schema.tables.VERSION AS VERSION,information_schema.tables.ROW_FORMAT AS ROW_FORMAT,information_schema.tables.TABLE_ROWS AS TABLE_ROWS,information_schema.tables.AVG_ROW_LENGTH AS AVG_ROW_LENGTH,information_schema.tables.DATA_LENGTH AS DATA_LENGTH,information_schema.tables.MAX_DATA_LENGTH AS MAX_DATA_LENGTH,information_schema.tables.INDEX_LENGTH AS INDEX_LENGTH,information_schema.tables.DATA_FREE AS DATA_FREE,information_schema.tables.AUTO_INCREMENT AS AUTO_INCREMENT,information_schema.tables.CREATE_TIME AS CREATE_TIME,information_schema.tables.UPDATE_TIME AS UPDATE_TIME,information_schema.tables.CHECK_TIME AS CHECK_TIME,information_schema.tables.TABLE_COLLATION AS TABLE_COLLATION,information_schema.tables.CHECKSUM AS CHECKSUM,information_schema.tables.CREATE_OPTIONS AS CREATE_OPTIONS,information_schema.tables.TABLE_COMMENT AS TABLE_COMMENT from information_schema.TABLES tables where (information_schema.tables.DATA_LENGTH > 100000000) */ <strong>Util.loadDump: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation (MYSQLSH 1227)</strong>

As you can see, it failed the exact same way as it happened with mysqldump and this is because by default we don’t enable the strip_definers option.

Now we will do the dump again enabling the option this time:

JS  util.dumpSchemas(['definers'], '/tmp/dump', <strong>{compatibility:["strip_definers"]}</strong>) Acquiring global read lock Global read lock acquired Gathering information - done All transactions have been started Locking instance for backup Global read lock has been released Writing global DDL files Running data dump using 4 threads. NOTE: Progress information uses estimated values and may not be accurate. Writing DDL for schema definers Writing DDL for view definers.large_tables NOTE: View definers.large_tables had definer clause removed and SQL SECURITY characteristic set to INVOKER ?% (0 rows / ?), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed Duration: 00:00:00s Schemas dumped: 1 Tables dumped: 0 Uncompressed data size: 0 bytes Compressed data size: 0 bytes Compression ratio: 0.0 Rows written: 0 Bytes written: 0 bytes Average uncompressed throughput: 0.00 B/s Average compressed throughput: 0.00 B/s

And we can see that the dump is now working as expected:

JS  util.loadDump('/tmp/dump') Loading DDL and Data from '/tmp/dump' using 4 threads. Opening dump… Target is MySQL 8.0.25. Dump was produced from MySQL 8.0.25 Checking for pre-existing objects… Executing common preamble SQL Executing DDL script for schema definers [Worker002] Executing DDL script for definers.large_tables (placeholder for view) Executing DDL script for view definers.large_tables Executing common postamble SQL No data loaded. 0 warnings were reported during the load.

And if we check the definer this time of the loaded view:

mysql> SELECT DEFINER, TABLE_SCHEMA, TABLE_NAME FROM information_schema.views WHERE TABLE_NAME='large_tables'; +--------------+--------------+--------------+ | DEFINER | TABLE_SCHEMA | TABLE_NAME | +--------------+--------------+--------------+ | production@% | definers | large_tables | +--------------+--------------+--------------+ 1 row in set (0.00 sec)

As you can see, MySQL Shell Dump & Load Utility is the way to go for any logical dump and load: faster and powerful !

Thank you for using MySQL !