diff options
| -rw-r--r-- | client/mysqldump.c | 563 | ||||
| -rw-r--r-- | man/mysqldump.1 | 117 | ||||
| -rw-r--r-- | mysql-test/r/mysqldump-system,win.rdiff | 120 | ||||
| -rw-r--r-- | mysql-test/r/mysqldump-system.result | 467 | ||||
| -rw-r--r-- | mysql-test/t/mysqldump-system.test | 152 |
5 files changed, 1400 insertions, 19 deletions
diff --git a/client/mysqldump.c b/client/mysqldump.c index b33c5ecc010..cc75d32d7e3 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -39,7 +39,8 @@ ** 10 Jun 2003: SET NAMES and --no-set-names by Alexander Barkov */ -#define DUMP_VERSION "10.16" +/* on merge conflict, bump to a higher version again */ +#define DUMP_VERSION "10.19" #include <my_global.h> #include <my_sys.h> @@ -116,6 +117,21 @@ static my_bool verbose= 0, opt_no_create_info= 0, opt_no_data= 0, opt_no_data_m opt_events= 0, opt_comments_used= 0, opt_alltspcs=0, opt_notspcs= 0, opt_logging, opt_drop_trigger= 0 ; +#define OPT_SYSTEM_ALL 1 +#define OPT_SYSTEM_USERS 2 +#define OPT_SYSTEM_PLUGINS 4 +#define OPT_SYSTEM_UDFS 8 +#define OPT_SYSTEM_SERVERS 16 +#define OPT_SYSTEM_STATS 32 +#define OPT_SYSTEM_TIMEZONES 64 +static const char *opt_system_type_values[]= + {"all", "users", "plugins", "udfs", "servers", "stats", "timezones"}; +static TYPELIB opt_system_types= +{ + array_elements(opt_system_type_values), "system dump options", + opt_system_type_values, NULL +}; +static ulonglong opt_system= 0ULL; static my_bool insert_pat_inited= 0, debug_info_flag= 0, debug_check_flag= 0; static ulong opt_max_allowed_packet, opt_net_buffer_length; static MYSQL mysql_connection,*mysql=0; @@ -525,6 +541,8 @@ static struct my_option my_long_options[] = &opt_mysql_unix_port, &opt_mysql_unix_port, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, #include <sslopt-longopts.h> + {"system", 256, "Dump system tables as portable SQL", + &opt_system, &opt_system, &opt_system_types, GET_SET, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"tab",'T', "Create tab-separated textfile for each table to given path. (Create .sql " "and .txt files.) NOTE: This only works if mysqldump is run on the same " @@ -567,7 +585,7 @@ static const char *load_default_groups[]= static void maybe_exit(int error); static void die(int error, const char* reason, ...); static void maybe_die(int error, const char* reason, ...); -static void write_header(FILE *sql_file, char *db_name); +static void write_header(FILE *sql_file, const char *db_name); static void print_value(FILE *file, MYSQL_RES *result, MYSQL_ROW row, const char *prefix,const char *name, int string_value); @@ -578,6 +596,12 @@ static int init_dumping_tables(char *); static int init_dumping(char *, int init_func(char*)); static int dump_databases(char **); static int dump_all_databases(); +static int dump_all_users_roles_and_grants(); +static int dump_all_plugins(); +static int dump_all_udfs(); +static int dump_all_servers(); +static int dump_all_stats(); +static int dump_all_timezones(); static char *quote_name(const char *name, char *buff, my_bool force); char check_if_ignore_table(const char *table_name, char *table_type); static char *primary_key_fields(const char *table_name); @@ -639,9 +663,10 @@ static void print_version(void) static void short_usage_sub(FILE *f) { fprintf(f, "Usage: %s [OPTIONS] database [tables]\n", my_progname_short); - fprintf(f, "OR %s [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]\n", + fprintf(f, "OR %s [OPTIONS] --databases DB1 [DB2 DB3...]\n", my_progname_short); - fprintf(f, "OR %s [OPTIONS] --all-databases [OPTIONS]\n", my_progname_short); + fprintf(f, "OR %s [OPTIONS] --all-databases\n", my_progname_short); + fprintf(f, "OR %s [OPTIONS] --system=[SYSTEMOPTIONS]]\n", my_progname_short); } @@ -689,7 +714,7 @@ static const char *fix_for_comment(const char *ident) } -static void write_header(FILE *sql_file, char *db_name) +static void write_header(FILE *sql_file, const char *db_name) { if (opt_xml) { @@ -1027,6 +1052,70 @@ static int get_options(int *argc, char ***argv) if ((ho_error= handle_options(argc, argv, my_long_options, get_one_option))) return(ho_error); + if (opt_system & OPT_SYSTEM_ALL) + opt_system|= ~0; + + if (opt_system & OPT_SYSTEM_USERS && + (my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.db", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.global_priv", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.tables_priv", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.columns_priv", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.procs_priv", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.roles_mapping", MYF(MY_WME))) || + /* and MySQL-8.0 role tables (role_edges and default_roles) as well */ + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.role_edges", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.default_roles", MYF(MY_WME))))) + return(EX_EOM); + + if (opt_system & OPT_SYSTEM_PLUGINS && + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.plugin", MYF(MY_WME)))) + return(EX_EOM); + + if (opt_system & OPT_SYSTEM_UDFS && + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.func", MYF(MY_WME)))) + return(EX_EOM); + + if (opt_system & OPT_SYSTEM_SERVERS && + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.servers", MYF(MY_WME)))) + return(EX_EOM); + + if (opt_system & OPT_SYSTEM_STATS && + (my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.column_stats", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.index_stats", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.table_stats", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.innodb_table_stats", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.innodb_index_stats", MYF(MY_WME))))) + return(EX_EOM); + + if (opt_system & OPT_SYSTEM_TIMEZONES && + (my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.time_zone", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.time_zone_leap_second", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.time_zone_name", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.time_zone_transition", MYF(MY_WME))) || + my_hash_insert(&ignore_table, + (uchar*) my_strdup("mysql.time_zone_transition_type", MYF(MY_WME))))) + return(EX_EOM); + *mysql_params->p_max_allowed_packet= opt_max_allowed_packet; *mysql_params->p_net_buffer_length= opt_net_buffer_length; if (debug_info_flag) @@ -1084,7 +1173,7 @@ static int get_options(int *argc, char ***argv) !(charset_info= get_charset_by_csname(default_charset, MY_CS_PRIMARY, MYF(MY_WME)))) exit(1); - if ((*argc < 1 && !opt_alldbs) || (*argc > 0 && opt_alldbs)) + if ((*argc < 1 && (!opt_alldbs && !opt_system)) || (*argc > 0 && opt_alldbs)) { short_usage(stderr); return EX_USAGE; @@ -1208,7 +1297,6 @@ static int fetch_db_collation(const char *db_name, my_bool err_status= FALSE; MYSQL_RES *db_cl_res; MYSQL_ROW db_cl_row; - if (mysql_select_db(mysql, db_name)) { DB_error(mysql, "when selecting the database"); @@ -2721,7 +2809,7 @@ static inline my_bool general_log_or_slow_log_tables(const char *db, number of fields in table, 0 if error */ -static uint get_table_structure(char *table, char *db, char *table_type, +static uint get_table_structure(const char *table, const char *db, char *table_type, char *ignore_flag) { my_bool init=0, delayed, write_data, complete_insert; @@ -3647,7 +3735,7 @@ static char *alloc_query_str(ulong size) */ -static void dump_table(char *table, char *db, const uchar *hash_key, size_t len) +static void dump_table(const char *table, const char *db, const uchar *hash_key, size_t len) { char ignore_flag; char buf[200], table_buff[NAME_LEN+3]; @@ -4166,6 +4254,442 @@ static char *getTableName(int reset) /* + dump user/role grants + ARGS + user_role: is either a user, or a role +*/ + +static int dump_grants(const char *user_role) +{ + DYNAMIC_STRING sqlbuf; + MYSQL_ROW row; + MYSQL_RES *tableres; + + init_dynamic_string_checked(&sqlbuf, "SHOW GRANTS FOR ", 256, 1024); + dynstr_append_checked(&sqlbuf, user_role); + + if (mysql_query_with_error_report(mysql, &tableres, sqlbuf.str)) + { + dynstr_free(&sqlbuf); + return 1; + } + while ((row= mysql_fetch_row(tableres))) + { + if (strncmp(row[0], "SET DEFAULT ROLE", sizeof("SET DEFAULT ROLE") - 1) == 0) + continue; + fprintf(md_result_file, "%s;\n", row[0]); + } + mysql_free_result(tableres); + dynstr_free(&sqlbuf); + return 0; +} + + +/* + dump create user +*/ + +static int dump_create_user(const char *user) +{ + DYNAMIC_STRING sqlbuf; + MYSQL_ROW row; + MYSQL_RES *tableres; + + init_dynamic_string_checked(&sqlbuf, "SHOW CREATE USER ", 256, 1024); + dynstr_append_checked(&sqlbuf, user); + + if (mysql_query_with_error_report(mysql, &tableres, sqlbuf.str)) + { + dynstr_free(&sqlbuf); + return 1; + } + while ((row= mysql_fetch_row(tableres))) + { + fprintf(md_result_file, "CREATE %sUSER %s%s;\n", opt_replace_into ? "/*M!100103 OR REPLACE */ ": "", + opt_ignore ? "IF NOT EXISTS " : "", + row[0] + sizeof("CREATE USER")); + } + mysql_free_result(tableres); + dynstr_free(&sqlbuf); + return 0; +} + + +/* + dump all users, roles and their grants +*/ + +static int dump_all_users_roles_and_grants() +{ + MYSQL_ROW row; + MYSQL_RES *tableres; + int result= 0; + /* Roles added in MariaDB-10.0.5 or MySQL-8.0 */ + my_bool maria_roles_exist= (mysql_get_server_version(mysql) >= 100005); + my_bool mysql_roles_exist= (mysql_get_server_version(mysql) >= 80001) && !maria_roles_exist; + + if (mysql_query_with_error_report(mysql, &tableres, + "SELECT CONCAT(QUOTE(u.user), '@', QUOTE(u.Host)) AS u " + "FROM mysql.user u " + " /*!80001 LEFT JOIN mysql.role_edges e " + " ON u.user=e.from_user " + " AND u.host=e.from_host " + " WHERE e.from_user IS NULL */" + " /*M!100005 WHERE is_role='N' */")) + return 1; + while ((row= mysql_fetch_row(tableres))) + { + if (opt_replace_into) + /* Protection against removing the current import user */ + /* MySQL-8.0 export capability */ + fprintf(md_result_file, + "DELIMITER |\n" + "/*M!100101 IF current_user()=\"%s\" THEN\n" + " SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001," + " MESSAGE_TEXT=\"Don't remove current user %s'\";\n" + "END IF */|\n" + "DELIMITER ;\n" + "/*!50701 DROP USER IF EXISTS %s */;\n", row[0], row[0], row[0]); + if (dump_create_user(row[0])) + result= 1; + /* if roles exist, defer dumping grants until after roles created */ + if (maria_roles_exist || mysql_roles_exist) + continue; + if (dump_grants(row[0])) + result= 1; + } + mysql_free_result(tableres); + + if (!(maria_roles_exist || mysql_roles_exist)) + goto exit; + + /* + Preserve current role active role, in case this dump is imported + in the same connection that assumes the active role at the beginning + is the same as at the end of the connection. This is so: + + #!/bin/sh + ( + echo "set role special_role; "; + cat mysqldump.sql; + echo "$dosomethingspecial" + ) | mysql -h $host + + doesn't end up with a suprise that the $dosomethingspecial cannot + be done because `special_role` isn't active. + + We create a new role for importing that becomes the default admin for new + roles. This is because without being a admin on new roles we don't + have the necessary privileges to grant users to a created role or to + create new admins for the created role. + + At the end of the import the mariadb_dump_import_role is be dropped, + which implictly drops all its admin aspects of the dropped role. + This is significiantly easlier than revoking the ADMIN of each role + from the current user. + */ + fputs("SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role;\n" + "CREATE ROLE IF NOT EXISTS mariadb_dump_import_role;\n" + "GRANT mariadb_dump_import_role TO CURRENT_USER();\n" + "SET ROLE mariadb_dump_import_role;\n" + , md_result_file); + /* No show create role yet, MDEV-22311 */ + /* Roles, with user admins first, then roles they administer, and recurse on that */ + if (maria_roles_exist && mysql_query_with_error_report(mysql, &tableres, + "WITH RECURSIVE create_role_order AS" + " (SELECT 1 as n, roles_mapping.*" + " FROM mysql.roles_mapping" + " JOIN mysql.user USING (user,host)" + " WHERE is_role='N'" + " AND Admin_option='Y'" + " UNION SELECT c.n+1, r.*" + " FROM create_role_order c" + " JOIN mysql.roles_mapping r ON c.role=r.user" + " AND r.host=''" + " AND r.Admin_option='Y') " + "SELECT QUOTE(ROLE) AS r," + " CONCAT(QUOTE(user)," + " IF(HOST='', '', CONCAT('@', QUOTE(HOST)))) AS c," + " Admin_option " + "FROM create_role_order ORDER BY n, r, user")) + return 1; + /* + TODO Mysql - misses roles that have no admin or role members. + MySQL roles don't require an admin. + */ + if (mysql_roles_exist && mysql_query_with_error_report(mysql, &tableres, + "WITH RECURSIVE create_role_order AS" + " (SELECT 1 AS n," + " re.*" + " FROM mysql.role_edges re" + " JOIN mysql.user u ON re.TO_HOST=u.HOST" + " AND re.TO_USER = u.USER" + " LEFT JOIN mysql.role_edges re2 ON re.TO_USER=re2.FROM_USER" + " AND re2.TO_HOST=re2.FROM_HOST" + " WHERE re2.FROM_USER IS NULL" + " UNION SELECT c.n+1," + " re.*" + " FROM create_role_order c" + " JOIN mysql.role_edges re ON c.FROM_USER=re.TO_USER" + " AND c.FROM_HOST=re.TO_HOST) " + "SELECT CONCAT(QUOTE(FROM_USER), '/*!80001 @', QUOTE(FROM_HOST), '*/') AS r," + " CONCAT(QUOTE(TO_USER), IF(n=1, CONCAT('@', QUOTE(TO_HOST))," + " CONCAT('/*!80001 @', QUOTE(TO_HOST), ' */'))) AS u," + " WITH_ADMIN_OPTION " + "FROM create_role_order " + "ORDER BY n," + " FROM_USER," + " FROM_HOST," + " TO_USER," + " TO_HOST," + " WITH_ADMIN_OPTION")) + return 1; + while ((row= mysql_fetch_row(tableres))) + { + /* MySQL-8.0 export capability */ + if (opt_replace_into) + fprintf(md_result_file, + "/*!80001 DROP ROLE IF EXISTS %s */;\n", row[0]); + fprintf(md_result_file, + "/*!80001 CREATE ROLE %s%s */;\n", opt_ignore ? "IF NOT EXISTS " : "", row[0]); + /* By default created with current role */ + fprintf(md_result_file, + "%sROLE %s%s WITH ADMIN mariadb_dump_import_role */;\n", + opt_replace_into ? "/*M!100103 CREATE OR REPLACE ": "/*M!100005 CREATE ", + opt_ignore ? "IF NOT EXISTS " : "", row[0]); + fprintf(md_result_file, "/*M!100005 GRANT %s TO %s%s*/;\n", + row[0], row[1], (row[2][0] == 'Y') ? " WITH ADMIN OPTION " : ""); + } + mysql_free_result(tableres); + + /* users and their default role */ + if (maria_roles_exist && mysql_query_with_error_report(mysql, &tableres, + "select IF(default_role='', 'NONE', QUOTE(default_role)) as r," + "concat(QUOTE(User), '@', QUOTE(Host)) as u FROM mysql.user " + "/*M!100005 WHERE is_role='N' */")) + return 1; + if (mysql_roles_exist && mysql_query_with_error_report(mysql, &tableres, + "SELECT IF(DEFAULT_ROLE_HOST IS NULL, 'NONE', CONCAT(QUOTE(DEFAULT_ROLE_USER)," + " '@', QUOTE(DEFAULT_ROLE_HOST))) as r," + " CONCAT(QUOTE(mu.USER),'@',QUOTE(mu.HOST)) as u " + "FROM mysql.user mu LEFT JOIN mysql.default_roles using (USER, HOST)")) + return 1; + while ((row= mysql_fetch_row(tableres))) + { + if (dump_grants(row[1])) + result= 1; + fprintf(md_result_file, "/*M!100005 SET DEFAULT ROLE %s FOR %s */;\n", row[0], row[1]); + fprintf(md_result_file, "/*!80001 ALTER USER %s DEFAULT ROLE %s */;\n", row[1], row[0]); + } + mysql_free_result(tableres); + + if (maria_roles_exist && mysql_query_with_error_report(mysql, &tableres, + "SELECT DISTINCT QUOTE(m.role) AS r " + " FROM mysql.roles_mapping m" + " JOIN mysql.user u ON u.user = m.role" + " WHERE is_role='Y'" + " AND Admin_option='Y'" + " ORDER BY m.role")) + return 1; + if (mysql_roles_exist && mysql_query_with_error_report(mysql, &tableres, + "SELECT DISTINCT CONCAT(QUOTE(FROM_USER),'@', QUOTE(FROM_HOST)) AS r " + "FROM mysql.role_edges")) + return 1; + while ((row= mysql_fetch_row(tableres))) + { + if (dump_grants(row[0])) + result= 1; + } + mysql_free_result(tableres); + /* switch back */ + fputs("SET ROLE NONE;\n" + "DROP ROLE mariadb_dump_import_role;\n" + "/*M!100203 EXECUTE IMMEDIATE CONCAT('SET ROLE ', @current_role) */;\n", + md_result_file); +exit: + + return result; +} + + +/* + dump all plugins +*/ + +static int dump_all_plugins() +{ + MYSQL_ROW row; + MYSQL_RES *tableres; + + if (mysql_query_with_error_report(mysql, &tableres, "SHOW PLUGINS")) + return 1; + /* Name, Status, Type, Library, License */ + while ((row= mysql_fetch_row(tableres))) + { + if (strcmp("ACTIVE", row[1]) != 0) + continue; + /* Should we be skipping builtins? */ + if (row[3] == NULL) + continue; + if (opt_replace_into) + { + fprintf(md_result_file, "/*M!100401 UNINSTALL PLUGIN IF EXIST %s */;\n", + row[0]); + } + fprintf(md_result_file, + "INSTALL PLUGIN %s %s SONAME '%s';\n", row[0], + opt_ignore ? "/*M!100401 IF NOT EXISTS */" : "", row[3]); + } + mysql_free_result(tableres); + + return 0; +} + + +/* + dump all udfs +*/ + +static int dump_all_udfs() +{ + /* we don't support all these types yet, but get prepared if we do */ + static const char *udf_types[] = {"STRING", "REAL", "INT", "ROW", "DECIMAL", "TIME" }; + MYSQL_ROW row; + MYSQL_RES *tableres; + int retresult, result= 0; + + if (mysql_query_with_error_report(mysql, &tableres, "SELECT * FROM mysql.func")) + return 1; + /* Name, ret, dl, type*/ + while ((row= mysql_fetch_row(tableres))) + { + retresult= atoi(row[1]); + if (retresult < 0 || array_elements(udf_types) <= (size_t) retresult) + { + fprintf(stderr, "%s: Error: invalid return type on udf function '%s'\n", + my_progname_short, row[0]); + result= 1; + continue; + } + if (opt_replace_into) + { + fprintf(md_result_file, "/*!50701 DROP FUNCTION IF EXISTS %s */;\n", + row[0]); + } + fprintf(md_result_file, + "CREATE %s%sFUNCTION %s%s RETURNS %s SONAME '%s';\n", + opt_replace_into ? "/*M!100103 OR REPLACE */ ": "", + (strcmp("AGGREGATE", row[2])==0 ? "AGGREGATE " : ""), + opt_ignore ? "IF NOT EXISTS " : "", row[0], udf_types[retresult], row[2]); + } + mysql_free_result(tableres); + + return result; +} + + +/* + dump all servers +*/ + +static int dump_all_servers() +{ + /* No create server yet - MDEV-15696 */ + MYSQL_ROW row; + MYSQL_RES *tableres; + MYSQL_FIELD *f; + unsigned int num_fields, i; + my_bool comma_prepend= 0; + const char *qstring; + + if (mysql_query_with_error_report(mysql, &tableres, "SELECT * FROM mysql.servers")) + return 1; + num_fields= mysql_num_fields(tableres); + while ((row= mysql_fetch_row(tableres))) + { + fprintf(md_result_file,"CREATE %sSERVER %s%s FOREIGN DATA WRAPPER %s OPTIONS (", + opt_replace_into ? "/*M!100103 OR REPLACE */ ": "", + opt_ignore ? "/*M!100103 IF NOT EXISTS */ " : "", row[0], row[7]); + for (i= 1; i < num_fields; i++) + { + if (i == 7 || row[i][0] == '\0') /* Wrapper or empty string */ + continue; + f= &tableres->fields[i]; + qstring= (f->type == MYSQL_TYPE_STRING || f->type == MYSQL_TYPE_VAR_STRING) ? "'" : ""; + fprintf(md_result_file, "%s%s %s%s%s", + (comma_prepend ? ", " : ""), f->name, qstring, row[i], qstring); + comma_prepend= 1; + } + fputs(");\n", md_result_file); + } + mysql_free_result(tableres); + + return 0; +} + + +/* + dump all system statistical tables +*/ + +static int dump_all_stats() +{ + my_bool prev_no_create_info; + + if (mysql_select_db(mysql, "mysql")) + { + DB_error(mysql, "when selecting the database"); + return 1; /* If --force */ + } + fprintf(md_result_file,"\nUSE mysql;\n"); + prev_no_create_info= opt_no_create_info; + opt_no_create_info= 1; /* don't overwrite recreate tables */ + /* EITS added in 10.0.1 */ + if (mysql_get_server_version(mysql) >= 100001) + { + dump_table("column_stats", "mysql", NULL, 0); + dump_table("index_stats", "mysql", NULL, 0); + dump_table("table_stats", "mysql", NULL, 0); + } + /* Innodb may be disabled */ + if (!mysql_query(mysql, "show fields from innodb_index_stats")) + { + MYSQL_RES *tableres= mysql_store_result(mysql); + mysql_free_result(tableres); + dump_table("innodb_index_stats", "mysql", NULL, 0); + dump_table("innodb_table_stats", "mysql", NULL, 0); + } + opt_no_create_info= prev_no_create_info; + return 0; +} + + +/* + dump all system timezones +*/ + +static int dump_all_timezones() +{ + my_bool opt_prev_no_create_info; + if (mysql_select_db(mysql, "mysql")) + { + DB_error(mysql, "when selecting the database"); + return 1; /* If --force */ + } + opt_prev_no_create_info= opt_no_create_info; + opt_no_create_info= 1; + fprintf(md_result_file,"\nUSE mysql;\n"); + dump_table("time_zone", "mysql", NULL, 0); + dump_table("time_zone_name", "mysql", NULL, 0); + dump_table("time_zone_leap_second", "mysql", NULL, 0); + dump_table("time_zone_transition", "mysql", NULL, 0); + dump_table("time_zone_transition_type", "mysql", NULL, 0); + opt_no_create_info= opt_prev_no_create_info; + return 0; +} + + +/* dump all logfile groups and tablespaces */ @@ -6175,7 +6699,7 @@ int main(int argc, char **argv) dump_tablespaces_for_tables(*argv, (argv + 1), (argc - 1)); dump_selected_tables(*argv, (argv + 1), (argc - 1)); } - else + else if (argc > 0) { /* One or more databases, all tables */ if (!opt_alltspcs && !opt_notspcs) @@ -6184,6 +6708,25 @@ int main(int argc, char **argv) } } + if (opt_system & OPT_SYSTEM_PLUGINS) + dump_all_plugins(); + + if (opt_system & OPT_SYSTEM_USERS) + dump_all_users_roles_and_grants(); + + if (opt_system & OPT_SYSTEM_UDFS) + dump_all_udfs(); + + if (opt_system & OPT_SYSTEM_SERVERS) + dump_all_servers(); + + /* These must be last as they explictly change the current database to mysql */ + if (opt_system & OPT_SYSTEM_STATS) + dump_all_stats(); + + if (opt_system & OPT_SYSTEM_TIMEZONES) + dump_all_timezones(); + /* add 'START SLAVE' to end of dump */ if (opt_slave_apply && add_slave_statements()) goto err; 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 diff --git a/mysql-test/r/mysqldump-system,win.rdiff b/mysql-test/r/mysqldump-system,win.rdiff new file mode 100644 index 00000000000..5a80c71ae57 --- /dev/null +++ b/mysql-test/r/mysqldump-system,win.rdiff @@ -0,0 +1,120 @@ +diff --git a/mysql-test/r/mysqldump-system.result b/mysql-test/r/mysqldump-system.result +index 88cf950c621..5f8b579fa67 100644 +--- a/mysql-test/r/mysqldump-system.result ++++ b/mysql-test/r/mysqldump-system.result +@@ -7,8 +7,6 @@ + delete from mysql.user where host not in ('localhost'); + flush privileges; + create user USER; +-install plugin /*M!100401 IF NOT EXISTS */ unix_socket soname 'auth_socket.so';; +-alter user USER identified via unix_socket; + CREATE ROLE role_1; + CREATE ROLE role_2 WITH ADMIN role_1; + GRANT SHOW DATABASES ON *.* TO role_1; +@@ -48,9 +46,8 @@ + /*!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 */; +-INSTALL PLUGIN unix_socket SONAME 'auth_socket.so'; + CREATE USER 'root'@'localhost'; +-CREATE USER 'USER'@'%' IDENTIFIED VIA unix_socket; ++CREATE USER 'USER'@'%'; + SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; + CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; + GRANT mariadb_dump_import_role TO CURRENT_USER(); +@@ -68,7 +65,7 @@ + /*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; + GRANT role_1 TO 'USER'@'%'; + GRANT role_2 TO 'USER'@'%'; +-GRANT USAGE ON *.* TO 'USER'@'%' IDENTIFIED VIA unix_socket; ++GRANT USAGE ON *.* TO 'USER'@'%'; + /*M!100005 SET DEFAULT ROLE 'role_2' FOR 'USER'@'%' */; + /*!80001 ALTER USER 'USER'@'%' DEFAULT ROLE 'role_2' */; + GRANT role_2 TO 'role_1' WITH ADMIN OPTION; +@@ -168,8 +165,6 @@ + /*!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 */; +-/*M!100401 UNINSTALL PLUGIN IF EXIST unix_socket */; +-INSTALL PLUGIN unix_socket SONAME 'auth_socket.so'; + DELIMITER | + /*M!100101 IF current_user()="'root'@'localhost'" THEN + SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT="Don't remove current user 'root'@'localhost''"; +@@ -183,7 +178,7 @@ + END IF */| + DELIMITER ; + /*!50701 DROP USER IF EXISTS 'USER'@'%' */; +-CREATE /*M!100103 OR REPLACE */ USER 'USER'@'%' IDENTIFIED VIA unix_socket; ++CREATE /*M!100103 OR REPLACE */ USER 'USER'@'%'; + SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; + CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; + GRANT mariadb_dump_import_role TO CURRENT_USER(); +@@ -203,7 +198,7 @@ + /*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; + GRANT role_1 TO 'USER'@'%'; + GRANT role_2 TO 'USER'@'%'; +-GRANT USAGE ON *.* TO 'USER'@'%' IDENTIFIED VIA unix_socket; ++GRANT USAGE ON *.* TO 'USER'@'%'; + /*M!100005 SET DEFAULT ROLE 'role_2' FOR 'USER'@'%' */; + /*!80001 ALTER USER 'USER'@'%' DEFAULT ROLE 'role_2' */; + GRANT role_2 TO 'role_1' WITH ADMIN OPTION; +@@ -304,9 +299,8 @@ + /*!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 */; +-INSTALL PLUGIN unix_socket /*M!100401 IF NOT EXISTS */ SONAME 'auth_socket.so'; + CREATE USER IF NOT EXISTS 'root'@'localhost'; +-CREATE USER IF NOT EXISTS 'USER'@'%' IDENTIFIED VIA unix_socket; ++CREATE USER IF NOT EXISTS 'USER'@'%'; + SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; + CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; + GRANT mariadb_dump_import_role TO CURRENT_USER(); +@@ -324,7 +318,7 @@ + /*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; + GRANT role_1 TO 'USER'@'%'; + GRANT role_2 TO 'USER'@'%'; +-GRANT USAGE ON *.* TO 'USER'@'%' IDENTIFIED VIA unix_socket; ++GRANT USAGE ON *.* TO 'USER'@'%'; + /*M!100005 SET DEFAULT ROLE 'role_2' FOR 'USER'@'%' */; + /*!80001 ALTER USER 'USER'@'%' DEFAULT ROLE 'role_2' */; + GRANT role_2 TO 'role_1' WITH ADMIN OPTION; +@@ -413,12 +407,12 @@ + CHECKSUM TABLE mysql.user, mysql.roles_mapping, mysql.time_zone_transition, mysql.plugin, + mysql.servers, mysql.func, mysql.innodb_table_stats, mysql.table_stats; + Table Checksum +-mysql.user 205632094 ++mysql.user 926820922 + mysql.roles_mapping 3150178430 + mysql.time_zone_transition 3895294076 +-mysql.plugin 1520695737 ++mysql.plugin 0 + mysql.servers 2783974349 +-mysql.func 3241572444 ++mysql.func 310494789 + mysql.innodb_table_stats 347867921 + mysql.table_stats 664320059 + # Opps.... +@@ -442,12 +436,12 @@ + CHECKSUM TABLE mysql.user, mysql.roles_mapping, mysql.time_zone_transition, mysql.plugin, + mysql.servers, mysql.func, mysql.innodb_table_stats, mysql.table_stats; + Table Checksum +-mysql.user 205632094 ++mysql.user 926820922 + mysql.roles_mapping 3150178430 + mysql.time_zone_transition 3895294076 +-mysql.plugin 1520695737 ++mysql.plugin 0 + mysql.servers 2783974349 +-mysql.func 3241572444 ++mysql.func 310494789 + mysql.innodb_table_stats 347867921 + mysql.table_stats 664320059 + DROP FUNCTION IF EXISTS metaphon; +@@ -461,7 +455,6 @@ + DROP ROLE role_2; + DROP ROLE role_1; + drop user USER; +-uninstall plugin unix_socket; + insert into mysql.user select * from backup_users; + flush privileges; + drop table backup_users; diff --git a/mysql-test/r/mysqldump-system.result b/mysql-test/r/mysqldump-system.result new file mode 100644 index 00000000000..88cf950c621 --- /dev/null +++ b/mysql-test/r/mysqldump-system.result @@ -0,0 +1,467 @@ +# +# MDEV-23630: mysqldump to logically dump system tables +# +# +create table backup_users like mysql.user; +insert into backup_users select * from mysql.user where host not in ('localhost'); +delete from mysql.user where host not in ('localhost'); +flush privileges; +create user USER; +install plugin /*M!100401 IF NOT EXISTS */ unix_socket soname 'auth_socket.so';; +alter user USER identified via unix_socket; +CREATE ROLE role_1; +CREATE ROLE role_2 WITH ADMIN role_1; +GRANT SHOW DATABASES ON *.* TO role_1; +GRANT role_1 TO USER; +GRANT role_2 TO USER; +SET DEFAULT ROLE role_2 FOR USER; +ALTER TABLE mysql.roles_mapping ORDER BY Host, User, Role; +set @save_innodb_stats_persistent= @@innodb_stats_persistent; +create table mysql.tz like mysql.time_zone_transition; +alter table mysql.tz engine=innodb; +insert into mysql.tz select * from mysql.time_zone_transition; +set global innodb_stats_persistent=1; +ANALYZE TABLE mysql.tz PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +mysql.tz analyze status Engine-independent statistics collected +mysql.tz analyze status OK +delete from mysql.index_stats where prefix_arity!=1; +delete from mysql.column_stats where column_name!='Time_zone_id'; +set time_zone="+03:00"; +update mysql.innodb_index_stats set last_update="2020-01-01" where database_name="mysql" and table_name="tz"; +update mysql.innodb_table_stats set last_update="2020-01-01" where database_name="mysql" and table_name="tz"; +set global innodb_stats_persistent= @save_innodb_stats_persistent; +alter table mysql.time_zone_name ORDER BY Name; +CREATE SERVER s1 FOREIGN DATA WRAPPER mysql OPTIONS(Host 'localhost'); +CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +# +# mysqldump of system tables with --system=all +# + +/*!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 */; +INSTALL PLUGIN unix_socket SONAME 'auth_socket.so'; +CREATE USER 'root'@'localhost'; +CREATE USER 'USER'@'%' IDENTIFIED VIA unix_socket; +SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; +CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; +GRANT mariadb_dump_import_role TO CURRENT_USER(); +SET ROLE mariadb_dump_import_role; +/*!80001 CREATE ROLE 'role_1' */; +/*M!100005 CREATE ROLE 'role_1' WITH ADMIN mariadb_dump_import_role */; +/*M!100005 GRANT 'role_1' TO 'root'@'localhost' WITH ADMIN OPTION */; +/*!80001 CREATE ROLE 'role_2' */; +/*M!100005 CREATE ROLE 'role_2' WITH ADMIN mariadb_dump_import_role */; +/*M!100005 GRANT 'role_2' TO 'role_1' WITH ADMIN OPTION */; +GRANT role_1 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; +/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; +/*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; +GRANT role_1 TO 'USER'@'%'; +GRANT role_2 TO 'USER'@'%'; +GRANT USAGE ON *.* TO 'USER'@'%' IDENTIFIED VIA unix_socket; +/*M!100005 SET DEFAULT ROLE 'role_2' FOR 'USER'@'%' */; +/*!80001 ALTER USER 'USER'@'%' DEFAULT ROLE 'role_2' */; +GRANT role_2 TO 'role_1' WITH ADMIN OPTION; +GRANT SHOW DATABASES ON *.* TO 'role_1'; +GRANT USAGE ON *.* TO 'role_2'; +GRANT USAGE ON *.* TO 'role_2'; +SET ROLE NONE; +DROP ROLE mariadb_dump_import_role; +/*M!100203 EXECUTE IMMEDIATE CONCAT('SET ROLE ', @current_role) */; +CREATE FUNCTION metaphon RETURNS STRING SONAME 'UDF_EXAMPLE_LIB'; +CREATE SERVER s1 FOREIGN DATA WRAPPER mysql OPTIONS (Host 'localhost', Port 3306); + +USE mysql; + +LOCK TABLES `column_stats` WRITE; +/*!40000 ALTER TABLE `column_stats` DISABLE KEYS */; +INSERT INTO `column_stats` VALUES ('mysql','tz','Time_zone_id','1','5',0.0000,4.0000,98.2500,0,NULL,NULL); +/*!40000 ALTER TABLE `column_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `index_stats` WRITE; +/*!40000 ALTER TABLE `index_stats` DISABLE KEYS */; +INSERT INTO `index_stats` VALUES ('mysql','tz','PRIMARY',1,98.2500); +/*!40000 ALTER TABLE `index_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `table_stats` WRITE; +/*!40000 ALTER TABLE `table_stats` DISABLE KEYS */; +INSERT INTO `table_stats` VALUES ('mysql','tz',393); +/*!40000 ALTER TABLE `table_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `innodb_index_stats` WRITE; +/*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */; +INSERT INTO `innodb_index_stats` VALUES ('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx01',4,1,'Time_zone_id'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx02',393,1,'Time_zone_id,Transition_time'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','size',1,NULL,'Number of pages in the index'); +/*!40000 ALTER TABLE `innodb_index_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `innodb_table_stats` WRITE; +/*!40000 ALTER TABLE `innodb_table_stats` DISABLE KEYS */; +INSERT INTO `innodb_table_stats` VALUES ('mysql','tz','2019-12-31 21:00:00',393,1,0); +/*!40000 ALTER TABLE `innodb_table_stats` ENABLE KEYS */; +UNLOCK TABLES; + +USE mysql; + +LOCK TABLES `time_zone` WRITE; +/*!40000 ALTER TABLE `time_zone` DISABLE KEYS */; +INSERT INTO `time_zone` VALUES (1,'N'),(2,'N'),(3,'N'),(4,'Y'),(5,'N'); +/*!40000 ALTER TABLE `time_zone` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_name` WRITE; +/*!40000 ALTER TABLE `time_zone_name` DISABLE KEYS */; +INSERT INTO `time_zone_name` VALUES ('Europe/Moscow',3),('Japan',5),('leap/Europe/Moscow',4),('MET',1),('Universal',2),('UTC',2); +/*!40000 ALTER TABLE `time_zone_name` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_leap_second` WRITE; +/*!40000 ALTER TABLE `time_zone_leap_second` DISABLE KEYS */; +INSERT INTO `time_zone_leap_second` VALUES (78796800,1),(94694401,2),(126230402,3),(157766403,4),(189302404,5),(220924805,6),(252460806,7),(283996807,8),(315532808,9),(362793609,10),(394329610,11),(425865611,12),(489024012,13),(567993613,14),(631152014,15),(662688015,16),(709948816,17),(741484817,18),(773020818,19),(820454419,20),(867715220,21),(915148821,22); +/*!40000 ALTER TABLE `time_zone_leap_second` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_transition` WRITE; +/*!40000 ALTER TABLE `time_zone_transition` DISABLE KEYS */; +INSERT INTO `time_zone_transition` VALUES (1,-1693706400,0),(1,-1680483600,1),(1,-1663455600,2),(1,-1650150000,3),(1,-1632006000,2),(1,-1618700400,3),(1,-938905200,2),(1,-857257200,3),(1,-844556400,2),(1,-828226800,3),(1,-812502000,2),(1,-796777200,3),(1,228877200,2),(1,243997200,3),(1,260326800,2),(1,276051600,3),(1,291776400,2),(1,307501200,3),(1,323830800,2),(1,338950800,3),(1,354675600,2),(1,370400400,3),(1,386125200,2),(1,401850000,3),(1,417574800,2),(1,433299600,3),(1,449024400,2),(1,465354000,3),(1,481078800,2),(1,496803600,3),(1,512528400,2),(1,528253200,3),(1,543978000,2),(1,559702800,3),(1,575427600,2),(1,591152400,3),(1,606877200,2),(1,622602000,3),(1,638326800,2),(1,654656400,3),(1,670381200,2),(1,686106000,3),(1,701830800,2),(1,717555600,3),(1,733280400,2),(1,749005200,3),(1,764730000,2),(1,780454800,3),(1,796179600,2),(1,811904400,3),(1,828234000,2),(1,846378000,3),(1,859683600,2),(1,877827600,3),(1,891133200,2),(1,909277200,3),(1,922582800,2),(1,941331600,3),(1,954032400,2),(1,972781200,3),(1,985482000,2),(1,1004230800,3),(1,1017536400,2),(1,1035680400,3),(1,1048986000,2),(1,1067130000,3),(1,1080435600,2),(1,1099184400,3),(1,1111885200,2),(1,1130634000,3),(1,1143334800,2),(1,1162083600,3),(1,1174784400,2),(1,1193533200,3),(1,1206838800,2),(1,1224982800,3),(1,1238288400,2),(1,1256432400,3),(1,1269738000,2),(1,1288486800,3),(1,1301187600,2),(1,1319936400,3),(1,1332637200,2),(1,1351386000,3),(1,1364691600,2),(1,1382835600,3),(1,1396141200,2),(1,1414285200,3),(1,1427590800,2),(1,1445734800,3),(1,1459040400,2),(1,1477789200,3),(1,1490490000,2),(1,1509238800,3),(1,1521939600,2),(1,1540688400,3),(1,1553994000,2),(1,1572138000,3),(1,1585443600,2),(1,1603587600,3),(1,1616893200,2),(1,1635642000,3),(1,1648342800,2),(1,1667091600,3),(1,1679792400,2),(1,1698541200,3),(1,1711846800,2),(1,1729990800,3),(1,1743296400,2),(1,1761440400,3),(1,1774746000,2),(1,1792890000,3),(1,1806195600,2),(1,1824944400,3),(1,1837645200,2),(1,1856394000,3),(1,1869094800,2),(1,1887843600,3),(1,1901149200,2),(1,1919293200,3),(1,1932598800,2),(1,1950742800,3),(1,1964048400,2),(1,1982797200,3),(1,1995498000,2),(1,2014246800,3),(1,2026947600,2),(1,2045696400,3),(1,2058397200,2),(1,2077146000,3),(1,2090451600,2),(1,2108595600,3),(1,2121901200,2),(1,2140045200,3),(3,-1688265000,2),(3,-1656819048,1),(3,-1641353448,2),(3,-1627965048,3),(3,-1618716648,1),(3,-1596429048,3),(3,-1593829848,5),(3,-1589860800,4),(3,-1542427200,5),(3,-1539493200,6),(3,-1525323600,5),(3,-1522728000,4),(3,-1491188400,7),(3,-1247536800,4),(3,354920400,5),(3,370728000,4),(3,386456400,5),(3,402264000,4),(3,417992400,5),(3,433800000,4),(3,449614800,5),(3,465346800,8),(3,481071600,9),(3,496796400,8),(3,512521200,9),(3,528246000,8),(3,543970800,9),(3,559695600,8),(3,575420400,9),(3,591145200,8),(3,606870000,9),(3,622594800,8),(3,638319600,9),(3,654649200,8),(3,670374000,10),(3,686102400,11),(3,695779200,8),(3,701812800,5),(3,717534000,4),(3,733273200,9),(3,748998000,8),(3,764722800,9),(3,780447600,8),(3,796172400,9),(3,811897200,8),(3,828226800,9),(3,846370800,8),(3,859676400,9),(3,877820400,8),(3,891126000,9),(3,909270000,8),(3,922575600,9),(3,941324400,8),(3,954025200,9),(3,972774000,8),(3,985474800,9),(3,1004223600,8),(3,1017529200,9),(3,1035673200,8),(3,1048978800,9),(3,1067122800,8),(3,1080428400,9),(3,1099177200,8),(3,1111878000,9),(3,1130626800,8),(3,1143327600,9),(3,1162076400,8),(3,1174777200,9),(3,1193526000,8),(3,1206831600,9),(3,1224975600,8),(3,1238281200,9),(3,1256425200,8),(3,1269730800,9),(3,1288479600,8),(3,1301180400,9),(3,1319929200,8),(3,1332630000,9),(3,1351378800,8),(3,1364684400,9),(3,1382828400,8),(3,1396134000,9),(3,1414278000,8),(3,1427583600,9),(3,1445727600,8),(3,1459033200,9),(3,1477782000,8),(3,1490482800,9),(3,1509231600,8),(3,1521932400,9),(3,1540681200,8),(3,1553986800,9),(3,1572130800,8),(3,1585436400,9),(3,1603580400,8),(3,1616886000,9),(3,1635634800,8),(3,1648335600,9),(3,1667084400,8),(3,1679785200,9),(3,1698534000,8),(3,1711839600,9),(3,1729983600,8),(3,1743289200,9),(3,1761433200,8),(3,1774738800,9),(3,1792882800,8),(3,1806188400,9),(3,1824937200,8),(3,1837638000,9),(3,1856386800,8),(3,1869087600,9),(3,1887836400,8),(3,1901142000,9),(3,1919286000,8),(3,1932591600,9),(3,1950735600,8),(3,1964041200,9),(3,1982790000,8),(3,1995490800,9),(3,2014239600,8),(3,2026940400,9),(3,2045689200,8),(3,2058390000,9),(3,2077138800,8),(3,2090444400,9),(3,2108588400,8),(3,2121894000,9),(3,2140038000,8),(4,-1688265000,2),(4,-1656819048,1),(4,-1641353448,2),(4,-1627965048,3),(4,-1618716648,1),(4,-1596429048,3),(4,-1593829848,5),(4,-1589860800,4),(4,-1542427200,5),(4,-1539493200,6),(4,-1525323600,5),(4,-1522728000,4),(4,-1491188400,7),(4,-1247536800,4),(4,354920409,5),(4,370728010,4),(4,386456410,5),(4,402264011,4),(4,417992411,5),(4,433800012,4),(4,449614812,5),(4,465346812,8),(4,481071612,9),(4,496796413,8),(4,512521213,9),(4,528246013,8),(4,543970813,9),(4,559695613,8),(4,575420414,9),(4,591145214,8),(4,606870014,9),(4,622594814,8),(4,638319615,9),(4,654649215,8),(4,670374016,10),(4,686102416,11),(4,695779216,8),(4,701812816,5),(4,717534017,4),(4,733273217,9),(4,748998018,8),(4,764722818,9),(4,780447619,8),(4,796172419,9),(4,811897219,8),(4,828226820,9),(4,846370820,8),(4,859676420,9),(4,877820421,8),(4,891126021,9),(4,909270021,8),(4,922575622,9),(4,941324422,8),(4,954025222,9),(4,972774022,8),(4,985474822,9),(4,1004223622,8),(4,1017529222,9),(4,1035673222,8),(4,1048978822,9),(4,1067122822,8),(4,1080428422,9),(4,1099177222,8),(4,1111878022,9),(4,1130626822,8),(4,1143327622,9),(4,1162076422,8),(4,1174777222,9),(4,1193526022,8),(4,1206831622,9),(4,1224975622,8),(4,1238281222,9),(4,1256425222,8),(4,1269730822,9),(4,1288479622,8),(4,1301180422,9),(4,1319929222,8),(4,1332630022,9),(4,1351378822,8),(4,1364684422,9),(4,1382828422,8),(4,1396134022,9),(4,1414278022,8),(4,1427583622,9),(4,1445727622,8),(4,1459033222,9),(4,1477782022,8),(4,1490482822,9),(4,1509231622,8),(4,1521932422,9),(4,1540681222,8),(4,1553986822,9),(4,1572130822,8),(4,1585436422,9),(4,1603580422,8),(4,1616886022,9),(4,1635634822,8),(4,1648335622,9),(4,1667084422,8),(4,1679785222,9),(4,1698534022,8),(4,1711839622,9),(4,1729983622,8),(4,1743289222,9),(4,1761433222,8),(4,1774738822,9),(4,1792882822,8),(4,1806188422,9),(4,1824937222,8),(4,1837638022,9),(4,1856386822,8),(4,1869087622,9),(4,1887836422,8),(4,1901142022,9),(4,1919286022,8),(4,1932591622,9),(4,1950735622,8),(4,1964041222,9),(4,1982790022,8),(4,1995490822,9),(4,2014239622,8),(4,2026940422,9),(4,2045689222,8),(4,2058390022,9),(4,2077138822,8),(4,2090444422,9),(4,2108588422,8),(4,2121894022,9),(4,2140038022,8),(5,-1009875600,1); +/*!40000 ALTER TABLE `time_zone_transition` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_transition_type` WRITE; +/*!40000 ALTER TABLE `time_zone_transition_type` DISABLE KEYS */; +INSERT INTO `time_zone_transition_type` VALUES (1,0,7200,1,'MEST'),(1,1,3600,0,'MET'),(1,2,7200,1,'MEST'),(1,3,3600,0,'MET'),(2,0,0,0,'UTC'),(3,0,9000,0,'MMT'),(3,1,12648,1,'MST'),(3,2,9048,0,'MMT'),(3,3,16248,1,'MDST'),(3,4,10800,0,'MSK'),(3,5,14400,1,'MSD'),(3,6,18000,1,'MSD'),(3,7,7200,0,'EET'),(3,8,10800,0,'MSK'),(3,9,14400,1,'MSD'),(3,10,10800,1,'EEST'),(3,11,7200,0,'EET'),(4,0,9000,0,'MMT'),(4,1,12648,1,'MST'),(4,2,9048,0,'MMT'),(4,3,16248,1,'MDST'),(4,4,10800,0,'MSK'),(4,5,14400,1,'MSD'),(4,6,18000,1,'MSD'),(4,7,7200,0,'EET'),(4,8,10800,0,'MSK'),(4,9,14400,1,'MSD'),(4,10,10800,1,'EEST'),(4,11,7200,0,'EET'),(5,0,32400,0,'CJT'),(5,1,32400,0,'JST'); +/*!40000 ALTER TABLE `time_zone_transition_type` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +# +# mysqldump of system tables with --system=all --replace +# + +/*!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 */; +/*M!100401 UNINSTALL PLUGIN IF EXIST unix_socket */; +INSTALL PLUGIN unix_socket SONAME 'auth_socket.so'; +DELIMITER | +/*M!100101 IF current_user()="'root'@'localhost'" THEN + SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT="Don't remove current user 'root'@'localhost''"; +END IF */| +DELIMITER ; +/*!50701 DROP USER IF EXISTS 'root'@'localhost' */; +CREATE /*M!100103 OR REPLACE */ USER 'root'@'localhost'; +DELIMITER | +/*M!100101 IF current_user()="'USER'@'%'" THEN + SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT="Don't remove current user 'USER'@'%''"; +END IF */| +DELIMITER ; +/*!50701 DROP USER IF EXISTS 'USER'@'%' */; +CREATE /*M!100103 OR REPLACE */ USER 'USER'@'%' IDENTIFIED VIA unix_socket; +SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; +CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; +GRANT mariadb_dump_import_role TO CURRENT_USER(); +SET ROLE mariadb_dump_import_role; +/*!80001 DROP ROLE IF EXISTS 'role_1' */; +/*!80001 CREATE ROLE 'role_1' */; +/*M!100103 CREATE OR REPLACE ROLE 'role_1' WITH ADMIN mariadb_dump_import_role */; +/*M!100005 GRANT 'role_1' TO 'root'@'localhost' WITH ADMIN OPTION */; +/*!80001 DROP ROLE IF EXISTS 'role_2' */; +/*!80001 CREATE ROLE 'role_2' */; +/*M!100103 CREATE OR REPLACE ROLE 'role_2' WITH ADMIN mariadb_dump_import_role */; +/*M!100005 GRANT 'role_2' TO 'role_1' WITH ADMIN OPTION */; +GRANT role_1 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; +/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; +/*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; +GRANT role_1 TO 'USER'@'%'; +GRANT role_2 TO 'USER'@'%'; +GRANT USAGE ON *.* TO 'USER'@'%' IDENTIFIED VIA unix_socket; +/*M!100005 SET DEFAULT ROLE 'role_2' FOR 'USER'@'%' */; +/*!80001 ALTER USER 'USER'@'%' DEFAULT ROLE 'role_2' */; +GRANT role_2 TO 'role_1' WITH ADMIN OPTION; +GRANT SHOW DATABASES ON *.* TO 'role_1'; +GRANT USAGE ON *.* TO 'role_2'; +GRANT USAGE ON *.* TO 'role_2'; +SET ROLE NONE; +DROP ROLE mariadb_dump_import_role; +/*M!100203 EXECUTE IMMEDIATE CONCAT('SET ROLE ', @current_role) */; +/*!50701 DROP FUNCTION IF EXISTS metaphon */; +CREATE /*M!100103 OR REPLACE */ FUNCTION metaphon RETURNS STRING SONAME 'UDF_EXAMPLE_LIB'; +CREATE /*M!100103 OR REPLACE */ SERVER s1 FOREIGN DATA WRAPPER mysql OPTIONS (Host 'localhost', Port 3306); + +USE mysql; + +LOCK TABLES `column_stats` WRITE; +/*!40000 ALTER TABLE `column_stats` DISABLE KEYS */; +REPLACE INTO `column_stats` VALUES ('mysql','tz','Time_zone_id','1','5',0.0000,4.0000,98.2500,0,NULL,NULL); +/*!40000 ALTER TABLE `column_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `index_stats` WRITE; +/*!40000 ALTER TABLE `index_stats` DISABLE KEYS */; +REPLACE INTO `index_stats` VALUES ('mysql','tz','PRIMARY',1,98.2500); +/*!40000 ALTER TABLE `index_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `table_stats` WRITE; +/*!40000 ALTER TABLE `table_stats` DISABLE KEYS */; +REPLACE INTO `table_stats` VALUES ('mysql','tz',393); +/*!40000 ALTER TABLE `table_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `innodb_index_stats` WRITE; +/*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */; +REPLACE INTO `innodb_index_stats` VALUES ('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx01',4,1,'Time_zone_id'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx02',393,1,'Time_zone_id,Transition_time'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','size',1,NULL,'Number of pages in the index'); +/*!40000 ALTER TABLE `innodb_index_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `innodb_table_stats` WRITE; +/*!40000 ALTER TABLE `innodb_table_stats` DISABLE KEYS */; +REPLACE INTO `innodb_table_stats` VALUES ('mysql','tz','2019-12-31 21:00:00',393,1,0); +/*!40000 ALTER TABLE `innodb_table_stats` ENABLE KEYS */; +UNLOCK TABLES; + +USE mysql; + +LOCK TABLES `time_zone` WRITE; +/*!40000 ALTER TABLE `time_zone` DISABLE KEYS */; +REPLACE INTO `time_zone` VALUES (1,'N'),(2,'N'),(3,'N'),(4,'Y'),(5,'N'); +/*!40000 ALTER TABLE `time_zone` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_name` WRITE; +/*!40000 ALTER TABLE `time_zone_name` DISABLE KEYS */; +REPLACE INTO `time_zone_name` VALUES ('Europe/Moscow',3),('Japan',5),('leap/Europe/Moscow',4),('MET',1),('Universal',2),('UTC',2); +/*!40000 ALTER TABLE `time_zone_name` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_leap_second` WRITE; +/*!40000 ALTER TABLE `time_zone_leap_second` DISABLE KEYS */; +REPLACE INTO `time_zone_leap_second` VALUES (78796800,1),(94694401,2),(126230402,3),(157766403,4),(189302404,5),(220924805,6),(252460806,7),(283996807,8),(315532808,9),(362793609,10),(394329610,11),(425865611,12),(489024012,13),(567993613,14),(631152014,15),(662688015,16),(709948816,17),(741484817,18),(773020818,19),(820454419,20),(867715220,21),(915148821,22); +/*!40000 ALTER TABLE `time_zone_leap_second` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_transition` WRITE; +/*!40000 ALTER TABLE `time_zone_transition` DISABLE KEYS */; +REPLACE INTO `time_zone_transition` VALUES (1,-1693706400,0),(1,-1680483600,1),(1,-1663455600,2),(1,-1650150000,3),(1,-1632006000,2),(1,-1618700400,3),(1,-938905200,2),(1,-857257200,3),(1,-844556400,2),(1,-828226800,3),(1,-812502000,2),(1,-796777200,3),(1,228877200,2),(1,243997200,3),(1,260326800,2),(1,276051600,3),(1,291776400,2),(1,307501200,3),(1,323830800,2),(1,338950800,3),(1,354675600,2),(1,370400400,3),(1,386125200,2),(1,401850000,3),(1,417574800,2),(1,433299600,3),(1,449024400,2),(1,465354000,3),(1,481078800,2),(1,496803600,3),(1,512528400,2),(1,528253200,3),(1,543978000,2),(1,559702800,3),(1,575427600,2),(1,591152400,3),(1,606877200,2),(1,622602000,3),(1,638326800,2),(1,654656400,3),(1,670381200,2),(1,686106000,3),(1,701830800,2),(1,717555600,3),(1,733280400,2),(1,749005200,3),(1,764730000,2),(1,780454800,3),(1,796179600,2),(1,811904400,3),(1,828234000,2),(1,846378000,3),(1,859683600,2),(1,877827600,3),(1,891133200,2),(1,909277200,3),(1,922582800,2),(1,941331600,3),(1,954032400,2),(1,972781200,3),(1,985482000,2),(1,1004230800,3),(1,1017536400,2),(1,1035680400,3),(1,1048986000,2),(1,1067130000,3),(1,1080435600,2),(1,1099184400,3),(1,1111885200,2),(1,1130634000,3),(1,1143334800,2),(1,1162083600,3),(1,1174784400,2),(1,1193533200,3),(1,1206838800,2),(1,1224982800,3),(1,1238288400,2),(1,1256432400,3),(1,1269738000,2),(1,1288486800,3),(1,1301187600,2),(1,1319936400,3),(1,1332637200,2),(1,1351386000,3),(1,1364691600,2),(1,1382835600,3),(1,1396141200,2),(1,1414285200,3),(1,1427590800,2),(1,1445734800,3),(1,1459040400,2),(1,1477789200,3),(1,1490490000,2),(1,1509238800,3),(1,1521939600,2),(1,1540688400,3),(1,1553994000,2),(1,1572138000,3),(1,1585443600,2),(1,1603587600,3),(1,1616893200,2),(1,1635642000,3),(1,1648342800,2),(1,1667091600,3),(1,1679792400,2),(1,1698541200,3),(1,1711846800,2),(1,1729990800,3),(1,1743296400,2),(1,1761440400,3),(1,1774746000,2),(1,1792890000,3),(1,1806195600,2),(1,1824944400,3),(1,1837645200,2),(1,1856394000,3),(1,1869094800,2),(1,1887843600,3),(1,1901149200,2),(1,1919293200,3),(1,1932598800,2),(1,1950742800,3),(1,1964048400,2),(1,1982797200,3),(1,1995498000,2),(1,2014246800,3),(1,2026947600,2),(1,2045696400,3),(1,2058397200,2),(1,2077146000,3),(1,2090451600,2),(1,2108595600,3),(1,2121901200,2),(1,2140045200,3),(3,-1688265000,2),(3,-1656819048,1),(3,-1641353448,2),(3,-1627965048,3),(3,-1618716648,1),(3,-1596429048,3),(3,-1593829848,5),(3,-1589860800,4),(3,-1542427200,5),(3,-1539493200,6),(3,-1525323600,5),(3,-1522728000,4),(3,-1491188400,7),(3,-1247536800,4),(3,354920400,5),(3,370728000,4),(3,386456400,5),(3,402264000,4),(3,417992400,5),(3,433800000,4),(3,449614800,5),(3,465346800,8),(3,481071600,9),(3,496796400,8),(3,512521200,9),(3,528246000,8),(3,543970800,9),(3,559695600,8),(3,575420400,9),(3,591145200,8),(3,606870000,9),(3,622594800,8),(3,638319600,9),(3,654649200,8),(3,670374000,10),(3,686102400,11),(3,695779200,8),(3,701812800,5),(3,717534000,4),(3,733273200,9),(3,748998000,8),(3,764722800,9),(3,780447600,8),(3,796172400,9),(3,811897200,8),(3,828226800,9),(3,846370800,8),(3,859676400,9),(3,877820400,8),(3,891126000,9),(3,909270000,8),(3,922575600,9),(3,941324400,8),(3,954025200,9),(3,972774000,8),(3,985474800,9),(3,1004223600,8),(3,1017529200,9),(3,1035673200,8),(3,1048978800,9),(3,1067122800,8),(3,1080428400,9),(3,1099177200,8),(3,1111878000,9),(3,1130626800,8),(3,1143327600,9),(3,1162076400,8),(3,1174777200,9),(3,1193526000,8),(3,1206831600,9),(3,1224975600,8),(3,1238281200,9),(3,1256425200,8),(3,1269730800,9),(3,1288479600,8),(3,1301180400,9),(3,1319929200,8),(3,1332630000,9),(3,1351378800,8),(3,1364684400,9),(3,1382828400,8),(3,1396134000,9),(3,1414278000,8),(3,1427583600,9),(3,1445727600,8),(3,1459033200,9),(3,1477782000,8),(3,1490482800,9),(3,1509231600,8),(3,1521932400,9),(3,1540681200,8),(3,1553986800,9),(3,1572130800,8),(3,1585436400,9),(3,1603580400,8),(3,1616886000,9),(3,1635634800,8),(3,1648335600,9),(3,1667084400,8),(3,1679785200,9),(3,1698534000,8),(3,1711839600,9),(3,1729983600,8),(3,1743289200,9),(3,1761433200,8),(3,1774738800,9),(3,1792882800,8),(3,1806188400,9),(3,1824937200,8),(3,1837638000,9),(3,1856386800,8),(3,1869087600,9),(3,1887836400,8),(3,1901142000,9),(3,1919286000,8),(3,1932591600,9),(3,1950735600,8),(3,1964041200,9),(3,1982790000,8),(3,1995490800,9),(3,2014239600,8),(3,2026940400,9),(3,2045689200,8),(3,2058390000,9),(3,2077138800,8),(3,2090444400,9),(3,2108588400,8),(3,2121894000,9),(3,2140038000,8),(4,-1688265000,2),(4,-1656819048,1),(4,-1641353448,2),(4,-1627965048,3),(4,-1618716648,1),(4,-1596429048,3),(4,-1593829848,5),(4,-1589860800,4),(4,-1542427200,5),(4,-1539493200,6),(4,-1525323600,5),(4,-1522728000,4),(4,-1491188400,7),(4,-1247536800,4),(4,354920409,5),(4,370728010,4),(4,386456410,5),(4,402264011,4),(4,417992411,5),(4,433800012,4),(4,449614812,5),(4,465346812,8),(4,481071612,9),(4,496796413,8),(4,512521213,9),(4,528246013,8),(4,543970813,9),(4,559695613,8),(4,575420414,9),(4,591145214,8),(4,606870014,9),(4,622594814,8),(4,638319615,9),(4,654649215,8),(4,670374016,10),(4,686102416,11),(4,695779216,8),(4,701812816,5),(4,717534017,4),(4,733273217,9),(4,748998018,8),(4,764722818,9),(4,780447619,8),(4,796172419,9),(4,811897219,8),(4,828226820,9),(4,846370820,8),(4,859676420,9),(4,877820421,8),(4,891126021,9),(4,909270021,8),(4,922575622,9),(4,941324422,8),(4,954025222,9),(4,972774022,8),(4,985474822,9),(4,1004223622,8),(4,1017529222,9),(4,1035673222,8),(4,1048978822,9),(4,1067122822,8),(4,1080428422,9),(4,1099177222,8),(4,1111878022,9),(4,1130626822,8),(4,1143327622,9),(4,1162076422,8),(4,1174777222,9),(4,1193526022,8),(4,1206831622,9),(4,1224975622,8),(4,1238281222,9),(4,1256425222,8),(4,1269730822,9),(4,1288479622,8),(4,1301180422,9),(4,1319929222,8),(4,1332630022,9),(4,1351378822,8),(4,1364684422,9),(4,1382828422,8),(4,1396134022,9),(4,1414278022,8),(4,1427583622,9),(4,1445727622,8),(4,1459033222,9),(4,1477782022,8),(4,1490482822,9),(4,1509231622,8),(4,1521932422,9),(4,1540681222,8),(4,1553986822,9),(4,1572130822,8),(4,1585436422,9),(4,1603580422,8),(4,1616886022,9),(4,1635634822,8),(4,1648335622,9),(4,1667084422,8),(4,1679785222,9),(4,1698534022,8),(4,1711839622,9),(4,1729983622,8),(4,1743289222,9),(4,1761433222,8),(4,1774738822,9),(4,1792882822,8),(4,1806188422,9),(4,1824937222,8),(4,1837638022,9),(4,1856386822,8),(4,1869087622,9),(4,1887836422,8),(4,1901142022,9),(4,1919286022,8),(4,1932591622,9),(4,1950735622,8),(4,1964041222,9),(4,1982790022,8),(4,1995490822,9),(4,2014239622,8),(4,2026940422,9),(4,2045689222,8),(4,2058390022,9),(4,2077138822,8),(4,2090444422,9),(4,2108588422,8),(4,2121894022,9),(4,2140038022,8),(5,-1009875600,1); +/*!40000 ALTER TABLE `time_zone_transition` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_transition_type` WRITE; +/*!40000 ALTER TABLE `time_zone_transition_type` DISABLE KEYS */; +REPLACE INTO `time_zone_transition_type` VALUES (1,0,7200,1,'MEST'),(1,1,3600,0,'MET'),(1,2,7200,1,'MEST'),(1,3,3600,0,'MET'),(2,0,0,0,'UTC'),(3,0,9000,0,'MMT'),(3,1,12648,1,'MST'),(3,2,9048,0,'MMT'),(3,3,16248,1,'MDST'),(3,4,10800,0,'MSK'),(3,5,14400,1,'MSD'),(3,6,18000,1,'MSD'),(3,7,7200,0,'EET'),(3,8,10800,0,'MSK'),(3,9,14400,1,'MSD'),(3,10,10800,1,'EEST'),(3,11,7200,0,'EET'),(4,0,9000,0,'MMT'),(4,1,12648,1,'MST'),(4,2,9048,0,'MMT'),(4,3,16248,1,'MDST'),(4,4,10800,0,'MSK'),(4,5,14400,1,'MSD'),(4,6,18000,1,'MSD'),(4,7,7200,0,'EET'),(4,8,10800,0,'MSK'),(4,9,14400,1,'MSD'),(4,10,10800,1,'EEST'),(4,11,7200,0,'EET'),(5,0,32400,0,'CJT'),(5,1,32400,0,'JST'); +/*!40000 ALTER TABLE `time_zone_transition_type` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +# +# mysqldump of system tables with --system=all --insert-ignore +# + +/*!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 */; +INSTALL PLUGIN unix_socket /*M!100401 IF NOT EXISTS */ SONAME 'auth_socket.so'; +CREATE USER IF NOT EXISTS 'root'@'localhost'; +CREATE USER IF NOT EXISTS 'USER'@'%' IDENTIFIED VIA unix_socket; +SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; +CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; +GRANT mariadb_dump_import_role TO CURRENT_USER(); +SET ROLE mariadb_dump_import_role; +/*!80001 CREATE ROLE IF NOT EXISTS 'role_1' */; +/*M!100005 CREATE ROLE IF NOT EXISTS 'role_1' WITH ADMIN mariadb_dump_import_role */; +/*M!100005 GRANT 'role_1' TO 'root'@'localhost' WITH ADMIN OPTION */; +/*!80001 CREATE ROLE IF NOT EXISTS 'role_2' */; +/*M!100005 CREATE ROLE IF NOT EXISTS 'role_2' WITH ADMIN mariadb_dump_import_role */; +/*M!100005 GRANT 'role_2' TO 'role_1' WITH ADMIN OPTION */; +GRANT role_1 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; +/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; +/*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; +GRANT role_1 TO 'USER'@'%'; +GRANT role_2 TO 'USER'@'%'; +GRANT USAGE ON *.* TO 'USER'@'%' IDENTIFIED VIA unix_socket; +/*M!100005 SET DEFAULT ROLE 'role_2' FOR 'USER'@'%' */; +/*!80001 ALTER USER 'USER'@'%' DEFAULT ROLE 'role_2' */; +GRANT role_2 TO 'role_1' WITH ADMIN OPTION; +GRANT SHOW DATABASES ON *.* TO 'role_1'; +GRANT USAGE ON *.* TO 'role_2'; +GRANT USAGE ON *.* TO 'role_2'; +SET ROLE NONE; +DROP ROLE mariadb_dump_import_role; +/*M!100203 EXECUTE IMMEDIATE CONCAT('SET ROLE ', @current_role) */; +CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME 'UDF_EXAMPLE_LIB'; +CREATE SERVER /*M!100103 IF NOT EXISTS */ s1 FOREIGN DATA WRAPPER mysql OPTIONS (Host 'localhost', Port 3306); + +USE mysql; + +LOCK TABLES `column_stats` WRITE; +/*!40000 ALTER TABLE `column_stats` DISABLE KEYS */; +INSERT IGNORE INTO `column_stats` VALUES ('mysql','tz','Time_zone_id','1','5',0.0000,4.0000,98.2500,0,NULL,NULL); +/*!40000 ALTER TABLE `column_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `index_stats` WRITE; +/*!40000 ALTER TABLE `index_stats` DISABLE KEYS */; +INSERT IGNORE INTO `index_stats` VALUES ('mysql','tz','PRIMARY',1,98.2500); +/*!40000 ALTER TABLE `index_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `table_stats` WRITE; +/*!40000 ALTER TABLE `table_stats` DISABLE KEYS */; +INSERT IGNORE INTO `table_stats` VALUES ('mysql','tz',393); +/*!40000 ALTER TABLE `table_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `innodb_index_stats` WRITE; +/*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */; +INSERT IGNORE INTO `innodb_index_stats` VALUES ('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx01',4,1,'Time_zone_id'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx02',393,1,'Time_zone_id,Transition_time'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','size',1,NULL,'Number of pages in the index'); +/*!40000 ALTER TABLE `innodb_index_stats` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `innodb_table_stats` WRITE; +/*!40000 ALTER TABLE `innodb_table_stats` DISABLE KEYS */; +INSERT IGNORE INTO `innodb_table_stats` VALUES ('mysql','tz','2019-12-31 21:00:00',393,1,0); +/*!40000 ALTER TABLE `innodb_table_stats` ENABLE KEYS */; +UNLOCK TABLES; + +USE mysql; + +LOCK TABLES `time_zone` WRITE; +/*!40000 ALTER TABLE `time_zone` DISABLE KEYS */; +INSERT IGNORE INTO `time_zone` VALUES (1,'N'),(2,'N'),(3,'N'),(4,'Y'),(5,'N'); +/*!40000 ALTER TABLE `time_zone` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_name` WRITE; +/*!40000 ALTER TABLE `time_zone_name` DISABLE KEYS */; +INSERT IGNORE INTO `time_zone_name` VALUES ('Europe/Moscow',3),('Japan',5),('leap/Europe/Moscow',4),('MET',1),('Universal',2),('UTC',2); +/*!40000 ALTER TABLE `time_zone_name` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_leap_second` WRITE; +/*!40000 ALTER TABLE `time_zone_leap_second` DISABLE KEYS */; +INSERT IGNORE INTO `time_zone_leap_second` VALUES (78796800,1),(94694401,2),(126230402,3),(157766403,4),(189302404,5),(220924805,6),(252460806,7),(283996807,8),(315532808,9),(362793609,10),(394329610,11),(425865611,12),(489024012,13),(567993613,14),(631152014,15),(662688015,16),(709948816,17),(741484817,18),(773020818,19),(820454419,20),(867715220,21),(915148821,22); +/*!40000 ALTER TABLE `time_zone_leap_second` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_transition` WRITE; +/*!40000 ALTER TABLE `time_zone_transition` DISABLE KEYS */; +INSERT IGNORE INTO `time_zone_transition` VALUES (1,-1693706400,0),(1,-1680483600,1),(1,-1663455600,2),(1,-1650150000,3),(1,-1632006000,2),(1,-1618700400,3),(1,-938905200,2),(1,-857257200,3),(1,-844556400,2),(1,-828226800,3),(1,-812502000,2),(1,-796777200,3),(1,228877200,2),(1,243997200,3),(1,260326800,2),(1,276051600,3),(1,291776400,2),(1,307501200,3),(1,323830800,2),(1,338950800,3),(1,354675600,2),(1,370400400,3),(1,386125200,2),(1,401850000,3),(1,417574800,2),(1,433299600,3),(1,449024400,2),(1,465354000,3),(1,481078800,2),(1,496803600,3),(1,512528400,2),(1,528253200,3),(1,543978000,2),(1,559702800,3),(1,575427600,2),(1,591152400,3),(1,606877200,2),(1,622602000,3),(1,638326800,2),(1,654656400,3),(1,670381200,2),(1,686106000,3),(1,701830800,2),(1,717555600,3),(1,733280400,2),(1,749005200,3),(1,764730000,2),(1,780454800,3),(1,796179600,2),(1,811904400,3),(1,828234000,2),(1,846378000,3),(1,859683600,2),(1,877827600,3),(1,891133200,2),(1,909277200,3),(1,922582800,2),(1,941331600,3),(1,954032400,2),(1,972781200,3),(1,985482000,2),(1,1004230800,3),(1,1017536400,2),(1,1035680400,3),(1,1048986000,2),(1,1067130000,3),(1,1080435600,2),(1,1099184400,3),(1,1111885200,2),(1,1130634000,3),(1,1143334800,2),(1,1162083600,3),(1,1174784400,2),(1,1193533200,3),(1,1206838800,2),(1,1224982800,3),(1,1238288400,2),(1,1256432400,3),(1,1269738000,2),(1,1288486800,3),(1,1301187600,2),(1,1319936400,3),(1,1332637200,2),(1,1351386000,3),(1,1364691600,2),(1,1382835600,3),(1,1396141200,2),(1,1414285200,3),(1,1427590800,2),(1,1445734800,3),(1,1459040400,2),(1,1477789200,3),(1,1490490000,2),(1,1509238800,3),(1,1521939600,2),(1,1540688400,3),(1,1553994000,2),(1,1572138000,3),(1,1585443600,2),(1,1603587600,3),(1,1616893200,2),(1,1635642000,3),(1,1648342800,2),(1,1667091600,3),(1,1679792400,2),(1,1698541200,3),(1,1711846800,2),(1,1729990800,3),(1,1743296400,2),(1,1761440400,3),(1,1774746000,2),(1,1792890000,3),(1,1806195600,2),(1,1824944400,3),(1,1837645200,2),(1,1856394000,3),(1,1869094800,2),(1,1887843600,3),(1,1901149200,2),(1,1919293200,3),(1,1932598800,2),(1,1950742800,3),(1,1964048400,2),(1,1982797200,3),(1,1995498000,2),(1,2014246800,3),(1,2026947600,2),(1,2045696400,3),(1,2058397200,2),(1,2077146000,3),(1,2090451600,2),(1,2108595600,3),(1,2121901200,2),(1,2140045200,3),(3,-1688265000,2),(3,-1656819048,1),(3,-1641353448,2),(3,-1627965048,3),(3,-1618716648,1),(3,-1596429048,3),(3,-1593829848,5),(3,-1589860800,4),(3,-1542427200,5),(3,-1539493200,6),(3,-1525323600,5),(3,-1522728000,4),(3,-1491188400,7),(3,-1247536800,4),(3,354920400,5),(3,370728000,4),(3,386456400,5),(3,402264000,4),(3,417992400,5),(3,433800000,4),(3,449614800,5),(3,465346800,8),(3,481071600,9),(3,496796400,8),(3,512521200,9),(3,528246000,8),(3,543970800,9),(3,559695600,8),(3,575420400,9),(3,591145200,8),(3,606870000,9),(3,622594800,8),(3,638319600,9),(3,654649200,8),(3,670374000,10),(3,686102400,11),(3,695779200,8),(3,701812800,5),(3,717534000,4),(3,733273200,9),(3,748998000,8),(3,764722800,9),(3,780447600,8),(3,796172400,9),(3,811897200,8),(3,828226800,9),(3,846370800,8),(3,859676400,9),(3,877820400,8),(3,891126000,9),(3,909270000,8),(3,922575600,9),(3,941324400,8),(3,954025200,9),(3,972774000,8),(3,985474800,9),(3,1004223600,8),(3,1017529200,9),(3,1035673200,8),(3,1048978800,9),(3,1067122800,8),(3,1080428400,9),(3,1099177200,8),(3,1111878000,9),(3,1130626800,8),(3,1143327600,9),(3,1162076400,8),(3,1174777200,9),(3,1193526000,8),(3,1206831600,9),(3,1224975600,8),(3,1238281200,9),(3,1256425200,8),(3,1269730800,9),(3,1288479600,8),(3,1301180400,9),(3,1319929200,8),(3,1332630000,9),(3,1351378800,8),(3,1364684400,9),(3,1382828400,8),(3,1396134000,9),(3,1414278000,8),(3,1427583600,9),(3,1445727600,8),(3,1459033200,9),(3,1477782000,8),(3,1490482800,9),(3,1509231600,8),(3,1521932400,9),(3,1540681200,8),(3,1553986800,9),(3,1572130800,8),(3,1585436400,9),(3,1603580400,8),(3,1616886000,9),(3,1635634800,8),(3,1648335600,9),(3,1667084400,8),(3,1679785200,9),(3,1698534000,8),(3,1711839600,9),(3,1729983600,8),(3,1743289200,9),(3,1761433200,8),(3,1774738800,9),(3,1792882800,8),(3,1806188400,9),(3,1824937200,8),(3,1837638000,9),(3,1856386800,8),(3,1869087600,9),(3,1887836400,8),(3,1901142000,9),(3,1919286000,8),(3,1932591600,9),(3,1950735600,8),(3,1964041200,9),(3,1982790000,8),(3,1995490800,9),(3,2014239600,8),(3,2026940400,9),(3,2045689200,8),(3,2058390000,9),(3,2077138800,8),(3,2090444400,9),(3,2108588400,8),(3,2121894000,9),(3,2140038000,8),(4,-1688265000,2),(4,-1656819048,1),(4,-1641353448,2),(4,-1627965048,3),(4,-1618716648,1),(4,-1596429048,3),(4,-1593829848,5),(4,-1589860800,4),(4,-1542427200,5),(4,-1539493200,6),(4,-1525323600,5),(4,-1522728000,4),(4,-1491188400,7),(4,-1247536800,4),(4,354920409,5),(4,370728010,4),(4,386456410,5),(4,402264011,4),(4,417992411,5),(4,433800012,4),(4,449614812,5),(4,465346812,8),(4,481071612,9),(4,496796413,8),(4,512521213,9),(4,528246013,8),(4,543970813,9),(4,559695613,8),(4,575420414,9),(4,591145214,8),(4,606870014,9),(4,622594814,8),(4,638319615,9),(4,654649215,8),(4,670374016,10),(4,686102416,11),(4,695779216,8),(4,701812816,5),(4,717534017,4),(4,733273217,9),(4,748998018,8),(4,764722818,9),(4,780447619,8),(4,796172419,9),(4,811897219,8),(4,828226820,9),(4,846370820,8),(4,859676420,9),(4,877820421,8),(4,891126021,9),(4,909270021,8),(4,922575622,9),(4,941324422,8),(4,954025222,9),(4,972774022,8),(4,985474822,9),(4,1004223622,8),(4,1017529222,9),(4,1035673222,8),(4,1048978822,9),(4,1067122822,8),(4,1080428422,9),(4,1099177222,8),(4,1111878022,9),(4,1130626822,8),(4,1143327622,9),(4,1162076422,8),(4,1174777222,9),(4,1193526022,8),(4,1206831622,9),(4,1224975622,8),(4,1238281222,9),(4,1256425222,8),(4,1269730822,9),(4,1288479622,8),(4,1301180422,9),(4,1319929222,8),(4,1332630022,9),(4,1351378822,8),(4,1364684422,9),(4,1382828422,8),(4,1396134022,9),(4,1414278022,8),(4,1427583622,9),(4,1445727622,8),(4,1459033222,9),(4,1477782022,8),(4,1490482822,9),(4,1509231622,8),(4,1521932422,9),(4,1540681222,8),(4,1553986822,9),(4,1572130822,8),(4,1585436422,9),(4,1603580422,8),(4,1616886022,9),(4,1635634822,8),(4,1648335622,9),(4,1667084422,8),(4,1679785222,9),(4,1698534022,8),(4,1711839622,9),(4,1729983622,8),(4,1743289222,9),(4,1761433222,8),(4,1774738822,9),(4,1792882822,8),(4,1806188422,9),(4,1824937222,8),(4,1837638022,9),(4,1856386822,8),(4,1869087622,9),(4,1887836422,8),(4,1901142022,9),(4,1919286022,8),(4,1932591622,9),(4,1950735622,8),(4,1964041222,9),(4,1982790022,8),(4,1995490822,9),(4,2014239622,8),(4,2026940422,9),(4,2045689222,8),(4,2058390022,9),(4,2077138822,8),(4,2090444422,9),(4,2108588422,8),(4,2121894022,9),(4,2140038022,8),(5,-1009875600,1); +/*!40000 ALTER TABLE `time_zone_transition` ENABLE KEYS */; +UNLOCK TABLES; + +LOCK TABLES `time_zone_transition_type` WRITE; +/*!40000 ALTER TABLE `time_zone_transition_type` DISABLE KEYS */; +INSERT IGNORE INTO `time_zone_transition_type` VALUES (1,0,7200,1,'MEST'),(1,1,3600,0,'MET'),(1,2,7200,1,'MEST'),(1,3,3600,0,'MET'),(2,0,0,0,'UTC'),(3,0,9000,0,'MMT'),(3,1,12648,1,'MST'),(3,2,9048,0,'MMT'),(3,3,16248,1,'MDST'),(3,4,10800,0,'MSK'),(3,5,14400,1,'MSD'),(3,6,18000,1,'MSD'),(3,7,7200,0,'EET'),(3,8,10800,0,'MSK'),(3,9,14400,1,'MSD'),(3,10,10800,1,'EEST'),(3,11,7200,0,'EET'),(4,0,9000,0,'MMT'),(4,1,12648,1,'MST'),(4,2,9048,0,'MMT'),(4,3,16248,1,'MDST'),(4,4,10800,0,'MSK'),(4,5,14400,1,'MSD'),(4,6,18000,1,'MSD'),(4,7,7200,0,'EET'),(4,8,10800,0,'MSK'),(4,9,14400,1,'MSD'),(4,10,10800,1,'EEST'),(4,11,7200,0,'EET'),(5,0,32400,0,'CJT'),(5,1,32400,0,'JST'); +/*!40000 ALTER TABLE `time_zone_transition_type` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +CHECKSUM TABLE mysql.user, mysql.roles_mapping, mysql.time_zone_transition, mysql.plugin, +mysql.servers, mysql.func, mysql.innodb_table_stats, mysql.table_stats; +Table Checksum +mysql.user 205632094 +mysql.roles_mapping 3150178430 +mysql.time_zone_transition 3895294076 +mysql.plugin 1520695737 +mysql.servers 2783974349 +mysql.func 3241572444 +mysql.innodb_table_stats 347867921 +mysql.table_stats 664320059 +# Opps.... +CREATE USER mariadb_test_restore IDENTIFIED BY 'getitback'; +GRANT ALL ON *.* TO mariadb_test_restore WITH GRANT OPTION; +GRANT PROXY ON ''@'%' TO mariadb_test_restore WITH GRANT OPTION; +GRANT SUPER, CREATE USER /*M!100502 ,FEDERATED ADMIN */ ON *.* TO mariadb_test_restore WITH GRANT OPTION; +drop user USER; +delete from mysql.table_stats; +delete from mysql.innodb_table_stats; +delete from mysql.time_zone_transition; +delete from mysql.time_zone_transition_type; +delete from mysql.time_zone; +delete from mysql.time_zone_name; +delete from mysql.time_zone_leap_second; +DROP FUNCTION IF EXISTS metaphon; +DROP SERVER s1; +set time_zone= @@global.time_zone; +# Restore from mysqldump +DROP USER mariadb_test_restore; +CHECKSUM TABLE mysql.user, mysql.roles_mapping, mysql.time_zone_transition, mysql.plugin, +mysql.servers, mysql.func, mysql.innodb_table_stats, mysql.table_stats; +Table Checksum +mysql.user 205632094 +mysql.roles_mapping 3150178430 +mysql.time_zone_transition 3895294076 +mysql.plugin 1520695737 +mysql.servers 2783974349 +mysql.func 3241572444 +mysql.innodb_table_stats 347867921 +mysql.table_stats 664320059 +DROP FUNCTION IF EXISTS metaphon; +DROP SERVER s1; +DELETE FROM mysql.column_stats WHERE db_name='mysql' and table_name in ('tz', 'gtid_slave_pos'); +DELETE FROM mysql.index_stats WHERE db_name='mysql' and table_name in ('tz', 'gtid_slave_pos'); +DELETE FROM mysql.table_stats WHERE db_name='mysql' and table_name in ('tz', 'gtid_slave_pos'); +DELETE FROM mysql.innodb_index_stats WHERE database_name='mysql' and table_name in ('tz','gtid_slave_pos'); +DELETE FROM mysql.innodb_table_stats WHERE database_name='mysql' and table_name in ('tz','gtid_slave_pos'); +drop table mysql.tz; +DROP ROLE role_2; +DROP ROLE role_1; +drop user USER; +uninstall plugin unix_socket; +insert into mysql.user select * from backup_users; +flush privileges; +drop table backup_users; diff --git a/mysql-test/t/mysqldump-system.test b/mysql-test/t/mysqldump-system.test new file mode 100644 index 00000000000..1fc0a45b3dc --- /dev/null +++ b/mysql-test/t/mysqldump-system.test @@ -0,0 +1,152 @@ +--source include/not_embedded.inc +--source include/have_innodb.inc +--source include/have_udf.inc +--source include/platform.inc + +--echo # +--echo # MDEV-23630: mysqldump to logically dump system tables +--echo # +--echo # + +# might need fixing in 10.4 to different mechanism +create table backup_users like mysql.user; +insert into backup_users select * from mysql.user where host not in ('localhost'); +delete from mysql.user where host not in ('localhost'); +flush privileges; + +create user USER; + +if (`SELECT CONVERT(@@VERSION_COMPILE_OS USING latin1) NOT IN ('Win32', 'Win64', 'Windows')`) +{ +--eval install plugin /*M!100401 IF NOT EXISTS */ unix_socket soname '$AUTH_SOCKET_SO'; +alter user USER identified via unix_socket; +} + +# time zone data already loaded + +CREATE ROLE role_1; +CREATE ROLE role_2 WITH ADMIN role_1; + +GRANT SHOW DATABASES ON *.* TO role_1; +GRANT role_1 TO USER; +GRANT role_2 TO USER; +SET DEFAULT ROLE role_2 FOR USER; + +ALTER TABLE mysql.roles_mapping ORDER BY Host, User, Role; + +# innodb and EITS tables statistics +# +set @save_innodb_stats_persistent= @@innodb_stats_persistent; +create table mysql.tz like mysql.time_zone_transition; +alter table mysql.tz engine=innodb; +insert into mysql.tz select * from mysql.time_zone_transition; +set global innodb_stats_persistent=1; +ANALYZE TABLE mysql.tz PERSISTENT FOR ALL; +# for predictable output in tests +delete from mysql.index_stats where prefix_arity!=1; +delete from mysql.column_stats where column_name!='Time_zone_id'; +set time_zone="+03:00"; +update mysql.innodb_index_stats set last_update="2020-01-01" where database_name="mysql" and table_name="tz"; +update mysql.innodb_table_stats set last_update="2020-01-01" where database_name="mysql" and table_name="tz"; +set global innodb_stats_persistent= @save_innodb_stats_persistent; +alter table mysql.time_zone_name ORDER BY Name; + +CREATE SERVER s1 FOREIGN DATA WRAPPER mysql OPTIONS(Host 'localhost'); + +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; + + +# +# Lets actually do some tests. +# + +--echo # +--echo # mysqldump of system tables with --system=all +--echo # + +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +--exec $MYSQL_DUMP --skip-comments --system=all + + +--echo # +--echo # mysqldump of system tables with --system=all --replace +--echo # + +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +--exec $MYSQL_DUMP --skip-comments --system=all --replace + + +# save this for restore +--exec $MYSQL_DUMP --system=users,servers,stats,timezones,udfs --replace > $MYSQLTEST_VARDIR/tmp/dump1.sql + +--echo # +--echo # mysqldump of system tables with --system=all --insert-ignore +--echo # + +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +--exec $MYSQL_DUMP --skip-comments --system=all --insert-ignore + + +CHECKSUM TABLE mysql.user, mysql.roles_mapping, mysql.time_zone_transition, mysql.plugin, + mysql.servers, mysql.func, mysql.innodb_table_stats, mysql.table_stats; + +--echo # Opps.... + +CREATE USER mariadb_test_restore IDENTIFIED BY 'getitback'; +GRANT ALL ON *.* TO mariadb_test_restore WITH GRANT OPTION; +GRANT PROXY ON ''@'%' TO mariadb_test_restore WITH GRANT OPTION; +GRANT SUPER, CREATE USER /*M!100502 ,FEDERATED ADMIN */ ON *.* TO mariadb_test_restore WITH GRANT OPTION; + +drop user USER; +delete from mysql.table_stats; +delete from mysql.innodb_table_stats; +delete from mysql.time_zone_transition; +delete from mysql.time_zone_transition_type; +delete from mysql.time_zone; +delete from mysql.time_zone_name; +delete from mysql.time_zone_leap_second; +DROP FUNCTION IF EXISTS metaphon; +DROP SERVER s1; +set time_zone= @@global.time_zone; + +--echo # Restore from mysqldump +--exec $MYSQL --user mariadb_test_restore --password=getitback --show-warnings < $MYSQLTEST_VARDIR/tmp/dump1.sql +#--remove_file $MYSQLTEST_VARDIR/tmp/dump1.sql; + +DROP USER mariadb_test_restore; + +# successful restore? + +CHECKSUM TABLE mysql.user, mysql.roles_mapping, mysql.time_zone_transition, mysql.plugin, + mysql.servers, mysql.func, mysql.innodb_table_stats, mysql.table_stats; + +# +# Cleanup +# + +DROP FUNCTION IF EXISTS metaphon; + +DROP SERVER s1; + +# EITS && innodb stats +DELETE FROM mysql.column_stats WHERE db_name='mysql' and table_name in ('tz', 'gtid_slave_pos'); +DELETE FROM mysql.index_stats WHERE db_name='mysql' and table_name in ('tz', 'gtid_slave_pos'); +DELETE FROM mysql.table_stats WHERE db_name='mysql' and table_name in ('tz', 'gtid_slave_pos'); +DELETE FROM mysql.innodb_index_stats WHERE database_name='mysql' and table_name in ('tz','gtid_slave_pos'); +DELETE FROM mysql.innodb_table_stats WHERE database_name='mysql' and table_name in ('tz','gtid_slave_pos'); +drop table mysql.tz; + +DROP ROLE role_2; +DROP ROLE role_1; + +drop user USER; + +if (`SELECT CONVERT(@@VERSION_COMPILE_OS USING latin1) NOT IN ('Win32', 'Win64', 'Windows')`) +{ +uninstall plugin unix_socket; +} + +insert into mysql.user select * from backup_users; +flush privileges; +drop table backup_users; |