Use Oracle to recover MySQL Data problems

Source: Internet
Author: User

Use Oracle to recover MySQL Data problems

Today, I want to solve a problem. In my daily work, it is still very difficult to restore some nonstandard operations, such as drop, truncate, and delete, the drop operation is basically safe if recycle bin is enabled in Oracle. The delete operation can be performed using the flashback delete operation, which may cause some minor operations such as update and insert, you can use the flashback query to assist in data restoration. If you use a truncate, you will have no choice. In fact, after the truncate operation is complete, generally, the data is still in the data file. At this time, you can use a third-party data recovery tool to try to recover the data. At this time, the data recovery is not within milliseconds, there is no way to tolerate it in minutes or even hours.

However, in Oracle, if you have enabled the flash back function, the truncate data will be available. But in other words, the whole system is stopped by restarting, which may have a greater impact. If you do not use the flashback database, you can use the recovery uard to recover the database at a time point or use other standard methods before the data is deleted.

Therefore, there are many methods to restore Data in Oracle, and you can choose the scenario as needed.

In MySQL, the data recovery options are relatively small. However, the redo log in MySQL is readable, and mysqlbinlog can be easily parsed. However, truncate, drop, and some DML error operations are difficult for MySQL.

Once a problem occurs, you can only use the latest backup to recover the data. You need to back up the data and then parse the binlog based on the latest backup, until the data is restored at the time of data change.

In general, this process still takes a lot of time. The first step is to determine the time point of backup and binlog, which can be completed in other test environments. The time required should not be short.

I thought about the following solution, combining Oracle and mysql, and making full use of the powerful flash back feature of Oracle. This solution may have many highlights for many data recovery scenarios.

It has not been tested locally, because some additional customization and Data Type ing are required, so it is just a rough idea.

First, we need to maintain the original MySQL architecture, one master database and two slave databases. Because the binlog in the master database is the key to data synchronization, you can consider setting a path for SQL parsing, SQL parsing or using binlog, and then make appropriate changes. This process can be an asynchronous process, which is then combined with Oracle and deployed to the schema in Oracle.

The data volume in MySQL is not very large, so you can consider ing multiple MySQL databases with multiple schemas in one Oracle. You can map data types appropriately, for example, big int and small int in MySQL are directly mapped to number in Oracle. Varchar and varchar2 ing.

After the data is in place, you can consider using various flash back features for data recovery. You can use flashback database to recover a truncate or other operations. The drop operation can be restored through recycle bin, flashback database, or based on time points. Delete can be restored through flashback deletion and flashback query. Update can be restored through Flash query. After obtaining the corresponding technical bureau, you can directly export the csv file or insert statement. MySQL uses mysqlimport or insert to deploy data.

In this process, MySQL can always keep moving forward. For example, if a army is marching, then an officer suddenly finds that his map is not taken and has left behind, at this time, you can send a soldier to ride a horse to get a map. At this time, Oracle was the soldier who was able to complete this arduous task, and the troops still traveled without any other impact.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.