If you frequently delete rows (or update rows with variable-length data types), you can end up with a lot of wasted space in your data file(s), similar to filesystem fragmentation. If you're not using the innodb_file_per_table option, the only thing you can do about it is export and import the database, a time-and-disk-intensive procedure. … [Read more...]
Recovering a Schema From InnoDB .frm Files
Sometimes you find yourself in a bad situation where your only hope of recovering your InnoDB data lies in a handful of .frm and .ibd data files that were heretofore part of a working MySQL installation. It could be the case that someone thought backing up InnoDB tables was simply a matter of of copying the .ibd and .frm files somewhere safe. … [Read more...]
Introducing the Bluegecko MySQL Training AMI
I created the Bluegecko MySQL training AMI as a MySQL sandbox that folks could use to learn things about MySQL. I wanted the AMI to have MySQL 5.0, and a large collection of tools — for both tinkering and visualizing what is happening inside MySQL and on the system in general. I chose to host the AMI on a small instance since they’re … [Read more...]
Using Checksums to Ensure Table Consistency in MySQL
If you're using MySQL replication, chances are your master and slave databases aren't entirely consistent. There are a number of reasons for this. Some MySQL functions like UUID() don't replicate properly with statement based replication. Poor network connectivity can sometimes result in corrupted relay logs. Misconfigured software can … [Read more...]