Type:
Task
Priority:
Major
Resolution:
Fixed
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 */;
{"report":{"fcp":713.2999999523163,"ttfb":155.39999985694885,"pageVisibility":"visible","entityId":91279,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"02b039b9-78ac-4548-8da3-391b5aa34b0e","navigationType":0,"readyForUser":803.1999998092651,"redirectCount":0,"resourceLoadedEnd":861.5999999046326,"resourceLoadedStart":162.09999990463257,"resourceTiming":[{"duration":19.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":162.09999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":162.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":181.59999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":19.299999952316284,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":162.39999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":162.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":181.69999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":71.89999985694885,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":162.5,"connectEnd":162.5,"connectStart":162.5,"domainLookupEnd":162.5,"domainLookupStart":162.5,"fetchStart":162.5,"redirectEnd":0,"redirectStart":0,"requestStart":162.5,"responseEnd":234.39999985694885,"responseStart":234.39999985694885,"secureConnectionStart":162.5},{"duration":185.20000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":162.59999990463257,"connectEnd":162.59999990463257,"connectStart":162.59999990463257,"domainLookupEnd":162.59999990463257,"domainLookupStart":162.59999990463257,"fetchStart":162.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":162.59999990463257,"responseEnd":347.7999999523163,"responseStart":347.7999999523163,"secureConnectionStart":162.59999990463257},{"duration":189,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":162.79999995231628,"connectEnd":162.79999995231628,"connectStart":162.79999995231628,"domainLookupEnd":162.79999995231628,"domainLookupStart":162.79999995231628,"fetchStart":162.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":162.79999995231628,"responseEnd":351.7999999523163,"responseStart":351.7999999523163,"secureConnectionStart":162.79999995231628},{"duration":189.70000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":162.79999995231628,"connectEnd":162.79999995231628,"connectStart":162.79999995231628,"domainLookupEnd":162.79999995231628,"domainLookupStart":162.79999995231628,"fetchStart":162.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":162.79999995231628,"responseEnd":352.5,"responseStart":352.5,"secureConnectionStart":162.79999995231628},{"duration":190.59999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":353.59999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":190.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":163,"connectEnd":163,"connectStart":163,"domainLookupEnd":163,"domainLookupStart":163,"fetchStart":163,"redirectEnd":0,"redirectStart":0,"requestStart":163,"responseEnd":353.09999990463257,"responseStart":353.09999990463257,"secureConnectionStart":163},{"duration":190.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":163.19999980926514,"connectEnd":163.19999980926514,"connectStart":163.19999980926514,"domainLookupEnd":163.19999980926514,"domainLookupStart":163.19999980926514,"fetchStart":163.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":163.19999980926514,"responseEnd":353.59999990463257,"responseStart":353.59999990463257,"secureConnectionStart":163.19999980926514},{"duration":191,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":163.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":163.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":354.5,"responseStart":0,"secureConnectionStart":0},{"duration":191,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":163.5,"connectEnd":163.5,"connectStart":163.5,"domainLookupEnd":163.5,"domainLookupStart":163.5,"fetchStart":163.5,"redirectEnd":0,"redirectStart":0,"requestStart":163.5,"responseEnd":354.5,"responseStart":354.5,"secureConnectionStart":163.5},{"duration":416.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":164.59999990463257,"connectEnd":164.59999990463257,"connectStart":164.59999990463257,"domainLookupEnd":164.59999990463257,"domainLookupStart":164.59999990463257,"fetchStart":164.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":164.59999990463257,"responseEnd":580.6999998092651,"responseStart":580.6999998092651,"secureConnectionStart":164.59999990463257},{"duration":480.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":164.59999990463257,"connectEnd":164.59999990463257,"connectStart":164.59999990463257,"domainLookupEnd":164.59999990463257,"domainLookupStart":164.59999990463257,"fetchStart":164.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":164.59999990463257,"responseEnd":644.7999999523163,"responseStart":644.7999999523163,"secureConnectionStart":164.59999990463257},{"duration":191.79999995231628,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":389.39999985694885,"connectEnd":389.39999985694885,"connectStart":389.39999985694885,"domainLookupEnd":389.39999985694885,"domainLookupStart":389.39999985694885,"fetchStart":389.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":389.39999985694885,"responseEnd":581.1999998092651,"responseStart":581.1999998092651,"secureConnectionStart":389.39999985694885},{"duration":35.799999952316284,"initiatorType":"link","name":"https://jira.mariadb.org/s/d5715adaadd168a9002b108b2b039b50-CDN/lu2bu7/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/css/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":615.2999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":615.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":651.0999999046326,"responseStart":0,"secureConnectionStart":0},{"duration":240.80000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/e65b778d185daf5aee24936755b43da6/_/download/contextbatch/js/browser-metrics-plugin.contrib,-_super,-project.issue.navigator,-jira.view.issue,-atl.general/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":616.1999998092651,"connectEnd":616.1999998092651,"connectStart":616.1999998092651,"domainLookupEnd":616.1999998092651,"domainLookupStart":616.1999998092651,"fetchStart":616.1999998092651,"redirectEnd":0,"redirectStart":0,"requestStart":616.1999998092651,"responseEnd":857,"responseStart":857,"secureConnectionStart":616.1999998092651},{"duration":245.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/f51ef5507eea4c158f257c66c93b2a3f-CDN/lu2bu7/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/js/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":616.5,"connectEnd":616.5,"connectStart":616.5,"domainLookupEnd":616.5,"domainLookupStart":616.5,"fetchStart":616.5,"redirectEnd":0,"redirectStart":0,"requestStart":616.5,"responseEnd":861.5999999046326,"responseStart":861.5999999046326,"secureConnectionStart":616.5},{"duration":168.5,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":705.2999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":705.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":873.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":204.60000014305115,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":728.8999998569489,"connectEnd":728.8999998569489,"connectStart":728.8999998569489,"domainLookupEnd":728.8999998569489,"domainLookupStart":728.8999998569489,"fetchStart":728.8999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":728.8999998569489,"responseEnd":933.5,"responseStart":933.5,"secureConnectionStart":728.8999998569489}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":12,"responseStart":155,"responseEnd":160,"domLoading":159,"domInteractive":874,"domContentLoadedEventStart":874,"domContentLoadedEventEnd":931,"domComplete":1118,"loadEventStart":1118,"loadEventEnd":1119,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":844.6999998092651},{"name":"bigPipe.sidebar-id.end","time":845.3999998569489},{"name":"bigPipe.activity-panel-pipe-id.start","time":845.5999999046326},{"name":"bigPipe.activity-panel-pipe-id.end","time":847.2999999523163},{"name":"activityTabFullyLoaded","time":954.3999998569489}],"measures":[],"correlationId":"cd8283be6eee45","effectiveType":"4g","downlink":9.5,"rtt":0,"serverDuration":87,"dbReadsTimeInMs":18,"dbConnsTimeInMs":25,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
WIP: https://github.com/MariaDB/server/compare/10.2...grooverdan:10.2-MDEV-23630-mysqldump-native-sql?expand=1
Needs tests.