Messing up things is not new to us humans. In this post, I'll tell you how to mess up your MySQL Server if you're frustrated with it. I'll also tell you how to recover your messed up MySQL instance when you realize your mistake.
Before doing that, we need to know the table storage architecture of MySQL. It has a Files per Tablespace architecture. It means MySQL keeps two files per table on the disk. These files have the same name as its respective table but different extensions (formats). One file is of format .frm and the other is of .ibd. The file with former format contains the schema information of the table like columns, constraints, primary key and foreign keys defined in the table whereas the latter contains the data, indexes of the table. Before MySQL 5.6.7, all the tables used to reside in System Tablespace. This contains metadata of Storage Engine and can be usually found in the following path,
C:\Program Files\MySQL\data\mysql
C:\Program Files\MySQL\data\performance_schema
C:\Program Files\MySQL\data\performance_schema
As the System Table space gets increased in size with the increasing data in the server, from version 5.6.7, InnoDB tables are stored in different filespace. If you install MySQL in your C:\ drive, it will be found in the following path,
C:\ProgramData\MySQL\data
The folder "ProgramData" is hidden by default. In this location, you can find your databases and the tables in the databases.
Take a backup of your whole instance. If you're using MySQL Enterprise then Enterprise Backup can be taken as shown here. If you're using Community Edition then backup can be taken as shown here.
Now let's see how to mess up MySQL Server. Go to the above data directory and open one database folder.
If you delete the .frm and .ibd files of any table then you can't query that table from your MySQL Editor. It says the table doesn't exist.
There is another major messing technique of MySQL. If you open any .frm file in NOTEPAD/WORDPAD, you can find the column names of your table. Though it is not clear, still you can find your schema roughly. If you open .ibd file, you can find the table data in the same rough manner.
If you try to edit any .ibd file then it says "Another process is accessing it" . This process is your MySQL Instance. So, type "services.msc" in Run and stop MySQL Service. Now come back to your .ibd file and edit any word you can read clearly (That word is your table data). Save the .ibd file. Now go back to Services and start MySQL Service. It displays the following dialog box,
This means the whole MySQL instance is corrupted. To get your instance back, do the following actions:
- Uninstall the existing MySQL Instance and delete all its corresponding folders from your system.
- Install an instance again.
Recovering an Enterprise Backup
In MySQL Workbench, go to the option Backup Recovery under MySQL Enterprise section, which directs you to Backup - Recovery Wizard.
If you've taken any backup before in the same directory where you've configured backup, you can select that backup profile and click next.
If you have the backup folder anywhere you can select the option Restore From Folder below. It allows you to select that folder. But this option works only when there exists a folder meta in which a file with name backup_variables.txt. This file contains the starting Log Sequence Number (LSN) and ending LSN which is important in recovering a database. If this file doesn't exist then this option doesn't work. This file is generated by InnoDB automatically while generating backup.
Suppose you have a backup created in the same directory you configured backup, you can get the field backup profile got filled automatically,
Now click next to restore the selected backup profile,
The list of tables in database(s) are shown here. If you check Show System Schemas then it shows the system databases which will be recovered. If there is no display of the system shcema then also they will be recovered. This is because the InnoDB system tablespace holds metadata about InnoDB tables from all databases in an instance. This information regarding the restoring databases is also restored to the instance.
Now click Next. This interface shows the steps to be performed for restoring operation. Click on Restore. It shows a warning that MySQL will be restored to the position when backup was made. This means if there exist the database(s) with the same name as the database(s) getting restored now, they will be replaced with the backed up objects. This may result in the loss of newly made changes to the database.
Click on Restore now which starts the restore operation. You can also see what's going on by clicking on Show Logs button,
One of the steps is Shut Down MySQL Server. This means, the backup is restored after shutting down the server. After shutting down, all the backup is copied to the data directory. This is one of the disadvantages with MySQL. Suppose you have not messed up the whole instance and there comes a situation to restore a particular database. If you have multiple databases in your instance then all the users who are working on other databases lose connection to the instance as it is shut down. It won't be restarted until the restoration of backup is completed. It's better to use this option when you have to restore the whole instance.
Click on Done after the whole operation which brings your instance back.
Recovering through Data Import
I've specified a disadvantage with MySQL while restoring database. To avoid that, restoring through Data Import is useful. Take a backup before by using Data Export. Now use the option Data Import which doesn't affect other databases. This is the better option to use when you have to restore only a single database.
The procedure of Data Import is shown in one of my previous posts,
How to get back an instance when no backup was taken before?
You may have a situation where you don't have any backup taken before but you've corrupted the instance.
There is no solution for this problem other than taking a backup before. After corrupting the instance, if you copy all the database folders to another location and then you copy all of them into the newly installed data directory then those databases are shown in the object explorer but you cannot get any data from them.
The reason for this is, there exists a difference in metadata of the newly created instance's databases InnoDb tables and the older database's InnoDb tables. So this trick will not work.
That's why BACKUP keyword plays a vital role in RBDMS. If you mess up your server without a backup then you have to start your database design from the foundation.
No comments:
Post a Comment