Author Andriy Lysyuk. Introduction In the previous post, we described a situation when the UnDrop For InnoDB toolkit can be used to recover an accidentally dropped table with innodb_file_per_table=OFF.In this post, we’ll show how to recover MySQL tables or databases if innodb_file_per_table is ON. This option tells InnoDB to store each table with a user… Continue reading Recovery After DROP TABLE, With innodb_file_per_table ON
Recovery After DROP TABLE, With innodb_file_per_table OFF
Author Andriy Lysyuk. Introduction Unfortunately, human mistakes are inevitable. That’s how life is. Wrong DROP DATABASE or DROP TABLE may destroy critical data on the MySQL server. Obviously, backups would help, however they’re not always available. This situation is frightening but not hopeless. In many cases it’s possible to recover almost all the data that… Continue reading Recovery After DROP TABLE, With innodb_file_per_table OFF
Verify MySQL Backups With TwinDB Backup Tool
By Maksym Kryva. If you don’t verify backups you may safely assume you don’t have them. It often happens that MySQL backups can be invalid or broken due to a software bug, or some hidden corruption. If you are lucky enough, hours and days will be needed to resurrect a database from a bad backup… Continue reading Verify MySQL Backups With TwinDB Backup Tool
Recover Table Structure From InnoDB Dictionary
When a table gets dropped, MySQL removes the respective .frm file. This post explains how to recover the table structure if the table was dropped. You need the table structure to recover a dropped table from the InnoDB tablespace. The B+tree structure of the InnoDB index doesn’t contain any information about field types. MySQL needs… Continue reading Recover Table Structure From InnoDB Dictionary
Simple shell script is bad idea
Internet is full of simple shell backup scripts. Every DBA and system administrators (including myself) has written one. Bash is a bad tool for this job and in this post I will explain why. Let’s find out why the need to write anything arises at first place. There is a lot of great backup tools… Continue reading Simple shell script is bad idea
Recovering A Corrupt MySQL Database
Author Andriy Lysyuk. The unDROP for InnoDB tool can be used to recover corrupt MySQL databases. In this post, we will show how to repair a MySQL database if its files got corrupted and even innodb_force_recovery=6 doesn’t help. The corruption of InnoDB tablespace may be caused by many reasons. A dying hard drive can write… Continue reading Recovering A Corrupt MySQL Database
Show Differences Found by pt-table-checksum
First of all, pt-table-checksum is perfect for checking if the master and its slaves are in-sync. It answers the question “Are the slaves consistent with the master?”. However, if they’re not, pt-table-checksum doesn’t actually tell you what exactly is wrong. master# pt-table-checksum -q TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 09-03T22:21:10 0 1 2 1 0… Continue reading Show Differences Found by pt-table-checksum
Efficient Use Of Indexes In MySQL
These are the slides of the “Efficient Use Of Indexes In MySQL” talk we delivered on the SFMySQL Meetup. This is an introductory talk for developers on MySQL indexes. In my opinion, it’s quite important to understand how InnoDB organizes data. If you know how MySQL accesses data, it’s easier to write optimal queries. When working… Continue reading Efficient Use Of Indexes In MySQL
InnoDB Dictionary
Why Does InnoDB Need A Dictionary An InnoDB dictionary is a set of internal tables InnoDB uses to maintain various information about user tables. It serves as API between a human and the database. While the humans refer to tables by their names, InnoDB works with integer identifiers. The dictionary stores correspondence between table name… Continue reading InnoDB Dictionary