MySQL fragmentation finder (mysqlfragfinder) is a script written by Phil Dufault that you can use to repair MyISAM and InnoDB tables. I just went through the process of converting all my databases over to InnoDB (on MariaDB)and was looking for a way to defrag and optimize my tables. mysqlfragfinder turned out to be what I need. Here’s how to download, install, and run the tool.

How I came to need mysqlfragfinder

I just moved all my sites over to a VPS at the hosting company asmallorange. During the move a lot of things came to light… things I hadn’t thought about in a long time like the collation of the tables in my MySQL databases. I just hadn’t given it much thought. Once I started looking into it though I found that I’ve had some sites that have been around for a LONG time and some that are very new. The DB’s had differences though and I figured it was time to clean it up and get everything in alignment. So I first made sure that I had the characterset and collation the same across all my databases. I configured MySQL to use the defaults I wanted and so on.

Once that was done I converted all the MyISAM tables over to InnoDB. You can argue if this is the best idea or not but considering InnoDB is the default table handler for MySQL 5.5 onward it’s the one I went with. There are pro’s and con’s do doing this… the biggest con being that you cannot use tools like myphpadmin or the plugin WP–Optimize to optimize (and defrag) InnoDB tables. Now… if you’re on MariaDB OPTIMIZE TABLE works for InnoDB… if the innodb_file_per_table server system variable is set. While there are reasons to use innodb_file_per_table, I haven’t made the jump to using that setting just yet. I may, maybe even in the near future, but I haven’t yet. I need to take things one step at a time. When do decide to I’ve found a great article on how to enable innodb_file_per_table. But again, I haven’t made that leap just yet.

According to the MySQL docs, to speed up index scans, you can periodically perform a “null” ALTER TABLE operation, which causes MySQL to rebuild the table: ALTER TABLE tbl_name ENGINE=INNODB. Alternatively, you can perform a defragmentation operation by using mysqldump to dump the table to a text file, drop the table, and reload it from the dump file. That sounds like a giant pain in the ass so I went looking for someone who’d solved the issue already and I ran across mysqlfragfinder.

Installing mysqlfragfinder

So let’s get to the meat and potatoes … how do we install mysqlfragfinder? Phil has generously put the mysqlfragfinder code up on GitHub; so you can grab it from there. I’m relatively new to MySQL and Linux so I didn’t know how to do this off the top of my head, which is why I’m sharing how to do it here. Now, I know that sounds weird because my sites have all run on Linux and MySQL for years…. I’ve just never dug into them. As a professional I was a Windows developer so that meant the Windows OS and MS SQL Server. There’s a lot of that I can do in my sleep… but this was pretty new, and it turned out very simple.

Just SSH into your machine and execute the following command:

wget https://raw.github.com/pdufault/mysqlfragfinder/master/mysqlfragfinder.sh
 chmod 755 ./mysqlfragfinder.sh

To execute it then just run:

./mysqlfragfinder.sh --user root --password "xxxxxx"

Once run you’ll see it spin through the databases and you’ll see results that look like this: