MDEV-23630: mysqldump logically dump system table information

Add --system={all, users, plugins, udfs, servers, stats, timezones}

This will dump system information from the server in
a logical form like:
* CREATE USER
* GRANT
* SET DEFAULT ROLE
* CREATE ROLE
* CREATE SERVER
* INSTALL PLUGIN
* CREATE FUNCTION

"stats" is the innodb statistics tables or EITS and
these are dumped as INSERT/REPLACE INTO statements
without recreating the table.

"timezones" is the collection of timezone tables
which are important to transfer to generate identical
results on restoration.

Two other options have an effect on the SQL generated by
--system=all. These are mutually exclusive of each other.
* --replace
* --insert-ignore

--replace will include "OR REPLACE" into the logical form
like:
* CREATE OR REPLACE USER ...
* DROP ROLE IF EXISTS (MySQL-8.0+)
* CREATE OR REPLACE ROLE ...
* UNINSTALL PLUGIN IF EXISTS (10.4+) ... (before INSTALL PLUGIN)
* DROP FUNCTION IF EXISTS (MySQL-5.7+)
* CREATE OR REPLACE [AGGREGATE] FUNCTION
* CREATE OR REPLACE SERVER

--insert-ignore uses the construct " IF NOT EXISTS" where
supported in the logical syntax.

'CREATE OR REPLACE USER' includes protection against
being run as the same user that is importing the mysqldump.

Includes experimental support for dumping mysql-5.7/8.0
system tables and exporting logical SQL compatible with MySQL.

Updates mysqldump man page, including this information and
(removing obsolute bug reference)

Reviewed-by: anel@mariadb.org

1 files changed, 108 insertions, 9 deletions

diff --git a/man/mysqldump.1 b/man/mysqldump.1
index 7594703d20a..ee2048d0008 100644
--- a/man/mysqldump.1
+++ b/man/mysqldump.1
@@ -1,6 +1,6 @@
 '\" t
 .\"
-.TH "\FBMYSQLDUMP\FR" "1" "3 April 2017" "MariaDB 10\&.2" "MariaDB Database System"
+.TH "\FBMYSQLDUMP\FR" "1" "24 October 2020" "MariaDB 10\&.2" "MariaDB Database System"
 .\" -----------------------------------------------------------------
 .\" * set default formatting
 .\" -----------------------------------------------------------------
@@ -36,7 +36,7 @@ tables, consider using the
 instead because it can accomplish faster backups and faster restores\&. See
 \fBmysqlhotcopy\fR(1)\&.
 .PP
-There are three general ways to invoke
+There are four general ways to invoke
 \fBmysqldump\fR:
 .sp
 .if n \{\
@@ -46,6 +46,7 @@ There are three general ways to invoke
 shell> \fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIdb_name\fR\fR\fB [\fR\fB\fItbl_name\fR\fR\fB \&.\&.\&.]\fR
 shell> \fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] \-\-databases \fR\fB\fIdb_name\fR\fR\fB \&.\&.\&.\fR
 shell> \fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] \-\-all\-databases\fR
+shell> \fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] \-\-system={options}\fR
 .fi
 .if n \{\
 .RE
@@ -2192,6 +2193,110 @@ Verify server's "Common Name" in its cert against hostname used when connecting.
 .sp -1
 .IP \(bu 2.3
 .\}
+.\" mysqladmin: Dump system tables option
+.\" Dump system tables option: mysqladmin
+\fB\-\-system=\fR\fB\fI{all, users, plugins, udfs, servers, stats, timezones}\fR\fR
+.sp
+Dump the system tables in the mysql database in a logical form\&. This option is an empty set by default\&.
+.sp
+One or more options can be listed in comma separated list\&.
+.sp
+The options here are:
+.sp
+.RS 4
+.ie n \{\
+\h'-04'\(bu\h'+03'\c
+.\}
+.el \{\
+.sp -1
+.IP \(bu 2.3
+.\}
+all \- an alias to enabling all of the below options\&.
+.RE
+.RS 4
+.ie n \{\
+\h'-04'\(bu\h'+03'\c
+.\}
+.el \{\
+.sp -1
+.IP \(bu 2.3
+.\}
+users \- the users, roles and their grants outputed as \fBCREATE USER\fB, \fBCREATE ROLE\fR, \fBGRANT\fR, and \fBSET DEFAULT ROLE\fR (\fBALTER USER\fR for MySQL-8.0+)\&.
+.RE
+.RS 4
+.ie n \{\
+\h'-04'\(bu\h'+03'\c
+.\}
+.el \{\
+.sp -1
+.IP \(bu 2.3
+.\}
+plugins \- active plugins of the server outputed as \fBINSTALL PLUGIN\fR\&.
+.RE
+.RS 4
+.ie n \{\
+\h'-04'\(bu\h'+03'\c
+.\}
+.el \{\
+.sp -1
+.IP \(bu 2.3
+.\}
+udfs \- user define functions outputed as \fBCREATE FUNCTION\fR\&.
+.RE
+.RS 4
+.ie n \{\
+\h'-04'\(bu\h'+03'\c
+.\}
+.el \{\
+.sp -1
+.IP \(bu 2.3
+.\}
+servers \- remote (federated) servers as \fBCREATE SERVER\fR\&.
+.RE
+.RS 4
+.ie n \{\
+\h'-04'\(bu\h'+03'\c
+.\}
+.el \{\
+.sp -1
+.IP \(bu 2.3
+.\}
+stats \- statistics tables, InnoDB and Engine Independent Table Statistics (EITS), are dumped as \fBINSERT\fR/\fBREPLACE INFO\fR statements without (re)creating tables\&.
+.RE
+.RS 4
+.ie n \{\
+\h'-04'\(bu\h'+03'\c
+.\}
+.el \{\
+.sp -1
+.IP \(bu 2.3
+.\}
+timezones \- timezone related system tables dumped as \fBINSERT\fR/\fBREPLACE INTO\fR statements without (re)creating tables\&.
+.RE
+.sp
+The format of the output is affected by \fB\-\-replace\fR and \fB\-\-insert\-into\fR\&. The \fB\-\-replace\fR option will output \fBCREATE OR REPLACE\fR
+forms of SQL, and also \fBDROP IF EXISTS\fR prior to \fBCREATE\fR, if a \fBCREATE OR REPLACE\fR option isn't available.
+.sp
+With \fB\-\-system=user\fR (or \fBall\fR), and \fB\-\-replace\fR, SQL is generated to generate an error if attempting to import the dump with a connection user that is being replaced within the dump\&.
+.sp
+The \fB\-\-insert\-into\fR option will cause \fBCREATE IF NOT EXIST\fR forms of SQL to generated if available.
+.sp
+For stats, and timezones, \fB\-\-replace\fR and \fB\-\-insert\-info\fR have the usual effects.
+.sp
+Enabling specific options here will cause the relevant tables in the mysql database to be ignored when dumping the mysql database or \fB\-\-all\-databases\fR\&.
+.sp
+Experimentally this option is designed to be able to dump system information from MySQL-5\&.7 and 8\&.0 servers\&. SQL generated is also
+experimentally compatible with MySQL-5\&.7/8\&.0\&. Mappings of implemenation specific grants/plugins isn't always one-to-one however\&.
+.sp
+.RE
+.RS 4
+.ie n \{\
+\h'-04'\(bu\h'+03'\c
+.\}
+.el \{\
+.sp -1
+.IP \(bu 2.3
+.\}
 .\" mysqldump: tab option
 .\" tab option: mysqldump
 \fB\-\-tab=\fR\fB\fIpath\fR\fR,
@@ -2673,7 +2778,7 @@ If you encounter problems backing up views, please read the section that covers
 .SH "COPYRIGHT"
 .br
 .PP
-Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc., 2010-2015 MariaDB Foundation
+Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc., 2010-2020 MariaDB Foundation
 .PP
 This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.
 .PP
@@ -2681,12 +2786,6 @@ This documentation is distributed in the hope that it will be useful, but WITHOU
 .PP
 You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA or see http://www.gnu.org/licenses/.
 .sp
-.SH "NOTES"
-.IP " 1." 4
-Bug#30123
-.RS 4
-\%http://bugs.mysql.com/bug.php?id=30123
-.RE
 .SH "SEE ALSO"
 For more information, please refer to the MariaDB Knowledge Base, available online at https://mariadb.com/kb/
 .SH AUTHOR