Mysql 運維常用指令

1. Database size

SELECT table_schema "Data Base Name",
    sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
    sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ; 

2. Show all indexs for a table

SHOW INDEX FROM yourtable;

3. Check how many connection are opened.

show status where `variable_name` = 'Threads_connected';

4. Count table rows

SELECT TABLE_NAME AS "table name", 
table_rows AS "Quant of Rows", ROUND( (
data_length + index_length
) /1024, 2 ) AS "Total Size Kb"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'xxx'

5. Tables size

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"

6. Create User

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

7. Grant permissions for a user (Please change database.table)

GRANT ALL PRIVILEGES ON database.table TO 'user'@'localhost';

8. Check permission

SHOW GRANTS FOR 'root'@'localhost';

9. Show locks

select * from information_schema.INNODB_LOCKS;