检查 MySql 空间

Check MySql Space

提问by MrB

Is there a command to check how much room MySql has left?

是否有命令可以检查 MySql 还剩下多少空间？

采纳答案by Konerak

Usually, MySQL is not limiting the space it can use. You can use all the space your hard disk(s)contain.

通常，MySQL 不会限制它可以使用的空间。您可以使用硬盘包含的所有空间。

There are a few exceptions though, for really big tables. For example the MyISAM Storage Engine, there is a Max_Data_Length which determines how much data a table can handle (depending on number of bytes used for pointers)... but by default 4Gb per table is no problem, and from MySQL 5 a table can store 256TB.

但是，对于非常大的桌子，也有一些例外。例如 MyISAM 存储引擎，有一个 Max_Data_Length 决定一个表可以处理多少数据（取决于用于指针的字节数）......但默认情况下，每个表 4Gb 没有问题，从 MySQL 5 开始，一个表可以存储 256TB。

回答by RolandoMySQLDBA

@PeterTurner I have an earlier post from Oct 29, 2010 on how much space is used by MySQL

@PeterTurner 我在 2010 年 10 月 29 日有一篇关于 MySQL 使用了多少空间的早期帖子

How to monitor MySQL space?

如何监控MySQL空间？

However, I know this is not what you are looking for. You do not want how much diskspace MySQL is using. Your question is simply how much space is left where mysql is writing. That's not a MySQL operation. Nevertheless, you can take advantage of MySQL to quickly answer your question in conjunction with the local OS. In conjunction with what? The command df

但是，我知道这不是您要查找的内容。您不希望 MySQL 使用多少磁盘空间。您的问题只是 mysql 正在写入的地方还剩下多少空间。那不是 MySQL 操作。不过，您可以利用 MySQL 结合本地操作系统快速回答您的问题。结合什么？命令df

The dfdisplays six columns

该df显示六列

[root@******** ~]# df -h | head -1
Filesystem            Size  Used Avail Use% Mounted on

That breaks down to

这分解为

• Filesystem
• Size
• Used
• Avail
• Use%
• Mounted on

• 文件系统
• 尺寸
• 用过的
• 可用性
• 用％
• 镶嵌在

You need to get column 4, Avail. There is one problem with this idea: If the filesystem name is too long, that moves columns 2-6 to the next line. Here is an example:

你需要得到第 4 列，Avail。这个想法有一个问题：如果文件系统名称太长，会将第 2-6 列移到下一行。下面是一个例子：

[root@******** ~]# df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg1-root 122236876   9196208 106731192   8% /
/dev/mapper/vg2-data01
                     1726991928 810877540 828388308  50% /data
/dev/sdc1            3844656172 559221048 3090137848  16% /backup
/dev/sda1               101086     17569     78298  19% /boot
tmpfs                 98976204         0  98976204   0% /dev/shm
none                  16777216     58576  16718640   1% /var/tmpfs
[root@i******** ~]# df -h /var/lib/mysql
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg2-data01
                      1.7T  774G  791G  50% /data

Note that the data folder's filesystem name moves columns 2-6 to the next line.

请注意，数据文件夹的文件系统名称会将第 2-6 列移至下一行。

Therefore, just do a line count of the dfdisplay. If there are two lines, get token #4. If there are three lines, get token #3. Here is the shell script that will echo the available space where mysql's datadir is mounted:

因此，只需对df显示进行行数计数。如果有两行，则获取令牌 #4。如果有三行，则获取令牌 #3。这是 shell 脚本，它将回显 mysql 的 datadir 挂载的可用空间：

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQLSTMT="SHOW VARIABLES LIKE 'datadir'"
DATADIR=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print }'`
df -B 1 ${DATADIR} > /tmp/dfmysql.txt
LC=`wc -l < /tmp/dfmysql.txt`
if [ ${LC} -eq 2 ] ; then SPC=`tail -1 /tmp/dfmysql.txt | awk '{print }'` ; fi
if [ ${LC} -eq 3 ] ; then SPC=`tail -1 /tmp/dfmysql.txt | awk '{print }'` ; fi
echo ${SPC}

Notice the line that has

请注意具有

df -B 1 ${DATADIR} > /tmp/dfmysql.txt

This will give the dfreport in bytes. You can change it:

这将以df字节为单位给出报告。你可以改变它：

• dfgives output in KB
• df -B 1Kgives output in KB
• df -B 1Mgives output in MB

• df以 KB 为单位输出
• df -B 1K以 KB 为单位输出
• df -B 1M以 MB 为单位给出输出

The only mysql interaction is retrieving datadirfrom mysql.

唯一的 mysql 交互是datadir从 mysql 中检索。

Give it a Try !!!

试一试 ！！！

回答by Somnath Muluk

MySQL using your disk space, so when your disk is having room left then MySQL also should also have room.

MySQL 使用您的磁盘空间，因此当您的磁盘有剩余空间时，MySQL 也应该有空间。

In MySQL, use the SHOW TABLE STATUScommand, and look at the Data_length column for each table, which is in bytes.

在 MySQL 中，使用该SHOW TABLE STATUS命令，并查看每个表的 Data_length 列，以字节为单位。

See also :

也可以看看 ：

1. How to monitor MySQL space?
2. How can you determine how much disk space a particular MySQL table is taking up?
3. Mysql table size on the HDD
4. How do I check how much disk space my database is using?

1. 如何监控MySQL空间？
2. 如何确定特定 MySQL 表占用了多少磁盘空间？
3. 硬盘上的 Mysql 表大小
4. 如何检查我的数据库使用了多少磁盘空间？

回答by BlueBird

Confusing, It will depends on your hard disk space.

令人困惑，这取决于您的硬盘空间。

If you have space in you Hard Drive where you have been installed mysql server, You can store million ++ records.

如果您安装了mysql服务器的硬盘驱动器中有空间，您可以存储百万条++记录。