é²æ©ã¯æ¢ãŸããªããããMySQL ã®ææ°ããŒãžã§ã³ã«ã¢ããã°ã¬ãŒãããçç±ã¯ãŸããŸã説åŸåãå¢ããŠããŸãã å°ãåãç§ãã¡ã®ãããžã§ã¯ãã® 5.7 ã€ã§ãå¿«é©ãª Percona Server 8 ã¯ã©ã¹ã¿ãŒãããŒãžã§ã³ 16.04 ã«æŽæ°ããææãæ¥ãŸããã ããããã¹ãŠã¯ Ubuntu Linux XNUMX ãã©ãããã©ãŒã ã§çºçããŸããã ããŠã³ã¿ã€ã ãæå°éã«æããŠãã®ãããªæäœãå®è¡ããæ¹æ³ãšãæŽæ°äžã«çºçããåé¡ã«ã€ããŠã¯ããã®èšäºããèªã¿ãã ããã
èšç·Ž
ããŒã¿ããŒã¹ ãµãŒããŒã®æŽæ°ã¯ããŒã¿ããŒã¹ã®åæ§æã«é¢é£ããŠããå¯èœæ§ãé«ããã·ã¹ãã ãªãœãŒã¹ã®å¶éã«é¢ããèŠä»¶ã®å€æŽããå€ããã£ã¬ã¯ãã£ããåé€ããå¿ èŠãããããŒã¿ããŒã¹æ§æã®ä¿®æ£ãè¡ãããŸãã
æŽæ°ããåã«ãå¿ ãå ¬åŒããã¥ã¡ã³ããåç §ããŸãã
-
MySQL 8 ãªãªãŒã¹ããŒã ; -
MySQL ã¢ããã°ã¬ãŒã ã¬ã€ã ; -
Percona ã¢ããããŒãã¬ã€ã ; -
ã¬ããªã«ãšãã¹ã¿ãŒãæŽæ°ããããã® MySQL ã¬ã€ã .
ãããŠãè¡åèšç»ãç«ãŠãŸãããã
- å€ããã£ã¬ã¯ãã£ããåé€ããŠæ§æãã¡ã€ã«ãä¿®æ£ããŸãã
- ãŠãŒãã£ãªãã£ãšã®äºææ§ã確èªããŸãã
- ããã±ãŒãžãã€ã³ã¹ããŒã«ããŠã¹ã¬ãŒãããŒã¿ããŒã¹ãæŽæ°ãã
percona-server-server
. - åãããã±ãŒãžã§ãã¹ã¿ãŒãæŽæ°ããŸãã
èšç»ã®åãã€ã³ããèŠãŠãäœãåé¡ã«ãªãå¯èœæ§ãããããèŠãŠã¿ãŸãããã
éèŠïŒ Galera ã«åºã¥ã㊠MySQL ã¯ã©ã¹ã¿ãŒãæŽæ°ããæé ã«ã¯ãèšäºã§ã¯èª¬æãããŠããªãç¬èªã®åŸ®åŠãªç¹ããããŸãã ãã®å Žåããã®åœä»€ã¯äœ¿çšããªãã§ãã ããã
ããŒã 1: æ§æã®ç¢ºèª
MySQL ã¯ããŒãžã§ã³ 8 ã§åé€ãããŸãã query_cache
ã å®ã¯åœŒã¯ããã§ãã
ãŸããèšå®ã«ã¯ãã«é¢ããå€ããã£ã¬ã¯ãã£ãããããŸãã innodb_file_format
ã MySQL 5.7 㧠InnoDB 圢åŒãéžæã§ããå Žåã8 çªç®ã®ããŒãžã§ã³ã¯ãã§ã«åäœããŸãã
ãã®çµæã次ã®ãã£ã¬ã¯ãã£ããåé€ãããŸããã
-
query_cache_type
,query_cache_limit
Оquery_cache_size
; -
innodb_file_format
Оinnodb_file_format_max
.
確èªããã«ã¯ãPercona ãµãŒããŒã® Docker ã€ã¡ãŒãžã䜿çšããŸãã ãµãŒããŒæ§æããã£ã¬ã¯ããªã«é
眮ããŸã mysql_config_test
ããã®é£ã«ããŒã¿ãšãã°çšã®ãã£ã¬ã¯ããªãäœæããŸãã 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
çµè«: æ§æã«å¿ããŠãDocker ãã°ãŸãã¯ãã°ã®ãããã£ã¬ã¯ããªã«ãåé¡ã®ãããã£ã¬ã¯ãã£ããèšè¿°ããããã¡ã€ã«ã衚瀺ãããŸãã
ç§ãã¡ãæã£ãŠãããã®ã¯æ¬¡ã®ãšããã§ãã
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.
ãããã£ãŠããšã³ã³ãŒãã£ã³ã°ãç解ããå€ããã£ã¬ã¯ãã£ãã眮ãæããå¿
èŠããããŸããã expire-logs-days
.
ããŒã 2: åäœããŠããã€ã³ã¹ããŒã«ã®ç¢ºèª
ã¢ããããŒãã®ããã¥ã¡ã³ãã«ã¯ãããŒã¿ããŒã¹ã®äºææ§ããã§ãã¯ããããã® 2 ã€ã®ãŠãŒãã£ãªãã£ãå«ãŸããŠããŸãã ãããã䜿çšãããšã管çè ãæ¢åã®ããŒã¿æ§é ã®äºææ§ã確èªããã®ã«åœ¹ç«ã¡ãŸãã
å€å žç㪠mysqlcheck ãŠãŒãã£ãªãã£ããå§ããŸãããã 以äžãå®è¡ããã ãã§ãã
mysqlcheck -u root -p --all-databases --check-upgrade
åé¡ãèŠã€ãããªãã£ãå ŽåããŠãŒãã£ãªãã£ã¯ã³ãŒã 0 ã§çµäºããŸãã
ããã«ãææ°ããŒãžã§ã³ã® MySQL ã§ã¯ãŠãŒãã£ãªãã£ãå©çšå¯èœã§ã percona-mysql-shell
ïŒã ããã¯ãå€å
žç㪠mysql ã¯ã©ã€ã¢ã³ãã®ä»£æ¿åã§ãããã¯ã©ã€ã¢ã³ããSQL ã³ãŒã ãšãã£ã¿ãããã³ MySQL 管çããŒã«ã®æ©èœãçµã¿åãããŠããŸãã æŽæ°ããåã«ãµãŒããŒã確èªããã«ã¯ããµãŒããŒãéããŠæ¬¡ã®ã³ãã³ããå®è¡ããŸãã
mysqlsh -- util check-for-server-upgrade { --user=root --host=1.1.1.1 --port=3306 } --config-path=/etc/mysql/my.cnf
ããã ããã³ã¡ã³ãã¯æ¬¡ã®ãšããã§ãã
äžè¬ã«ãéèŠãªããšã¯äœããããŸããããšã³ã³ãŒãã£ã³ã°ã«é¢ããèŠåã®ã¿ã衚瀺ãããŸãã ïŒäžèšåç
§ïŒã å
šäœçãªå®è¡çµæ:
ã¢ããããŒãã¯åé¡ãªãå®äºãããšå€æããŸããã
ãšã³ã³ãŒãã£ã³ã°ã®åé¡ã瀺ãäžèšã®èŠåã«é¢ããã¡ã¢ã å®ã¯ãMySQL ã§ã¯æè¿ãŸã§ UTF-8 ã䜿çšãããŠããŸããã utf8
ããã«ã³ãŒãã£ã³ã°ã«ã€ãªãããŸã utf8mb4
ãããŒãã«å
ã®å€ãåã¯æ¬¡ã®ããã«ãªããŸãã utf8mb3
ã ããã«ãšã³ã³ãŒããã utf8mb3
åé€ãããäºå®ã§ããããã®ãªãªãŒã¹ã§ã¯åé€ãããŸããã ãããã£ãŠãå®è¡äžã® DBMS ã€ã³ã¹ããŒã«ã«æ¢ã«ååšãããšã³ã³ãŒãã£ã³ã°ãæŽæ°åŸã«ä¿®æ£ããããšã«ããŸããã
ããŒã 3: ãµãŒããŒã®ã¢ããããŒã
ãã®ãããªè³¢æãªèšç»ãããã®ã«ãäœãåé¡ã«ãªãã®ã§ãããã? 埮åŠãªéããåžžã«çºçããããšãååã«ç解ããäžã§ãç§ãã¡ã¯ MySQL éçºã¯ã©ã¹ã¿ãŒã§æåã®å®éšãå®æœããŸããã
ãã§ã«è¿°ã¹ãããã«
ããããžã¯æ¬¡ã®ããã«ãªããŸãã
æŽæ°ã¯ã¬ããªã«ããéå§ããå¿
èŠããããŸã mysqlã¬ããªã«dc2, mysqlãã¹ã¿ãŒDC2 О mysql replica dc 1
ãmysql ãã¹ã¿ãŒ dc 1 ãµãŒããŒã§çµäºããŸããä¿¡é Œæ§ãé«ããããã«ãä»®æ³ãã·ã³ãåæ¢ããã¹ãããã·ã§ãããååŸããæŽæ°ã®çŽåã«æ¬¡ã®ã³ãã³ãã§ã¬ããªã±ãŒã·ã§ã³ãåæ¢ããŸããã STOP SLAVE
ã ã¢ããããŒãã®æ®ãã®éšåã¯æ¬¡ã®ããã«ãªããŸãã
- æ§æã« 3 ã€ã®ãªãã·ã§ã³ãè¿œå ããŠãåã¬ããªã«ãåèµ·åããŸãã
skip-networking
,skip-slave-start
,skip-log-bin
ã å®éãããŒã¿ããŒã¹ãæŽæ°ãããšãã·ã¹ãã ããŒãã«ã®æŽæ°ãå«ããã€ã㪠ãã°ãçæãããŸãã ãããã®ãã£ã¬ã¯ãã£ãã¯ãããŒã¿ããŒã¹å ã®ã¢ããªã±ãŒã·ã§ã³ ããŒã¿ãå€æŽãããªãããšãããã³ã·ã¹ãã ããŒãã«ã®æŽæ°ã«é¢ããæ å ±ããã€ã㪠ãã°ã«å«ãŸããªãããšãä¿èšŒããŸãã ããã«ãããã¬ããªã±ãŒã·ã§ã³ãåéãããšãã®åé¡ãåé¿ãããŸãã - ããã±ãŒãžã®ã€ã³ã¹ããŒã«
percona-server-server
ã MySQL ããŒãžã§ã³ 8 ã§ã¯æ¬¡ã®ç¹ã«æ³šæããããšãéèŠã§ãã ã㌠ã³ãã³ããå®è¡ããå¿ èŠããããŸãmysqlupgrade
ãµãŒããŒæŽæ°åŸã - æ£åžžã«èµ·åããåŸãæåã®æ®µèœã§è¿œå ãããã©ã¡ãŒã¿ã䜿çšããã«ãµãŒããŒãåèµ·åããŸãã
- ã¬ããªã±ãŒã·ã§ã³ãæ£åžžã«æ©èœããããšã確èªããŸãã
SHOW SLAVE STATUS
ã¢ããªã±ãŒã·ã§ã³ ããŒã¿ããŒã¹å ã®ã«ãŠã³ã¿ãŒãå«ãããŒãã«ãæŽæ°ãããŠããããšã確èªããŸãã
ãã¹ãŠã¯éåžžã«åçŽã«èŠããŸããéçºã¢ããããŒãã¯æåããŸããã ããããŸãããæ¬çªç°å¢ã®å€éæŽæ°ãå®å šã«ã¹ã±ãžã¥ãŒã«ã§ããŸãã
æ²ãã¿ã¯ãããŸããã§ãã - 補åãæŽæ°ããŸãã
ãã ããæåããéçºãšã¯ã¹ããªãšã³ã¹ãå®çšŒåç°å¢ã«ç§»è¡ããã«ã¯ãé©ãã¹ãããšããªãã£ãããã§ã¯ãããŸããã
幞ããªããšã«ãæŽæ°ããã»ã¹èªäœã¯ã¬ããªã«ããå§ãŸããããåé¡ãçºçããå Žåã¯äœæ¥ãåæ¢ããã¹ãããã·ã§ããããã¬ããªã«ã埩å ããŸããã åé¡ã®èª¿æ»ã¯ç¿æãŸã§å»¶æãããã ãã°ã«ã¯æ¬¡ã®ãšã³ããªãå«ãŸããŠããŸããã
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
Google äžã®ããŸããŸãªã¡ãŒãªã³ã° ãªã¹ãã®ã¢ãŒã«ã€ãã調æ»ãããšããããã®åé¡ã¯æ¬¡ã®åå ã§çºçããŠããããšãããããŸããã mysqlcheck
О mysqlsh
.
MySQL ã XNUMX é²æ°ãã£ãŒã«ã (intãtinyint ãªã©) ã®ããŒã¿ãè¡šçŸããæ¹æ³ãå€æŽãããããmysql-server ã¯ããããæ ŒçŽããããã«å¥ã®æ¹æ³ã䜿çšããŠããããšãããããŸããã ããŒã¿ããŒã¹ã®å Žå åã㯠ããŒãžã§ã³ 5.5 ãŸã㯠5.1 ã§ããã®åŸ 5.7 ã«æŽæ°ããå Žåã¯ã次ã®æäœãå¿
èŠã«ãªãå ŽåããããŸãã OPTIMIZE
äžéšã®ããŒãã«çšã 次ã«ãMySQL ã¯ããŒã¿ ãã¡ã€ã«ãæŽæ°ããçŸåšã®ã¹ãã¬ãŒãžåœ¢åŒã«è»¢éããŸãã
ããã¯ãŠãŒãã£ãªãã£ã§ã確èªã§ããŸã 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',
...
ãã field_type
ããã 0 ã«çããå Žåãå€ãã¿ã€ããããŒãã«ã§äœ¿çšãããŸãã次ã®ããšãå®è¡ããå¿
èŠããããŸãã OPTIMIZE
ã ãã ããå€ã 246 ã®å Žåã¯ããã§ã«æ°ããã¿ã€ããååšããŸãã ã¿ã€ãã®è©³çŽ°ã«ã€ããŠã¯ã次ãåç
§ããŠãã ããã
ãŸãã INNODB_SYS_TABLESPACES
ãããŒãã«ãããŒãžã§ã³ 5.1 ã§äœæãããå Žåã æŽæ°æã®åé¡ãåé¿ããã«ã¯ã次ã®ããã«ããŸãã
ãªãéçºæã«ãã®ãããªåé¡ãçºçããªãã£ãã®ã§ãããã? ããŒã¿ããŒã¹ã¯éçšç°å¢ããå®æçã«ã³ããŒãããŸããã€ãŸãã ããŒãã«ãåäœæããã.
æ®å¿µãªãããå®éã«åäœãã倧èŠæš¡ãªããŒã¿ããŒã¹ã§ã¯ãæ±çšçãªããŒã¿ããŒã¹ãåã«ååŸããŠå®è¡ããããšã¯ã§ããŸããã OPTIMIZE
ã percona-toolkit ãããã§åœ¹ã«ç«ã¡ãŸããpt-online-schema-change ãŠãŒãã£ãªãã£ã¯ããªã³ã©ã€ã³ã® OPTIMIZE æäœã«åªããŠããŸãã
æŽæ°ãããèšç»ã¯æ¬¡ã®ããã«ãªããŸãã
- ãã¹ãŠã®ããŒãã«ãæé©åããŸãã
- ããŒã¿ããŒã¹ãæŽæ°ããŸãã
ããã確èªããåæã«æŽæ°æéã調ã¹ãããã«ãã¬ããªã«ã® XNUMX ã€ãç¡å¹ã«ãããã¹ãŠã®ããŒãã«ã«å¯ŸããŠæ¬¡ã®ã³ãã³ããå®è¡ããŸããã
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
ãŠãŒãã£ãªãã£ã¯æ°ããäžæããŒãã«ãäœæããããã«ã¡ã€ã³ ããŒãã«ããããŒã¿ãã³ããŒãããããããŒãã«ã¯é·æéã®ããã¯ãªãã§æŽæ°ãããŸãã äž¡æ¹ã®ããŒãã«ãåäžã«ãªã£ãæç¹ã§ãå ã®ããŒãã«ã¯ããã¯ãããæ°ããããŒãã«ã«çœ®ãæããããŸãã ç§ãã¡ã®å Žåããã¹ãå®è¡ã®çµæããã¹ãŠã®ããŒãã«ãæŽæ°ããã«ã¯çŽ XNUMX æ¥ãããããšãããããŸããããããŒã¿ã®ã³ããŒã«ãããã£ã¹ã¯ã«é床ã®è² è·ãããããŸããã
ãããåé¿ããããã«ãéçšç°å¢ã§ã¯ã³ãã³ãã«åŒæ°ãè¿œå ããŸããã --sleep
å€ã 10 ã®å Žåããã®ãã©ã¡ãŒã¿ã¯ããŒã¿ã®ããããæ°ããããŒãã«ã«è»¢éããåŸã®åŸ
æ©æéã調æŽããŸãã ããã«ãããå®éã«å®è¡äžã®ã¢ããªã±ãŒã·ã§ã³ãå¿çæéãèŠæ±ããå Žåã«è² è·ã軜æžã§ããŸãã
æé©åãå®è¡ããåŸãæŽæ°ã¯æåããŸããã
...ã§ãå®å šã§ã¯ãããŸãã!
ã¢ããããŒãåŸ XNUMX å以å ã«ãã¯ã©ã€ã¢ã³ãã«åé¡ãçºçããŸããã ããŒã¿ããŒã¹ã¯éåžžã«å¥åŠã«åäœããŸãããå®æçã«ããŒã¿ããŒã¹ãèµ·åããŸããã æ¥ç¶ã®ãªã»ããã ã¢ãã¿ãªã³ã°ã§ã¯ãããªæãã§ããã
ã¹ã¯ãªãŒã³ã·ã§ããã«ã¯ãMySQL ãµãŒã㌠ã¹ã¬ããã®äžéšããšã©ãŒã§å®æçã«ã¯ã©ãã·ã¥ãããããã®ãããæ³¢ã°ã©ãã衚瀺ãããŸãã ã¢ããªã±ãŒã·ã§ã³ã«ãšã©ãŒã衚瀺ãããŸãã:
[PDOException] SQLSTATE[HY000] [2002] Connection refused
ãã°ãç°¡åã«èª¿ã¹ããšãããmysqld ããŒã¢ã³ããªãã¬ãŒãã£ã³ã° ã·ã¹ãã ããå¿ èŠãªãªãœãŒã¹ãååŸã§ããªãããšãããããŸããã ãšã©ãŒãæŽçããŠãããšãã«ãã·ã¹ãã å ã§çºèŠãã ãå€ç«ãããApparmor ããªã·ãŒ ãã¡ã€ã«:
# 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
ãããã®ãã¡ã€ã«ã¯ãæ°å¹Žåã« MySQL 5.7 ã«ã¢ããã°ã¬ãŒããããšãã«äœæãããåé€ãããããã±ãŒãžã«å±ããŠããŸãã ãã¡ã€ã«ãåé€ã㊠apparmor ãµãŒãã¹ãåèµ·åãããšãåé¡ã解決ããŸããã
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
çµè«
æãåçŽãªæäœã§ãã£ãŠããäºæããªãåé¡ãçºçããå¯èœæ§ããããŸãã ãŸãã綿å¯ã«èšç»ãããšããŠããå¿ ãããæåŸ ããçµæãä¿èšŒãããããã§ã¯ãããŸããã çŸåšãç§ãã¡ã®ããŒã ã®æŽæ°èšç»ã«ã¯ãæè¿ã®ã¢ã¯ã·ã§ã³ã®çµæãšããŠçºçããå¯èœæ§ã®ããäžèŠãªãã¡ã€ã«ã®åŒ·å¶çãªåé€ãå«ãŸããŠããŸãã
ããŸããããã§ãã·ã§ãã«ãšã¯ãããªãã°ã©ãã£ãã¯ã®åµé åã§ãããåªãã補åãæäŸããŠããã Percona ã«å€å€§ãªæè¬ãç³ãäžããããšæããŸãã
PS
ç§ãã¡ã®ããã°ããèªã¿ãã ãã:
- «
ããŒã¿ããŒã¹ãš Kubernetes (ã¬ãã¥ãŒãšãã㪠ã¬ããŒã) "; - «
Kubernetes ã®ãã³ããšã³ã: 倧èŠæš¡ããŒã¿ããŒã¹ã®ããŒãã¹ãã©ãããé«éåãã "; - «
SRE ã®æ¥åžžç掻ããåŸã 6 ã€ã®å®è·µçãªã¹ããŒãªãŒ "; - «
K8s ã® Redis ãªãã¬ãŒã¿ãŒã«é¢ãã XNUMX ã€ã®ã¹ããŒãªãŒãšããã®ããŒã¿ããŒã¹ã®ããŒã¿ãåæããããã®ãŠãŒãã£ãªãã£ã®ããã¬ãã¥ãŒ "; - «
MongoDB ãã Kubernetes ãžã®ã·ãŒã ã¬ã¹ãªç§»è¡ 'ã
åºæïŒ habr.com