[MDEV-23630] mysqldump to logically dump system tables Created: 2020-08-30  Updated: 2023-09-26  Resolved: 2020-10-31

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Fix Version/s: 10.2.37, 10.3.28, 10.4.18, 10.5.9

Type: Task Priority: Major
Reporter: Daniel Black Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-19558 Dump "mysql" system database first on... Open
relates to MDEV-22127 MariaDB 10.4 can't import database du... Stalled
relates to MDEV-22311 implement SHOW CREATE ROLE Open
relates to MDEV-22313 SHOW GRANTS does not prints a user's ... Closed
relates to MDEV-22645 default_role gets removed when migrat... Open
relates to MDEV-29254 Easy upgrade from MySQL 8.0 to MariaDB Open

 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 */;



 Comments   
Comment by Daniel Black [ 2020-08-30 ]

WIP: https://github.com/MariaDB/server/compare/10.2...grooverdan:10.2-MDEV-23630-mysqldump-native-sql?expand=1

Needs tests.

Comment by Daniel Black [ 2020-08-31 ]

GeoffMontee I see from MDEV-22313 you are significantly interested in this. I haven't accounted for default roles yet but haven't forgotten/ignored it yet.

If you have any insights on ordering of output or tests cases here that would be appreciated.

Undoubtedly I'll find a few more bugs as I write test cases.

Comment by Geoff Montee (Inactive) [ 2020-08-31 ]

Thanks, danblack. This sounds like a cool idea to me. Some questions:

  • Do we also need to dump sequences in a special way? I am not sure how those are currently dumped.
  • Does --master-data work with multi-source replication, or is that another limitation that can be fixed?
Comment by Daniel Black [ 2020-08-31 ]

anel is working on the dumping of sequences to cover MDEV-21786 and avoid triggering MDEV-20070

--master-data is the local gtid and binlog position. only slave-data is relevant to multi-source and it appears to be implemented.

I'm tempted to implement --system=stats,timezones so they get included under all, that way the statistics of the engines and the timezones are migrated to ensure queries stay the same. With this I'm pretty sure that the "mysql" database can be skipped when going to a new version. I want to look closer to see particular the stats tables to see if they have changed over time. A subgoal is to migrate this over without requiring mysql_update on the destination so I don't want to break that goal on a whim.

Comment by Daniel Black [ 2020-10-26 ]

bb-10.2-danielblack-MDEV-23630-mysqldump-native-sql

Ready for review.

anel covered all but the `dump_all_users_and_grants`

Comment by cybernet2u [ 2022-05-02 ]

dumping users from MySQL and then try to import them in a MariaDB ... it's futile

Generated at Thu Feb 08 09:23:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.