Kusintha MySQL (Percona Server) kuchokera 5.7 mpaka 8.0

Kupita patsogolo sikuyima, chifukwa chake zifukwa zosinthira kumitundu yaposachedwa ya MySQL zikuchulukirachulukira. Osati kale kwambiri, mu imodzi mwama projekiti athu, inali nthawi yosinthira magulu a Percona Server 5.7 kukhala mtundu 8. Zonsezi zidachitika pa nsanja ya Ubuntu Linux 16.04. Momwe mungapangire opaleshoni yotereyi ndi nthawi yochepa yochepetsera komanso mavuto omwe tidakumana nawo pakusinthidwa - werengani m'nkhaniyi.

Kukonzekera

Kusintha kulikonse kwa seva ya database nthawi zambiri kumalumikizidwa ndi kukonzanso nkhokwe: kusintha kwa zofunikira pamalire azinthu zamakina ndi kukonza makonzedwe a database omwe akuyenera kuchotsedwa ku malangizo akale.

Tisanasinthidwe, tidzanenanso zolembedwa zovomerezeka:

• MySQL 8 kumasulidwa zolemba;
• Kuwongolera kwa MySQL;
• Percona update guide;
• Chitsogozo cha MySQL chosinthira ma replicas ndi masters.

Ndipo tiyeni tipange dongosolo:

1. Konzani mafayilo osinthira pochotsa malangizo akale.
2. Onani kuyanjana ndi zofunikira.
3. Sinthani nkhokwe za akapolo poyika phukusi percona-server-server.
4. Sinthani mbuye ndi phukusi lomwelo.

Tiyeni tiyang'ane pa mfundo iliyonse ya ndondomekoyi ndikuwona zomwe zingasokoneze.

ZOFUNIKA KWAMBIRI! Njira yosinthira gulu la MySQL kutengera Galera ili ndi zobisika zake zomwe sizinafotokozedwe m'nkhaniyi. Musagwiritse ntchito malangizowa pankhaniyi.

Gawo 1: Kuyang'ana ma configs

MySQL idachotsedwa mu mtundu 8 query_cache. Kwenikweni iye anali zanenedwa kuti ndi zachikale kubwerera mu mtundu 5.7, koma tsopano kwathunthu zichotsedwa. Chifukwa chake, ndikofunikira kuchotsa malangizo ogwirizana nawo. Ndipo posungira zopempha mutha kugwiritsa ntchito zida zakunja - mwachitsanzo, ProxySQL.

Komanso mu config munali malangizo akale okhudza innodb_file_format. Ngati mu MySQL 5.7 zinali zotheka kusankha mtundu wa InnoDB, ndiye kuti mtundu wa 8 ukugwira ntchito kale kokha ndi mtundu wa Barracuda.

Chotsatira chathu ndikuchotsa malangizo awa:

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

Kuti muwone, tidzagwiritsa ntchito chithunzi cha Docker cha Percona Server. Tidzayika kasinthidwe ka seva mu chikwatu mysql_config_test, ndipo pafupi ndi izo tidzapanga zolemba za deta ndi zolemba. Chitsanzo choyesera kasinthidwe ka 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

Pansi: mwina muzolemba za Docker kapena m'chikwatu chokhala ndi zipika - kutengera masinthidwe anu - fayilo idzawonekera momwe malangizo ovuta adzafotokozedwera.

Nazi zomwe tinali nazo:

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.

Chifukwa chake, tidafunikirabe kudziwa ma encoding ndikusintha malangizo akale expire-logs-days.

Gawo 2: Kuyang'ana makhazikitsidwe ogwira ntchito

Zolemba zosinthidwa zili ndi zida ziwiri zowunikira nkhokwe kuti zigwirizane. Kugwiritsa ntchito kwawo kumathandiza woyang'anira kuyang'ana kugwirizana kwa deta yomwe ilipo.

Tiyeni tiyambe ndi zida zapamwamba za mysqlcheck. Ingothamangani:

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

Ngati palibe mavuto omwe apezeka, ntchitoyo idzatuluka ndi code 0:

Kuphatikiza apo, chida chikupezeka m'mitundu yamakono ya MySQL mysql-chipolopolo (pankhani ya Percona iyi ndiye phukusi percona-mysql-shell). Ndilo m'malo mwa kasitomala wakale wa mysql ndikuphatikiza ntchito za kasitomala, mkonzi wamakhodi a SQL ndi zida zowongolera za MySQL. Kuti muwone seva musanasinthire, mutha kugwiritsa ntchito malamulo awa:

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

Nawa ndemanga zomwe talandira:

Nthawi zambiri, palibe chofunikira - machenjezo okha okhudza ma encodings (Onani pansipa). Zotsatira zonse:

Tinaganiza kuti zosinthazi zipite popanda mavuto.

Chidziwitso cha machenjezo pamwambapa omwe akuwonetsa zovuta ndi ma encoding. Chowonadi ndi chakuti UTF-8 mu MySQL mpaka posachedwa sizinali "zowona" UTF-8, popeza idasunga ma byte atatu okha m'malo mwa 3. Mu MySQL 4 izi ndizomaliza adaganiza zokonza: ayi utf8 posachedwa zidzatsogolera ku coding utf8mb4, ndipo mizati yakale m'magomewo idzakhala utf8mb3. Encoding ina utf8mb3 zidzachotsedwa, koma osati mu kutulutsidwa uku. Chifukwa chake, tidaganiza zokonza ma encodings omwe ali kale pakukhazikitsa kwa DBMS, titakonzanso.

Gawo 3: Zosintha za Seva

Kodi chingachitike ndi chiyani pakakhala dongosolo lanzeru chotere?.

Monga tanenera kale, zolemba zovomerezeka chimakwirira nkhani yosintha ma seva a MySQL okhala ndi zofananira. Chofunikira ndichakuti muyenera kusintha kaye zolemba zonse (akapolo), popeza MySQL 8 imatha kubwereza kuchokera ku master version 5.7. Vuto lina limakhala kuti timagwiritsa ntchito mawonekedwe bwana <-> bwana, pamene mbuye wakutali ali mu mode kuwerenga kokha. Izi zikutanthauza kuti, magalimoto olimbana nawo amapita kumalo amodzi a data, ndipo yachiwiri ndi yosunga zobwezeretsera.

Topology ikuwoneka motere:

Zosintha ziyenera kuyamba ndi zofananira mysql replica dc 2, mysql master dc 2 и mysql replica dc 1, ndikumaliza ndi seva ya mysql master dc 1. Kuti tikhale odalirika, tinayimitsa makina enieni, tinajambula zithunzi zawo, ndipo nthawi yomweyo kusinthidwa kusanasiya kubwereza ndi lamulo. STOP SLAVE. Zosintha zina zonse zikuwoneka motere:

1. Timayambanso chofanizira chilichonse powonjezera zosankha za 3 ku ma configs: skip-networking, skip-slave-start, skip-log-bin. Chowonadi ndi chakuti kukonzanso nkhokwe kumapanga zipika za binary ndi zosintha pamatebulo adongosolo. Malangizowa amatsimikizira kuti sipadzakhala kusintha kwa deta yogwiritsira ntchito mu database, ndipo zambiri zokhudza kukonzanso matebulo adongosolo sizidzaphatikizidwa muzolemba zamabina. Izi zidzapewa mavuto poyambiranso kubwereza.
2. Kuyika phukusi percona-server-server. Ndikofunika kuzindikira kuti mu MySQL version 8 osati muyenera kuyendetsa lamulo mysqlupgrade pambuyo pakusintha kwa seva.
3. Pambuyo poyambira bwino, timayambiranso seva - popanda magawo omwe adawonjezedwa m'ndime yoyamba.
4. Timaonetsetsa kuti kubwereza kumagwira ntchito bwino: fufuzani SHOW SLAVE STATUS ndikuwona kuti matebulo okhala ndi zowerengera mu nkhokwe ya pulogalamu akusinthidwa.

Zonse zikuwoneka zosavuta: zosintha za dev zidapambana. Chabwino, mutha kukonza zosintha zausiku kuti zipangidwe.

Panalibe zachisoni - tidasinthiratu prod

Komabe, kusamutsidwa kwa luso la dev lopambana pakupanga sikunali kodabwitsa.

Mwamwayi, zosinthazo zimayamba ndi zofananira, kotero titakumana ndi zovuta, tidayimitsa ntchitoyi ndikubwezeretsanso chithunzicho. Kufufuza kwa mavutowa kunayimitsidwa mpaka mmawa wotsatira. Zolembazo zinali ndi zolemba izi:

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

Kufufuza zolemba zakale zamakalata osiyanasiyana pa Google kudapangitsa kuti timvetsetse kuti vutoli limachitika chifukwa cha MySQL bug. Ngakhale izi zitha kukhala vuto lothandizira mysqlcheck и mysqlsh.

Zinapezeka kuti MySQL inasintha momwe amayimira deta ya magawo a decimal (int, tinyint, etc.), kotero mysql-server imagwiritsa ntchito njira ina yosungira. Ngati database yanu poyamba anali mu mtundu 5.5 kapena 5.1, ndiyeno mudasinthidwa kukhala 5.7, ndiye mungafunike kutero OPTIMIZE pa matebulo ena. Kenako MySQL idzasintha mafayilo a data, kuwasamutsira ku mawonekedwe apano osungira.

Mukhozanso kuyang'ana izi ndi zothandiza 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',
...

ngati field_type Ngati muli nacho chofanana ndi 0, ndiye kuti mtundu wakale umagwiritsidwa ntchito patebulo - muyenera kuchita OPTIMIZE. Komabe, ngati mtengo ndi 246, muli ndi mtundu watsopano. Zambiri zamitunduyi zitha kupezeka mu kachidindo.

Komanso, mu cholakwika ichi Tikulingalira chifukwa chachiwiri chomwe chingatidutse: kusowa kwa matebulo a InnoDB patebulo ladongosolo INNODB_SYS_TABLESPACES, ngati iwo, matebulo, adapangidwa mu mtundu 5.1. Kuti mupewe mavuto mukamaliza, mutha kugwiritsa ntchito script SQL yophatikizidwa.

Chifukwa chiyani sitinakhale ndi zovuta zotere pa dev? Nawonso database imakopera nthawi ndi nthawi kuchokera pakupanga - motero, matebulo amapangidwanso.

Tsoka ilo, pa database yayikulu yogwira ntchito, simungathe kungotenga ndikuchita zonse OPTIMIZE. Percona-toolkit ithandiza apa: chida cha pt-online-schema-change ndichabwino kwambiri pakugwiritsa ntchito OPTIMIZE pa intaneti.

Pulani yosinthidwa idawoneka motere:

1. Konzani matebulo onse.
2. Sinthani nkhokwe.

Kuti tifufuze komanso nthawi yomweyo kudziwa nthawi yosinthira, tidaletsa imodzi mwazofananira ndikuyendetsa lamulo ili pamagome onse:

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

Matebulo amasinthidwa popanda maloko aatali chifukwa chothandizira chimapanga tebulo losakhalitsa lomwe limakoperamo deta kuchokera patebulo lalikulu. Panthawi yomwe matebulo onsewa ali ofanana, tebulo loyambirira limatsekedwa ndikusinthidwa ndi latsopano. Kwa ife, kuyesa koyesa kunawonetsa kuti zingatenge pafupifupi tsiku kuti zisinthe matebulo onse, koma kukopera deta kunayambitsa katundu wambiri pa disks.

Kuti tipewe izi, popanga tinawonjezera mkangano ku lamulo --sleep ndi mtengo wa 10 - chizindikiro ichi chimasintha kutalika kwa kuyembekezera mutatha kusamutsa gulu la deta ku tebulo latsopano. Mwanjira iyi mutha kuchepetsa katundu ngati pulogalamu yomwe ikuyendetsa ikufuna panthawi yoyankha.

Pambuyo pochita kukhathamiritsa, zosinthazo zidapambana.

... koma osati kwathunthu!

Pasanathe theka la ola pambuyo pomwe, kasitomala anabwera ndi vuto. Ma database adagwira ntchito modabwitsa: nthawi ndi nthawi adayamba kugwirizana kukonzanso. Izi ndi zomwe zinkawoneka poyang'anira:

Chithunzicho chikuwonetsa graph ya sawtooth chifukwa chakuti ulusi wina wa seva ya MySQL nthawi ndi nthawi umagwa ndi cholakwika. Zolakwa zidawoneka mu pulogalamu:

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

Kuyang'ana mwachangu kwa zipikazo kunawonetsa kuti mysqld daemon sakanatha kupeza zofunikira kuchokera pamakina ogwiritsira ntchito. Pamene tikukonza zolakwika, tinapeza mu dongosolo mafayilo a "orphan" apparmor policy:

# 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

Mafayilowa adapangidwa pokwezera ku MySQL 5.7 zaka zingapo zapitazo ndipo ndi a phukusi lochotsedwa. Kuchotsa mafayilo ndikuyambitsanso ntchito ya apparmor kunathetsa vutoli:

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

Pomaliza

Chilichonse, ngakhale ntchito yosavuta, ikhoza kubweretsa mavuto osayembekezereka. Ndipo ngakhale kukhala ndi dongosolo lolinganizidwa bwino sikumatsimikizira nthaŵi zonse zotulukapo zoyembekezeredwa. Tsopano, mapulani aliwonse osinthika omwe gulu lathu lili nawo akuphatikizanso kuyeretsa koyenera kwa mafayilo osafunikira omwe akanawoneka chifukwa cha zomwe zachitika posachedwa.

Ndipo ndi luso lojambula bwino kwambirili, ndikufuna kunena zikomo kwambiri kwa Percona chifukwa chazinthu zabwino kwambiri!

PS

Werenganinso pa blog yathu:

• «Databases ndi Kubernetes (review and video report)";
• «Malangizo & zidule za Kubernetes: kufulumizitsa bootstrap pama database akulu";
• «Nkhani 6 zothandiza pa moyo wathu watsiku ndi tsiku wa SRE";
• «Nkhani imodzi yokhala ndi Redis operator mu K8s ndi kuwunika kwapang'onopang'ono kwazinthu zothandizira kusanthula deta kuchokera pankhokwe iyi.";
• «Kusamuka kosasunthika kwa MongoDB kupita ku Kubernetes".

Source: www.habr.com