Ibuyekeza i-MySQL (Percona Server) isuka ku-5.7 iye ku-8.0

Inqubekelaphambili ayimile, ngakho-ke izizathu zokuthuthukela ezinguqulweni zakamuva ze-MySQL ziya ngokuya ziphoqa. Esikhathini esingeside esidlule, kwenye yamaphrojekthi ethu, kwase kuyisikhathi sokubuyekeza amaqoqo athokomele e-Percona Server 5.7 abe yinguqulo yesi-8. Konke lokhu kwenzeke ku-Ubuntu Linux 16.04 platform. Ungawenza kanjani umsebenzi onjalo nge-downtime encane futhi yiziphi izinkinga esihlangabezane nazo ngesikhathi sokuvuselela - funda kulesi sihloko.

Training

Noma isiphi isibuyekezo seseva yesizindalwazi cishe sihlotshaniswa nokulungiswa kabusha kwesizindalwazi: izinguquko ezidingweni zemikhawulo yezinsiza zesistimu nokulungiswa kwezilungiselelo zesizindalwazi okudingeka kusulwe kuziqondiso eziphelelwe yisikhathi.

Ngaphambi kokubuyekeza, nakanjani sizobhekisela kumadokhumenti asemthethweni:

• Amanothi okukhululwa e-MySQL 8;
• Umhlahlandlela wokuthuthukisa i-MySQL;
• Percona update guide;
• Umhlahlandlela we-MySQL wokubuyekeza ama-replicas kanye nezingcweti.

Ake senze uhlelo lokusebenza:

1. Lungisa amafayela okumisa ngokukhipha iziqondiso eziphelelwe yisikhathi.
2. Hlola ukuhambisana nezinsiza.
3. Buyekeza imininingo egciniwe yesigqila ngokufaka iphakheji percona-server-server.
4. Buyekeza okuyinhloko ngephakheji efanayo.

Ake sibheke iphuzu ngalinye lohlelo futhi sibone ukuthi yini engase ihambe kabi.

KUBALULEKILE! Inqubo yokubuyekeza iqoqo le-MySQL ngokususelwe ku-Galera inobuqili bayo obungachazwanga esihlokweni. Akufanele usebenzise lo myalelo kuleli cala.

Ingxenye 1: Ihlola izilungiselelo

I-MySQL ikhishwe kunguqulo 8 query_cache. Empeleni wayenjalo kumenyezelwe ukuthi isiphelelwe yisikhathi emuva kunguqulo 5.7, kodwa manje isuswe ngokuphelele. Ngakho-ke, kuyadingeka ukususa iziqondiso ezihambisanayo. Futhi ukufaka izicelo kunqolobane manje ungasebenzisa amathuluzi angaphandle - isibonelo, I-ProxySQL.

Futhi ku-config bekuneziqondiso eziphelelwe yisikhathi mayelana innodb_file_format. Uma ku-MySQL 5.7 bekungenzeka ukukhetha ifomethi ye-InnoDB, inguqulo yesi-8 isivele iyasebenza kuphela ngefomethi ye-Barracuda.

Umphumela wethu uwukususwa kweziqondiso ezilandelayo:

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

Ukuhlola, sizosebenzisa isithombe se-Docker se-Percona Server. Sizobeka i-server config ohlwini lwemibhalo mysql_config_test, futhi eduze kwayo sizodala izinkomba zedatha namalogi. Isibonelo sokuhlolwa kokucushwa kwe-Percona-server:

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

Umugqa ongezansi: kungaba kulogi ye-Docker noma kunkomba enamalogi - kuye ngokulungiselelwa kwakho - kuzovela ifayela lapho kuzochazwa khona iziqondiso eziyinkinga.

Nakhu esasinakho:

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.

Ngakho-ke, besisadinga ukuthola amakhodi bese sibuyisela iziyalezo eziphelelwe yisikhathi expire-logs-days.

Ingxenye 2: Ihlola ukufakwa okusebenzayo

Amadokhumenti okubuyekeza aqukethe izinsiza ezingu-2 zokuhlola isizindalwazi ukuthi ziyahambisana yini. Ukusebenzisa kwabo kusiza umlawuli ukuthi ahlole ukuhambisana kwesakhiwo sedatha esikhona.

Ake siqale ngesisetshenziswa sakudala se-mysqlcheck. Vele ugijime:

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

Uma zingatholakali izinkinga, insiza izophuma ngekhodi 0:

Ngaphezu kwalokho, insiza iyatholakala kuzinguqulo zesimanje ze-MySQL mysql-igobolondo (esimweni sePercona leli yiphakheji percona-mysql-shell). Ingena esikhundleni seklayenti le-mysql yakudala futhi ihlanganisa imisebenzi yeklayenti, umhleli wekhodi ye-SQL namathuluzi wokuphatha we-MySQL. Ukuhlola iseva ngaphambi kokubuyekeza, ungasebenzisa imiyalo elandelayo ngayo:

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

Nanka amazwana esiwatholile:

Ngokuvamile, akukho lutho olubucayi - kuphela izexwayiso mayelana nombhalo wekhodi (bona ngezansi). Umphumela wokwenziwa usuwonke:

Sinqume ukuthi isibuyekezo kufanele sihambe ngaphandle kwezinkinga.

Inothi mayelana nezexwayiso ezingenhla ezibonisa izinkinga ngombhalo wekhodi. Iqiniso liwukuthi i-UTF-8 ku-MySQL kuze kube muva nje bekungelona "iqiniso" i-UTF-8, njengoba igcine amabhayithi angu-3 kuphela esikhundleni sika-4. Ku-MySQL 8 lokhu ekugcineni wanquma ukuyilungisa: isibizo utf8 maduze kuzoholela ekubhaleni ikhodi utf8mb4, futhi amakholomu amadala ematafuleni azoba utf8mb3. Ukufaka ikhodi okwengeziwe utf8mb3 izosuswa, kodwa hhayi kulokhu kukhishwa. Ngakho-ke, sinqume ukulungisa ama-encoding asevele ekufakweni kwe-DBMS esebenzayo, ngemuva kokuyibuyekeza.

Ingxenye 3: Izibuyekezo Zeseva

Yini engahle yonakale uma kunohlelo olukhaliphile kangaka?.. Ngokuqonda kahle ukuthi ama-nuances ayenzeka njalo, senze ukuhlolwa kokuqala kuqoqo le-MySQL dev.

Njengoba sekushiwo, imibhalo esemthethweni ihlanganisa indaba yokubuyekeza amaseva e-MySQL ngamakhophi. Okubalulekile ukuthi kufanele uqale ubuyekeze zonke izifaniso (izigqila), njengoba i-MySQL 8 ingakwazi ukuphindaphinda isuka ku-master version 5.7. Obunye ubunzima bulele eqinisweni lokuthi sisebenzisa imodi master <-> master, uma okuyinhloko okukude kukumodi Funda kuphela. Okusho ukuthi, empeleni, ithrafikhi yokulwa iya esikhungweni esisodwa sedatha, kanti esesibili siyisipele.

I-topology ibonakala kanje:

Isibuyekezo kufanele siqale ngezifaniso i-mysql replica dc 2, mysql master dc 2 и mysql replica dc 1, futhi iphethe ngeseva ye-mysql master dc 1. Ukuze sithembeke kakhudlwana, samisa imishini ebonakalayo, sathatha izithonjana zayo, futhi ngokushesha ngaphambi kokuba isibuyekezo simise ukuphindaphinda ngomyalo. STOP SLAVE. Esinye isibuyekezo sibukeka kanje:

1. Siqala kabusha isifaniso ngasinye ngokwengeza izinketho ezi-3 kuzilungiselelo: skip-networking, skip-slave-start, skip-log-bin. Iqiniso liwukuthi ukuvuselela i-database kukhiqiza izingodo kanambambili ngezibuyekezo kumatafula esistimu. Lezi ziqondiso ziqinisekisa ukuthi ngeke kube khona izinguquko kudatha yohlelo lokusebenza kusizindalwazi, futhi ulwazi olumayelana nokubuyekeza amathebula esistimu ngeke lufakwe kulogi kanambambili. Lokhu kuzogwema izinkinga lapho kuqalwa kabusha ukuphindaphinda.
2. Ifaka iphakheji percona-server-server. Kubalulekile ukuqaphela ukuthi ku-MySQL version 8 hhayi udinga ukusebenzisa umyalo mysqlupgrade ngemva kokuvuselelwa kweseva.
3. Ngemva kokuqala ngempumelelo, siqala kabusha iseva futhi - ngaphandle kwamapharamitha ayengezwe esigabeni sokuqala.
4. Siyaqiniseka ukuthi ukuphindaphinda kusebenza ngempumelelo: hlola SHOW SLAVE STATUS futhi ubone ukuthi amathebula anezibali kusizindalwazi sohlelo lokusebenza ayabuyekezwa.

Konke kubukeka kulula: isibuyekezo se-dev sibe yimpumelelo. Kulungile, ungakwazi ukuhlela ngokuphephile isibuyekezo sasebusuku sokukhiqiza.

Bekungekho ukudabuka - sibuyekeze i-prod

Kodwa-ke, ukudluliswa kokuhlangenwe nakho okuphumelelayo kwe-dev ekukhiqizeni akuzange kube ngaphandle kokumangala.

Ngenhlanhla, inqubo yokuvuselela ngokwayo iqala ngezifaniso, ngakho-ke lapho sihlangabezana nobunzima, sawumisa umsebenzi futhi sabuyisela umfanekiso ovela esifinyezweni. Uphenyo lwezinkinga luhlehliselwe ukusa ngakusasa. Amalogi aqukethe okufakiwe okulandelayo:

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

Ukucwaninga izingobo zomlando zohlu oluhlukile lwama-imeyili ku-Google kuholele ekuqondeni ukuthi le nkinga yenzeka ngenxa MySQL bug. Nakuba lokhu kungase kube isiphazamisi sokusetshenziswa mysqlcheck и mysqlsh.

Kuvele ukuthi i-MySQL ishintshe indlela emele ngayo idatha yezinkambu zamadesimali (int, tinyint, njll.), ngakho-ke i-mysql-server isebenzisa indlela ehlukile ukuyigcina. Uma database yakho ekuqaleni bekuyinguqulo 5.5 noma 5.1, wabe usuthuthukela ku-5.7, lapho-ke kungase kudingeke ukwenze. OPTIMIZE kwamanye amatafula. Bese i-MySQL izobuyekeza amafayela edatha, iwadlulisele kufomethi yamanje yokugcina.

Ungahlola futhi lokhu ngosizo 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',
...

Uma field_type Uma unayo ilingana no-0, khona-ke uhlobo oludala lusetshenziswa etafuleni - udinga ukwenza OPTIMIZE. Nokho, uma inani lingu-246, usuvele unohlobo olusha. Ulwazi olwengeziwe mayelana nezinhlobo lungatholakala ku ikhodi.

Ngaphezu kwalokho, ku lesi siphazamisi Sicubungula isizathu sesibili esingaba khona, esiseqile: ukungabi khona kwamatafula e-InnoDB kuthebula lesistimu INNODB_SYS_TABLESPACES, uma wona, amathebula, adalwe kunguqulo 5.1. Ukuze ugweme izinkinga lapho ubuyekeza, ungasebenzisa iskripthi se-SQL esinamathiselwe.

Kungani singazange sibe nezinkinga ezinjalo ku-dev? I-database ikopishwa ngezikhathi ezithile lapho kusukela ekukhiqizeni - ngaleyo ndlela, amatafula adalwa kabusha.

Ngeshwa, ku-database enkulu esebenza ngempela, ngeke ukwazi ukuvele uthathe futhi wenze yonke indawo OPTIMIZE. I-percona-toolkit izosiza lapha: insiza ye-pt-online-schema-change inhle kakhulu ekusebenzeni kwe-inthanethi ye-OPTIMIZE.

Uhlelo olubuyekeziwe lubukeke kanje:

1. Lungiselela wonke amathebula.
2. Buyekeza ama-database.

Ukuze uyihlole futhi ngesikhathi esifanayo uthole isikhathi sokubuyekeza, sikhubaze enye yezifanekiselo futhi sasebenzisa umyalo olandelayo kuwo wonke amatafula:

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

Amathebula abuyekezwa ngaphandle kokukhiya okude ngenxa yokuthi insiza idala ithebula lesikhashana elisha lapho ikopisha khona idatha kusuka kuthebula elikhulu. Okwamanje lapho womabili amathebula efana, ithebula langempela likhiyiwe futhi kufakwe elisha esikhundleni salo. Esimweni sethu, ukuhlolwa kokuhlola kubonise ukuthi kuzothatha cishe usuku ukuvuselela wonke amatafula, kodwa ukukopisha idatha kubangele umthwalo omningi kumadiski.

Ukuze ugweme lokhu, ekukhiqizeni sengeze ingxabano kumyalo --sleep ngevelu engu-10 - le parameter ilungisa ubude bokulinda ngemva kokudlulisa inqwaba yedatha kuthebula elisha. Ngale ndlela unganciphisa umthwalo uma uhlelo lokusebenza olusebenzayo ludinga ngesikhathi sokuphendula.

Ngemva kokwenza ukulungiselelwa, isibuyekezo sibe yimpumelelo.

... kodwa hhayi ngokuphelele!

Phakathi nesigamu sehora ngemva kokubuyekezwa, iklayenti lifike nenkinga. I-database isebenze ngokumangazayo: ngezikhathi ezithile baqala ukusetha kabusha uxhumano. Nansi indlela ebibukeka ngayo ekuqaphelweni:

Isithombe-skrini sibonisa igrafu ye-sawtooth ngenxa yokuthi ezinye zezintambo zeseva ye-MySQL zaziphahlazeka ngezikhathi ezithile ngephutha. Amaphutha avele ohlelweni lokusebenza:

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

Ukuhlolwa okusheshayo kwamalogi kwembula ukuthi i-mysqld daemon ayikwazanga ukuthola izinsiza ezidingekayo ohlelweni lokusebenza. Ngenkathi silungisa amaphutha, sithole ohlelweni amafayela enqubomgomo ye-apparmor "yezintandane".:

# 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

Lawa mafayela adalwe ngenkathi uthuthukela ku-MySQL 5.7 eminyakeni embalwa edlule futhi ayingxenye yephakheji ekhishiwe. Ukususa amafayela nokuqalisa kabusha isevisi ye-armor kuxazulule inkinga:

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

Ekuphethweni

Noma yikuphi, ngisho nokusebenza okulula, kungaholela ezinkingeni ezingalindelekile. Futhi ngisho nokuba nohlelo olucatshangelwe kahle akuqinisekisi ngaso sonke isikhathi umphumela olindelekile. Manje, noma yiziphi izinhlelo zokubuyekeza ithimba lethu elinazo zihlanganisa nokuhlanzwa okuyisibopho kwamafayela angadingekile abengase avele ngenxa yezenzo zakamuva.

Futhi ngalobu buciko bengcaca obungenabo ubungcweti, ngithanda ukubonga kakhulu kuPercona ngemikhiqizo yabo emihle kakhulu!

PS

Funda futhi kubhulogi yethu:

• «Imininingo egciniwe kanye ne-Kubernetes (isibuyekezo kanye nombiko wevidiyo)";
• «Amathiphu namasu akwa-Kubernetes: ukusheshisa i-bootstrap kusizindalwazi esikhulu";
• «Izindaba ezisebenzayo ezi-6 ezivela empilweni yethu yansuku zonke ye-SRE";
• «Indaba eyodwa no-opharetha we-Redis kuma-K8 kanye nokubuyekezwa okuncane kwezinsiza zokuhlaziya idatha evela kulesi sizindalwazi.";
• «Ukufuduka okungenamthungo kwe-MongoDB kuya ku-Kubernetes".

Source: www.habr.com