MySQL databases are used by many Content Management Systems (“CMS”, like WordPress, for example) to support websites. These databases are often accessed directly from a web interface, and they may be more vulnerable than other systems.
That is why MySQL protection needs to have more attention, not merely a bit of attention. What should you do if you experience CMS downtime due to the inaccessibility of the database? It affects your site and has caused problems with the online store and much more.
If the problem is really with the database, then you need to look for data recovery options. I want to warn you right away that working with databases has never been a simple solution; this may require in-depth knowledge of databases and the SQL language.
Nevertheless, there’s a profession that takes care of this issue—a database administrator—which means there are specially trained people to make sure that the database will not be “damaged.” Have you not heard much about the administrators of Word, Excel, and other things?
Well, it goes without saying when working with databases, they are needed. What to do if such people are not in your company, and they are necessary right now?
Application developers using MySQL databases have tried to provide some recovery options. There are many articles about administrating and recovering databases, but most of the ways can be very complicated.
Therefore, if in the end you couldn’t figure it out or these methods were useless, you can try the unique universal tool – Recovery Toolbox for MySQL https://mysql.recoverytoolbox.com/, which is for simple and efficient recovery of MySQL databases using InnoDB and MyISAM engines.
Of course, recovery makes sense if there is no fresh backup for the database. It can be a dump, a “cold copy” of a file, a disk image, or something else. If you can restore from a backup, you won’t need a recovery system.
MySQL built-in recovery
So, what should you do if the MySQL database has stopped running:
- Open the cnf file and set the following parameter: innodb_force_recovery = 1;
- Restart MySQL with the following command: /etc/init.d/mysql restart;
- Made a dump of the database and pack it: mysqldump db | gzip> db.sql.gz;
- Create a new database: mysql -e create database “new_DB”;
- Import the data there: zcat database.sql.gz | mysql new_DB.
Restarting MySQL this way does not start all related processes, and in some cases, it even allows you to open an old database. Using these commands, we try to dump the database, then import it into a new database, which you open in the usual way. Once you are sure that everything works, you can delete the old database.
By the way, if the method above does not help, and the old database does not start, you need to try other values of the innodb_force_recovery parameter, up to 6.
During normal database startup, the system attempts to start all processes, including those that were, for example, terminated abnormally due to problems with power shutdowns. Thanks to the innodb_force_recovery option, you can disable some parameters that interfere with the database’s regular launch. The innodb_force_recovery numbers mean the following:
- Starting MySQL does not stop, even if the system detects corrupted pages during startup
- Cancel the launch of background operations
- Cancel attempts to roll back transactions
- Refusal of calculating statistics and using of saved changes
- Does not take into account rollback logs at startup
- Does not take ib_logfiles parameters into account during startup
Alternative Ways of Database Recovery
The method described above is quite useful, but unfortunately, it can be difficult for some users. But, working with any database format is always tricky. If you don’t want to edit configurations, open the command line, and learn the necessary commands’ syntax, you need a relatively simple and user-friendly tool. We recommend our Recovery Toolbox for MySQL for this.
Perhaps this is the easiest and most effective way to recover the MySQL database which is precisely what most novice users need.
There are a few options:
- Deal with everything yourself, spending several days doing so.
- Restore the database within an hour.
- Continue to work and avoid losses. Using Recovery Toolbox for MySQL is very simple; you don’t even need to read instructions—unlike detailed descriptions of how to edit configurations and the like—all actions are performed intuitively.
The program works with a copy of the database only, so it will never mess up what remains of the old (original) database.
Using this tool is much different from delving into the configs and settings on the “production” server and worrying about making a mistake that could lead to the destruction of the already damaged database.
How to Recover a Database Using Recovery Toolbox for MySQL
To restore a damaged MySQL database using this program, you need to do the following:
- Download Recovery Toolbox for MySQL from the page https://mysql.recoverytoolbox.com/
- Install and run the program
- Select the folder that stores the damaged MySQL database files
- Select a damaged database from the list
- Run the analysis of the selected database
- View recovery results: tables, objects, indexes
- Set up a method for saving recovered data
- Save data—available in the full version
The program is downloaded entirely free and installed without registration. Using Recovery Toolbox for MySQL, you can view the recovered data for free and evaluate the performed work.
You can even spy on some changes and easily make them manually in a “spoilt” but still functioning backup. If you make any edits, the changes will be integrated.
You can test the tool and see potential changes and outcomes, but only the paid version can save the recovered data, recreate the MySQL database, and connect it to the system.
Security when Working with Recovery Toolbox for MySQL
The program can be installed on any computer with Windows. You can do this installation within a couple of minutes. After that you can start the recovery, which, by the way, can take quite a long time, depending on the size of the original database.
You can control the absence of suspicious connections in various ways, the simplest of which is the physical disconnection of the network cable. And most importantly, it does not affect the effectiveness of work in any way because Recovery Toolbox for MySQL does not use third-party connections.
There is a slightly more complicated method: install a network traffic analyzer—I advise WireShark—and check if suspicious packages from Recovery Toolbox for MySQL are coming. Of course, this method involves a particular healthy enthusiasm, a desire to learn new things—the manuals on WireShark are quite big—and, most importantly, the availability of free time. Therefore, I recommend Recovery Toolbox for MySQL just to save time and recover the database to the system as quickly as possible.
The most important thing is that the program always works only with a copy of the original database, so this recovery method is much more reliable than making changes to the database configurations and waiting to see what will happen. Remember, an attempt to repair a database on a production server can finish it; this occurs even with experienced admins.
Filed in . Read more about Wordpress.