Elizabeth
Engineer
Engineer
  • UID625
  • Fans2
  • Follows1
  • Posts68
Reads:1831Replies:1

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
Guest