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
|
--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 #
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 USER;
# 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
# 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 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
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 USER;
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;
|
