MySQL数据库常用操作汇总

1.查询数据库各个库大小

语法：

SELECT
  TABLE_SCHEMA AS "库名",
  TRUNCATE(SUM(`DATA_LENGTH`) / 1024 / 1024, 2) AS "表所占空间（MB）",
  TRUNCATE(SUM(`INDEX_LENGTH`) / 1024 / 1024, 2) AS "索引所占空间（MB）",
  TRUNCATE((SUM(`DATA_LENGTH`) + SUM(`INDEX_LENGTH`)) / 1024 / 1024,2) AS "空间累计（MB）"
FROM
  information_schema.`TABLES`
GROUP BY `TABLE_SCHEMA`;

例子：

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 16891974
Server version: 5.6.28-cdb2016-log 20180130

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SELECT
    ->   TABLE_SCHEMA AS "库名",
    ->   TRUNCATE(SUM(`DATA_LENGTH`) / 1024 / 1024, 2) AS "表所占空间（MB）",
    ->   TRUNCATE(SUM(`INDEX_LENGTH`) / 1024 / 1024, 2) AS "索引所占空间（MB）",
    ->   TRUNCATE((SUM(`DATA_LENGTH`) + SUM(`INDEX_LENGTH`)) / 1024 / 1024,2) AS "空间累计（MB）"
    -> FROM
    ->   information_schema.`TABLES`
    -> GROUP BY `TABLE_SCHEMA`;
+----------------------+-------------------------+----------------------------+----------------------+
| 库名                 | 表所占空间（MB）        | 索引所占空间（MB）         | 空间累计（MB）       |
+----------------------+-------------------------+----------------------------+----------------------+
| test                |                60653.62 |                   29787.29 |             90440.92 |

2.查询并发

show global status like 'Threads%';

例子：

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1868103
Server version: 5.7.18-txsql-log 20190203

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show global status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 121   |
| Threads_connected | 202   |
| Threads_created   | 326   |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)

MySQL [(none)]>

3.查询从节点
语法：

show slave hosts;

例子：

MySQL [(none)]> show slave hosts;
+-----------+------+-------+-----------+--------------------------------------+
| Server_id | Host | Port  | Master_id | Slave_UUID                           |
+-----------+------+-------+-----------+--------------------------------------+
|      6946 |      | 20144 |    129415 | cc7d2390-2791-11ea-8185-b8599f4e4f29 |
|    129414 |      | 20130 |    129415 | 7b302430-2790-11ea-be67-58f9875cd401 |
+-----------+------+-------+-----------+--------------------------------------+
2 rows in set (0.00 sec)