MySQL管理工具MySQL Utilities — 查询MySQL使用的空间大小(32)

当我们准备备份或维护数据的时候，常常会考虑磁盘空间大小的问题，通常需要知道我们的数据和日志有多大。数据是至关重要的，数据是生命线。

使用mysqldiskusage 工具来查看数据库实例使用到的空间，包括数据库和各种日志大小。文章源自运维生存时间-https://www.ttlsa.com/mysql/find-out-how-much-space-uses/

实例

shell> sudo env PYTHONPATH=$PYTHONPATH mysqldiskusage \
--server=root:root@localhost --all
# Source on localhost: ... connected.
# Database totals:
+-----------------+--------------+
| db_name         |       total  |
+-----------------+--------------+
| oltp2           | 829,669      |
| bvm             | 15,129       |
| db1             | 9,895        |
| db2             | 11,035       |
| employees       | 206,117,692  |
| griots          | 14,415       |
| mysql           | 995,722      |
| oltp1           | 177,393      |
| room_temp       | 9,847        |
| sakila          | 791,727      |
| test            | 647,911      |
| test_arduino    | 9,999        |
| welford_kindle  | 72,032       |
| world           | 472,785      |
| world_innodb    | 829,669      |
+-----------------+--------------+

Total database disk usage = 210,175,251 bytes or 200.44 MB

# Log information.
+--------------------+--------------+
| log_name           |        size  |
+--------------------+--------------+
| host123.log        | 957,282,265  |
| host123-slow.log   |     123,647  |
| host123.local.err  | 321,772,803  |
+--------------------+--------------+

Total size of logs = 1,279,178,715 bytes or 1.19 GB

# Binary log information:
Current binary log file = my_log.000287
+----------------+---------+
| log_file       | size    |
+----------------+---------+
| my_log.000285  | 252208  |
| my_log.000286  | 256     |
| my_log.000287  | 3063    |
| my_log.index   | 48      |
+----------------+---------+

Total size of binary logs = 255,575 bytes or 249.58 KB

# Server is not an active slave - no relay log information.
# InnoDB tablespace information:
+--------------+--------------+
| innodb_file  |        size  |
+--------------+--------------+
| ib_logfile0  |   5,242,880  |
| ib_logfile1  |   5,242,880  |
| ibdata1      | 815,792,128  |
| ibdata2      |  52,428,800  |
+--------------+--------------+

Total size of InnoDB files = 889,192,448 bytes or 848.00 MB

InnoDB freespace = 635,437,056 bytes or 606.00 MB

使用--all选项，将显示所有日志和InnoDB的磁盘使用情况。也会列出所有数据库，即使不包含任何数据。文章源自运维生存时间-https://www.ttlsa.com/mysql/find-out-how-much-space-uses/

权限

用户必须具有读取数据目录的权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/find-out-how-much-space-uses/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/find-out-how-much-space-uses/