Personal blog of Yzmir Ramirez

“It would be nice to have an option, that would allow to suppress the DEFINER statement in the CREATE VIEW statements generated by mysqldump. This would help when transferring data structures between databases with different security models.” 

TLDR;

Use mysqlpump with option

--skip-definer

instead of

mysqldump

.

The Story

This was requested as MySQL Bug #24680 on Nov 29, 2006. This feature request got large Community support. Even if we cannot see the number of people who voted for this request, the number of comments is impressive.

The request is very reasonable:

mysqldump

is widely used during application development and it is a very common practice to migrate database structure between developers’ machines and to the production servers.

Imagine a situation where developer Sveta creates a database and adds few objects with

DEFINER

  clauses there. We will use only one for this post but in reality, she can have dozens.

mysql> CREATE VIEW large_tables AS SELECT * FROM information_schema.tables WHERE DATA_LENGTH > 100000000;
Query OK, 0 rows affected (0,01 sec)

Once you create a view default

DEFINER

  is the user who created this view:

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

And this causes issues when another user tries to import such a view into a different server:

mysql> CREATE USER production;
Query OK, 0 rows affected (0,01 sec)

mysql> GRANT ALL ON definers.* TO production@'%';
Query OK, 0 rows affected (0,01 sec)

mysql> GRANT SESSION_VARIABLES_ADMIN ON *.* TO production@'%';
Query OK, 0 rows affected (0,01 sec)

$ mysqldump -usveta definers | mysql -uproduction production
ERROR 1227 (42000) at line 61: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

Here is the content of line 61:

$ mysqldump -usveta definers | head -n 62 | tail
/*!50001 DROP VIEW IF EXISTS `large_tables`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`sveta`@`%` SQL SECURITY DEFINER */

So this is a

CREATE VIEW

  operation that failed during import.

Unfortunately,

mysqldump

still does not have an option that allows migrating definers.

But since August 2015 and MySQL 5.7.8 we have a solution that, unfortunately, was overlooked in favor of the famous tool

mysqldump

.

Version 5.7.8 and all which created after it, come with a new dump tool:

mysqlpump

  that has the option

--skip-definer

  and allows to migrate database objects without any issue:

$ mysqlpump -h127.0.0.1 -P3306 -usveta --skip-definer definers | mysql -h127.0.0.1 -P13000 -uproduction definers
Dump completed in 17

$ mysql -h127.0.0.1 -P13000 -uproduction definers -e "SHOW FULL TABLES"
+--------------------+------------+
| Tables_in_definers | Table_type |
+--------------------+------------+
| large_tables       | VIEW       |
+--------------------+------------+

$ mysql -h127.0.0.1 -P13000 -uproduction definers -e "SELECT DEFINER, TABLE_SCHEMA, TABLE_NAME FROM information_schema.views WHERE TABLE_NAME='large_tables';"
+--------------+--------------+--------------+
| DEFINER      | TABLE_SCHEMA | TABLE_NAME   |
+--------------+--------------+--------------+
| production@% | definers     | large_tables |
+--------------+--------------+--------------+

Note that

mysqlpump

automatically adds

CREATE DATABASE

  into the dump and full path to the database objects. E.g.

CREATE ALGORITHM=UNDEFINED VIEW `definers`.`large_tables` AS select …

  Therefore this method cannot be used to migrate view, routine, or trigger definitions between different databases on the same server.

For more information about

mysqlpump

 and why you should switch to this tool from

mysqldump

 read this blog post, The mysqlpump Utility.

In this blog, we’ll look at the

mysqlpump

 utility.

mysqlpump

 is a utility that performs logical backups (which means backing up your data as SQL statements instead of a raw copy of data files). It was added in MySQL Server version 5.7.8, and can be used to dump a database or a set of databases to a file and then loaded on another SQL server (not necessarily a MySQL server).

Its usage is similar to

mysqldump

, but it includes a new set of features. Many of the options are the same, but it was written from scratch to avoid being limited to

mysqldump

 compatibility.

The Main Features Include:

• To make the dump process faster, it allows parallel processing of databases and objects within databases.
• There are more options to customize your dumps and choose which databases and objects to dump (tables, stored programs, user accounts), using the

  --include-*

   and 

  --exclude-*

   parameters.

• User accounts can be dumped now as

  CREATE USER

   and

  GRANT

   statements, instead of inserting directly to the MySQL system database.

• Information between the client and the server can be compressed using the

  --compress

   option. This feature is very useful for remote backups, as it saves bandwidth and transfer time. You can also compress the output file using

  --compress-output

  , which supports ZLIB and LZ4 compression algorithms.

• It has an estimated progress indicator. This is really useful to check the current status of the dump process. You can see the total amount of rows dumped and the number of databases completed. It also reports an estimate of the total time to complete the dump.
• Creation of secondary indexes for InnoDB tables happens after data load for shorter load times.

Exclude/Include:

This feature provides more control over customizing your dumps, and filter the data that you need. Using this feature, you can be more selective with the data you want to dump (databases, tables, triggers, events, routines, users) and save file size, process time and transferring time while copying/moving the file to another host.

Keep in mind that there are some options that are mutually exclusive: e.g., if you use the

--all-databases

 option, the

--exclude-databases

  parameter won’t take effect. By default,

mysqlpump

 will not dump the following databases unless you specify them using the

--include-databases

 option:

INFORMATION_SCHEMA

,

performance_schema

,

ndbinfo

  and

sys

.

Values for these options need to be declared by comma-separated listing. Using a “%” as a value for any of the exclude/include options acts as a wildcard. For example, you can dump all databases starting with “t” and “p” by adding the option

--include-databases=t%,p%

  to the command line.

For users, routines, triggers and events,

mysqlpump

 has

--include-*

 and

--exclude-*

 options with similar usage. Some specific notes:

• Triggers are dumped by default, but you can also filter them using the

  --include-triggers

  /

  --exclude-triggers

   options

• Routines and events are not dumped by default, and need to be specified in the command line with

  --routines

   and

  --events

  , or the corresponding

  --include

   and 

  --exclude

   options

• Keep in mind that if a stored procedure and a function have the same name, then include/exclude applies to both

Parallel Processing:

This feature allows you to process several databases, and tables within the databases, in parallel. By default,

mysqlpump

 uses one processing queue with two threads. You can increase the number of threads for this default queue with

--default-parallelism

. Unless you create additional queues, all the databases and/or tables you elect to dump go through the default queue.

To create additional queues you can use the 

--parallel-schemas

 option, which takes two parameters: the number of threads for the queue and the sub-set of databases this queue processes.  As an example, you could run:

mysqlpump --include-databases=a,b,c,d,e,f,g,h --default-parallelism=3 --parallel-schemas=4:a,b

so that schemas c, d, e, f, g and h are processed by the default queue (which uses three threads), and then tables from schemas a and b are processed by a separate queue (that uses four threads). Database names should be included as a comma-separated list:

$ mysqlpump --parallel-schemas=4:example1,example2,example3 --parallel-schemas=3:example4,example5 > examples.sql
Dump progress: 0/1 tables, 250/261184 rows
Dump progress: 24/30 tables, 1204891/17893833 rows
Dump progress: 29/30 tables, 1755611/17893833 rows
Dump progress: 29/30 tables, 2309111/17893833 rows
...
Dump completed in 42424 milliseconds

User Accounts:

User accounts can be dumped using this tool. Here’s a comparison of our Percona Tool

pt-show-grants

 versus

mysqlpump

 to check their differences.

By default,

mysqlpump

 doesn’t dump user account definitions (even while dumping the MySQL database). To include user accounts on the dump, you must specify the

--users

 option.

Here’s an example on how use

mysqlpump

 to get only user accounts dumped to a file:

$ mysqlpump --exclude-databases=% --exclude-triggers=% --users
-- Dump created by MySQL dump utility, version: 5.7.8-rc, linux-glibc2.5 (x86_64)
-- Dump start time: Thu Aug 27 17:10:10 2015
-- Server version: 5.7.8
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
CREATE USER 'msandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'127.%';
CREATE USER 'msandbox_ro'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, EXECUTE ON *.* TO 'msandbox_ro'@'127.%';
CREATE USER 'msandbox_rw'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'msandbox_rw'@'127.%';
CREATE USER 'rsandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT REPLICATION SLAVE ON *.* TO 'rsandbox'@'127.%';
CREATE USER 'furrywall'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*AB8D50A9E3B8D1F3ACE85C54736B5BF472B44539' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
GRANT USAGE ON *.* TO 'furrywall'@'localhost';
CREATE USER 'msandbox'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost';
CREATE USER 'msandbox_ro'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, EXECUTE ON *.* TO 'msandbox_ro'@'localhost';
CREATE USER 'msandbox_rw'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'msandbox_rw'@'localhost';
CREATE USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
CREATE USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6E543F385210D9BD42A4FDB4BB23FD2C31C95462' REQUIRE NONE PASSWORD EXPIRE INTERVAL 30 DAY ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'testuser'@'localhost';
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- Dump end time: Thu Aug 27 17:10:10 2015
Dump completed in 823 milliseconds

As you can see, above the tool makes sure the session uses known values for timezone and character sets. This won’t affect users, it’s part of the dump process to ensure correctness while restoring on the destination.

Comparing it with

pt-show-grants

 from Percona Toolkit, we can see that 

mysqlpump

 dumps the

CREATE USER

  information as well. The statements produced by

mysqlpump

 are the right thing to run to recreate users (and should be the preferred method), especially because of the

sql_mode

 NO_AUTO_CREATE_USERS. If enabled, it renders

pt-show-grants

 useless.

Here’s an example of

pt-show-grants

 usage:

$ pt-show-grants --host 127.0.0.1 --port 5708 --user msandbox --ask-pass
Enter password:
-- Grants dumped by pt-show-grants
-- Dumped from server 127.0.0.1 via TCP/IP, MySQL 5.7.8-rc at 2015-08-27 17:06:52
-- Grants for 'furrywall'@'localhost'
GRANT USAGE ON *.* TO 'furrywall'@'localhost';
-- Grants for 'msandbox'@'127.%'
GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'127.%';
-- Grants for 'msandbox'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost';
-- Grants for 'msandbox_ro'@'127.%'
GRANT EXECUTE, SELECT ON *.* TO 'msandbox_ro'@'127.%';
-- Grants for 'msandbox_ro'@'localhost'
GRANT EXECUTE, SELECT ON *.* TO 'msandbox_ro'@'localhost';
-- Grants for 'msandbox_rw'@'127.%'
GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW DATABASES, UPDATE ON *.* TO 'msandbox_rw'@'127.%';
-- Grants for 'msandbox_rw'@'localhost'
GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW DATABASES, UPDATE ON *.* TO 'msandbox_rw'@'localhost';
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
-- Grants for 'rsandbox'@'127.%'
GRANT REPLICATION SLAVE ON *.* TO 'rsandbox'@'127.%';
-- Grants for 'testuser'@'localhost'
GRANT USAGE ON *.* TO 'testuser'@'localhost';

Some Miscellaneous Notes:

• One of the differences with

  mysqldump

   is that

  mysqlpump

   adds 

  CREATE DATABASE

   statements to the dump by default, unless specified with the

  --no-create-db

  option.

  • There’s an important difference on the dump process that is closely related: it includes the database name while adding the

    CREATE TABLE

     statement. This causes a problem when trying to use the tool to create a duplicate.