Ana sabunta MySQL (Percona Server) daga 5.7 zuwa 8.0

Ci gaba ba ya tsayawa har yanzu, don haka dalilan haɓakawa zuwa sabbin sigogin MySQL suna ƙara tursasawa. Ba da dadewa ba, a cikin ɗayan ayyukanmu, lokaci ya yi da za a sabunta ƙungiyoyin Percona Server 5.7 masu jin daɗi zuwa sigar 8. Duk wannan ya faru akan dandamalin Ubuntu Linux 16.04. Yadda za a yi irin wannan aiki tare da ɗan gajeren lokaci da kuma matsalolin da muka fuskanta yayin sabuntawa - karanta a cikin wannan labarin.

Horo

Duk wani sabuntawa na uwar garken bayanan yana da alaƙa da sake tsara bayanai: canje-canje a cikin buƙatu don iyaka akan albarkatun tsarin da kuma daidaita saitunan bayanai waɗanda ke buƙatar sharewa daga tsoffin umarni.

Kafin sabuntawa, tabbas za mu koma ga takaddun hukuma:

• Bayanan saki na MySQL 8;
• Jagorar haɓaka MySQL;
• Jagoran sabunta Percona;
• Jagorar MySQL don sabunta kwafi da masters.

Kuma bari mu tsara tsarin aiki:

1. Gyara fayilolin sanyi ta hanyar cire tsoffin umarni.
2. Duba dacewa tare da kayan aiki.
3. Sabunta bayanan bawan ta hanyar shigar da kunshin percona-server-server.
4. Sabunta maigidan tare da fakiti iri ɗaya.

Bari mu dubi kowane batu na shirin mu ga abin da zai iya faruwa ba daidai ba.

Muhimmanci! Hanyar sabunta tarin MySQL dangane da Galera yana da nasa dabarar da ba a bayyana a cikin labarin ba. Kada ku yi amfani da wannan umarni a wannan yanayin.

Sashe na 1: Duba saitunan

An cire MySQL a cikin sigar 8 query_cache. A gaskiya ya kasance bayyana wanda ya daina aiki dawo cikin sigar 5.7, amma yanzu gaba daya share. Sabili da haka, wajibi ne a cire umarnin da aka haɗa. Kuma don buƙatun cache yanzu zaku iya amfani da kayan aikin waje - alal misali, ProxySQL.

Hakanan a cikin saitin akwai umarni da suka gabata game da innodb_file_format. Idan a cikin MySQL 5.7 yana yiwuwa a zaɓi tsarin InnoDB, to sigar 8th ta riga ta yi aiki kawai tare da tsarin Barracuda.

Sakamakonmu shine cire umarni masu zuwa:

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

Don bincika, za mu yi amfani da hoton Docker na Percona Server. Za mu sanya saitin uwar garken a cikin kundin adireshi mysql_config_test, kuma kusa da shi za mu ƙirƙira kundayen adireshi don bayanai da logs. Misalin gwajin sanyi na uwar garken Percona:

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

Layin ƙasa: ko dai a cikin rajistan ayyukan Docker ko a cikin kundin adireshi tare da rajistan ayyukan - ya danganta da saitunan ku - fayil zai bayyana wanda za a bayyana umarnin matsala.

Ga abin da muka samu:

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.

Don haka, har yanzu muna buƙatar gano abubuwan ɓoye kuma mu maye gurbin tsohon umarnin expire-logs-days.

Sashe na 2: Duba kayan aiki

Takaddun sabuntawa sun ƙunshi abubuwan amfani guda 2 don bincika bayanan bayanai don dacewa. Amfani da su yana taimaka wa mai gudanarwa don bincika daidaiton tsarin bayanan da ke akwai.

Bari mu fara da kayan aikin mysqlcheck na gargajiya. Gudu kawai:

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

Idan ba a sami matsala ba, mai amfani zai fita tare da lambar 0:

Bugu da kari, ana samun abin amfani a cikin nau'ikan MySQL na zamani mysql-shell (a cikin yanayin Percona wannan shine kunshin percona-mysql-shell). Sauyawa ce ga abokin ciniki na mysql na yau da kullun kuma yana haɗa ayyukan abokin ciniki, editan lambar SQL da kayan aikin gudanarwa na MySQL. Don bincika uwar garken kafin ɗaukaka, kuna iya aiwatar da umarni masu zuwa ta cikinsa:

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

Ga tsokacin da muka samu:

Gabaɗaya, babu wani abu mai mahimmanci - gargaɗi kawai game da rufaffiyar bayanai (duba ƙasa). Gabaɗaya sakamakon kisa:

Mun yanke shawarar cewa sabuntawa ya kamata ya tafi ba tare da matsala ba.

Bayanin kula game da gargaɗin da ke sama yana nuni da matsaloli tare da rikodi. Gaskiyar ita ce UTF-8 a cikin MySQL har zuwa kwanan nan ba "gaskiya" UTF-8 ba, Tun da ya adana kawai 3 bytes maimakon 4. A cikin MySQL 8 wannan shine ƙarshe yanke shawarar gyara shi: alkiyama utf8 nan ba da jimawa ba zai kai ga yin codeing utf8mb4, kuma tsofaffin ginshiƙai a cikin tebur za su zama utf8mb3. Ƙarin ɓoyewa utf8mb3 za a cire, amma ba a cikin wannan sakin ba. Saboda haka, mun yanke shawarar gyara abubuwan da aka riga aka shigar akan DBMS mai gudana, bayan sabunta shi.

Sashe na 3: Sabunta uwar garke

Menene zai iya faruwa ba daidai ba lokacin da akwai irin wannan shirin mai wayo?... Fahimtar da kyau cewa nuances koyaushe yana faruwa, mun gudanar da gwaji na farko akan gungu na MySQL dev.

Kamar yadda aka ambata, takardun hukuma yana rufe batun sabunta sabobin MySQL tare da kwafi. Layin ƙasa shine yakamata ku fara sabunta duk kwafi (bayi), tunda MySQL 8 na iya yin kwafi daga babban sigar 5.7. Wasu wahala suna cikin gaskiyar cewa muna amfani da yanayin master <> master, lokacin da maigidan nesa yana cikin yanayin karanta-kawai. Wato, a haƙiƙa, zirga-zirgar yaƙi yana zuwa cibiyar bayanai ɗaya, na biyu kuma shine madadin.

Topology yayi kama da haka:

Dole ne sabuntawa ya fara da kwafi mysql kwafi dc 2, mysql master dc 2 и mysql replica dc 1, kuma ya ƙare tare da uwar garken mysql master dc 1. Don ƙarin aminci, mun dakatar da injunan kama-da-wane, mun ɗauki hotunan su, kuma nan da nan kafin sabuntawa ya daina yin kwafi tare da umarnin. STOP SLAVE. Sauran sabuntawa yayi kama da haka:

1. Muna sake kunna kowane kwafi ta ƙara zaɓuka 3 zuwa masu daidaitawa: skip-networking, skip-slave-start, skip-log-bin. Gaskiyar ita ce sabunta bayanan bayanan yana haifar da rajistan ayyukan binary tare da sabuntawa zuwa teburin tsarin. Waɗannan umarnin sun ba da tabbacin cewa ba za a sami canje-canje ga bayanan aikace-aikace a cikin ma'ajin bayanai ba, kuma ba za a haɗa bayanai game da sabunta teburin tsarin a cikin rajistan ayyukan binary ba. Wannan zai guje wa matsaloli lokacin da ake sake maimaitawa.
2. Shigar da kunshin percona-server-server. Yana da mahimmanci a lura cewa a cikin MySQL version 8 ba kuna buƙatar gudanar da umarni mysqlupgrade bayan sabunta uwar garken.
3. Bayan farawa mai nasara, za mu sake kunna uwar garken - ba tare da sigogi waɗanda aka ƙara a cikin sakin layi na farko ba.
4. Tabbatar cewa kwafi yana aiki cikin nasara: duba SHOW SLAVE STATUS kuma ka ga cewa an sabunta allunan masu kirga a cikin bayanan aikace-aikacen.

Duk ya yi kama da sauƙi: sabuntawar dev ya yi nasara. Ok, zaku iya tsara sabuntawar dare lafiya don samarwa.

Babu bakin ciki - mun sabunta prod

Koyaya, canja wurin ƙwarewar dev mai nasara zuwa samarwa ba tare da mamaki ba.

Abin farin ciki, tsarin sabuntawa da kansa yana farawa da kwafi, don haka lokacin da muka ci karo da matsaloli, mun dakatar da aikin kuma mun dawo da kwafin daga hoton. An dage binciken matsalolin har sai da safe. Rukunan sun ƙunshi abubuwan shiga masu zuwa:

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

Binciken rumbun adana bayanan imel daban-daban akan Google ya haifar da fahimtar cewa wannan matsalar tana faruwa ne saboda MySQL bug. Ko da yake wannan ya fi yuwuwar kwaro mai amfani mysqlcheck и mysqlsh.

Ya zama cewa MySQL ya canza yadda suke wakiltar bayanai don filayen ƙima (int, tinyint, da dai sauransu), don haka mysql-uwar garken yana amfani da wata hanya ta daban don adana su. Idan database da farko ya kasance a cikin sigar 5.5 ko 5.1, sannan kun sabunta zuwa 5.7, to kuna iya buƙatar yin OPTIMIZE ga wasu teburi. Sa'an nan MySQL zai sabunta fayilolin bayanai, canja wurin su zuwa tsarin ajiya na yanzu.

Hakanan zaka iya duba wannan tare da mai amfani 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',
...

idan field_type Idan kuna da shi daidai da 0, to ana amfani da tsohuwar nau'in a cikin tebur - kuna buƙatar aiwatarwa OPTIMIZE. Koyaya, idan ƙimar ta kasance 246, kuna da sabon nau'in. Ana iya samun ƙarin bayani game da nau'ikan a ciki lambar.

Haka kuma, cikin wannan kwaro Muna la'akari da dalili na biyu mai yiwuwa, wanda ya wuce mu: rashin innoDB tebur a cikin tsarin tsarin INNODB_SYS_TABLESPACES, idan su, Tables, an ƙirƙira su a cikin sigar 5.1. Don guje wa matsaloli lokacin ɗaukakawa, zaku iya amfani a haɗe rubutun SQL.

Me yasa bamu sami irin waɗannan matsalolin akan dev ba? Ana kwafi bayanan lokaci-lokaci a can daga samarwa - don haka, Ana sake yin allunan.

Abin baƙin ciki, a kan ainihin aiki mai girma bayanai, ba za ka iya kawai dauka da aiwatar da na duniya OPTIMIZE. percona-Toolkit zai taimaka a nan: pt-online-schema-canjin mai amfani yana da kyau ga aikin OPTIMIZE na kan layi.

Shirin da aka sabunta yayi kama da haka:

1. Haɓaka duk teburi.
2. Sabunta bayanan bayanai.

Don bincika shi kuma a lokaci guda nemo lokacin sabuntawa, mun kashe ɗaya daga cikin kwafin kuma mun gudanar da umarni mai zuwa don duk tebur:

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

Ana sabunta tebur ba tare da dogon makulli ba saboda gaskiyar cewa mai amfani yana ƙirƙirar sabon tebur na wucin gadi wanda a ciki yake kwafin bayanai daga babban tebur. A daidai lokacin da allunan biyu suka yi iri ɗaya, ana kulle ainihin tebur ɗin kuma an maye gurbinsu da sabon. A cikin yanayinmu, gwajin gwaji ya nuna cewa zai ɗauki kusan kwana ɗaya don sabunta dukkan tebur, amma kwafin bayanai ya haifar da nauyi mai yawa akan faifai.

Don guje wa wannan, a cikin samarwa mun ƙara hujja ga umarnin --sleep tare da darajar 10 - wannan siga yana daidaita tsayin jira bayan canja wurin batch na bayanai zuwa sabon tebur. Ta wannan hanyar zaku iya rage nauyi idan ainihin aikace-aikacen da ke gudana yana buƙata akan lokacin amsawa.

Bayan aiwatar da ingantawa, sabuntawa ya yi nasara.

... amma ba gaba daya ba!

A cikin rabin sa'a bayan sabuntawa, abokin ciniki ya zo da matsala. Database yayi aiki sosai ban mamaki: lokaci-lokaci suna farawa sake saitin haɗi. Wannan shine yadda yayi kama a cikin sa ido:

Hoton hoton yana nuna hoton sawtooth saboda gaskiyar cewa wasu zaren uwar garken MySQL sun yi karo lokaci-lokaci tare da kuskure. Kurakurai sun bayyana a cikin aikace-aikacen:

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

Binciken da aka yi da sauri na rajistan ayyukan ya nuna cewa mysqld daemon ba zai iya samun albarkatun da ake buƙata daga tsarin aiki ba. Yayin da ake warware kurakurai, mun gano a cikin tsarin fayilolin manufofin apparmor na "marayu".:

# 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

An ƙirƙiri waɗannan fayilolin lokacin haɓakawa zuwa MySQL 5.7 shekaru biyu da suka gabata kuma suna cikin kunshin da aka cire. Share fayilolin da sake kunna sabis na kayan aiki sun warware matsalar:

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

A ƙarshe

Duk wani, har ma da aiki mafi sauƙi, na iya haifar da matsalolin da ba zato ba tsammani. Kuma ko da samun kyakkyawan shiri ba koyaushe yana ba da tabbacin sakamakon da ake tsammani ba. Yanzu, duk wani tsare-tsare na sabuntawa ƙungiyarmu kuma ta haɗa da tsabtace wajibi na fayilolin da ba dole ba waɗanda zasu iya bayyana sakamakon ayyukan kwanan nan.

Kuma tare da wannan ba ƙwararrun kerawa mai hoto ba, Ina so in faɗi babbar godiya ga Percona don kyawawan samfuran su!

PS

Karanta kuma a kan shafinmu:

• «Databases da Kubernetes (bita da rahoton bidiyo)";
• «Kubernetes tukwici & dabaru: haɓaka bootstrap don manyan bayanan bayanai";
• «6 m labaru daga SRE rayuwar yau da kullum";
• «Labari ɗaya tare da ma'aikacin Redis a cikin K8s da ƙaramin bita na abubuwan amfani don nazarin bayanai daga wannan bayanan.";
• «Hijira mara kyau na MongoDB zuwa Kubernetes".

source: www.habr.com