Kutora data kubva kuXtraDB matafura pasina faira rechimiro uchishandisa byte-by-byte kuongororwa kweiyo ibd faira.

prehistory

Zvakazoitika kuti sevha yakarwiswa nehutachiona hweransomware, iyo, "nemhanza yemhanza," yakasiya zvishoma mafaira .ibd (raw data mafaira ematafura e-innodb) asina kubatwa, asi panguva imwechete yakavharira zvachose mafaira e.fpm ( chimiro mafaira). Pakadai, .idb inogona kukamurwa kuita:

• pasi pekudzoreredzwa kuburikidza neyakajairwa maturusi uye madhairekitori. Muzviitiko zvakadaro, pane yakanaka kuva;
• matafura akavharidzirwa zvishoma. Kunyanya aya matafura makuru, ayo (sezvandinonzwisisa) vapambi vaive vasina RAM yakakwana yekuvharirwa kwakazara;
• Zvakanaka, matafura akavharidzirwa zvizere asingagone kudzoserwa.

Zvaigoneka kuona kuti ndeipi sarudzo yematafura nekungovhura mune chero chinyorwa chinyorwa pasi pechinodiwa encoding (munyaya yangu iri UTF8) uye nekungoona faira rekuvepo kwemavara mameseji, semuenzaniso:

Zvakare, pakutanga kwefaira iwe unogona kuona nhamba huru ye0 bytes, uye mavhairasi anoshandisa block encryption algorithm (yakanyanya kuwanda) anowanzo akanganisa ivo zvakare.

Mune yangu, varwisi vakasiya 4-byte tambo (1, 0, 0, 0) pakupera kwefaira rega rega rakavharidzirwa, iro rakarerutsa basa. Kutsvaga mafaera asina hutachiona, script yacho yaive yakakwana:

def opened(path):
    files = os.listdir(path)
    for f in files:
        if os.path.isfile(path + f):
            yield path + f

for full_path in opened("C:somepath"):
    file = open(full_path, "rb")
    last_string = ""
    for line in file:
        last_string = line
        file.close()
    if (last_string[len(last_string) -4:len(last_string)]) != (1, 0, 0, 0):
        print(full_path)

Saka, zvakazoitika kuwana mafaera emhando yekutanga. Chechipiri chinosanganisira basa rakawanda remaoko, asi zvakawanikwa zvakanga zvatokwana. Zvese zvingave zvakanaka, asi iwe unofanirwa kuziva chimiro chakanyatsojeka uye (zvechokwadi) nyaya yakamuka yekuti ndaifanira kushanda netafura yaigara ichichinja. Hapana akarangarira kuti rudzi rwemunda rwakachinjwa here kana kuti koramu itsva yakawedzerwa.

Wilds City, zvinosuwisa, haina kukwanisa kubatsira panyaya yakadai, ndosaka chinyorwa ichi chiri kunyorwa.

Svika papfungwa

Pane chimiro chetafura kubva kumwedzi mitatu yapfuura isingaenderane neyazvino (pamwe munda, uye pamwe zvimwe). Tafura chimiro:

CREATE TABLE `table_1` (
    `id` INT (11),
    `date` DATETIME ,
    `description` TEXT ,
    `id_point` INT (11),
    `id_user` INT (11),
    `date_start` DATETIME ,
    `date_finish` DATETIME ,
    `photo` INT (1),
    `id_client` INT (11),
    `status` INT (1),
    `lead__time` TIME ,
    `sendstatus` TINYINT (4)
); 

muchiitiko ichi, unofanira kubvisa:

• id_point int(11);
• id_user int(11);
• date_start DATETIME;
• date_finish DATETIME.

Kuti udzore, kuongorora kwebyte-byte kwe .ibd file inoshandiswa, inoteverwa nekuvashandura kuva fomu inoverengwa. Kubva kuti tiwane zvatinoda, isu tinongoda kuongorora mhando dze data senge int uye datatime, chinyorwa chinotsanangura ivo chete, asi dzimwe nguva isu tichataurawo kune mamwe marudzi e data, anogona kubatsira mune zvimwe zviitiko zvakafanana.

Dambudziko 1: minda ine mhando DATETIME uye TEXT yakanga ine NULL tsika, uye ivo vanongo svetuka mufaira, nekuda kweizvi, zvaisaita kuziva chimiro chekudzorera mune yangu. Mumakoramu matsva, kukosha kwekutanga kwakange kusina, uye chikamu chekutengeserana chinogona kurasika nekuda kwekugadzirisa innodb_flush_log_at_trx_commit = 0, saka imwe nguva yaizofanira kushandiswa kuona chimiro.

Dambudziko 2: zvinofanirwa kuverengerwa kuti mitsara yakadzimwa kuburikidza neDELETE yese ichange iri mubd faira, asi neALTER TABLE chimiro chavo hachizogadziridzwa. Somugumisiro, iyo data data inogona kusiyana kubva pakutanga kwefaira kusvika kumagumo ayo. Kana iwe uchiwanzo shandisa OPTIMIZE TABLE, saka haugone kusangana nedambudziko rakadaro.

Ngwarira, shanduro yeDBMS inokanganisa nzira iyo data inochengetwa, uye muenzaniso uyu unogona kusashanda kune dzimwe shanduro huru. Mune mhaka yangu, iyo windows version ye mariadb 10.1.24 yakashandiswa. Zvakare, kunyangwe mumariadb unoshanda neInnoDB matafura, kutaura zvazviri XtraDB, iyo isingabatanidzi kushanda kweiyo nzira neInnoDB mysql.

Kuongorora kwefaira

Mune python, rudzi rwe data mabayiti() inoratidza Unicode data panzvimbo yenguva dzose seti yenhamba. Kunyangwe iwe uchigona kuona iyo faira iri mufomu iri, kuti zvive nyore iwe unogona kushandura mabyte kuita manhamba fomu nekushandura iyo byte array kuita yakajairwa array (rondedzero(example_byte_array)). Chero zvazvingava, nzira mbiri dzakakodzera kuongororwa.

Mushure mekutarisa kuburikidza akati wandei ibd mafaera, unogona kuwana zvinotevera:

Uyezve, kana iwe ukagovanisa faira neaya mazwi akakosha, iwe unowana zvakanyanya kunyange mabhuraki edata. Tichashandisa infimum sedivisor.

table = table.split("infimum".encode())

Kucherechedzwa kunonakidza: kune matafura ane diki data, pakati peinfimum uye supremum pane chinongedzo kune nhamba yemitsara mubhuroko.

- tafura yekuedza ine 1st mutsara

- tafura yekuedza ine mitsara miviri

Tafura yemitsara yetafura[0] inogona kusvetuka. Mushure mekutarisa mukati mayo, ndakanga ndichiri kukwanisa kuwana data retafura. Zvingangodaro, iyi block inoshandiswa kuchengetedza indexes nemakiyi.
Kutanga netafura[1] nekuishandura kuita nhamba yenhamba, unogona kutoona mamwe mapatani, anoti:

Aya ma int values ​​akachengetwa mutambo. Baiti yekutanga inoratidza kana nhamba iri positive kana negative. Muchiitiko changu, nhamba dzose dzakanaka. Kubva pane asara 3 bytes, unogona kuona nhamba uchishandisa rinotevera basa. Script:

def find_int(val: str):  # example '128, 1, 2, 3'
    val = [int(v) for v in  val.split(", ")]
    result_int = val[1]*256**2 + val[2]*256*1 + val[3]
    return result_int

Somuenzaniso, 128, 0, 0, 1 = 1, kana 128, 0, 75, 108 = 19308.
Iyo tafura yaive nekiyi yekutanga ine auto-increment, uye inogona kuwanikwawo pano

Taenzanisa data kubva pamatafura ekuedzwa, zvakaratidzwa kuti DATETIME chinhu chine 5 bytes uye yakatanga ne153 (inonyanya kuratidza nguva dzepagore). Sezvo iyo DATTIME renji iri '1000-01-01' kusvika '9999-12-31', ndinofunga huwandu hwemabheti hunogona kusiyana, asi mune yangu, iyo data inowira munguva kubva 2016 kusvika 2019, saka isu tichafunga. kuti 5 bytes zvakakwana.

Kuti uone nguva isina masekonzi, mabasa anotevera akanyorwa. Script:

day_ = lambda x: x % 64 // 2  # {x,x,X,x,x }

def hour_(x1, x2):  # {x,x,X1,X2,x}
    if x1 % 2 == 0:
        return x2 // 16
    elif x1 % 2 == 1:
        return x2 // 16 + 16
    else:
        raise ValueError

min_ = lambda x1, x2: (x1 % 16) * 4 + (x2 // 64)  # {x,x,x,X1,X2}

Zvakanga zvisingaite kunyora basa rinoshanda kwegore nemwedzi, saka ndaifanira kubhinya. Script:

ym_list = {'2016, 1': '153, 152, 64', '2016, 2': '153, 152, 128', 
           '2016, 3': '153, 152, 192', '2016, 4': '153, 153, 0',
           '2016, 5': '153, 153, 64', '2016, 6': '153, 153, 128', 
           '2016, 7': '153, 153, 192', '2016, 8': '153, 154, 0', 
           '2016, 9': '153, 154, 64', '2016, 10': '153, 154, 128', 
           '2016, 11': '153, 154, 192', '2016, 12': '153, 155, 0',
           '2017, 1': '153, 155, 128', '2017, 2': '153, 155, 192', 
           '2017, 3': '153, 156, 0', '2017, 4': '153, 156, 64',
           '2017, 5': '153, 156, 128', '2017, 6': '153, 156, 192',
           '2017, 7': '153, 157, 0', '2017, 8': '153, 157, 64',
           '2017, 9': '153, 157, 128', '2017, 10': '153, 157, 192', 
           '2017, 11': '153, 158, 0', '2017, 12': '153, 158, 64', 
           '2018, 1': '153, 158, 192', '2018, 2': '153, 159, 0',
           '2018, 3': '153, 159, 64', '2018, 4': '153, 159, 128', 
           '2018, 5': '153, 159, 192', '2018, 6': '153, 160, 0',
           '2018, 7': '153, 160, 64', '2018, 8': '153, 160, 128',
           '2018, 9': '153, 160, 192', '2018, 10': '153, 161, 0', 
           '2018, 11': '153, 161, 64', '2018, 12': '153, 161, 128',
           '2019, 1': '153, 162, 0', '2019, 2': '153, 162, 64', 
           '2019, 3': '153, 162, 128', '2019, 4': '153, 162, 192', 
           '2019, 5': '153, 163, 0', '2019, 6': '153, 163, 64',
           '2019, 7': '153, 163, 128', '2019, 8': '153, 163, 192',
           '2019, 9': '153, 164, 0', '2019, 10': '153, 164, 64', 
           '2019, 11': '153, 164, 128', '2019, 12': '153, 164, 192',
           '2020, 1': '153, 165, 64', '2020, 2': '153, 165, 128',
           '2020, 3': '153, 165, 192','2020, 4': '153, 166, 0', 
           '2020, 5': '153, 166, 64', '2020, 6': '153, 1, 128',
           '2020, 7': '153, 166, 192', '2020, 8': '153, 167, 0', 
           '2020, 9': '153, 167, 64','2020, 10': '153, 167, 128',
           '2020, 11': '153, 167, 192', '2020, 12': '153, 168, 0'}

def year_month(x1, x2):  # {x,X,X,x,x }

    for key, value in ym_list.items():
        key = [int(k) for k in key.replace("'", "").split(", ")]
        value = [int(v) for v in value.split(", ")]
        if x1 == value[1] and x2 // 64 == value[2] // 64:
            return key
    return 0, 0

Ndine chokwadi chekuti kana ukashandisa n yakawanda yenguva, kusanzwisisa uku kunogona kugadziriswa.
Zvadaro, basa rinodzorera chinhu chemazuva kubva patambo. Script:

def find_data_time(val:str):
    val = [int(v) for v in val.split(", ")]
    day = day_(val[2])
    hour = hour_(val[2], val[3])
    minutes = min_(val[3], val[4])
    year, month = year_month(val[1], val[2])
    return datetime(year, month, day, hour, minutes)

Yakakwenenzverwa kuona inowanzodzokororwa tsika kubva int, int, datetime, datetime , zvinoita sekunge izvi ndizvo zvaunoda. Uyezve, kutevedzana kwakadaro hakudzokororwi kaviri pamutsetse.

Tichishandisa chirevo chenguva dzose, tinowana data inodiwa:

fined = re.findall(r'128, d*, d*, d*, 128, d*, d*, d*, 153, 1[6,5,4,3]d, d*, d*, d*, 153, 1[6,5,4,3]d, d*, d*, d*', int_array)

Ndokumbira utarise kuti kana uchitsvaga uchishandisa chirevo ichi, hazvizogone kuona NULL tsika muminda inodiwa, asi mune yangu izvi hazvina kukosha. Tinobva tapfuura nezvatakawana mune loop. Script:

result = []
for val in fined:
    pre_result = []
    bd_int  = re.findall(r"128, d*, d*, d*", val)
    bd_date= re.findall(r"(153, 1[6,5,4,3]d, d*, d*, d*)", val)
    for it in bd_int:
        pre_result.append(find_int(bd_int[it]))
    for bd in bd_date:
        pre_result.append(find_data_time(bd))
    result.append(pre_result)

Chaizvoizvo, ndizvo zvese, iyo data kubva mumhedzisiro array ndiyo data yatinoda. ###PS.###
Ndinonzwisisa kuti iyi nzira haina kukodzera kumunhu wese, asi chinangwa chikuru chechinyorwa ndechekukurumidza kuita pane kugadzirisa matambudziko ako ese. Ini ndinofunga mhinduro chaiyo ingave yekutanga kudzidza iyo kodhi kodhi iwe pachako mariadb, asi nemhaka yenguva yakaganhurirwa, nzira yazvino uno yakaratidzika kuva inokurumidza zvikurusa.

Mune zvimwe zviitiko, mushure mekuongorora iyo faira, iwe unozokwanisa kuona iyo inenge yakarongeka uye kuidzorera uchishandisa imwe yemaitiro akajairwa kubva pazvisungo zviri pamusoro. Izvi zvichave zvakanyanya kururamisa uye kukonzera mashoma matambudziko.

Source: www.habr.com