DROP DATABASE IF EXISTS test1;
CREATE DATABASE test1;
SELECT *,
LEFT( table_comment,
IF(INSTR(table_comment,'InnoDB free') = 0
AND INSTR(table_comment,'number_of_replicas') = 0,
LENGTH(table_comment),
INSTR(table_comment,'InnoDB free')
+ INSTR(table_comment,'number_of_replicas') - 1))
AS "user_comment",
'-----------------------------------------------------' AS "Separator"
FROM information_schema.tables
WHERE table_schema = 'mysql'
ORDER BY table_schema,table_name;
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	columns_priv
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_bin
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Column privileges
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	db
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_bin
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Database privileges
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	event
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Dynamic
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Events
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	func
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_bin
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	User defined functions
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	general_log
TABLE_TYPE	BASE TABLE
ENGINE	CSV
VERSION	10
ROW_FORMAT	Dynamic
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	General log
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	help_category
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Dynamic
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	help categories
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	help_keyword
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	help keywords
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	help_relation
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	keyword-topic relation
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	help_topic
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Dynamic
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	help topics
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	host
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_bin
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Host privileges;  Merged with database privileges
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	ndb_binlog_index
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Dynamic
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	latin1_swedish_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	plugin
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_bin
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	MySQL plugins
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	proc
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Dynamic
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Stored Procedures
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	procs_priv
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_bin
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Procedure privileges
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	servers
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	MySQL Foreign Servers table
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	slow_log
TABLE_TYPE	BASE TABLE
ENGINE	CSV
VERSION	10
ROW_FORMAT	Dynamic
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Slow log
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	tables_priv
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_bin
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Table privileges
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	time_zone
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	6
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Time zones
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	time_zone_leap_second
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Leap seconds information for time zones
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	time_zone_name
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Time zone names
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	time_zone_transition
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Time zone transitions
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	time_zone_transition_type
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Fixed
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_general_ci
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Time zone transition types
Separator	-----------------------------------------------------
TABLE_CATALOG	NULL
TABLE_SCHEMA	mysql
TABLE_NAME	user
TABLE_TYPE	BASE TABLE
ENGINE	MyISAM
VERSION	10
ROW_FORMAT	Dynamic
TABLE_ROWS	#TBLR#
AVG_ROW_LENGTH	#ARL#
DATA_LENGTH	#DL#
MAX_DATA_LENGTH	#MDL#
INDEX_LENGTH	#IL#
DATA_FREE	#DF#
AUTO_INCREMENT	NULL
CREATE_TIME	#CRT#
UPDATE_TIME	#UT#
CHECK_TIME	#CT#
TABLE_COLLATION	utf8_bin
CHECKSUM	NULL
CREATE_OPTIONS	#CO#
TABLE_COMMENT	#TC#
user_comment	Users and global privileges
Separator	-----------------------------------------------------
DROP   USER testuser1@localhost;
CREATE USER testuser1@localhost;
GRANT SELECT ON test1.* TO testuser1@localhost;
# Establish connection testuser1 (user=testuser1)
SELECT *,
LEFT( table_comment,
IF(INSTR(table_comment,'InnoDB free') = 0
AND INSTR(table_comment,'number_of_replicas') = 0,
LENGTH(table_comment),
INSTR(table_comment,'InnoDB free')
+ INSTR(table_comment,'number_of_replicas') - 1))
AS "user_comment",
'-----------------------------------------------------' AS "Separator"
FROM information_schema.tables
WHERE table_schema = 'mysql'
ORDER BY table_schema,table_name;
# Switch to connection default and close connection testuser1
DROP USER testuser1@localhost;
DROP DATABASE test1;
