Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
Description
MDEV-22127 highlights the lack of import ability into 10.4+ from mysqldump.
When saving system tables, the logical form of the mysql.user table is not insert statements but create user statements. Show create user was added in 10.2.
users, roles, their grants, servers, plugins, and udf functions all are a global defination where being created as a logical form aids portability between major versions.
So:
- users - CREATE USER, GRANT
- roles - CREATE ROLE, GRANT
- servers - CREATE SERVER
- plugin - INSTALL PLUGIN
- udf - CREATE FUNCTION
Requirements:
- compatibility - so its a new dump option
- use "IF NOT EXISTS" is added with --insert-ignore
- is dumped first to solve MDEV-19558
- logically dumped tables are omitted from mysql database
- because of how users, roles and grants are tied together in output, tie these to a single option
- because there are better options for data migration, allow the mysqldump to be limited to just these system attributes (e.g. MDEV-22645)
- server, plugins, and udf can be separate options.
Possible:
- include "stats" as a virtual alias for the mysql EITS and innodb statistics tables
- timezones as an alias for timezone tables
|
example dump of output |
$ client/mysqldump --system=all -S /tmp/s2.sock -u root --insert-ignore
|
-- MySQL dump 10.16 Distrib 10.2.34-MariaDB, for Linux (x86_64)
|
--
|
-- Host: localhost Database:
|
-- ------------------------------------------------------
|
-- Server version 10.2.34-MariaDB
|
 |
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
/*!40101 SET NAMES utf8 */;
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
CREATE USER IF NOT EXISTS 'root'@'127.0.0.1';
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
|
CREATE USER IF NOT EXISTS 'root'@'::1';
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION;
|
CREATE USER IF NOT EXISTS ''@'grit';
|
GRANT USAGE ON *.* TO ''@'grit';
|
CREATE USER IF NOT EXISTS 'root'@'grit';
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'grit' WITH GRANT OPTION;
|
GRANT PROXY ON ''@'%' TO 'root'@'grit' WITH GRANT OPTION;
|
CREATE USER IF NOT EXISTS ''@'localhost';
|
GRANT USAGE ON *.* TO ''@'localhost';
|
CREATE USER IF NOT EXISTS 'bob'@'localhost' IDENTIFIED BY PASSWORD '*8BBB9D40A7CA31A54D42E2422DDD54EE47799B29';
|
GRANT USAGE ON *.* TO 'bob'@'localhost' IDENTIFIED BY PASSWORD '*8BBB9D40A7CA31A54D42E2422DDD54EE47799B29';
|
CREATE USER IF NOT EXISTS 'root'@'localhost';
|
GRANT boss TO 'root'@'localhost' WITH ADMIN OPTION;
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
|
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION;
|
CREATE ROLE IF NOT EXISTS boss WITH ADMIN 'root'@'localhost';
|
GRANT middle TO 'boss' WITH ADMIN OPTION;
|
GRANT USAGE ON *.* TO 'boss';
|
GRANT USAGE ON *.* TO 'middle';
|
CREATE ROLE IF NOT EXISTS middle WITH ADMIN 'boss';
|
GRANT USAGE ON *.* TO 'middle';
|
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
Attachments
Issue Links
- relates to
-
MDEV-19558 Dump "mysql" system database first on "mysqldump --all-databases"
-
- Open
-
-
-
- Stalled
-
-
MDEV-22311 implement SHOW CREATE ROLE
-
- Open
-
-
-
- Closed
-
-
-
- Open
-
-
MDEV-29254 Easy upgrade from MySQL 8.0 to MariaDB
-
- Open
-