diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/mysqldump-system.test | 163 |
1 files changed, 163 insertions, 0 deletions
diff --git a/mysql-test/main/mysqldump-system.test b/mysql-test/main/mysqldump-system.test new file mode 100644 index 00000000..85ad4421 --- /dev/null +++ b/mysql-test/main/mysqldump-system.test @@ -0,0 +1,163 @@ +--source include/not_embedded.inc +--source include/have_innodb.inc +--source include/have_udf.inc +--source include/platform.inc + +if (!$AUTH_TEST_PLUGIN_SO) { + --skip Need auth test plugin +} +install soname 'auth_test_plugin'; + +--echo # +--echo # MDEV-23630: mysqldump to logically dump system tables +--echo # +--echo # + +create table backup_users like mysql.global_priv; +create table tables_priv like mysql.tables_priv; +insert into backup_users select * from mysql.global_priv; +insert into tables_priv select * from mysql.tables_priv; +delete from mysql.global_priv where host not in ('localhost'); +flush privileges; + +# mariadb.sys because of MDEV-24098 +alter user 'mariadb.sys'@'localhost' ACCOUNT UNLOCK; +create user foobar IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; + +# 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 foobar; +GRANT role_2 TO foobar; +SET DEFAULT ROLE role_2 FOR foobar; + +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; +set time_zone="+03:00"; +SET TIMESTAMP= UNIX_TIMESTAMP('2022-01-07 07:07:00'); +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'; +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"; +update mysql.column_stats set histogram=json_replace(histogram, '$.collected_by', replace(json_value(histogram, '$.collected_by'), @@version, 'version')); +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 $AUTH_TEST_PLUGIN_SO AUTH_TEST_PLUGIN_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 $AUTH_TEST_PLUGIN_SO AUTH_TEST_PLUGIN_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 $AUTH_TEST_PLUGIN_SO AUTH_TEST_PLUGIN_LIB +--exec $MYSQL_DUMP --skip-comments --system=all --insert-ignore + + +# global_priv checksum not restored because: +# mariadb.sys - different Priv on restore +# password_last_changed date isn't saved/restored +# root user's Priv $.access lower number on restore + +--replace_regex /"password_last_changed":[0-9]+/"password_last_changed":NOW/ /"version_id":[0-9]+/"version_id":VERSION/ +SELECT * FROM mysql.global_priv ORDER BY User,Host; + +CHECKSUM TABLE 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 foobar; +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 + +DROP USER mariadb_test_restore; + +# successful restore? + +--replace_regex /"password_last_changed":[0-9]+/"password_last_changed":NOW/ /"version_id":[0-9]+/"version_id":VERSION/ +SELECT * FROM mysql.global_priv ORDER BY User,Host; + +CHECKSUM TABLE 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 foobar; + +replace into mysql.global_priv select * from backup_users; +replace into mysql.tables_priv select * from tables_priv; +flush privileges; +drop table backup_users, tables_priv; +uninstall soname 'auth_test_plugin'; |