Friday, 14 September 2012

Rebuilding MySQL databases without filling your disk

To reclaim disk space used by an old MySQL database it is not sufficient to drop the database. This is a famous 2003 bug. It is unbelievable that an infrastructure element as crucial and ubiquitous as MySQL has any outstanding bugs, let alone loads.

Assuming you are using InnoDb tables (the only sane choice if you want normal SQL functionality, such as transactions) then by default the data will be stored all on one big file. You will not be allowed to move the data unless you configure appArmor.

Another approach is to dump, drop, delete and recreate your databases.

Machine spec

The machine is a VM running Ubuntu

Linux version 3.2.0-23-generic (buildd@crested) (gcc version 4.6.3 (Ubuntu/Linar
o 4.6.3-1ubuntu4) ) #36-Ubuntu SMP Tue Apr 10 20:39:51 UTC 2012

The default MySQL setup is not great so setup some more sensible ones. Additionally lest put databases in separate files:



I am, writing this from the position of recovering from a filled disk, with a single innodata1 file. I have more space on another partition but you cannot just use a symlink because of this bug. So the first we need to get to a working position, DON'T

cd /var/lib/mysql
rm ibdata1
rm ib_logfile0
rm ib_logfile1

So lets move our modified /etc/mysql/my.cnf out of the way and reinstall mysql.

mv /etc/mysql/my.cnf . 
apt-get remove --purge mysql-server
apt-get install mysql-common
apt-get install mysql-server

However this still left stuff hanging around, so:

rm -rf /etc/mysql
rm -rf /var/lib/mysql

Then reinstall and copy our saved config back:

apt-get install mysql-server
/etc/init.d/mysql stop
cp ~/my.cnf /etc/mysql/
/etc/init.d/mysql start


apt-get install apparmor-utils
aa-complain /usr/sbin/mysqld


apt-get remove --purge apparmor
/etc/init.d/mysql stop
cp ~/my.cnf /etc/mysql/
/etc/init.d/mysql start

/etc/init.d/mysql stop
rm /var/lib/mysql/ib_logfile0
rm /var/lib/mysql/ib_logfile1
/etc/init.d/mysql start

Setup mysql user

create user 'repo_builder'@'localhost' identified by 'scrt';
grant all privileges on *.* to 'repo_builder'@'localhost' with grant option;

create database chassisPruned;
create database curated_files;

Restart Jenkins then kick off the job

/etc/init.d/jenkins start

Just need to enable this process to be repeated before each build...

No comments:

Post a Comment