Kaip iš SQL išklotinės eksportuoti tik schemą (struktūrą)

Nuolat pasikartoja trys situacijos:

• Paruošti schemą testinei ar CI aplinkai — norite tuščios lentelių struktūros, kad testai galėtų vykti su realiais stulpeliais, indeksais ir apribojimais, bet visiškai nenorite į laikiną aplinką kelti gigabaitų produkcinių eilučių.
• Tik pradiniai duomenys — schema tikslinėje duomenų bazėje jau egzistuoja, ir jums tereikia INSERT eilučių jai užpildyti, be CREATE sakinių, kurie susidurtų su tuo, kas jau yra.
• Atsikratyti triukšmo — išklotinę išpučia logs, sessions, audit_trail ar cache lentelės su dešimtimis milijonų eilučių, kurių niekada nenorite importuoti.

Kodėl mysqldump --no-data neužtenka

Klasikinis patarimas — mysqldump --no-data (tik struktūra) arba mysqldump --no-create-info (tik duomenys). Abu yra eksporto parametrai: jie keičia tai, ką turės nauja išklotinė iš veikiančios duomenų bazės. Jie nieko nepadeda išklotinei, kurią jau turite gulinčią diske kaip .sql failą.

Praktikoje failas dažnai ateina iš kito žmogaus, iš atsarginės kopijos užduoties arba iš serverio, prie kurio nebeturite tiesioginės prieigos. Negalite iš naujo paleisti mysqldump prieš duomenų bazę, kuri nepasiekiama — prisijungimo duomenys dingę, serveris nurašytas arba failas tiesiog yra viskas, ką jums perdavė. Tuomet parametrai bevertis, ir jums lieka redaguoti patį failą.

Rankinis būdas

Akivaizdus ėjimas — pašalinti eilutes su grep ar sed. Norėdami palikti struktūrą ir atmesti duomenis:

# palikti viską, išskyrus INSERT eilutes
grep -v '^INSERT INTO' dump.sql > schema.sql

# arba palikti tik INSERT eilutes tik duomenų failui
grep '^INSERT INTO' dump.sql > data.sql

Mažoje, tvarkingoje išklotinėje tai atrodo tvarkingai — o tada lūžta ties tikra:

• Kelių eilučių INSERT. Vienas INSERT INTO … VALUES gali užimti tūkstančius eilučių. grep atitinka pirmąją eilutę ir palieka likusias reikšmes „našlaitėmis“, sukurdamas sugadintą failą.
• Išplėsti įterpimai. mysqldump sugrupuoja daug eilučių į vieną milžinišką sakinį. Į eilutes orientuoti filtrai negali pasakyti, kur toks sakinys baigiasi.
• Komentarai ir blokai. Sąlyginiai /*! … */ blokai, LOCK TABLES ir -- komentarai persipina su duomenimis, todėl naivus filtras arba palieka šiukšles, arba iškerpa tai, ko importui reikia.
• Tik duomenys – dar blogiau. Grynas grep '^INSERT' išmeta SET ir LOCK sakinius, nuo kurių priklauso eilutės, o bet kuris INSERT, kuris neprasideda tiksliai nuliniame stulpelyje, tyliai dingsta.

Galiausiai užuot gavę savo schemą, derinsite savo paties filtrą.

Tik struktūra arba tik duomenys su DumpCleaner

DumpCleaner supranta SQL sakinius, o ne atitikinėja eilutes, todėl patikimai atskiria struktūrą nuo duomenų — bet kokio dydžio failuose:

1. Nutempkite savo .sql išklotinę į DumpCleaner (MySQL, MariaDB ar PostgreSQL). Ji analizuoja failą srautiniu būdu, todėl kelių gigabaitų išklotinė įkeliama su pastoviu atminties naudojimu.
2. Perjunkite norimus sakinių tipus: CREATE TABLE, CREATE INDEX, INSERT INTO, ALTER, DROP, LOCK, SET. Atžymėkite INSERT INTO ir gausite tik struktūrą; atžymėkite CREATE TABLE / CREATE INDEX ir gausite tik duomenis.
3. Arba pasirinkite šabloną — Tik struktūra arba Tik duomenys — kad vienu žingsniu nustatytumėte visus perjungiklius, tada, jei norite, patikslinkite pagal lentelę.
4. Eksportuokite. Gaunate lieknesnį schemos failą testinei aplinkai ar CI arba švarų tik duomenų dump pradinių duomenų įkėlimui.

Kadangi filtras supranta sakinius, kelių eilučių išplėstas INSERT paliekamas arba atmetamas kaip visuma — niekada neperkerpamas per vidurį.

Tikroji galia ta, kad filtravimas veikia pagal sakinio tipą ir pagal lentelę, ir šie du deriniai jungiasi. Galite palikti kiekvienos lentelės struktūrą, bet įtraukti INSERT duomenis tik toms trims lentelėms, kurių jums iš tikrųjų reikia — palikdami 89 milijonų eilučių logs lentelę kaip tuščią kiautą. To negali išreikšti joks --no-data parametras.

Dažnai užduodami klausimai