MariaDB / MySQL: How to vacuum your deleted database (shrink size of ibdata1 file)

After checking out the data from a well known leak, I realised that MySQL poorly handles the deletion of databases and that the default configuration doesn't make it very easy to vacuum the obsolete data.

(5.6 changes the default so it's better, but still an issue)

Have no fear, twig (and the internet's collective knowledge) to the rescue!

• Get a list of all databases on your server

SHOW DATABASES;

• Make a backup of all the data. You're going to need it. You can skip internal databases information_schema, mysql, and performance_schema.

mysqldump -u root -p database_name > database_name.sql

• If you're lazy you can just use this instead:

mysqldump -u root -p --all-databases > everything.sql

• Now drop every single table EXCEPT for the internal databases; databases information_schema, mysql, and performance_schema.

DROP DATABASE database_name;

• Now terminate the server process.
• Open up my.cfg or my.ini (Windows)
• Under "mysqld", add in "innodb_file_per_table". This changes the setting so the information for each database goes into a separate file.
• From your MySQL data directory (MySQL\5.5\data or /var/lib/mysql/), delete the ibdata1 and ib_logfile* files.
• Restart the MySQL service.
• Log back into the SQL shell and create each database again.

CREATE DATABASE database_name;

• And finally, reimport the data.

mysql -u root -p database_name < database_name.sql

Once again, thank you technology for keeping me up way past my bedtime. Hopefully this has helped you avoid the situation I'm in right now... Looking for suitable gif animations can be hard, but I've found a photo that's surmised how I feel about the way MySQL has been built.

Sources

• database - How to shrink/purge ibdata1 file in MySQL - Stack Overflow
• innodb - What is the best way to reduce the size of ibdata in mysql? - Database Administrators Stack Exchange
• database - How to shrink/purge ibdata1 file in MySQL - Stack Overflow
• MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
• mysqldump - Export and Import all MySQL databases at one time - Stack Overflow

Python: Add MySQL support on Windows

I was starting work on migrating a project over to Django and needed access to an existing MySQL database. However, I couldn't as I was using Instant Django as a development server on Windows.

When trying to run Instant Django with MySQL, you're gonna see this error:

File "instant_django\Python27\lib\site-packages\django\db\backends\mysql\base.py", line 14, in <module>
raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e)
django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named _mysql

This is because Instant Django only ships with only sqlite support. If you want to run it with MySQL support, you'll have to add a few files your the setup.

Installation

Unfortunately, Instant Django is not installed so the installer for MySQL Python won't work because there is no registry value for Python.

• Download and install the "MySQL-python-1.2.3.win32-py2.7.exe" from codegood.com.
• Extract the contents of "MySQL-python-1.2.3.win32-py2.7.exe" (using WinRar or 7-zip) and you'll get a folder called "PLATLIB".
• Move "_mysql.pyd" and "_mysql_exceptions.py" to "instant_django\Python27\Lib\site-packages\PIL".
• Move "MySQLdb" to "instant_django\Python27\Lib\site-packages".
• Try to start your Django server and it should actually work.

Just in case

Instant Django is running the 32bit version of Python 2.7 (at time of writing), which cannot load the MySQL-Python x64 libraries.

If you're trying to install the 64-bit version, you'll get this error:

File "instant_django\Python27\lib\site-packages\django\db\backends\mysql\base.py", line 14, in <module>
raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e)
django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: DLL load failed: %1 is not a valid Win32 application.

Now that you're over this hurdle, happy coding!

[ Download ]

SQL: Zero padding your numbers

Most SQL servers should support LPAD() and RPAD().

The syntax for these functions are "LPAD(string, length, pad)".

The padding is space by default, but just set it to "0" to use zeros.