ืคึผืจืึธืืจืขืก ืืื ื ืืฉื ืฉืืืื ื ืึธื, ืึทืืื ืื ืกืืืืช ืฆื ืึทืคึผืืจืืื ืฆื ืื ืืขืฆืืข ืืืขืจืกืืขืก ืคืื MySQL ืืืขืจื ืื ืงืจืืกืื ืืื ืงืึทืืคึผืขืืื ื. ื ืื ืืึทื ื ืฆืืจืืง, ืืื ืืืื ืขืจ ืคืื ืืื ืืืขืจ ืคึผืจืึทืืืฉืขืงืก, ืขืก ืืื ืืขืืืขื ืฆืืื ืฆื ืืขืจืืืึทื ืืืงื ืื ืืืืืืฉ ืคึผืขืจืงืึธื ืึท ืกืขืจืืืืจืขืจ 5.7 ืงืืึทืกืืขืจื ืฆื ืืืขืจืกืืข 8. ืึทืืข ืืขื ืืขืืจืืคื ืืืืฃ ืื Ubuntu Linux 16.04 ืคึผืืึทืืคืึธืจืืข. ืืื ืฆื ืืืจืืคืืจื ืึทืืึท ืึท ืึธืคึผืขืจืึทืฆืืข ืืื ืืื ืืืึทื ืืึทืื ืืืื ืืื ืืืึธืก ืคึผืจืึธืืืขืืก ืืืจ ืืึธืื ืืขืคึผืืึธื ืืขืจื ืืขืฉืึทืก ืื ืืขืจืืืึทื ืืืงื - ืืืืขื ืขื ืืื ืืขื ืึทืจืืืงื.
ืืจืึทืื ืื ื
ืงืืื ืืขืจืืืึทื ืืืงื ืคืื ืื ืืึทืืึทืืืืก ืกืขืจืืืขืจ ืืื ืจืืึฟ ืืกืชึผืื ืคึฟืึทืจืืื ืื ืืื ืืึทืืึทืืืืก ืจืืงืึทื ืคืืืืขืจืืืฉืึทื: ืขื ืืขืจืื ืืขื ืืื ืจืขืงืืืืจืขืืขื ืฅ ืคึฟืึทืจ ืืืืึทืฅ ืืืืฃ ืกืืกืืขื ืจืขืกืืจืกื ืืื ืงืขืจืขืงืฉืึทื ืคืื ืืืืืึทืืืืก ืงืึทื ืคืืืืขืจืื ืืืึธืก ืืึทืจืคึฟื ืฆื ืืืื ืงืืืจื ืคืื ืึทืืืืืืืื ืืืืจืขืงืืืืื.
ืืืืืขืจ ืืขืจ ืืขืจืืืึทื ืืืงื, ืืืจ ืืืขืื ืืืฉืืืื ืึธืคึผืฉืืงื ืฆื ืืขืจ ืืึทืึทืืืขืจ ืืึทืงืืืืขื ืืืืฉืึทื:
- ;
- ;
- ;
- .
ืืื ืืึธืืืจ ืืึทืื ืึทื ืึทืงืฆืืข ืคึผืืึทื:
- ืจืืืืืง ืงืึทื ืคืืืืขืจืืืฉืึทื ืืขืงืขืก ืืืจื ืจืืืืืืื ื ืึทืืืืืืืื ืืืืจืขืงืืืืื.
- ืงืึธื ืืจืึธืืืจื ืงืึทืืคึผืึทืืึทืืืืึทืื ืืื ืืืืืืึทืืื.
- ืืขืจืืืึทื ืืืงื ืฉืงืืึทืฃ ืืึทืืึทืืืืกืื ืืืจื ืื ืกืืึธืืื ื ืืขื ืคึผืขืงื
percona-server-server. - ืืขืจืืืึทื ืืืงื ืื ืืขื ืืื ืืขืจ ืืขืืืืงืขืจ ืคึผืขืงื.
ืืื ืก ืงืืง ืืืึท ืืขืืขืจ ืคืื ื ืคืื ืื ืคึผืืึทื ืืื ืืขื ืืืึธืก ืงืขื ืืืื ืคืึทืืฉ.
ืืืืืืืง! ืืขืจ ืคึผืจืึธืฆืขืืืจ ืคึฟืึทืจ ืึทืคึผืืืืืื ื ืึท MySQL ืงื ืืื ืืืืืจื ืืืืฃ Galera ืืื ืืืื ืืืืืขื ืข ืกืึทืืึทืืืื ืืืึธืก ืืขื ืขื ื ืืฉื ืืืกืงืจืืืื ืืื ืืขื ืึทืจืืืงื. ืืืจ ืืึธื ื ืืฉื ื ืืฆื ืืขื ืืืืขื ืืื ืืขื ืคืึทื.
ืืืื 1: ืงืึธื ืืจืึธืืืจืื ื ืงืึทื ืคืืืืขืจืืืฉืึทื ื
MySQL ืืื ืึทืืืขืงืืขื ืืืขื ืืื ืืืขืจืกืืข 8 query_cache. ืืืขืฉื ืืื ืขืจ ืืขืืืขื ืฆืืจืืง ืืื ืืืขืจืกืืข 5.7, ืึธืืขืจ ืืืฆื . ืึทืงืงืึธืจืืื ืืื, ืขืก ืืื ื ืืืืืง ืฆื ืืึทืืืึทืืืงื ืื ืคืืจืืื ืื ืืืืจืขืงืืืืื. ืืื ืฆื ืงืึทืฉ ืจืืงืืืขืก ืืืจ ืงืขื ืขื ืืืฆื ื ืืฆื ืคืื ืืจืืืกื ืืืง ืืืฉืืจืื - ืืืฉื, .
ืืืื ืืื ืื ืงืึทื ืคืืืืขืจืืืฉืึทื ืขืก ืืขื ืขื ืึทืืืืืืืื ืืืืจืขืงืืืืื ืืืขืื innodb_file_format. ืืืื ืืื MySQL 5.7 ืขืก ืืื ืืขืืืขื ืฆื ืกืขืืขืงืืืจื ืืขื InnoDB ืคึฟืึธืจืืึทื, ืื 8 ืืืขืจืกืืข ืฉืืื ืึทืจืืขื .
ืืื ืืืขืจ ืจืขืืืืืึทื ืืื ืื ืืึทืืืึทืืืงืื ื ืคืื ืื ืคืืืืขื ืืข ืืืืจืขืงืืืืื:
-
query_cache_type,query_cache_limitะธquery_cache_size; -
innodb_file_formatะธinnodb_file_format_max.
ืฆื ืงืึธื ืืจืึธืืืจื, ืืืจ ืืืขืื ื ืืฆื ืื ืืึธืงืงืขืจ ืืืื ืคืื ืคึผืขืจืงืึธื ืึท ืกืขืจืืืืจืขืจ. ืืืจ ืฉืืขืื ืื ืกืขืจืืืืจืขืจ ืงืึทื ืคืืืืขืจืืืฉืึทื ืืื ืื ืืืขืืืืืึทืืขืจ mysql_config_test, ืืื ืืืืึทืืขืจ ืฆื ืืื, ืืืจ ืืืขืื ืฉืึทืคึฟื ืืืจืขืงืืขืจืื ืคึฟืึทืจ ืืึทืื ืืื ืืึธืืก. ืคึผืขืจืงืึธื ืึท-ืกืขืจืืืขืจ ืงืึทื ืคืืืืขืจืืืฉืึทื ืืขืกื ืืืึทืฉืคึผืื:
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ืื ืึธ ืฉืืจื: ืึธืืขืจ ืืื ืื ืืึธืงืงืขืจ ืืึธืืก ืึธืืขืจ ืืื ืื ืืืขืืืืืึทืืขืจ ืืื ืื ืืึธืืก - ืืืคึผืขื ืืื ื ืืืืฃ ืืืื ืงืึธื ืคืืืก - ืึท ืืขืงืข ืืืขื ืืขืจืฉืืึทื ืขื ืืื ืืืึธืก ืื ืคึผืจืึธืืืขืืึทืืืง ืืืืจืขืงืืืืื ืืืขื ืืืื ืืืกืงืจืืืื.
ืืึธ ืก ืืืึธืก ืืืจ ืืึธืื:
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). ืขืก ืืื ืึท ืคืึทืจืืืึทื ืคึฟืึทืจ ืืขืจ ืงืืึทืกืืฉ ืืืกืงื ืงืืืขื ื ืืื ืงืึทืืืืื ื ืื ืคืึทื ืืงืฉืึทื ื ืคืื ืึท ืงืืืขื ื, ืึท ืกืงื ืงืึธื ืจืขืืึทืงืืึธืจ ืืื ืืืกืงื ืึทืืืื ืืกืืจืึทืฆืืข ืืืฉืืจืื. ืฆื ืงืึธื ืืจืึธืืืจื ืืขื ืกืขืจืืืืจืขืจ ืืืืืขืจ ืึทืคึผืืืืืื ื, ืืืจ ืงืขื ืขื ืืืืคื ืื ืคืืืืขื ืืข ืงืึทืืึทื ืื ืืืจื ืืื:
mysqlsh -- util check-for-server-upgrade { --user=root --host=1.1.1.1 --port=3306 } --config-path=/etc/mysql/my.cnfืืึธ ืืขื ืขื ืื ืืึทืืขืจืงืื ืืขื ืืืึธืก ืืืจ ืืืงืืืขื:
ืืื ืึทืืืขืืืื, ืืึธืจื ืืฉื ืงืจืืืืฉ - ื ืึธืจ ืืืึธืจื ืื ืื ืืืขืื ืงืึธืืืจืื ื (ืืข ืืื ืื). ืงืืืืขืืืืง ืืืจืืคืืจืื ื ืจืขืืืืืึทื:
ืืืจ ืืึทืฉืืึธืกื ืึทื ืืขืจ ืืขืจืืืึทื ืืืงื ืืึธื ืืืื ืึธื ืคืจืืืืขืืขื.
ื ืืึทืืขืจืงืื ื ืืืขืื ืื ืืืึธืจื ืื ืื ืืืืื ืืืึธืก ืื ืืืงืืืฅ ืคึผืจืึธืืืขืืก ืืื ืงืึธืืืจืื ื. ืืขืจ ืคืึทืงื ืืื ืึทื UTF-8 ืืื MySQL ืืื ืืขืฆืื ืก , ืืื ื ืขืก ืกืืึธืจื ืืืืื 3 ืืืืขืก ืึทื ืฉืืึธื ืคืื 4. ืืื MySQL 8 ืืึธืก ืืื ืืขืกืึธืฃ ืืขืืืขื : alias utf8 ืืืขื ืืึทืื ืคืืจื ืฆื ืงืึธืืืจืื ื utf8mb4, ืืื ืื ืึทืืืข ืฉืคืืืื ืืื ืื ืืืฉื ืืืขืื ืืืขืจื utf8mb3. ืืืืึทืืขืจ ืงืึธืืืจืื ื utf8mb3 ืืืขื ืืืื ืึทืืืขืงืืขื ืืืขื, ืึธืืขืจ ื ืืฉื ืืื ืืขื ืืขืืืื ื. ืืขืจืืืขืจ, ืืืจ ืืึทืฉืืึธืกื ืฆื ืจืืืืืง ืื ืงืึธืืืจืื ื ืฉืืื ืืืืฃ ืื ืคืืืกื ืืืง DBMS ืื ืกืืึทืืืจืื ื, ื ืึธื ืึทืคึผืืืืืื ื ืขืก.
ืืืื 3: ืกืขืจืืืืจืขืจ ืืขืจืืืึทื ืืืงืื ืืขื
ืืืึธืก ืงืขื ืืืื ืคืึทืืฉ ืืืขื ืขืก ืืื ืึทืืึท ืึท ืงืืื ืคึผืืึทื? .. ืคืืจืฉืืื ื ืคืืืฉืืขื ืืืง ืึทื ื ืืึทื ืกืื ืฉืืขื ืืืง ืคึผืึทืกืืจื, ืืืจ ืืขืคืืจื ืืขืจ ืขืจืฉืืขืจ ืขืงืกืคึผืขืจืืืขื ื ืืืืฃ ืึท MySQL ืืขืื ืงื ืืื.
ืืื ืฉืืื ืืขืจืืื ื, ืงืึธืืืขืจืก ืื ืึทืจืืืกืืขืื ืคืื ืึทืคึผืืืืืื ื MySQL ืกืขืจืืืขืจืก ืืื ืจืขืคึผืืึทืงืึทื. ืื ืื ืึธ ืฉืืจื ืืื ืึทื ืืืจ ืืึธื ืขืจืฉืืขืจ ืืขืจืืืึทื ืืืงื ืึทืืข ืจืขืคึผืืึทืงืึทื (ืกืืึทืืืขืก), ืืื ื MySQL 8 ืงืขื ืขื ืจืขืคึผืืึทืงืืื ืคึฟืื ืึท ืืขื ืืืขืจืกืืข 5.7. ืขืืืขืืข ืฉืืืขืจืืงืืื ืืืื ืืื ืืขื ืคืึทืงื ืึทื ืืืจ ื ืืฆื ืื ืืึธืืข ืืขื <-> ืืขื, ืืืขื ืื ืืืืึทื ืืขื ืืื ืืื ืืึธืืข ื ืืจ ืฆืื ืืืื ืขื. ืึทื ืืื, ืืื ืคืึทืงื, ืงืึทืืืึทื ืคืึทืจืงืขืจ ืืืื ืฆื ืืืื ืืึทืื ืฆืขื ืืขืจ, ืืื ืื ืจืืข ืืื ืึท ืืึทืงืึทืคึผ ืืืื ืขืจ.
ืื ืืึธืคึผืึธืืึธืื ืงืืงื ืืื ืืึธืก:
ืืขืจ ืืขืจืืืึทื ืืืงื ืืืื ืึธื ืืืืื ืืื ืจืขืคึผืืึทืงืึทื mysql ืจืขืคึผืืืงืข DC 2, mysql master DC 2 ะธ mysql replica dc 1, ืืื ืกืืฃ ืืื ืื mysql master 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 ืืฉืืื ืืืฉื ืื ืืืขื ืืื ืจืขืคึผืจืึทืืขื ืฅ ืืึทืื ืคึฟืึทืจ ืืขืฆืืืึทื ืคืขืืืขืจ (ืื ื, ืืื ืืื ื, ืืื"ื ื), ืึทืืื 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 ืืืฉื ืืื ืื ืกืืกืืขื ืืืฉ. INNODB_SYS_TABLESPACES, ืืืื ืืื, ืืืฉื, ืืขื ืขื ืืืฉืืคื ืืื ืืืขืจืกืืข 5.1. ืฆื ืืืกืืืืื ืคึผืจืึธืืืขืืก ืืืขื ืึทืคึผืืืืืื ื, ืืืจ ืงืขื ืขื ื ืืฆื .
ืคืืจืืืืก ืืืื ืืืจ ื ืืฉื ืืขืืื ืืืขืืืข ืคืจืืืืขืืขื ืืืืฃ ืืขืื? ืื ืืึทืืึทืืืืก ืืื ืคึผืืจืืึทืืืงืื ืงืึทืคึผืื ืืึธืจื ืคึฟืื ืคึผืจืึธืืืงืฆืืข - ืึทืืื, ืืืฉื ืืขื ืขื ืจืืงืจืืืืืื.
ืฆืื ืืึทืืืืขืจื, ืืืืฃ ืึท ืืึทืงืข ืืจืืขืื ืืจืืืก ืืึทืืึทืืืืก, ืืืจ ืืืขื ื ืืฉื ืงืขื ืขื ืฆื ื ืึธืจ ื ืขืืขื ืืื ืืืกืคืืจื ืึท ืื ืืืืขืจืกืึทื OPTIMIZE. ืคึผืขืจcona-toolkit ืืืขื ืืขืืคื ืืึธ: ืื pt-online-schema-change ื ืืฆื ืืื ืืืกืืขืฆืืืื ื ืคึฟืึทืจ ืื ืึธื ืืืื ืึธืคึผืืืืืืข ืึธืคึผืขืจืึทืฆืืข.
ืืขืจ ืืขืจืืืึทื ืืืงื ืคึผืืึทื ืืื ืืื ืืึธืก:
- ืึธืคึผืืืืืืืจื ืึทืืข ืืืฉื.
- ืืขืจืืืึทื ืืืงื ืื ืืึทืืึทืืืืกืื.
ืฆื ืงืึธื ืืจืึธืืืจื ืขืก ืืื ืืื ืืขืจ ืืขืืืืงืขืจ ืฆืืื ืืขืคึฟืื ืขื ืื ืืขืจืืืึทื ืืืงื ืฆืืื, ืืืจ ืคืึทืจืงืจืืคึผืื ืืืื ืขืจ ืคืื ืื ืจืขืคึผืืึทืงืึทื ืืื ืืืืคื ืื ืคืืืืขื ืืข ืืึทืคึฟืขื ืคึฟืึทืจ ืึทืืข ืืืฉื:
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ืืึทืืืขืก ืืขื ืขื ืืขืจืืืึทื ืืืงื ืึธื ืืึทื ื ืืึทืงืก ืจืขืื ืฆื ืืขื ืคืึทืงื ืึทื ืื ื ืืฆื ืงืจืืืืฅ ืึท ื ืืึท ืฆืืึทืืืืืึทืืืง ืืืฉ ืืื ืืืึธืก ืขืก ืงืึทืคึผืื ืืึทืื ืคืื ืื ืืืืคึผื ืืืฉ. ืืื ืืขื ืืึธืืขื ื ืืืขื ืืืืืข ืืืฉื ืืขื ืขื ืืืืขื ืืงืึทื, ืืขืจ ืึธืจืืืื ืขื ืืืฉ ืืื ืคืืจืฉืคืืจื ืืื ืจืืคึผืืืืกื ืืื ืื ื ืืึทืข. ืืื ืืื ืืืขืจ ืคืึทื, ืึท ืคึผืจืึธืืข ืืืืคื ืืขืืืืื ืึทื ืขืก ืืืึธืื ื ืขืืขื ืืืขืื ืึท ืืึธื ืฆื ืืขืจืืืึทื ืืืงื ืึทืืข ืืืฉื, ืึธืืขืจ ืงืึทืคึผืืื ื ืืึทืื ืืขืคึฟืืจื ืฆื ืคืื ืืึทืกืข ืืืืฃ ืื ืืืกืงืก.
ืฆื ืืืกืืืืื ืืขื, ืืื ืคึผืจืึธืืืงืฆืืข ืืืจ ืฆืืืขืืขืื ืืขื ืึทืจืืืืขื ื ืฆื ืื ืืึทืคึฟืขื --sleep ืืื ืึท ืืืขืจื ืคืื 10 - ืืขื ืคึผืึทืจืึทืืขืืขืจ ืึทืืืฉืึทืกืืื ืื ืืืืจืื ืืขื ื ื ืึธื ืืจืึทื ืกืคืขืจืื ื ืึท ืคึผืขืงื ืคืื ืืึทืื ืฆื ืึท ื ืืึท ืืืฉ. ืืขื ืืืขื ืืืจ ืงืขื ืขื ืจืขืืืฆืืจื ืื ืืึทืกืข ืืืื ืื ืคืึทืงืืืฉ ืคืืืกื ืืืง ืึทืคึผืืึทืงืืืฉืึทื ืืื ืคืืจืืื ืื ืคึฟืึทืจ ืขื ืืคืขืจ ืฆืืื.
ื ืึธื ืคึผืขืจืคืึธืจืืื ื ืื ืึทืคึผืืึทืืึทืืืืฉืึทื, ืื ืืขืจืืืึทื ืืืงื ืืื ืืขืืืขื ืืขืจืึธืื.
... ืึธืืขืจ ื ืืฉื ืืึธืจ!
ืื ืืึทืื ืึท ืฉืขื ื ืึธื ืื ืืขืจืืืึทื ืืืงื, ืืขืจ ืงืืืขื ื ืืขืงืืืขื ืืื ืึท ืคึผืจืึธืืืขื. ืื ืืึทืืึทืืืืก ืืขืืจืืขื ืืืืขืจ ืืึธืื ืข: ืคึผืืจืืึทืืืงืื ืื ืืขืืืืื ืงืฉืจ ืจืืกืขืฅ. ืืึธืก ืืื ืืื ืขืก ืืขืงืืงื ืืื ืืื ืืึธื ืืืึธืจืื ื:
ืื ืกืงืจืขืขื ืฉืึธื ืืืืืื ืึท ืืขืืืึธืึธื ืืจืึทืคืืง ืจืขืื ืฆื ืืขื ืคืึทืงื ืึทื ืขืืืขืืข ืคืื โโืื MySQL ืกืขืจืืืขืจ ืคึฟืขืืขื ืคึผืืจืืึทืืืงืื ืงืจืึทืฉื ืืื ืึท ืืขืืช. ืขืจืจืึธืจืก ืืจืืืก ืืื ืื ืึทืคึผืืึทืงืืืฉืึทื:
[PDOException] SQLSTATE[HY000] [2002] Connection refusedื ืฉื ืขื ืืืจืืงืืง ืคืื ืื ืืึธืืก ืื ืืคืืขืงื ืึทื ืื mysqld ืืืืืึทื ืงืขื ื ืืฉื ืืึทืงืืืขื ืื ืคืืจืืื ืื ืจืขืกืืจืกื ืคืื ืื ืึธืคึผืขืจืืืืื ื ืกืืกืืขื. ืืฉืขืช ืกืึธืจืืื ื ืืืืก ืขืจืจืึธืจืก, ืืืจ ืืืกืงืึทืืืขืจื ืืื ืื ืกืืกืืขื "ืืชืื" ืึทืคึผืึทืจืืึธืจ ืคึผืึธืืืืืง ืืขืงืขืก:
# 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 ืึท ืคึผืึธืจ ืคืื ืืึธืจื ืฆืืจืืง ืืื ืืขืืขืจื ืฆื ืึท ืึทืืืขืงืืขื ืืืขื ืคึผืขืงื. ืืืืืืื ื ืื ืืขืงืขืก ืืื ืจืืกืืึทืจืืื ื ืื ืึทืคึผืึทืจืืึธืจ ืืื ืกื ืกืึทืืืื ืื ืคึผืจืึธืืืขื:
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ืืื ืืกืงื ื
ืงืืื, ืืคืืื ืื ืกืืืคึผืืึทืกื ืึธืคึผืขืจืึทืฆืืข, ืงืขื ืขื ืคืืจื ืฆื ืืืืืขืจืืื ืคึผืจืึธืืืขืืก. ืืื ืืคืืื ืึท ืืขืืื ื-ืืขืืจืึทืื ืคึผืืึทื ืงืขื ื ืืฉื ืฉืืขื ืืืง ืืึทืจืึทื ืืืจื ืื ืืขืจืืืึทืจื ืจืขืืืืืึทื. ืืืฆื, ืงืืื ืืขืจืืืึทื ืืืงื ืคึผืืึทื ื ืืืึธืก ืืื ืืืขืจ ืืึทื ืฉืึทืคึฟื ืืืื ืื ืงืืืื ืื ืืึทื ืืึทืืึธืจื ืจืืื ืืงืื ื ืคืื ืืื ืืืืืง ืืขืงืขืก ืืืึธืก ืงืขื ืืึธืื ืืจืืืก ืืื ืึท ืจืขืืืืืึทื ืคืื ืืขืฆืืข ืึทืงืฉืึทื ื.
ืืื ืืื ืืขื ื ืื ืืืืขืจ ืคืึทืืืึทื ืืจืึทืคืืง ืฉืืคื, ืืื ืืืึธืื ืืื ืฆื ืืึธืื ืึท ืจืืืืง ืืึทื ืงืขื ืคึผืขืจืงืึธื ืึท ืคึฟืึทืจ ืืืืขืจ ืืืกืืขืฆืืืื ื ืคึผืจืึธืืืงืื!
ืคึผืก
ืืืืขื ืขื ืืืื ืืืืฃ ืืื ืืืขืจ ืืืึธื:
- ยซ";
- ยซ";
- ยซ";
- ยซ";
- ยซ'.
ืืงืืจ: www.habr.com