Commands to manage Mariadb.

1) View definers.

MariaDB [(none)]> SHOW PROCEDURE STATUS;

2) Update definers.

MariaDB [(none)]> UPDATE `mysql`.`proc` p SET definer = ‘root@%’ WHERE definer=’admin@%’;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0

3) Display the privileges for a specific user.

MariaDB [(none)]> SHOW GRANTS FOR ‘root’@’localhost’;

Mariadb_command01

4) Display Privileges for all users.

MariaDB [(none)]> SELECT * FROM information_schema.user_privileges;

Mariadb_command02

 

5) Display a table size in a database.

MariaDB [none]> SELECT table_name AS ‘Tables’, round(((data_length + index_length) / 1024 / 1024), 2) ‘Size in MB’ FROM information_schema.TABLES WHERE table_schema = ‘Your_database_name’ ORDER BY (data_length + index_length) DESC;

6) Display database size in Mariadb.

MariaDB [none]> SELECT table_schema ‘DB Name’, Round(Sum(data_length + index_length) / 1024 / 1024, 1) ‘DB Size in MB’ FROM   information_schema.tables GROUP BY table_schema;

 

7) Display detail size of a database.

MariaDB [none]> SELECT CONCAT(table_schema, ‘.’, table_name) AS ‘Schema.Table’, CONCAT(ROUND(table_rows / 1000000, 2), ‘M’) AS rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), ‘G’) AS DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), ‘G’) AS idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), ‘G’) AS total_size, ROUND(index_length / data_length, 2) AS idxfrac FROM   information_schema.TABLES WHERE table_schema = ‘Your_database_name’ ORDER BY data_length + index_length DESC LIMIT 500;

 

Be the first to comment

Leave a Reply