How to free up /var/lib/mysql (virtualbox vm)

Question

I imported big dump into mysql and filled up my whole space (dumb me). Machine that's mysql server is virtualbox virtual machine. It's using automatically expanding virtual hard drive. I freed up few GBs on host system but guest vm still shows that it's hard drive is full. Mysql won't start because /var/lib/mysql is too full. How do I free up /var/lib/mysql so mysql will start?

Answer 1

You have a couple of options depending what your setup is: a) if you have replication or binary logs turned on, you could turn it off and move/remove the binary logs to free up some space - this seems a temporary solution b) if you use innodb and you have large innodb log files you could tune them to smaller sizes - not great for performance but will free up some space c) if you use lvm you could try these instructions to stretch your partition http://www.knd.com.au/stretching-the-lvm-on-a-vmware-debian-virtual-appliance-live/

Answer 2

If you don't care about any of the data in that directory you can just delete that directory,

rm -rf /var/lib/mysql

and this run this command to set up the basic mysql tables:

mysql_install_db --user=mysql

Alternativly you can cd into /var/lib/mysql and delete folders and files. MySQL / MYIASM uses one directory per database and a few files per table (you can see the table name in the filename). You can delete them.

I'm not sure if it's 100% safe to just delete the files. You should delete the files, then do a mysqldump of the remaining data you want to keep and recreate all the data using the above command.