Kugadziridza MySQL (Percona Server) kubva 5.7 kusvika 8.0

Kufambira mberi hakumire, saka zvikonzero zvekusimudzira kune yazvino vhezheni yeMySQL zviri kuwedzera kumanikidza. Munguva pfupi yapfuura, mune imwe yemapurojekiti edu, yaive nguva yekuvandudza inotonhorera Percona Server 5.7 masumbu kushanduro 8. Zvese izvi zvakaitika paUbuntu Linux 16.04 papuratifomu. Maitiro ekuita oparesheni yakadai nediki downtime uye ndeapi matambudziko atakasangana nawo panguva yekuvandudza - verenga mune ino chinyorwa.

Kugadzirira kwe

Chero gadziriso yesevha yedatabase inogona kunge yakabatana nekugadzirisazve dhatabhesi: shanduko mune zvinodiwa pamiganho pane zviwanikwa zvehurongwa uye kugadziridzwa kwe database configs inoda kubvisirwa mirairo yechinyakare.

Tisati tagadziridza, isu tichanyatso tarisa kune zviri pamutemo zvinyorwa:

• MySQL 8 kuburitsa manotsi;
• MySQL kusimudzira gwara;
• Percona update guide;
• MySQL gwara rekuvandudza replicas uye masters.

Uye ngatirongei chirongwa chekuita:

1. Ruramisa mafaera ekugadzirisa nekubvisa mirairo yechinyakare.
2. Tarisa kuenderana nezvinoshandiswa.
3. Gadziridza dhatabhesi revaranda nekuisa iyo package percona-server-server.
4. Gadziridza tenzi nepasuru imwechete.

Ngatitarisei pane imwe neimwe yechirongwa uye tione kuti chii chinogona kukanganisa.

CHINOKOSHA! Maitiro ekugadzirisa a MySQL cluster yakavakirwa paGalera ine yayo yakavanzika isina kutsanangurwa muchinyorwa. Iwe haufaniri kushandisa murairo uyu munyaya iyi.

Chikamu 1: Kutarisa configs

MySQL yakabviswa muvhezheni 8 query_cache. Chaizvoizvo aiva yakanzi haichashandi kumashure mushanduro 5.7, asi ikozvino zvadzimwa zvachose. Saizvozvo, zvinodikanwa kubvisa mirairo yakabatana. Uye kune cache zvikumbiro iwe unogona ikozvino kushandisa ekunze maturusi - semuenzaniso, ProxySQL.

Zvakare mune iyo config maive nemirairo yechinyakare nezve innodb_file_format. Kana muMySQL 5.7 zvaikwanisika kusarudza iyo InnoDB fomati, ipapo iyo yechisere vhezheni yatoshanda chete neBarracuda format.

Mhedzisiro yedu kubviswa kwemirairo inotevera:

• query_cache_type, query_cache_limit и query_cache_size;
• innodb_file_format и innodb_file_format_max.

Kuti titarise, isu tichashandisa iyo Docker mufananidzo wePercona Server. Isu tichaisa server config mudhairekitori mysql_config_test, uye padivi payo tichagadzira madhairekitori edata uye matanda. Percona-server kumisikidza bvunzo muenzaniso:

mkdir -p {mysql_config_test,mysql_data,mysql_logs}
cp -r /etc/mysql/conf.d/* mysql_config_test/
docker run  --name some-percona -v $(pwd)/mysql_config_test:/etc/my.cnf.d/  -v $(pwd)/mysql_data/:/var/lib/mysql/ -v $(pwd)/mysql_logs/:/var/log/mysql/ -e MYSQL_ROOT_PASSWORD=${MYSQL_PASSWORD} -d percona:8-centos

Pazasi mutsara: ingave muDocker matanda kana mudhairekitori ine matanda - zvichienderana nemagadzirirwo ako - faira richaonekwa umo zvinonetsa zvinongedzo zvinotsanangurwa.

Hezvino zvataiva nazvo:

2020-04-03T12:44:19.670831Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2020-04-03T12:44:19.671678Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2020-04-03T12:44:19.671682Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.

Nekudaro, isu takanga tichiri kuda kuona ma encodings uye kutsiva dhiraivha yechinyakare expire-logs-days.

Chikamu 2: Kutarisa kushanda kwekuisa

Zvinyorwa zvekuvandudza zvine 2 zvishandiso zvekutarisa dhatabhesi kuti inoenderana. Kushandisa kwavo kunobatsira maneja kuti atarise kuenderana kweiyo iripo data chimiro.

Ngatitangei neyekirasi mysqlcheck utility. Simply run:

mysqlcheck -u root -p --all-databases --check-upgrade

Kana pasina matambudziko akawanikwa, chishandiso chinobuda nekodhi 0:

Uye zvakare, chishandiso chinowanikwa mune zvemazuva ano shanduro dzeMySQL mysql-shell (munyaya yePercona iyi ndiyo package percona-mysql-shell) Iyo inotsiva yekare mysql mutengi uye inosanganisa mabasa emutengi, SQL kodhi mupepeti uye MySQL manejimendi ekushandisa. Kuti utarise sevha usati wagadziridza, unogona kumhanyisa inotevera mirairo kuburikidza nayo:

mysqlsh -- util check-for-server-upgrade { --user=root --host=1.1.1.1 --port=3306 } --config-path=/etc/mysql/my.cnf

Heano mashoko atakagamuchira:

Kazhinji, hapana chakakosha - chete yambiro nezve encodings (ona pazasi). Zvose zvinoguma zvaitika:

Takasarudza kuti update inofanira kuenda pasina matambudziko.

Chinyorwa nezve yambiro pamusoro apa inoratidza matambudziko nemaencodings. Icho chokwadi ndechekuti UTF-8 muMySQL kusvika nguva pfupi yadarika yakanga isiri "yechokwadi" UTF-8, sezvo yakachengeta 3 bytes chete pane 4. MuMySQL 8 izvi zvinogoneka pakupedzisira akasarudza kuzvigadzirisa: zita utf8 ichakurumidza kutungamirira kukodhi utf8mb4, uye mbiru dzekare mumatafura dzichava utf8mb3. Kuwedzera encoding utf8mb3 ichabviswa, asi kwete mukuburitswa uku. Naizvozvo, isu takasarudza kugadzirisa encodings yatove pane inomhanya DBMS yekuisa, mushure mekugadzirisa.

Chikamu 3: Server Updates

Chii chinogona kukanganisa kana paine chirongwa chakangwara chakadaro?.. Kunzwisisa zvizere kuti nuances inogara ichiitika, takaita kuyedza kwekutanga pane MySQL dev cluster.

Sezvambotaurwa, zvinyorwa zvepamutemo inovhara nyaya yekuvandudza maseva eMySQL ane replicas. Chinokosha ndechekuti iwe unofanirwa kutanga wagadzirisa zvese replicas (varanda), sezvo MySQL 8 inogona kudzokorora kubva kune master version 5.7. Kumwe kuoma kuri mukuti isu tinoshandisa iyo modhi tenzi <-> tenzi, kana tenzi ari kure ari mumodhi verenga-chete. Iko ndiko, kutaura zvazviri, kurwisa traffic inoenda kune imwe data data, uye yechipiri ndeye backup imwe.

Iyo topology inoita seizvi:

Iyo yekuvandudza inofanira kutanga nemifananidzo mysql replica dc 2, mysql tenzi dc 2 и mysql replica dc 1, uye kupera ne mysql master dc server 1. Kuti tive nekuvimbika zvakanyanya, takamisa michina chaiyo, takatora mifananidzo yavo, uye pakarepo iyo update isati yamira kudzokorora nemurairo. STOP SLAVE. Iyo yakasara yekuvandudza inoita seizvi:

1. Isu tinotangazve imwe neimwe replica nekuwedzera 3 sarudzo kune configs: skip-networking, skip-slave-start, skip-log-bin. Icho chokwadi ndechekuti kugadzirisa dhatabhesi kunogadzira mabhinari matanda ane zvigadziriso kumatafura ehurongwa. Iyi mirairo inovimbisa kuti hapazove nekuchinja kune data rekushandisa mudhatabhesi, uye ruzivo nezve kuvandudza masisitimu matafura haazoverengerwa mumabhinari matanda. Izvi zvinodzivirira matambudziko kana uchitangazve kudzokorora.
2. Kuisa pasuru percona-server-server. Zvakakosha kuziva kuti muMySQL version 8 kwete unofanira kumhanya murairo mysqlupgrade mushure mekuvandudza server.
3. Mushure mekutanga kwakabudirira, tinotangazve sevha zvakare - pasina maparameter akawedzerwa mundima yekutanga.
4. Isu tinoita shuwa kuti kudzokorora kunoshanda zvinobudirira: tarisa SHOW SLAVE STATUS uye ona kuti matafura ane zviverengero mudura rekushandisa anogadziridzwa.

Izvo zvese zvinotaridzika zvakapusa: iyo dev yekuvandudza yakabudirira. Zvakanaka, unogona kuronga zvakachengeteka kugadzirisa kwehusiku kugadzirwa.

Pakanga pasina kusuwa - takagadziridza prod

Nekudaro, kuendeswa kweakabudirira dev ruzivo kune kugadzirwa kwaisave pasina zvinoshamisa.

Neraki, iyo yekuvandudza iyo pachayo inotanga nemareplicas, saka patakasangana nematambudziko, isu takamisa basa ndokudzoreredza replica kubva pamufananidzo. Kuferefetwa kwematambudziko kwakamiswa kusvika mangwana mangwanani. Mapepa acho aive nezvinyorwa zvinotevera:

2020-01-14T21:43:21.500563Z 2 [ERROR] [MY-012069] [InnoDB] table: t1 has 19 columns but InnoDB dictionary has 20 columns
2020-01-14T21:43:21.500722Z 2 [ERROR] [MY-010767] [Server] Error in fixing SE data for db1.t1
2020-01-14T21:43:24.208365Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2020-01-14T21:43:24.208658Z 0 [ERROR] [MY-010119] [Server] Aborting

Kutsvaga mudura reakasiyana ezvinyorwa zvekutumira paGoogle kwakatungamira mukunzwisisa kuti dambudziko iri rinoitika nekuda MySQL bug. Kunyangwe ichi chingangodaro chiri chekushandisa bug mysqlcheck и mysqlsh.

Zvinoitika kuti MySQL yakachinja nzira yavanomiririra dhata yeminda yegumi (int, tinyint, nezvimwewo), saka mysql-server inoshandisa imwe nzira yekuichengeta. Kana yako database pakutanga yaive muvhezheni 5.5 kana 5.1, wobva wagadziridza kusvika pa5.7, ipapo ungangoda kuita. OPTIMIZE kune mamwe matafura. Ipapo MySQL inogadziridza mafaera e data, ichiaendesa kune yazvino chengetedzo fomati.

Iwe unogona zvakare kutarisa izvi nekushandisa mysqlfrm:

mysqlfrm --diagnostic -vv /var/lib/mysql/db/table.frm
...
 'field_length': 8,
  'field_type': 246, # формат поля
  'field_type_name': 'decimal',
  'flags': 3,
  'flags_extra': 67,
  'interval_nr': 0,
 'name': 'you_deciaml_column',
...

kana field_type Kana iwe uine yakaenzana ne0, ipapo rudzi rwekare runoshandiswa patafura - unofanirwa kuita OPTIMIZE. Nekudaro, kana kukosha kuri 246, watova nerudzi rutsva. Ruzivo rwakawanda nezvemhando dzinogona kuwanikwa mukati code.

Uyezve, mu bug iyi Isu tiri kufunga chechipiri chingaite chikonzero, icho chakatipfuura: kusavapo kwematafura eInnoDB mutafura yehurongwa INNODB_SYS_TABLESPACES, kana ivo, matafura, akagadzirwa mushanduro 5.1. Kuti udzivise matambudziko paunenge uchivandudza, unogona kushandisa yakabatanidzwa SQL script.

Nei takanga tisina matambudziko akadaro padev? Iyo dhatabhesi inoteedzerwa nguva nenguva kubva mukugadzirwa - nekudaro, matafura anogadzirwazve.

Nehurombo, pane inoshanda yakakura dhatabhesi, haugone kungotora uye kuita yepasirese OPTIMIZE. percona-toolkit ichabatsira pano: iyo pt-online-schema-shanduko yekushandisa yakanakira iyo online OPTIMIZE mashandiro.

Chirongwa chakagadziridzwa chakaita seizvi:

1. Gadzirisa matafura ese.
2. Gadzirisa dhatabhesi.

Kuti uzvitarise uye panguva imwe chete kuziva iyo yekuvandudza nguva, isu takadzima imwe replicas uye takamhanyisa unotevera kuraira kune ese matafura:

pt-online-schema-change --critical-load Threads_running=150 --alter "ENGINE=InnoDB" --execute --chunk-size 100 --quiet --alter-foreign-keys-method auto h=127.0.0.1,u=root,p=${MYSQL_PASSWORD},D=db1,t=t1

Matafura anogadziridzwa pasina makiyi akareba nekuda kwekuti iyo yekushandisa inogadzira nyowani yenguva tafura iyo inokopa data kubva patafura huru. Panguva iyo matafura ese ari maviri akafanana, tafura yepakutanga yakavharwa uye inotsiviwa neitsva. Muchiitiko chedu, bvunzo yekumhanya yakaratidza kuti zvaizotora rinenge zuva kugadzirisa matafura ese, asi kukopa data kwakakonzera mutoro wakawandisa pamadisiki.

Kuti tidzivise izvi, mukugadzira isu takawedzera nharo kune murairo --sleep ine kukosha kwegumi - iyi parameter inogadzirisa kureba kwekumirira mushure mekuendesa batch yedata kune tafura itsva. Nenzira iyi unogona kuderedza mutoro kana iyo chaiyo yekumhanya application irikuda panguva yekupindura.

Mushure mekuita optimization, iyo update yakabudirira.

... asi kwete zvachose!

Mukati mehafu yeawa mushure mekuvandudzwa, mutengi akauya nedambudziko. Iyo dhatabhesi yakashanda zvinoshamisa: nguva nenguva vakatanga kubatanidza resets. Izvi ndizvo zvazvaita pakutarisa:

Iyo skrini inoratidza sawtooth graph nekuda kwekuti dzimwe dzeMySQL server tambo nguva nenguva dzaidonha nekukanganisa. Zvikanganiso zvakaonekwa mukushandisa:

[PDOException] SQLSTATE[HY000] [2002] Connection refused

Kuongorora nekukurumidza kwematanda kwakaratidza kuti mysqld daemon yaisakwanisa kuwana zviwanikwa zvaidiwa kubva kune inoshanda sisitimu. Tichiri kugadzirisa zvikanganiso, takawana musystem "nherera" apparmor policy mafaira:

# dpkg -S /etc/apparmor.d/cache/usr.sbin.mysqld
dpkg-query: no path found matching pattern /etc/apparmor.d/cache/usr.sbin.mysqld
# dpkg -S /etc/apparmor.d/local/usr.sbin.mysqld
dpkg-query: no path found matching pattern /etc/apparmor.d/local/usr.sbin.mysqld
# dpkg -S /etc/apparmor.d/usr.sbin.mysqld
mysql-server-5.7: /etc/apparmor.d/usr.sbin.mysqld
# dpkg -l mysql-server-5.7
rc  mysql-server-5.7 5.7.23-0ubuntu0.16.04.1      amd64

Aya mafaera akagadzirwa pakukwidziridzwa kuMySQL 5.7 makore akati wandei apfuura uye ndewepasuru yakabviswa. Kudzima mafaera uye kutangazve apparmor sevhisi kwakagadzirisa dambudziko:

systemctl stop apparmor
rm /etc/apparmor.d/cache/usr.sbin.mysqld
rm /etc/apparmor.d/local/usr.sbin.mysqld
rm /etc/apparmor.d/usr.sbin.mysqld
systemctl start apparmor

Mukupedzisa

Chero, kunyange kushanda kuri nyore, kunogona kutungamirira kumatambudziko asingatarisirwi. Uye kunyange kuva nepurogiramu yakanyatsofungwa hakuvimbisi nguva dzose muuyo unotarisirwa. Ikozvino, chero zvirongwa zvekuvandudza timu yedu ine zvakare inosungirwa kucheneswa kwemafaira asina basa angadai akaonekwa semhedzisiro yezvichangobva kuitika.

Uye neiyi isiri yakanyanya hunyanzvi graphic dhizaini, ndinoda kutaura zvikuru kutenda kuPercona nekuda kwezvigadzirwa zvavo zvakanakisa!

PS

Verenga zvakare pablog yedu:

• «Databases uye Kubernetes (wongororo uye vhidhiyo mushumo)";
• «Kubernetes matipi & matipi: kumhanyisa bootstrap yemahombe dhatabhesi";
• «6 nyaya dzinoshanda kubva kuSRE yedu yemazuva ese hupenyu";
• «Imwe nyaya ine Redis opareta muK8s uye mini-ongororo yezvishandiso zvekuongorora data kubva pane iyi database.";
• «Seamless kutama kweMongoDB kuenda Kubernetes".

Source: www.habr.com