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

This article is to share with you about how mysql mistakenly modified the records of the whole table. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

# add data

Insert into testdb1.student (id,name,class,score) value (arecronomy) value (arecronomy), (2) (2) (2) (3) (3) (3) (3) (3) (3) (4) (4) (4) (4) (4) (4) (4), (4) (4), (4) (4), (4) (4), (4) (4) (4), (4) (4) (4), (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (

Mysql > select * from testdb1.student

+-+

| | id | name | class | score | |

+-+

| | 1 | a | 1 | 45 |

| | 2 | b | 1 | 46 | |

| | 3 | c | 2 | 89 | |

| | 4 | d | 2 | 90 | |

| | 5 | e | 3 | 67 | |

| | 6 | f | 3 | 87 | |

| | 7 | g | 4 | 77 | |

| | 8 | h | 4 | 91 | |

+-+

8 rows in set (0.00 sec)

# modify data

Update student set score=100

Commit

Mysql > select * from testdb1.student

+-+

| | id | name | class | score | |

+-+

| | 1 | a | 1 | 100 | |

| | 2 | b | 1 | 100 | |

| | 3 | c | 2 | 100 | |

| | 4 | d | 2 | 100 | |

| | 5 | e | 3 | 100 | |

| | 6 | f | 3 | 100 | |

| | 7 | g | 4 | 100 | |

| | 8 | h | 4 | 100 | |

+-+

8 rows in set (0.00 sec)

Mysql > set global read_only=1

Mysql > show master status\ G

* * 1. Row *

File: ray-bin.000004

Position: 1992

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

# at 2192 is found in the binlog log

[root@localhost ~] # mysqlbinlog / data/3306/logs/ray-bin.000004-v-S / data/3306/soket/mysql.sock-- base64-output=decode-rows | grep-A 15 student | sed-n'/ # at 2192 p' | sed-n's\ # #\\ p' | sed "s /\ *. *\ / g" | sed's / `/ g' > / tmp/1.txt

[root@localhost ~] # cat / tmp/1.txt

UPDATE testdb1.student

WHERE

@ 1x 1

@ 2 million dollars'

@ 3 days 1'

@ 445

SET

@ 1x 1

@ 2 million dollars'

@ 3 days 1'

@ 4000100

UPDATE testdb1.student

WHERE

@ 1x 2

@ 2 million dollars b'

@ 3 days 1'

@ 400046

SET

@ 1x 2

@ 2 million dollars b'

@ 3 days 1'

@ 4000100

UPDATE testdb1.student

WHERE

@ 1x 3

@ 2cm / c'

@ 3 years 2'

@ 420 89

SET

@ 1x 3

@ 2cm / c'

@ 3 years 2'

@ 4000100

UPDATE testdb1.student

WHERE

@ 1x 4

@ 2 million dollars.

@ 3 years 2'

@ 4: 90

SET

@ 1x 4

@ 2 million dollars.

@ 3 years 2'

@ 4000100

UPDATE testdb1.student

WHERE

@ 1x 5

@ 2roomroome'

@ 3 years 3'

@ 4x67

SET

@ 1x 5

@ 2roomroome'

@ 3 years 3'

@ 4000100

UPDATE testdb1.student

WHERE

@ 1x 6

@ 2percent f'

@ 3 years 3'

@ 4x87

SET

@ 1x 6

@ 2percent f'

@ 3 years 3'

@ 4000100

UPDATE testdb1.student

WHERE

@ 1x 7

@ 2 million dollars g'

@ 3 million 4'

@ 4x77

SET

@ 1x 7

@ 2 million dollars g'

@ 3 million 4'

@ 4000100

UPDATE testdb1.student

WHERE

@ 1x 8

@ 2 hours h'

@ 3 million 4'

@ 4x91

SET

@ 1x 8

@ 2 hours h'

@ 3 million 4'

@ 4000100

[root@localhost ~] # cat column.txt

Id

Name

Class

Score

[root@localhost ~] # cat getSQL.sh

#! / bin/bash

# by ray

Iswhere=1 # determines the position of the line of the loop. 1 means after where, 0 means no more where.

ColNum=0 # calculates the number of columns. Generally, the first column in the binlog log is @ 1, the second column is @ 2, and so on.

WhereNum=0 # judges the number of occurrences of the fields behind where to facilitate the splicing of strings. The first occurrence is not applicable to metropolis, and comma concatenation is used after the second occurrence.

SetNum=0 # judges the number of occurrences of the fields behind set to facilitate the splicing of strings. The first occurrence is not applicable to metropolis, and comma concatenation is used after the second occurrence.

ReplaceColumn () {# replaces the column beginning with @ with the column in the column profile, and the security profile is executed sequentially

Cat $1 | while read line

Do

ColNum=$ [${colNum} + 1]

Sed-I "sram replacement ${colNum} / ${line} / g". / execSQL.sql # replacement column

Done

}

GetSQL () {# get sql

Sql1=''

Sql_result=''

Sql_condition=''

While read line # reads processed binlog logs

Do

If [[${line} = ~ 'UPDATE']]; whether then # matches update

If ["${sql1}"! = "]; then

Echo ${sql1}'${sql_result}''${sql_condition}';'>. / execSQL.sql # print sql

Sql1=''

Sql_result=''

Sql_condition=''

WhereNum=0

SetNum=0

Fi

Sql1=$ {line} # splices sql strings to get update

Elif [[${line} = ~ 'WHERE']]; then

Sql_condition=$ {line} # concatenate the string and put the content after the where in the binlog log

Iswhere=1 # determines whether it is where, because the contents after where and set are interchanged.

Elif [[${line} = ~ 'SET']]; then

Sql_result=' SET'${sql_result} # concatenation string

Iswhere=0

Elif [[${iswhere}-eq 1]]; then # 1 is after where. Concatenate the content after binlog log where to set of sql.

If [[${whereNum}-eq 0]]; then # determines whether the string after the where string appears at once

Sql_result=$ {sql_result}'${line}

WhereNum=1 # is set to 1, which means it is not the first time it has appeared.

Elif [[${whereNum}-eq 1]]; then

Sql_result=$ {sql_result}','${line}

Fi

Elif [[${iswhere}-eq 0]]; then # determines whether it is a string after set

If [[${setNum}-eq 0]]; then # determines whether the string after the set string appears at once

Sql_condition=$ {sql_condition}'${line}

SetNum=1 # is set to 1, which means it is not the first time it has appeared.

Elif [[${setNum}-eq 1]]; then

Sql_condition=$ {sql_condition} 'and' ${line}

Fi

Fi

Done

< $1 #把文件用while循环读取每一行 echo ${sql1}' '${sql_result}' '${sql_condition}';' >

>. / execSQL.sql # the last line exits the loop, so print the last line

Echo "commit;" > >. / execSQL.sql

ReplaceColumn $2

}

# entry of script, call function to get content

If [- e $1]; then # determines whether the first parameter is a file

GetSQL $1 $2

Else

Echo $1'is not a fileholders

Fi

[root@localhost ~] # bash getSQL.sh'/ tmp/1.txt' ". / column.txt"

Mysql > select * from testdb1.student

+-+

| | id | name | class | score | |

+-+

| | 1 | a | 1 | 100 | |

| | 2 | b | 1 | 100 | |

| | 3 | c | 2 | 100 | |

| | 4 | d | 2 | 100 | |

| | 5 | e | 3 | 100 | |

| | 6 | f | 3 | 100 | |

| | 7 | g | 4 | 100 | |

| | 8 | h | 4 | 100 | |

+-+

8 rows in set (0.00 sec)

[root@localhost] # mysql-uroot-p123456-S / data/3306/soket/mysql.sock

< /root/execSQL.sql mysql: [Warning] Using a password on the command line interface can be insecure. mysql>

Select * from testdb1.student

+-+

| | id | name | class | score | |

+-+

| | 1 | a | 1 | 45 |

| | 2 | b | 1 | 46 | |

| | 3 | c | 2 | 89 | |

| | 4 | d | 2 | 90 | |

| | 5 | e | 3 | 67 | |

| | 6 | f | 3 | 87 | |

| | 7 | g | 4 | 77 | |

| | 8 | h | 4 | 91 | |

+-+

8 rows in set (0.00 sec)

Thank you for reading! This is the end of the article on "what to do when mysql mistakenly modifies the full table record". 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, you can 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.