Database


Handling InnoDB Corruption

Having your storage ripped from out from under your database is never good. I recently had to go through this where our iscsi storage backend powering our virtual machine vmdk’s magically died and even worse, its write cache was completely cleared out. Upon things coming back, mysqld was in a reboot loop which could only be stopped by enabling a innodb_force_recovery option in my.conf. Here’s some of the output starting up with recovery on:

Continue reading ↦

Backing up MySQL privileges/users

When migrating from one server to another, migrating the sql database is usually advantageous. There are two ways to do this, dumping the old database to a file(s) and re-importing that file to the new server, or, copy the database files themselves over to the new server (/var/lib/mysql) making sure they are owned by the new mysql user! If you perform the first method for migration/backup, it may be advantageous to also back up the mysql users you’ve created and granted access to certain db’s for.

Continue reading ↦

Compress a MySQL database table

MySQL InnoDB engine based databases support compression of table data using zlib compression algorithm. From the official documentation, it is quite easy to create or alter a table to support compression! It of course helps quite a bit with column’s you might plan to use which contain a lot of text (using the TEXT, MEDIUMTEXT, LONGTEXT column formats). Here is how I altered my table using phpmyadmin (since I didn’t see an obvious place in the GUI to do it, I just ran the following SQL statements on the DB):

Continue reading ↦

Finding a needle in a haystack: SQL with regex

So I run multiple pastebin services. One day, a friend needed a paste from weeks ago and so to the database I went (using stikked). I needed to search the paste content, known as column “*raw*” and pull out the record corresponding to the content (date, id, etc..). What’s awesome is you can use regex in all of your sql statements when searching anywhere in the db. So I just did this:

Continue reading ↦