Mysql show databases with size8/6/2023 ![]() SELECT table_schema, table_name, engine, data_free, data_length+index_length as data_used, (DATA_FREE / (DATA_LENGTH+INDEX_LENGTH)) as TAUX_FRAG FROM information_schema. I tried various combination of privileges on different DBs and tables and none of them works.ĭid someone had the same need? How did he/she resolved it?įor completeness, the queries executed by the monitoring plugin are: show variables like 'innodb_file_per_table' it's password is stored in cleartext in the monitoring software) and so it must not be able to see application data. I could achive the result giving the SELECT privilege on *.* to monitor user, but I don't want to do that because the monitor user is not protected enough (i.e. SELECT TABLESCHEMA AS Database, TABLENAME AS Table, ROUND( (DATALENGTH + INDEXLENGTH) / 1024 / 1024) AS Size (MB) FROM informationschema.TABLES ORDER BY (DATALENGTH + INDEXLENGTH) DESC This will return not only the size of the table, but also the table name and parent database it is associated with. The access denied is due to the particular use that mysql reserves to the information_schema DB the access to that DB is limited by the granted access to all other mysql objects. Mysql> grant SELECT on information_schema.tables to 1044 (42000): Access denied for user to database 'information_schema' | GRANT ALL PRIVILEGES ON *.* TO WITH GRANT OPTION | Obviously the root user I'm using has the GRANT privilege. To access DB size I need that the user monitor can access information_schema.tables, but I cannot give it direct select privileges to that table. Mysql> grant SHOW DATABASES, REPLICATION CLIENT on *.* to flush privileges ![]() As a best practice I create a dedicated user for monitoring and I give it the least possible privileges.įor simple monitoring I usually create a monitor user with only 2 global privileges: mysql> create user identified by 'monitorpassword' I'm using centreon_plugins to monitor some mysql databases. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |