Check MySql Space

Question

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

Answer 1

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

How to monitor MySQL space?

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

The df displays six columns

[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:

[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.

Therefore, just do a line count of the df display. 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:

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 $2}'`
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 $4}'` ; fi
if [ ${LC} -eq 3 ] ; then SPC=`tail -1 /tmp/dfmysql.txt | awk '{print $3}'` ; fi
echo ${SPC}

Notice the line that has

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

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

• df gives output in KB
• df -B 1K gives output in KB
• df -B 1M gives output in MB

The only mysql interaction is retrieving datadir from mysql.

Give it a Try !!!

Answer 2

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

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.