Ho nchafatsa MySQL (Percona Server) ho tloha ho 5.7 ho isa ho 8.0

Tsoelo-pele ha e eme, kahoo mabaka a ho ntlafatsa liphetolelong tsa morao-rao tsa MySQL a ntse a eketseha. Ha se khale haholo, ho e 'ngoe ea merero ea rona, e ne e le nako ea ho nchafatsa lihlopha tse monate tsa Percona Server 5.7 ho mofuta oa 8. Sena sohle se etsahetse sethaleng sa Ubuntu Linux 16.04. Mokhoa oa ho etsa opereishene e joalo ka nako e fokolang le hore na re ile ra kopana le mathata afe nakong ea ntlafatso - bala sehloohong sena.

Ho lokisetsa

Ntlafatso efe kapa efe ea seva sa database e kanna ea amahanngoa le tlhophiso bocha ea database: liphetoho litlhoko tsa meeli ho lisebelisoa tsa sistimi le tokiso ea litlhophiso tsa database tse hlokang ho hlakoloa ho latela litaelo tsa khale.

Pele re ntlafatsa, ka sebele re tla bua ka litokomane tsa molao:

• Lintlha tsa tokollo tsa MySQL 8;
• Tataiso ea ntlafatso ea MySQL;
• Tataiso ea ntlafatso ea Percona;
• Tataiso ea MySQL ea ho ntlafatsa likopi le masters.

'Me ha re etse moralo oa ts'ebetso:

1. Lokisa lifaele tsa tlhophiso ka ho tlosa litaelo tsa khale.
2. Hlahloba ho lumellana le lisebelisoa.
3. Ntlafatsa li-database tsa makhoba ka ho kenya sephutheloana percona-server-server.
4. Nchafatsa monghali ka sephutheloana se tšoanang.

Ha re shebe ntlha e 'ngoe le e 'ngoe ea moralo 'me re bone hore na ke eng e ka senyehang.

BOHLOKOA! Mokhoa oa ho nchafatsa sehlopha sa MySQL se thehiloeng ho Galera se na le lintho tse poteletseng tse sa hlalosoang sengolong. Ha ua lokela ho sebelisa taelo ena tabeng ena.

Karolo ea 1: Ho hlahloba litlhophiso

MySQL e tlositsoe ho mofuta oa 8 query_cache. Ha e le hantle o ne a le joalo phatlalatsoa hore ha e sebetse morao ho mofuta oa 5.7, empa hona joale hlakotsoe ka botlalo. Ka hona, hoa hlokahala ho tlosa litaelo tse amanang. 'Me ho li-cache lipotso joale u ka sebelisa lisebelisoa tsa kantle - mohlala, ProxySQL.

Hape ho config ho ne ho e-na le litaelo tsa khale mabapi le innodb_file_format. Haeba ho MySQL 5.7 ho ne ho khoneha ho khetha sebopeho sa InnoDB, joale mofuta oa 8 o se o ntse o sebetsa feela ka mokhoa oa Barracuda.

Sephetho sa rona ke ho tlosoa ha litaelo tse latelang:

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

Ho hlahloba, re tla sebelisa setšoantšo sa Docker sa Percona Server. Re tla beha config seva ka har'a directory mysql_config_test, 'me haufi le eona re tla theha li-directory tsa data le li-log. Mohlala oa tlhahlobo ea tlhophiso ea 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

Ntlha ea bohlokoa: ekaba ka har'a li-logs tsa Docker kapa bukeng e nang le lits'oants'o - ho latela litlhophiso tsa hau - ho tla hlaha faele eo ho eona ho tla hlalosoa litaelo tsa mathata.

Seo re neng re e-na le sona ke sena:

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.

Kahoo, re ne re ntse re hloka ho fumana li-encodings le ho khutlisa taelo ea khale expire-logs-days.

Karolo ea 2: Ho hlahloba lisebelisoa tse sebetsang

Litokomane tsa ntlafatso li na le lisebelisoa tse 2 bakeng sa ho lekola database bakeng sa ho lumellana. Tšebeliso ea bona e thusa mookameli ho hlahloba ho lumellana ha sebopeho sa data se teng.

Ha re qaleng ka tšebeliso ea khale ea mysqlcheck. Matha feela:

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

Haeba ho se na mathata a fumanoeng, sesebelisoa se tla tsoa ka khoutu 0:

Ntle le moo, sesebelisoa se fumaneha liphetolelong tsa sejoale-joale tsa MySQL mysql-shell (tabeng ea Percona sena ke sephutheloana percona-mysql-shell). Ke sebaka sa moreki oa mysql oa khale mme e kopanya mesebetsi ea moreki, mohlophisi oa khoutu ea SQL le lisebelisoa tsa tsamaiso tsa MySQL. Ho hlahloba seva pele u ntlafatsa, u ka tsamaisa litaelo tse latelang ka eona:

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

Maikutlo ao re a fumaneng ke ana:

Ka kakaretso, ha ho letho le bohlokoa - ke litemoso feela mabapi le li-encodings (sheba ka tlase). Kakaretso ea sephetho sa ts'ebetso:

Re nkile qeto ea hore ntlafatso e lokela ho tsamaea ntle le mathata.

Molaetsa o mabapi le litemoso tse kaholimo tse bonts'ang mathata a likhokahano. 'Nete ke hore UTF-8 ho MySQL ho fihlela morao tjena e ne e se "nete" UTF-8, kaha e bolokile li-byte tse 3 feela sebakeng sa 4. Ho MySQL 8 sena ke qetellong etsa qeto ea ho e lokisa: tse ling utf8 haufinyane e tla lebisa ho khouto utf8mb4, 'me litšiea tsa khale litafoleng li tla fetoha utf8mb3. Khouto e eketsehileng utf8mb3 e tla tlosoa, empa eseng tokollong ena. Ka hona, re nkile qeto ea ho lokisa li-encodings tse seng li ntse li le ts'ebetsong ea DBMS, kamora ho e ntlafatsa.

Karolo ea 3: Lintlafatso tsa Seva

Ke eng e ka senyehang ha ho na le moralo o bohlale joalo?.

Joalokaha ho se ho boletsoe, litokomane tsa molao e akaretsa taba ea ho nchafatsa li-server tsa MySQL ka likopi. Taba ea bohlokoa ke hore o lokela ho qala ka ho ntlafatsa likopi tsohle (makhoba), kaha MySQL 8 e ka ikatisetsa ho tsoa ho mofuta oa master 5.7. Bothata bo bong bo teng tabeng ea hore re sebelisa mokhoa monghali <-> mong, ha mookameli oa hole a le boemong bala feela. Ke hore, ha e le hantle, sephethephethe sa ntoa se ea setsing se le seng sa data, 'me sa bobeli ke backup.

The topology e shebahala tjena:

Ntlafatso e tlameha ho qala ka likopi mysql replica dc 2, mysql master dc 2 и mysql replica dc 1, 'me u qetelle ka seva sa mysql master dc 1. Ho tšepahala haholoanyane, re ile ra emisa mechine ea sebele, ra nka linepe tsa eona, 'me hang-hang pele ntlafatso e emisa ho pheta-pheta ka taelo. STOP SLAVE. Lintlafatso tse ling kaofela li shebahala tjena:

1. Re qala setšoantšo se seng le se seng ka ho kenyelletsa likhetho tse 3 ho li-configs: skip-networking, skip-slave-start, skip-log-bin. 'Nete ke hore ho ntlafatsa database ho hlahisa li-binary logs tse nang le lisebelisoa tsa litafole tsa tsamaiso. Litaelo tsena li tiisa hore ho ke ke ha e-ba le liphetoho ho data ea kopo sebakeng sa polokelo ea boitsebiso, 'me tlhahisoleseding e mabapi le ho ntlafatsa litafole tsa tsamaiso e ke ke ea kenngoa ho li-binary logs. Sena se tla qoba mathata ha o qala ho ikatisa hape.
2. Ho kenya sephutheloana percona-server-server. Ke habohlokoa ho hlokomela hore ho MySQL version 8 ha o hloka ho tsamaisa taelo mysqlupgrade ka mor'a ntlafatso ea seva.
3. Ka mor'a ho qala ka katleho, re qala seva hape - ntle le litekanyo tse kentsoeng serapeng sa pele.
4. Re etsa bonnete ba hore phetisetso e sebetsa ka katleho: hlahloba SHOW SLAVE STATUS 'me u bone hore litafole tse nang le li-counters sebakeng sa polokelo ea lisebelisoa li nchafalitsoe.

Tsohle li shebahala li le bonolo: ntlafatso ea dev e atlehile. Ho lokile, o ka hlophisa ntlafatso ea bosiu bakeng sa tlhahiso ka mokhoa o sireletsehileng.

Ho ne ho se na masoabi - re ntlafalitse prod

Leha ho le joalo, phetisetso ea boiphihlelo bo atlehileng ba dev ho tlhahiso e ne e se ntle le lintho tse makatsang.

Ka lehlohonolo, ts'ebetso ea ntlafatso ka boeona e qala ka li-replicas, kahoo ha re kopana le mathata, re ile ra emisa mosebetsi mme ra khutlisetsa replica ho tsoa ho senepe. Lipatlisiso tsa mathata li ile tsa chechisoa ho fihlela hoseng ho hlahlamang. Li-log li ne li e-na le lintlha tse latelang:

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

Ho etsa lipatlisiso ho li-archives tsa manane a fapaneng a mangolo ho Google ho lebisitse kutloisisong ea hore bothata bona bo hlaha ka lebaka la MySQL bug. Le hoja sena e ka 'na ea e-ba bothata ba lisebelisoa mysqlcheck и mysqlsh.

Ho ile ha fumaneha hore MySQL e fetotse tsela eo e emelang data bakeng sa masimo a decimal (int, tinyint, joalo-joalo), kahoo mysql-server e sebelisa mokhoa o fapaneng oa ho li boloka. Haeba database ea hau qalong e ne e le ka har'a mofuta oa 5.5 kapa 5.1, 'me u ntlafalitse ho 5.7, joale u ka hloka ho e etsa. OPTIMIZE bakeng sa litafole tse ling. Joale MySQL e tla ntlafatsa lifaele tsa data, e li fetisetse ho mokhoa oa hona joale oa polokelo.

U ka boela ua sheba sena ka lisebelisoa 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',
...

haeba field_type Haeba u e-na le eona e lekanang le 0, joale mofuta oa khale o sebelisoa tafoleng - o hloka ho o phetha OPTIMIZE. Leha ho le joalo, haeba boleng ke 246, u se u ntse u e-na le mofuta o mocha. Lintlha tse ling mabapi le mefuta li ka fumanoa ho khoutu.

Ho feta moo, ho kokoanyana ena Re ntse re nahana ka lebaka la bobeli le ka bang teng, le ileng la re feta: ho ba sieo ha litafole tsa InnoDB tafoleng ea sistimi INNODB_SYS_TABLESPACES, haeba tsona, litafole, li entsoe ka mofuta oa 5.1. Ho qoba mathata ha u ntlafatsa, u ka sebelisa script e khomaretsoeng ea SQL.

Hobaneng re ne re se na mathata a joalo ho dev? Database e kopitsoa nako le nako moo ho tsoa tlhahiso - ka hona, litafole li entsoe bocha.

Ka bomalimabe, ho database e kholo e sebetsang hantle, u ke ke ua khona ho nka le ho phethahatsa bokahohle OPTIMIZE. Percona-toolkit e tla thusa mona: pt-online-schema-change utility e ntle haholo bakeng sa ts'ebetso ea OPTIMIZE ea inthaneteng.

Moralo o ntlafalitsoeng o ne o shebahala tjena:

1. Ntlafatsa litafole kaofela.
2. Nchafatsa li-database.

Ho e hlahloba le ka nako e ts'oanang ho tseba nako ea ntlafatso, re ile ra tima e 'ngoe ea likopi mme ra tsamaisa taelo e latelang bakeng sa litafole tsohle:

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

Litafole li nchafatsoa ntle le linotlolo tse telele ka lebaka la hore ts'ebeliso e theha tafole e ncha ea nakoana eo ho eona e kopitsang data ho tsoa tafoleng e kholo. Nakong eo ka eona litafole ka bobeli li tšoanang, tafole ea pele e notletsoe ebe e nkeloa sebaka ke e ncha. Tabeng ea rona, tlhahlobo ea teko e bontšitse hore ho tla nka letsatsi ho ntlafatsa litafole tsohle, empa ho kopitsa data ho bakile mojaro o mongata ho li-disk.

Ho qoba sena, tlhahisong re kentse khang ho taelo --sleep ka boleng ba 10 - parameter ena e fetola bolelele ba ho leta ka mor'a ho fetisetsa palo ea data tafoleng e ncha. Ka tsela ena o ka fokotsa mojaro haeba ts'ebeliso ea 'nete e sebetsang e batla ka nako ea karabelo.

Kamora ho etsa optimization, ntlafatso e atlehile.

... empa eseng ka botlalo!

Nakong ea halofo ea hora ka mor'a ho ntlafatsa, moreki o ile a tla le bothata. Database e sebelitse ka mokhoa o makatsang haholo: nako le nako ba ile ba qala ho tsosolosa khokahano. Ke kamoo e neng e shebahala kateng ha ho behoa leihlo:

Setšoantšo sa skrini se bontša graph ea sawtooth ka lebaka la hore likhoele tse ling tsa seva sa MySQL li ne li oela nako le nako ka phoso. Liphoso li hlahile ts'ebetsong:

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

Tlhahlobo e potlakileng ea li-log e senotse hore mysqld daemon e sitoa ho fumana lisebelisoa tse hlokahalang ho tloha tsamaisong ea ts'ebetso. Ha re ntse re hlophisa liphoso, re ile ra fumana tsamaisong "Likhutsana" lifaele tsa pholisi ea lihlomo:

# 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

Lifaele tsena li entsoe ha li ntlafatsoa ho MySQL 5.7 lilemong tse 'maloa tse fetileng mme ke tsa sephutheloana se tlositsoeng. Ho hlakola lifaele le ho qala ts'ebeletso ea apparmor ho rarollotse bothata:

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

Qetellong

Leha e le efe, esita le ts'ebetso e bonolo ka ho fetisisa, e ka lebisa mathateng a sa lebelloang. Hape esita le ho ba le moralo o nahannoeng hantle hase kamehla ho tiiselitseng liphello tse lebeletsoeng. Hona joale, merero leha e le efe ea ntlafatso eo sehlopha sa rona e nang le eona e boetse e kenyelletsa ho hloekisoa ho tlamang ha lifaele tse sa hlokahaleng tse ka beng li hlahile ka lebaka la liketso tsa morao-rao.

'Me ka boqapi bona bo seng bo hloahloa haholo, ke rata ho leboha Percona haholo ka lihlahisoa tsa bona tse ntle haholo!

PES

Bala hape ho blog ea rona:

• «Databases le Kubernetes (kakaretso le tlaleho ea video)";
• «Malebela le maqheka a Kubernetes: ho potlakisa bootstrap bakeng sa database tse kholo";
• «Lipale tse 6 tse sebetsang tse tsoang bophelong ba rona ba letsatsi le letsatsi ba SRE";
• «Pale e le 'ngoe e nang le opareitara ea Redis ho K8s le tlhahlobo e nyane ea lisebelisoa bakeng sa ho sekaseka data ho tsoa polokelong ena.";
• «Ho falla ho se nang moeli ha MongoDB ho ea Kubernetes".

Source: www.habr.com