Database recovery tools in MYSQL need to consider the active functionality of transactional database attributes, storage engine, table optimization, error code recognition, recovery options and data import features. Before you can even think of recovery methods, your primary focus needs to be on backup procedures and methods of executing them flawlessly. The first option you have for backup is the MySQL-dump. It is also recommended by many of the RDBMS experts who have worked extensively with MySQL. The utility gives you multiple options for data backup onto locally mounted tape drives, USB storage devices and remote backup onto another computer in the network.
Backup formats supported by MySQL are delimited text and SQL format. You need to go to the shell command prompt and type in the following command “mysqldump –all-databases > backupfilename.sql”. If you wish to specify only particular databases you can use the command “mysqldump — databases database_name1, database_name2 > backupfilename.sql”. When this command is executed, the backup will be created on the same drive. If you wish to specify a particular path to the external storage device or a network storage path, you have to mention that complete path along with the backupfilename.sql file name.
Physical backup: – Database backup in the physical form can help you in recovering the entire structure of database systems along with all the associated objects when the disk drive in the server has crashed. This is performed by just dumping back all the backup files onto the recovery volume at the time of restoration.
Logical backup: – This format of backup is preferred when the MySQL software system is working fine and only the databases have become corrupt. While performing this type of backup the system may convert the backup files based on the type of backup you have selected. Most of the logical backup commands are replica of data definition language and data manipulation language of the MySQL environment.
Online backup: – In this method you backup the MySQL databases when the database engine is still running at the server. This may interrupt some of the operations being performed in the database or the commands being issued by the users. It is a better practice to avoid the modifications of existing records or the structure of tables when the backup process is in action. For this purpose you need to lock the tables / records that are being backed up. Now the question for you would be to choose between read/write and write locks. If you chose to apply the write lock, the users will not be able to INSERT, EDIT or perform any other kind of data manipulation activities on tables and other objects in the database. But they will be able to read records with the help of SELECT commands.
On the other hand if you apply the read/write lock, the users will not be able to read only thing from the tables and objects, make any changes or INSERT new records into the table. If you let the MySQL system perform the auto locking, it will do it more efficiently.
Offline backup: – This is the relatively safer type of backup which you perform by shutting down the MySQL database engine. No users will be able to get connected to the database during backup. It is safer because you don’t have the fear of affecting the integrity of databases and tables during backup.
Remote backup: – When you opt for remote backup, you will be using a network mapped drive for this purpose. The system allows you to perform both physical as well as logical backup onto remote drives. In case of physical backup the available option is only local. But this problem can be overcome by mapping the remote server /client disk volume as network drive. You need to specify that [particular path when you are taking a physical backup.
The recovery process of databases can be done when the individual or collective databases /tables have become corrupt, MySQL objects are deleted or records have been inaccessible. For all these purposes you will be able to use the logical backup files. When the entire database is crashed you need the recovery to be performed from physical backup files.
Just like in SQL server and Oracle, MySQL offers you two main types of recovery that are point-in-time-recovery and Crash-recovery. In the former type, only those changes will be recovered which were initiated after a specific date and time. This can also be used for performing a complete restore by changing the date and time parameters or by choosing the FULL recovery option from the menu.
There is another way in which point-in-time recovery can be performed. This is done by specifying the starting and end positions within the backup log. But this option can be specified by only a person who is well versed in the MySQL database and backup formats. This option is also said to be more accurate when compared to the date & time format.
Crash recovery is another option offered by MySQL. In this case you need to check for parameters like MYISAM-table and MYISAM-check utilities. Once these options are checked and appropriate values are specified, it is possible to recover the complete set of database objects and restore them onto the crashed databases.
In this method, the backup and recovery happen between the primary hard disk drive in which the databases have been stored and the secondary slave into which the backup files are taken. This type of backup allows you to take large chunks of data in and out of the secondary disk drive. Care should be taken to keep the secondary disk free of any other type of data. This is due to the reason that such data may cause shortage of space in the disk to accommodate large volumes of data which is being backed up. The execution of SQL threads related to backup and restore will work efficiently if you choose a secondary slave drive that has full free space.