Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use Oracle to assist in the recovery of MySQL data problems. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Today, thinking about a problem, if you encounter some non-standard operations in your usual work, drop,truncate,delete, it is still very difficult to recover. If drop operations are basically safe in Oracle, delete operations can be operated with the help of flashback delete operations. Some more subtle operations such as update,insert may cause problems. When you need to do data repair, you can use flashback query to assist you. If you have a truncate, there is nothing you can do. In fact, after the completion of the truncate operation, generally speaking, the data is still in the data file. At this time, you can try to recover with the help of third-party data recovery tools. At this time, data recovery is not millisecond, and there is no way to tolerate it in minutes or even hours.

However, in Oracle, if you previously turned on the flashback database feature, then the truncate data can be recovered. But in other words, the entire system has been stopped by rebooting, and the impact may be even greater. If you do not use flashback database, it is also a way to do point-in-time recovery directly through dataguard or other standard recovery before the data is deleted.

So there are many ways to recover data in Oracle, and the use scenario can be selected according to your needs.

There are relatively few options for data recovery in MySQL. However, one bright spot is that the redo log in MySQL is readable, and mysqlbinlog can easily parse the contents. However, truncate,drop, some DML misoperation scenarios, is more difficult for MySQL.

Once there is a problem, the recovery of the data can only rely on the recent backup, need the corresponding backup, and then parse the relevant binlog on the basis of the recent backup until the data is changed and restored at the point in time.

Generally speaking, this process takes a lot of time, the first is to determine the time point of backup and binlog, which can be completed in other test environments, and it should take a long time.

I thought of the following scheme, which combines Oracle with mysql and takes full advantage of Oracle's powerful flashback function, which may have many bright spots for many data recoveries.

It hasn't been tested locally yet, because it also requires some additional customization and data type mapping, so it's just a general idea.

First of all, we should maintain the original architecture of MySQL, one main library and two standby libraries. Because binlog in the main library 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 and then deployed in combination with Oracle to schema in Oracle.

The amount of data in MySQL is relatively small, so you can consider mapping multiple MySQL database to multiple schema in an Oracle, and data types can do some type mapping appropriately, for example, big int,small int in MySQL and number in Oracle. Varchar and varchar2 mapping and so on.

Once the data is in place, you can consider recovering the data through a variety of flashback features. Operations such as truncate can be recovered using flashback database, and drop operations can be restored through recycle bin,flashback database or based on a point-in-time, etc. Delete can be recovered through flashback deletions, flashback queries, etc. Update can be recovered through flashback queries, and so on. After getting the corresponding technical bureau, you can directly export the csv file, or insert statement. Data deployment is accomplished through mysqlimport or insert in MySQL.

In this process, you can keep the MySQL end moving forward all the time, for example, an army is marching, and suddenly an officer finds that he doesn't have his map with him and falls halfway down the road. At this time, a soldier can be sent to get the map on horseback. At this time, Oracle is the soldier, able to complete this arduous task, the troops are still marching, there will be no other impact.

This is the end of the article on "how to assist the recovery of MySQL data problems through Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.