Scaling down DB where filesystem is almost full Our DBA is on holiday, and since I know 'a bit' of MySQL, they've asked me to fix this. The data slice is at 99%, and I need to know what to get rid of.


  [root@servername]# find . -type f -size +1000000k -exec ls -lh {} \; |
  awk '{ print $9 ": " $5 }' ./mysql/stuffInDatabase.ibd: 6.8G


Thing is, I've never done this before, and so don't know where to start. Can someone point me in the right direction? Which database do I need to use, the production one or the information_schema? Which query do I use? Do I need to dump and then reimport?

Thanks in advance.
If you are not experienced at both mysql and linux sysadmin I would suggest backing off from this task: if this is a production system there could be much trouble if something goes wrong and data is lost! If you do proceed, take care to make sure you take, and test, fresh backups before doing anything.

I'm assuming the tables are of InnoDB type as that is the default these days. IIRC InnoDB files can not be shrink by the standard tools: when data is deleted the space is just left unallocated internally (not released by to the filesystem) to be used again as more data comes in. As your file is not ibdata I assume you have the "one file per innodb table` option turned on, in which case to free unallocated space within the file, dump that table to file, drop the table, recreate the table and then import the dump file back into it. If there is little free space in the file to start with (i.e. there has been very little delete activity recently) this won't help much though: the resulting file will be more-or-less the same size after you have finished. Also, as the database will be in an inconsistent state during the process, you should block access to it (aside from the admin accounts you need to perform the dump/drop/rebuild/restore of course) for the duration.

If your server uses LVM for space/partition management then you may have free space in the appropriate volume group and can just expend the volume to allow extra space. If you have room on other file-systems you could move the file to one of those and link to it from its currently location (IIRC mysql is happy to follow softlinks to find data-files) like so:


stop mysql
move the files to a volume with more space (though not /tmp or anything with tmp in the name - these may be wiped on start-up after reboot or power-fail)
create a sym-link with the same name in the old location pointing to the new on
restart mysql


(obviously all your databases on this machine will be inaccessible during this process)

To see what free space you have where on current filesystems run df -h. If you are using LVM run the commands lvs, vgs and pvs to see what space you have where outside that currently allocated to filesystems (if you are not using LVM these three commands will either return nothing or not be found). I may be straying a tad far off-topic here IMO (out of DBA-land and into ServerFault territory) so I'll not go into more detail.

I'll say again though: if you are not a confident DBA+sysadmin and this is a production system I would stay away. Failing that, at least try practice on another system first (do you have a development environment setup similarly to the production one?) so you can verify what you are about to do before doing it on real data.

If you do do anything make sure you fully document your actions (all commands entered and why, all output received) so your usual DBA/sysadmin can review things upon return and make any further adjustments that may be needed to keep things running well long-term.