Shulou(Shulou.com)06/01 Report--

Last night, a programmer of a luxury e-commerce website shook his hand and deleted a library of Aliyun rds. It was more than 11:00 at that time, and ten thousand alpacas were galloping in his heart. Fortunately, he didn't let me do it, so I restored it. Yesterday morning, the programmer of another website shook his hands and deleted a library that he still needed. This is not all his fault, originally planned to migrate the data, because the database is too large, split, make the large database smaller. If the migration has gone a little bit, it is necessary to delete the relocation, so as to facilitate the later migration to speed up.

Some people may say that this is a management problem, and it is true. The authority management should be strengthened, and the corresponding technical guarantee measures and process norms should be established. There are corresponding safeguards or measures, although misoperation can not be completely eliminated, but at least it will greatly reduce the probability. Why do I often encounter such things and toss back and forth? Mainly in recent years, what I have done is external support, which is mainly at the technical level and does not participate in more affairs. Of course, for most limited companies, improving the process system is also difficult to implement. Therefore, although you encounter a lot of trouble, sum up and write it down, which should be helpful to others.

The first priority for recovery is to find a backup. Fortunately, there is a backup of June 9, 2018. It was always in automatic backup, but later, due to migration, and it is said that during the recent night backup, it took up a lot of resources and the load was very high. When I went to work in the morning, the backup was stopped before the backup compression step was over.

This is after the migration of part of the data, but also delete the capacity after the relocation of the library, it is indeed a bit large. In a situation like this, it is really risky to either split or do an incremental backup and stop the automatic backup.

In order to reduce the risk, it is best to make a full database backup before recovery, and you can go back in case something goes wrong. Based on this idea, start performing backups. After two hours of implementation, there is no movement, and it is impossible to assess how long it will take. Others can't wait. At the same time, backup also affects the normal operation of other businesses, so we have to stop backup directly.

Calculate a gossip before you go to work and test your luck and misfortune. It is good to use the Shenwang phase, and there is hope of repair. The steps to import data are as follows:

Unpack the ◆ backup file and open the file to have a look at the contents

[root@db-209 mysql_bk_dir] # pwd

/ data/databk/mysql_bk_dir

[root@db-209 mysql_bk_dir] # tar zxvf mobile7_quanzhen20180609.tgz

Mobile7_quanzhen20180609.sql

[root@db-209 mysql_bk_dir] # more mobile7_quanzhen20180609.sql

-- MySQL dump 10.13 Distrib 5.5.29, for Linux (x86 / 64)

--

-- Host: localhost Database: mobile7_quanzhen

-

-- Server version 5.5.29-log

/ * 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /

/ * 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /

/ * 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /

/ * 40101 SET NAMES utf8 * /

/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /

/ * 40103 SET TIME_ZONE='+00:00' * /

/ * 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /

/ * 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /

/ * 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /

/ * 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /

--

-- Table structure for table `quanzhen_ aboutegg`

--

DROP TABLE IF EXISTS `quanzhen_ aboutegg`

/ *! 40101 SET @ saved_cs_client = @ @ character_set_client * /

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `quanzhen_ aboutegg` (

`id`int (8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID'

`uid` mediumint (8) unsigned NOT NULL COMMENT 'member ID'

`content`text NOT NULL COMMENT 'content'

`title`varchar (100) NOT NULL COMMENT 'title'

`sort`int (5) unsigned NOT NULL COMMENT 'sort'

`status` tinyint (1) unsigned NOT NULL COMMENT'0 does not display, 1 shows'

`updated`int (8) NOT NULL COMMENT 'modification time'

`created`int (8) NOT NULL COMMENT 'increase time'

PRIMARY KEY (`id`)

(ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT=' about Golden Egg system description form'

/ * 40101 SET character_set_client = @ saved_cs_client * /

... Omit some... .

◆ creates the database and imports the data

Mysql > create database mobile7_quanzhen

Mysql > use mobile7_quanzhen

Mysql > source / data/databk/mysql_bk_dir/mobile7_quanzhen20180609.sql

The above operation is very smooth, basically no obstacles.

Here comes the key part, and you need to determine where to restore it. Where can I get this information? The database stores binary logs in the directory.

[root@db-209 mysql_db] # pwd

/ data/mysql_db

[root@db-209 mysql_db] # ls-al | grep mysql-bin

-rw-rw---- 1 mysql mysql 1073744253 Jun 17 01:20 mysql-bin.000856

-rw-rw---- 1 mysql mysql 106606897 Jun 17 04:02 mysql-bin.000857

-rw-rw---- 1 mysql mysql 1073742155 Jun 18 02:22 mysql-bin.000858

-rw-rw---- 1 mysql mysql 58702046 Jun 18 04:02 mysql-bin.000859

-rw-rw---- 1 mysql mysql 1073745791 Jun 19 03:20 mysql-bin.000860

-rw-rw---- 1 mysql mysql 24180628 Jun 19 04:02 mysql-bin.000861

-rw-rw---- 1 mysql mysql 1073741973 Jun 20 00:31 mysql-bin.000862

-rw-rw---- 1 mysql mysql 172478749 Jun 20 04:02 mysql-bin.000863

-rw-rw---- 1 mysql mysql 1073741972 Jun 20 23:11 mysql-bin.000864

-rw-rw---- 1 mysql mysql 172554351 Jun 21 04:02 mysql-bin.000865

-rw-rw---- 1 mysql mysql 150489 Jun 21 04:02 mysql-bin.000866

-rw-rw---- 1 mysql mysql 747165143 Jun 21 16:11 mysql-bin.000867

-rw-rw---- 1 mysql mysql 228 Jun 21 04:02 mysql-bin.index

As can be seen from the output, there are not many binaries, and the oldest timestamp is June 17, which was lost from the 10th to the 16th. Only keep the log for the last 5 days, which is set by the mysql options file / etc/my.cnf, which contains:

[root@db-209 mysql_db] # more / etc/my.cnf

... . Omit some...

[mysqld]

Port = 3306

Socket = / tmp/mysql.sock

Datadir = / data/mysql_db

# log_bin_trust_function_creators = 1

Skip-external-locking

Skip-name-resolve

Wait_timeout=10

Interactive_timeout=300

Connect_timeout=300

Max_connections = 2000

Max_connect_errors = 50

Tmp_table_size = 128m

Expire_logs_days = 5

Slave-skip-errors = 1690,1062

# Try number of CPU's*2 for thread_concurrency

Thread_concurrency = 8

Slow_query_log = 1

Slow_query_log_file=/data/mysql_db/slow.log

... Omit some... .

Although it is five days' data, there are several documents. Why not set more days of retention? It's because the machine is too old and its resources are limited. Inform you of the risk and state that a few days of data may be lost. We all agree that it is better to recover as much as we can.

Asked the programmer who performed the misoperation, about what time did the drop operation be performed? The answer is a few minutes past 10: 00 a. M. on June 20th. With this reference point, and then looking at the timestamp of the binary log file, you can navigate to the file "mysql-bin.000864" and determine that the reason for this file is that the last time the file "mysql-bin.000863" was written is "4:02 on June 20th", which narrows it down a lot. Of course, you can ignore this and use wildcards to search all binary log files to determine the recovery point. I decided to restore at a point in time, as follows:

◆ determines the point in time for recovery

[root@db-209 mysql_db] # pwd

/ data/mysql_db

[root@db-209 mysql_db] # / usr/local/mysql/bin/mysqlbinlog-v-- base64-output=DECODE-ROWS mysql-bin.000864 | grep-C 10-I drop

INSERT INTO `quanzhen_ everydaycpdcontrol` (todaytime,uid,todaynum) VALUES (1529424000 djcdcontrol` (1529424000pdcontrol`)

/ *! * /

# at 393124773

# 180620 10:08:44 server id 21 end_log_pos 393124800 Xid = 629128212

Com _ MIT _ blank /

# at 393124800

# 180620 10:08:44 server id 21 end_log_pos 393124901 Query thread_id=85127174 exec_time=0 error_code=0

SET timestamp 1529460524

/ *!\ C latin1 * /! * /

SET @ @ session. Session. Session setting client.session. Session. Collationalization connections8 session. Session. Collationalization server33.

Drop database mobile7_quanzhen

/ *! * /

# at 393124901

# 180620 10:08:44 server id 21 end_log_pos 393124989 Query thread_id=85160078 exec_time=0 error_code=0

SET timestamp 1529460524

/ *!\ C utf8 * /! * /

SET @ @ session. Session. Session setting setting clientmakers 33 minutes. Session. Collationalization connections 33 minutes.

BEGIN

/ *! * /

# at 393124989

# 180620 10:08:44 server id 21 end_log_pos 393125017 Intvar

--

INSERT INTO `quanzhen_ everydaycpdcontrol` (todaytime,uid,todaynum) VALUES (1529424000 399677)

/ *! * /

# at 399014378

# 180620 10:13:27 server id 21 end_log_pos 399014405 Xid = 629434799

Com _ MIT _ blank /

... .. Omit some...

The exact time to delete the database is 10:08:44 on June 20, 2018, and the recovery time shall prevail, and it is reasonable to move forward a little.

Here is an episode that starts with no options, simplifies the execution of "mysqlbinlog mysql-bin.000864" with only a few lines of output, and ends with an error message, as shown below:

ERROR: Error in Log_event::read_log_event (): 'Found invalid event in binary log', data_len: 96, event_type: 19

Could not read entry at offset 820:Error in log format or read error

DELIMITER

Try to open several other binary logs, and the results are similar. Add the option to execute "mysqlbinlog-v-- base64-output=DECODE-ROWS mysql-bin.000864 | grep-C 10-I drop", which is not supported.

From this point of view, the chance of recovery is not too big, scared to drink kimchi water pressure shock. Use a certain degree of search, know that it is caused by inconsistent versions, find, there are indeed two mysql installations. This mysql was personally deployed by me a few years ago and is usually customized and installed in the / usr/local/mysql directory with separate data files. But I don't know that the programmer behind, accustomed to yum, installed a lower version. After I carry out the full path, there will be no problem, but I don't want to tell them for the time being, so as not to be friendly with big businessmen.

◆ data recovery

Still use the mysqlbinlog instruction and add the pipe "|" to output to mysql. The specific operation is as follows:

[root@db-209 ~] # / usr/local/mysql/bin/mysqlbinlog-- database=mobile7_quanzhen-- stop-datetime='2018-06-20 10 database=mobile7_quanzhen 08VR 40' mysql-bin.000* | mysql mobile7_quanzhen-p

At the beginning of the operation, mysqlbinlog does not have the option "--database=mobile7_quanzhen". After execution, the primary key conflicts with the error message. After that, the option "- f" is forced to be added on the mysql side of the right side of the pipe, although it can continue to report error messages of primary key conflicts.

Later, I thought, did you specify the name of the database on the side of the pipeline? There's no risk anyway. Hey, after adding, enter the mysql password, but also really often imported. My guess is that without specifying the database option, mysqlbinlog will try to recover all the libraries, while the other libraries will not lose data, resulting in primary key conflicts.

There is an interesting problem. The old library was imported before recovery, and the associated directories and files have been generated, but when performing mysqlbinlog recovery, the files and subdirectories in the corresponding directory of the library will disappear for a while and will come back later. As the recovery continues, you can see that the timestamps of the files in the data directory are changing, and the timestamps of the data that do not need to be recovered are fixed.

The recovered data is obviously different from the original time when the old database imported the data. After the recovery, ask the programmer to check the number of tables and whether the number of records in the table is as alive as it was before deletion. It has been confirmed that the recovery is successful and everyone does not have to run away.

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.