1
DROP DATABASE IF EXISTS test1;
5
IF(INSTR(table_comment,'InnoDB free') = 0
6
AND INSTR(table_comment,'number_of_replicas') = 0,
8
INSTR(table_comment,'InnoDB free')
9
+ INSTR(table_comment,'number_of_replicas') - 1))
11
'-----------------------------------------------------' AS "Separator"
12
FROM information_schema.tables
13
WHERE table_schema = 'mysql'
14
ORDER BY table_schema,table_name;
17
TABLE_NAME columns_priv
32
TABLE_COLLATION utf8_bin
36
user_comment Column privileges
37
Separator -----------------------------------------------------
55
TABLE_COLLATION utf8_bin
59
user_comment Database privileges
60
Separator -----------------------------------------------------
78
TABLE_COLLATION utf8_general_ci
83
Separator -----------------------------------------------------
101
TABLE_COLLATION utf8_bin
105
user_comment User defined functions
106
Separator -----------------------------------------------------
109
TABLE_NAME general_log
110
TABLE_TYPE BASE TABLE
117
MAX_DATA_LENGTH #MDL#
124
TABLE_COLLATION utf8_general_ci
128
user_comment General log
129
Separator -----------------------------------------------------
132
TABLE_NAME help_category
133
TABLE_TYPE BASE TABLE
140
MAX_DATA_LENGTH #MDL#
147
TABLE_COLLATION utf8_general_ci
151
user_comment help categories
152
Separator -----------------------------------------------------
155
TABLE_NAME help_keyword
156
TABLE_TYPE BASE TABLE
163
MAX_DATA_LENGTH #MDL#
170
TABLE_COLLATION utf8_general_ci
174
user_comment help keywords
175
Separator -----------------------------------------------------
178
TABLE_NAME help_relation
179
TABLE_TYPE BASE TABLE
186
MAX_DATA_LENGTH #MDL#
193
TABLE_COLLATION utf8_general_ci
197
user_comment keyword-topic relation
198
Separator -----------------------------------------------------
201
TABLE_NAME help_topic
202
TABLE_TYPE BASE TABLE
209
MAX_DATA_LENGTH #MDL#
216
TABLE_COLLATION utf8_general_ci
220
user_comment help topics
221
Separator -----------------------------------------------------
225
TABLE_TYPE BASE TABLE
232
MAX_DATA_LENGTH #MDL#
239
TABLE_COLLATION utf8_bin
243
user_comment Host privileges; Merged with database privileges
244
Separator -----------------------------------------------------
247
TABLE_NAME ndb_binlog_index
248
TABLE_TYPE BASE TABLE
255
MAX_DATA_LENGTH #MDL#
262
TABLE_COLLATION latin1_swedish_ci
267
Separator -----------------------------------------------------
271
TABLE_TYPE BASE TABLE
278
MAX_DATA_LENGTH #MDL#
285
TABLE_COLLATION utf8_bin
289
user_comment MySQL plugins
290
Separator -----------------------------------------------------
294
TABLE_TYPE BASE TABLE
301
MAX_DATA_LENGTH #MDL#
308
TABLE_COLLATION utf8_general_ci
312
user_comment Stored Procedures
313
Separator -----------------------------------------------------
316
TABLE_NAME procs_priv
317
TABLE_TYPE BASE TABLE
324
MAX_DATA_LENGTH #MDL#
331
TABLE_COLLATION utf8_bin
335
user_comment Procedure privileges
336
Separator -----------------------------------------------------
340
TABLE_TYPE BASE TABLE
347
MAX_DATA_LENGTH #MDL#
354
TABLE_COLLATION utf8_general_ci
358
user_comment MySQL Foreign Servers table
359
Separator -----------------------------------------------------
363
TABLE_TYPE BASE TABLE
370
MAX_DATA_LENGTH #MDL#
377
TABLE_COLLATION utf8_general_ci
381
user_comment Slow log
382
Separator -----------------------------------------------------
385
TABLE_NAME tables_priv
386
TABLE_TYPE BASE TABLE
393
MAX_DATA_LENGTH #MDL#
400
TABLE_COLLATION utf8_bin
404
user_comment Table privileges
405
Separator -----------------------------------------------------
409
TABLE_TYPE BASE TABLE
416
MAX_DATA_LENGTH #MDL#
423
TABLE_COLLATION utf8_general_ci
427
user_comment Time zones
428
Separator -----------------------------------------------------
431
TABLE_NAME time_zone_leap_second
432
TABLE_TYPE BASE TABLE
439
MAX_DATA_LENGTH #MDL#
446
TABLE_COLLATION utf8_general_ci
450
user_comment Leap seconds information for time zones
451
Separator -----------------------------------------------------
454
TABLE_NAME time_zone_name
455
TABLE_TYPE BASE TABLE
462
MAX_DATA_LENGTH #MDL#
469
TABLE_COLLATION utf8_general_ci
473
user_comment Time zone names
474
Separator -----------------------------------------------------
477
TABLE_NAME time_zone_transition
478
TABLE_TYPE BASE TABLE
485
MAX_DATA_LENGTH #MDL#
492
TABLE_COLLATION utf8_general_ci
496
user_comment Time zone transitions
497
Separator -----------------------------------------------------
500
TABLE_NAME time_zone_transition_type
501
TABLE_TYPE BASE TABLE
508
MAX_DATA_LENGTH #MDL#
515
TABLE_COLLATION utf8_general_ci
519
user_comment Time zone transition types
520
Separator -----------------------------------------------------
524
TABLE_TYPE BASE TABLE
531
MAX_DATA_LENGTH #MDL#
538
TABLE_COLLATION utf8_bin
542
user_comment Users and global privileges
543
Separator -----------------------------------------------------
544
DROP USER testuser1@localhost;
545
CREATE USER testuser1@localhost;
546
GRANT SELECT ON test1.* TO testuser1@localhost;
547
# Establish connection testuser1 (user=testuser1)
550
IF(INSTR(table_comment,'InnoDB free') = 0
551
AND INSTR(table_comment,'number_of_replicas') = 0,
552
LENGTH(table_comment),
553
INSTR(table_comment,'InnoDB free')
554
+ INSTR(table_comment,'number_of_replicas') - 1))
556
'-----------------------------------------------------' AS "Separator"
557
FROM information_schema.tables
558
WHERE table_schema = 'mysql'
559
ORDER BY table_schema,table_name;
562
TABLE_NAME columns_priv
563
TABLE_TYPE BASE TABLE
570
MAX_DATA_LENGTH #MDL#
577
TABLE_COLLATION utf8_bin
581
user_comment Column privileges
582
Separator -----------------------------------------------------
586
TABLE_TYPE BASE TABLE
593
MAX_DATA_LENGTH #MDL#
600
TABLE_COLLATION utf8_bin
604
user_comment Database privileges
605
Separator -----------------------------------------------------
609
TABLE_TYPE BASE TABLE
616
MAX_DATA_LENGTH #MDL#
623
TABLE_COLLATION utf8_general_ci
628
Separator -----------------------------------------------------
632
TABLE_TYPE BASE TABLE
639
MAX_DATA_LENGTH #MDL#
646
TABLE_COLLATION utf8_bin
650
user_comment User defined functions
651
Separator -----------------------------------------------------
654
TABLE_NAME general_log
655
TABLE_TYPE BASE TABLE
662
MAX_DATA_LENGTH #MDL#
669
TABLE_COLLATION utf8_general_ci
673
user_comment General log
674
Separator -----------------------------------------------------
677
TABLE_NAME help_category
678
TABLE_TYPE BASE TABLE
685
MAX_DATA_LENGTH #MDL#
692
TABLE_COLLATION utf8_general_ci
696
user_comment help categories
697
Separator -----------------------------------------------------
700
TABLE_NAME help_keyword
701
TABLE_TYPE BASE TABLE
708
MAX_DATA_LENGTH #MDL#
715
TABLE_COLLATION utf8_general_ci
719
user_comment help keywords
720
Separator -----------------------------------------------------
723
TABLE_NAME help_relation
724
TABLE_TYPE BASE TABLE
731
MAX_DATA_LENGTH #MDL#
738
TABLE_COLLATION utf8_general_ci
742
user_comment keyword-topic relation
743
Separator -----------------------------------------------------
746
TABLE_NAME help_topic
747
TABLE_TYPE BASE TABLE
754
MAX_DATA_LENGTH #MDL#
761
TABLE_COLLATION utf8_general_ci
765
user_comment help topics
766
Separator -----------------------------------------------------
770
TABLE_TYPE BASE TABLE
777
MAX_DATA_LENGTH #MDL#
784
TABLE_COLLATION utf8_bin
788
user_comment Host privileges; Merged with database privileges
789
Separator -----------------------------------------------------
792
TABLE_NAME ndb_binlog_index
793
TABLE_TYPE BASE TABLE
800
MAX_DATA_LENGTH #MDL#
807
TABLE_COLLATION latin1_swedish_ci
812
Separator -----------------------------------------------------
816
TABLE_TYPE BASE TABLE
823
MAX_DATA_LENGTH #MDL#
830
TABLE_COLLATION utf8_bin
834
user_comment MySQL plugins
835
Separator -----------------------------------------------------
839
TABLE_TYPE BASE TABLE
846
MAX_DATA_LENGTH #MDL#
853
TABLE_COLLATION utf8_general_ci
857
user_comment Stored Procedures
858
Separator -----------------------------------------------------
861
TABLE_NAME procs_priv
862
TABLE_TYPE BASE TABLE
869
MAX_DATA_LENGTH #MDL#
876
TABLE_COLLATION utf8_bin
880
user_comment Procedure privileges
881
Separator -----------------------------------------------------
885
TABLE_TYPE BASE TABLE
892
MAX_DATA_LENGTH #MDL#
899
TABLE_COLLATION utf8_general_ci
903
user_comment MySQL Foreign Servers table
904
Separator -----------------------------------------------------
908
TABLE_TYPE BASE TABLE
915
MAX_DATA_LENGTH #MDL#
922
TABLE_COLLATION utf8_general_ci
926
user_comment Slow log
927
Separator -----------------------------------------------------
930
TABLE_NAME tables_priv
931
TABLE_TYPE BASE TABLE
938
MAX_DATA_LENGTH #MDL#
945
TABLE_COLLATION utf8_bin
949
user_comment Table privileges
950
Separator -----------------------------------------------------
954
TABLE_TYPE BASE TABLE
961
MAX_DATA_LENGTH #MDL#
968
TABLE_COLLATION utf8_general_ci
972
user_comment Time zones
973
Separator -----------------------------------------------------
976
TABLE_NAME time_zone_leap_second
977
TABLE_TYPE BASE TABLE
984
MAX_DATA_LENGTH #MDL#
991
TABLE_COLLATION utf8_general_ci
995
user_comment Leap seconds information for time zones
996
Separator -----------------------------------------------------
999
TABLE_NAME time_zone_name
1000
TABLE_TYPE BASE TABLE
1005
AVG_ROW_LENGTH #ARL#
1007
MAX_DATA_LENGTH #MDL#
1014
TABLE_COLLATION utf8_general_ci
1018
user_comment Time zone names
1019
Separator -----------------------------------------------------
1022
TABLE_NAME time_zone_transition
1023
TABLE_TYPE BASE TABLE
1028
AVG_ROW_LENGTH #ARL#
1030
MAX_DATA_LENGTH #MDL#
1037
TABLE_COLLATION utf8_general_ci
1041
user_comment Time zone transitions
1042
Separator -----------------------------------------------------
1045
TABLE_NAME time_zone_transition_type
1046
TABLE_TYPE BASE TABLE
1051
AVG_ROW_LENGTH #ARL#
1053
MAX_DATA_LENGTH #MDL#
1060
TABLE_COLLATION utf8_general_ci
1064
user_comment Time zone transition types
1065
Separator -----------------------------------------------------
1069
TABLE_TYPE BASE TABLE
1074
AVG_ROW_LENGTH #ARL#
1076
MAX_DATA_LENGTH #MDL#
1083
TABLE_COLLATION utf8_bin
1087
user_comment Users and global privileges
1088
Separator -----------------------------------------------------
1089
# Switch to connection default and close connection testuser1
1090
DROP USER testuser1@localhost;
1091
DROP DATABASE test1;