Hloov kho MySQL (Percona Server) los ntawm 5.7 txog 8.0

Kev nce qib tsis nyob twj ywm, yog li vim li cas thiaj li hloov mus rau MySQL versions tshiab tau dhau los ua qhov nyuaj. Tsis ntev tas los no, nyob rau hauv ib qho ntawm peb cov haujlwm, nws yog lub sijhawm los hloov kho qhov chaw zoo Percona Server 5.7 pawg rau version 8. Txhua qhov no tau tshwm sim ntawm Ubuntu Linux 16.04 platform. Yuav ua li cas ua haujlwm zoo li no nrog tsawg kawg downtime thiab teeb meem dab tsi peb ntsib thaum hloov tshiab - nyeem hauv kab lus no.

Kev cob qhia

Ib qho kev hloov tshiab ntawm database server feem ntau yuav cuam tshuam nrog database reconfiguration: kev hloov pauv hauv cov kev txwv rau kev txwv ntawm cov peev txheej hauv lub cev thiab kev kho cov database configs uas yuav tsum tau muab tshem tawm ntawm cov lus qhia dhau los.

Ua ntej hloov kho, peb yuav twv yuav raug xa mus rau cov ntaub ntawv raug cai:

• MySQL 8 tso cov ntawv sau;
• MySQL upgrade phau ntawv qhia;
• Percona hloov tshiab phau ntawv qhia;
• MySQL phau ntawv qhia txog kev hloov kho replicas thiab masters.

Thiab cia peb kos ib qho kev npaj ua:

1. Kho cov ntaub ntawv teeb tsa los ntawm kev tshem tawm cov lus qhia tsis tu ncua.
2. Tshawb xyuas kev sib raug zoo nrog cov khoom siv hluav taws xob.
3. Hloov cov qhev databases los ntawm kev txhim kho lub pob percona-server-server.
4. Hloov tshiab tus tswv nrog tib lub pob.

Cia peb saib txhua lub ntsiab lus ntawm txoj kev npaj thiab saib seb yuav ua li cas tsis raug.

TSEEM CEEB! Cov txheej txheem rau kev hloov kho MySQL pawg raws li Galera muaj nws cov subtleties uas tsis tau piav qhia hauv tsab xov xwm. Koj yuav tsum tsis txhob siv cov lus qhia hauv qhov no.

Ntu 1: Tshawb xyuas configs

MySQL raug tshem tawm hauv version 8 query_cache. Qhov tseeb nws yog tshaj tawm tias obsolete rov qab rau hauv version 5.7, tab sis tam sim no tshem tag nrho. Yog li, nws yog ib qho tsim nyog yuav tsum tshem tawm cov lus qhia cuam tshuam. Thiab rau cache thov tam sim no koj tuaj yeem siv cov cuab yeej sab nraud - piv txwv li, ProxySQL.

Tsis tas li ntawd nyob rau hauv lub config muaj outdated directives txog innodb_file_format. Yog tias hauv MySQL 5.7 nws tuaj yeem xaiv InnoDB hom, ces 8th version twb ua haujlwm tsuas yog nrog Barracuda hom ntawv.

Peb qhov txiaj ntsig yog tshem tawm cov lus qhia hauv qab no:

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

Txhawm rau kuaj xyuas, peb yuav siv Docker duab ntawm Percona Server. Peb yuav muab lub server config rau hauv phau ntawv qhia mysql_config_test, thiab ib sab ntawm nws peb yuav tsim cov npe rau cov ntaub ntawv thiab cov cav. Percona-server configuration test piv txwv:

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

Cov kab hauv qab: nyob rau hauv Docker cav lossis hauv phau ntawv teev npe nrog cov cav - nyob ntawm koj qhov kev teeb tsa - cov ntaub ntawv yuav tshwm sim uas cov lus qhia teeb meem yuav raug piav qhia.

Nov yog qhov peb muaj:

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.

Yog li, peb tseem xav tau los txheeb xyuas cov encodings thiab hloov cov lus qhia dhau los expire-logs-days.

Ntu 2: Tshawb xyuas kev teeb tsa ua haujlwm

Cov ntaub ntawv hloov tshiab muaj 2 cov khoom siv rau kev txheeb xyuas cov ntaub ntawv rau kev sib raug zoo. Lawv siv pab tus thawj coj saib xyuas qhov sib haum ntawm cov ntaub ntawv uas twb muaj lawm.

Cia peb pib nrog cov khoom siv mysqlcheck classic. Tsuas yog khiav:

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

Yog tias tsis pom muaj teeb meem, qhov hluav taws xob yuav tawm nrog code 0:

Tsis tas li ntawd, kev siv hluav taws xob muaj nyob rau hauv niaj hnub versions ntawm MySQL mysql-plhaub (nyob rau hauv rooj plaub ntawm Percona qhov no yog pob percona-mysql-shell). Nws yog kev hloov pauv rau cov neeg siv mysql classic thiab sib txuas cov haujlwm ntawm tus neeg siv khoom, tus SQL code editor thiab MySQL cov cuab yeej tswj hwm. Txhawm rau txheeb xyuas lub server ua ntej hloov kho, koj tuaj yeem khiav cov lus txib hauv qab no los ntawm nws:

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

Nov yog cov lus peb tau txais:

Feem ntau, tsis muaj dab tsi tseem ceeb - tsuas yog ceeb toom txog encodings (saib hauv qab no). Zuag qhia tag nrho execution result:

Peb txiav txim siab tias qhov hloov tshiab yuav tsum mus yam tsis muaj teeb meem.

Ib daim ntawv qhia txog cov lus ceeb toom saum toj no qhia txog teeb meem nrog cov encodings. Qhov tseeb yog tias UTF-8 hauv MySQL mus txog rau tam sim no tsis yog "muaj tseeb" UTF-8, txij li nws khaws cia tsuas yog 3 bytes es tsis txhob 4. Hauv MySQL 8 qhov no yog thaum kawg txiav txim siab kho nws: npe utf8 tsis ntev yuav ua rau coding utf8mb4, thiab cov kab qub hauv cov ntxhuav yuav dhau los utf8mb3. Ntxiv encoding utf8mb3 yuav raug tshem tawm, tab sis tsis nyob hauv qhov kev tso tawm no. Yog li ntawd, peb txiav txim siab los kho cov encodings twb nyob rau hauv lub khiav DBMS installation, tom qab muab kho dua nws.

Ntu 3: Kev Hloov Kho Neeg rau zaub mov

Yuav ua li cas yuav mus tsis ncaj ncees lawm thaum muaj xws li ib tug ntse txoj kev npaj?.. Nkag siab tag nrho zoo tias nuances ib txwm tshwm sim, peb ua thawj qhov kev sim ntawm MySQL dev pawg.

Raws li twb tau hais lawm, cov ntaub ntawv raug cai npog qhov teeb meem ntawm kev hloov kho MySQL servers nrog replicas. Cov kab hauv qab yog tias koj yuav tsum xub hloov kho tag nrho cov replicas (cov qhev), txij li MySQL 8 tuaj yeem rov ua dua los ntawm tus tswv version 5.7. Qee qhov teeb meem nyob hauv qhov tseeb tias peb siv hom tswv <-> master, thaum tus tswv chaw taws teeb nyob hauv hom nyeem-nkaus xwb. Ntawd yog, qhov tseeb, kev sib ntaus sib tua mus rau ib lub chaw khaws ntaub ntawv, thiab qhov thib ob yog ib qho thaub qab.

Lub topology zoo li no:

Qhov hloov tshiab yuav tsum pib nrog cov ntawv luam tawm mysql replica dc 2, mysql master dc 2 и mysql replica dc 1, thiab xaus nrog mysql master dc 1 server. Yuav kom ntseeg tau ntau dua, peb nres lub tshuab virtual, coj snapshots ntawm lawv, thiab tam sim ntawd ua ntej qhov hloov tshiab nres replication nrog cov lus txib. STOP SLAVE. Lwm qhov hloov tshiab zoo li no:

1. Peb rov pib dua txhua qhov kev hloov pauv los ntawm kev ntxiv 3 txoj hauv kev rau kev teeb tsa: skip-networking, skip-slave-start, skip-log-bin. Qhov tseeb yog tias kev hloov kho cov ntaub ntawv tsim tawm binary cav nrog kev hloov tshiab rau cov rooj kaw lus. Cov lus qhia no tau lees paub tias yuav tsis muaj kev hloov pauv rau daim ntawv thov cov ntaub ntawv hauv cov ntaub ntawv, thiab cov ntaub ntawv hais txog kev hloov kho cov rooj sib tham yuav tsis suav nrog hauv binary cav. Qhov no yuav zam teeb meem thaum rov pib dua.
2. Txhim kho lub pob percona-server-server. Nws yog ib qho tseem ceeb kom nco ntsoov tias hauv MySQL version 8 tsis koj yuav tsum khiav cov lus txib mysqlupgrade tom qab hloov tshiab server.
3. Tom qab pib ua tiav, peb rov pib dua lub server dua - tsis muaj qhov txwv uas tau ntxiv hauv thawj kab lus.
4. Peb xyuas kom meej tias replication ua haujlwm tiav: kos SHOW SLAVE STATUS thiab pom tias cov ntxhuav nrog cov txee hauv daim ntawv thov database tau hloov kho.

Txhua yam zoo li yooj yim heev: qhov hloov tshiab dev tau ua tiav. Ok, koj tuaj yeem teem sijhawm ua haujlwm hmo ntuj rau kev tsim khoom.

Tsis muaj kev tu siab - peb hloov kho cov khoom

Txawm li cas los xij, kev hloov pauv ntawm kev vam meej dev kev tsim khoom tsis yog tsis muaj qhov xav tsis thoob.

Hmoov zoo, cov txheej txheem hloov tshiab nws tus kheej pib nrog replicas, yog li thaum peb ntsib teeb meem, peb tau tso tseg txoj haujlwm thiab rov qab kho cov replica ntawm snapshot. Kev tshawb nrhiav cov teeb meem raug ncua mus txog rau tag kis sawv ntxov. Cov log tsheb muaj cov ntaub ntawv hauv qab no:

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

Kev tshawb nrhiav cov ntaub ntawv khaws tseg ntawm ntau cov npe xa ntawv hauv Google ua rau nkag siab tias qhov teeb meem no tshwm sim vim MySQL kab laum. Txawm hais tias qhov no feem ntau yuav yog kab laum siv hluav taws xob mysqlcheck и mysqlsh.

Nws hloov tawm tias MySQL hloov txoj kev lawv sawv cev cov ntaub ntawv rau cov lej lej (int, tinyint, thiab lwm yam), yog li mysql-server siv lwm txoj hauv kev los khaws lawv. Yog koj database thaum pib yog nyob rau hauv version 5.5 lossis 5.1, thiab tom qab ntawd koj hloov kho rau 5.7, ces koj yuav tau ua OPTIMIZE rau tej rooj. Tom qab ntawd MySQL yuav hloov kho cov ntaub ntawv cov ntaub ntawv, hloov lawv mus rau hom cia tam sim no.

Koj tuaj yeem tshawb xyuas qhov no nrog cov khoom siv hluav taws xob 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',
...

Yog hais tias tus field_type Yog tias koj muaj qhov sib npaug ntawm 0, ces hom qub yog siv rau hauv lub rooj - koj yuav tsum nqa tawm OPTIMIZE. Txawm li cas los xij, yog tias tus nqi yog 246, koj twb muaj hom tshiab. Xav paub ntau ntxiv txog cov hom tuaj yeem pom hauv chaws.

Ntxiv mus, hauv tus kab mob no Peb tab tom xav txog qhov laj thawj thib ob, uas dhau los ntawm peb: qhov tsis muaj InnoDB cov lus hauv cov lus system INNODB_SYS_TABLESPACES, yog tias lawv, cov ntxhuav, tau tsim hauv version 5.1. Kom tsis txhob muaj teeb meem thaum hloov kho, koj tuaj yeem siv txuas SQL tsab ntawv.

Vim li cas peb tsis muaj teeb meem li dev? Cov ntaub ntawv khaws cia ib ntus theej muaj los ntawm kev tsim khoom - yog li, cov rooj rov tsim dua.

Hmoov tsis zoo, ntawm cov ntaub ntawv loj ua haujlwm tiag tiag, koj yuav tsis tuaj yeem tsuas yog coj thiab ua haujlwm thoob ntiaj teb. OPTIMIZE. percona-toolkit yuav pab tau ntawm no: pt-online-schema-hloov kev siv hluav taws xob yog qhov zoo heev rau kev ua haujlwm hauv online OPTIMIZE.

Txoj kev npaj kho tshiab zoo li no:

1. Optimize tag nrho cov rooj.
2. Hloov kho cov databases.

Txhawm rau txheeb xyuas nws thiab tib lub sijhawm pom lub sijhawm hloov tshiab, peb tau xiam ib qho ntawm cov replicas thiab khiav cov lus txib hauv qab no rau txhua lub rooj:

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

Cov ntxhuav tau hloov kho yam tsis muaj qhov xauv ntev vim qhov tseeb tias qhov hluav taws xob tsim ib lub rooj tshiab ib ntus uas nws luam cov ntaub ntawv los ntawm lub rooj loj. Thaum lub sij hawm thaum ob lub rooj zoo ib yam, lub rooj qub raug kaw thiab hloov nrog lub tshiab. Hauv peb qhov xwm txheej, kev sim ua haujlwm tau pom tias nws yuav siv sijhawm li ib hnub los hloov kho txhua lub rooj, tab sis kev luam cov ntaub ntawv ua rau muaj ntau dhau ntawm cov disks.

Txhawm rau zam qhov no, hauv kev tsim khoom peb ntxiv qhov kev sib cav rau cov lus txib --sleep nrog tus nqi ntawm 10 - qhov ntsuas no kho qhov ntev tos tom qab hloov cov ntaub ntawv mus rau lub rooj tshiab. Txoj kev no koj tuaj yeem txo cov load yog tias daim ntawv thov khiav tiag tiag xav tau ntawm lub sijhawm teb.

Tom qab ua qhov optimization, qhov hloov tshiab tau ua tiav.

... tab sis tsis tag!

Tsis pub dhau ib nrab teev tom qab qhov hloov tshiab, tus neeg siv khoom tuaj nrog teeb meem. Lub database ua haujlwm txawv heev: ib ntus lawv pib kev sib txuas pib dua. Nov yog qhov nws zoo li hauv kev saib xyuas:

Cov duab thaij duab qhia pom cov duab sawtooth vim qhov tseeb tias qee qhov ntawm MySQL server xov xwm ib ntus poob nrog qhov yuam kev. Qhov yuam kev tshwm sim hauv daim ntawv thov:

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

Kev tshuaj xyuas sai ntawm cov cav tau qhia tias mysqld daemon tsis tuaj yeem tau txais cov peev txheej xav tau los ntawm kev ua haujlwm. Thaum txheeb xyuas qhov yuam kev, peb pom hauv qhov system "orphan" apparmor txoj cai cov ntaub ntawv:

# 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

Cov ntaub ntawv no tau tsim thaum kho dua tshiab rau MySQL 5.7 ob peb xyoos dhau los thiab koom nrog lub pob tshem tawm. Rho tawm cov ntaub ntawv thiab rov pib qhov kev pabcuam apparmor daws qhov teeb meem:

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

Nyob rau hauv xaus

Ib qho twg, txawm tias qhov haujlwm yooj yim tshaj plaws, tuaj yeem ua rau muaj teeb meem tsis txaus ntseeg. Thiab txawm tias muaj ib txoj kev xav zoo los yeej tsis lav qhov kev xav tau. Tam sim no, cov phiaj xwm hloov tshiab peb pab neeg kuj tseem suav nrog kev yuav tsum tau ntxuav cov ntaub ntawv tsis tsim nyog uas tuaj yeem tshwm sim los ntawm kev ua tsis ntev los no.

Thiab nrog qhov no tsis yog kev tshaj lij nraaj creativity, Kuv xav hais ua tsaug ntau rau Percona rau lawv cov khoom zoo heev!

PS

Nyeem kuj ntawm peb blog:

• «Databases thiab Kubernetes (xyuas thiab tshaj tawm video)»;
• «Kubernetes cov lus qhia & kev ua kom yuam kev: ua kom nrawm dua bootstrap rau cov ntaub ntawv loj»;
• «6 zaj dab neeg siv tau los ntawm peb SRE lub neej txhua hnub»;
• «Ib zaj dab neeg nrog Redis tus neeg teb xov tooj hauv K8s thiab kev tshuaj xyuas me me ntawm cov khoom siv rau kev tshuaj xyuas cov ntaub ntawv los ntawm cov ntaub ntawv no»;
• «Seamless migration ntawm MongoDB rau Kubernetes".

Tau qhov twg los: www.hab.com