Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23630

mysqldump to logically dump system tables

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              danblack Daniel Black
              Reporter:
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration