Elizabeth
Engineer
Engineer
  • UID625
  • Fans5
  • Follows1
  • Posts68
Reads:45965Replies:6

Manual Recovery of RDS MySQL Database

Created#
More Posted time:Sep 12, 2016 10:08 AM
Sometimes we have to recover the database to a previous given time point (For example, due to SQL misoperation that has been committed). In Alibaba Cloud console, there are two methods to recover RDS: One is overwriting recovery, which can be interpreted as overwriting the current database through full backup, where you can only select a full backup; the other is recovery by creating a temporary database, where you can specify any time point in the past 7 days. Apparently, the latter can relatively minimize any impact. However, if the former is selected due to some unreliable operations or for other reasons, once the overwriting recovery is completed, you cannot create a temporary database at any time point before the overwriting recovery. This article only covers how to recover a database to a point closest to the misoperation time after an overwriting recovery operation.
First, we should download the recent normal full backup, and then all the binlog increments after the backup time point. The last binlog increment will be mixed with both normal operations and misoperations.
First decompress the full backup file via rds_backup_extract according to the document. Then recover the data file via innobackupex. After that, modify backup-my.cnf, as this document is somewhat outdated, and actually you should only reserve the few lines in the middle as shown in the figure below.


After that, download a MySQL with the same or a newer version according to the database version displayed on RDS console. As it is for temporary use, you can direct it to any directory, and delete it after use.
$ mkdir build && cd build
$ cmake .. -DCMAKE_INSTALL_PREFIX=/path/to/tmp/mysql
$ make && make install


Later, add /path/to/tmp/mysql/bin to the PATH environment variable temporarily, and place it at the front.
Then you can start this database.
$ mysqld_safe --defaults-file=/path/to/mysqldata/backup-my.cnf --datadir=/path/to/mysqldata

First connect it to the client to see whether it is normal. If an exception occurs, you should check the error log, and generally the problem can be easily solved.
Then you can execute those binlog files one by one, except, of course, the binlog mixed with both normal operations and misoperations.
$ mysqlbinlog mysql-bin.xxxxxx | mysql -uroot --force

If GTID is opened in MySQL 5.6, there may be a GTID error. There are two solutions:
•  Modify the startup parameters as follows:
$ mysqld_safe --defaults-file=/path/to/mysqldata/backup-my.cnf --datadir=/path/to/mysqldata --log-bin --log-slave-updates --gtid-mode=ON --enforce-gtid-consistency --binlog_format=row

•  Add --skip-gtids parameter to mysqlbinlog.
Finally, for the binlog mixed with both normal operations and misoperations, first convert it into a text file with mysqlbinlog, find the time point of the last normal transaction, cut out the normal portion via head -c, then edit it manually to have it well-formed, and finally re-execute this binlog.
Then you can get a complete database the closest to the time point of misoperation.

jackadision
Intern
Intern
  • UID1181
  • Fans0
  • Follows0
  • Posts1
1st Reply#
Posted time:Nov 12, 2016 18:42 PM
Manual recovery of MySQL database is very effective but very complex, so it is recommended to use a professional  MySql recovery tool. Via this software you can easily deal with any kind of MySQL recovery.

If you want to read more blogs related to MySql then kindly visit: http://mysql.filerepairtool.net/blog

danishtj55
Engineer
Engineer
  • UID12038
  • Fans0
  • Follows1
  • Posts249
2nd Reply#
Posted time:Feb 8, 2022 14:32 PM
Manual Solution of MySQL database recovery is too complex, it has certain limitations and requires technical skills to perform, Instead of Manual Solution you can go with Expert Solutions which allows users to recover the database with an automated tool.
This recovery software can perform SQL Server recovery with utmost accuracy and restore SQL database contents. Also, it supports recovery from NDF file, a secondary database file of SQL Server. All the recovered data can be saved into an MS SQL database file or in the form of SQL Script.

akashsingh55
Engineer
Engineer
  • UID11910
  • Fans1
  • Follows0
  • Posts107
3rd Reply#
Posted time:May 4, 2022 13:07 PM
If you find a manual method, that's great, but keep in mind that you'll require technical capabilities to execute this process. You won't be able to recover your MySQL database file if you make an error.


MySQL Database Tool is a risk-free MySQL recovery tool that allows users to repair faulty MySQL databases and restore any databases that have been unreachable. MySQL Database Recovery Tool is sophisticated software that can troubleshoot any MySQL database fault, and it can rapidly and accurately repair and restore faulty MySQL database files without effort.

gloriaflores
Engineer
Engineer
  • UID12092
  • Fans0
  • Follows0
  • Posts109
4Floor#
Posted time:May 10, 2022 15:44 PM
DRS MySQL Database Repair Tool is a relaible software that enables users to fix broken MySQL data sets and recover any data sets that have been inaccessible. It is a automated application that can investigate any MySQL data set issue, and can quickly precisely fix the problem. Any novice user can simply use this utility without any hindrance . It is designed with high quality algorithms to provide safety to users' data. To know more downlaod its free demo version and repair your corruot MySQL database files effortlessly.

priyankaarya
Assistant Engineer
Assistant Engineer
  • UID11194
  • Fans0
  • Follows0
  • Posts66
5Floor#
Posted time:May 12, 2022 16:34 PM
SysInfo MySQL Database Recovery Tool is one of the most reliable method to restore your database file within seconds. The tool will allow you to recover triggers, keys, views, tables, etc. It is a simple or effective application. Many advanced options are included with this tool. Trial version allows you to take the preview of recovered database files

Panchi
Assistant Engineer
Assistant Engineer
  • UID12625
  • Fans0
  • Follows0
  • Posts70
6Floor#
Posted time:Jul 13, 2022 17:57 PM
In my opinion, you must use a third-party utility to Recover Corrupt MySQL Database files. In this concern, I advise using the MySQL Database Recovery tool. It is best in terms of performance and interface. With the help of this tool, you can rapidly and safely recover MySQL database and restore database objects like triggers, table properties,  tables, views, etc. Moreover, It runs smoothly on all Windows Operating Systems.
Guest