1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
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';
|
