[MySQL][Install] 8.0 Upgrade 방법
- Databases/MySQL
- 2020. 1. 13.
■ MySQL 8.0 업그레이드시 참고할 사항들.
MySQL 5.7에서만 지원됩니다. MySQL 5.6에서는 지원하지 않습니다. MySQL 5.6에서 8.0으로 업그레이드 하려면 2번의 과정을 거쳐야 합니다. MySQL 5.7로 업그레이드 후 8.0으로 업그레이드 해야 합니다.
■ MySQL 8.0 업그레이드 진행순서
MySQL5.7에서는 엔진 교체 후 시작전에 mysql_upgrade를 수행시켜 시스템 관련 데이터 베이스와 유저 관련 데이터베이스를 업그레이드를 시켰습니다. 그러나 8.0은 조금 다릅니다. 시스템 스키마와 유저 관련 스키마를 업그레이드 시는것은 같습니다. 그러나 자동으로 된다는것이 틀립니다. MySQL 5.7이하 버전은 mysql_upgrade를 수동으로 수행해서 업그레이드를 해야 했지만 8.0같은 경우는 내부적으로 MySQL엔진 시작과 동시에 내부적으로 mysql_upgrade를 호출 시켜 자동으로 업그레이드가 완료되도록 변경했습니다.
한가지 기억해야 할것은 같은 8.0도 8.0.16 미만버전이냐 이후 버전이냐에 따라 업그레이드 방법이 조금 틀립니다. 한번 보면서 확인해 보도록 하겠습니다.
■ 업그레이드 사전지식
MySQL에서 사용되는 시스템 스키마는 크게 두가지로 나뉩니다.
- Database Object 메타데이터가 저장되는 Data dictionary Table
- 다른 운영 목적으로 사용되는 시스템 테이블
서버용으로 사용되는 스키마는 다음과 같습니다.
- performance_schema, sys, information_schema
- user schema
업그레이드 작업은 크게 위의 스키마들을 업그레이드 하는 작업이라고 생각하시면 됩니다. 즉 모든 데이터베이스가 해당이 됩니다.
■ MySQL 8.0 업그레이드 방법.
MySQL 8.0부터 위에서 말씀드렸듯이 자동 업그레이드 방식으로 바뀌었습니다. 그러나 자동업그레이드를 막는 방법도 있습니다. 그러나 이게 위에서 말씀드렸듯이 8.0.16 미만버전이냐 이후 버전이냐에 따라 조금 다루는 방법이 틀립니다.
# 8.0.16 이상 버전에서 mysql_upgrade 실행시 에러
5.7에서 사용하던 방식으로 업그레이드를 하면 다음과 같은 에러를 만날 수 있습니다.
shell> mysql_upgrade -uroot -p
Enter password:
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.
그래서 mysql_upgrade는 8.0.16 미만 버전에서만 사용해야 합니다.
참고로 버전별 업그레이드는 아래의 방식대로 업그레이드를 진행해야 합니다.
업그레이드 프로그램
8.0.16 미만 : mysql_upgrade
8.0.16 이상 : mysqld
자동 업그레이드를 막는 명령어
8.0.16 미만 : mysql_upgrade --no-dd-upgrade
8.0.16 이상 : mysqld --upgrade=NONE
최소 업그레이드만 진행
8.0.16 미만 : mysql_upgrade --upgrade-system-tables
8.0.16 이상 : mysqld --upgrade=MINIMAL
강제 업그레이드 진행
8.0.16 미만 : mysql_upgrade --force
8.0.16 이상 : mysqld --upgrade=FORCE
권장하지 않습니다. 문제가 생길 가능성이 큽니다.
그리고 업그레이드 테스트 프로그램을 이용해서 확인해 봅니다.
여기서는 최소 업그레이드 진행과 자동업그레이드만 정리해 보겠습니다.
# MySQL 8.0 업그레이드 수행
* Upgrade Option이 NONE인경우
다른 사정에 의해 업그레이드를 막아야 할 수도 있습니다. 엔진 바이너리만 교체하고 mysql 내부 스키마나 데이터는 변경을 원하지 않는다면 다음과 같이 수행합니다.
shell > mysqld_safe --defaults-file=/etc/my.cnf --upgrade=NONE
2020-01-12T16:09:26.832491Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-01-12T16:09:26.832606Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) starting as process 2569
2020-01-12T16:09:26.846765Z 1 [ERROR] [MY-013377] [Server] Server shutting down because upgrade is required, yet prohibited by the command line option '--upgrade=NONE'.
2020-01-12T16:09:26.846870Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-01-12T16:09:26.846953Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-01-12T16:09:26.847843Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.17) Source distribution.그러나 upgrade를 NONE으로 하면 무조건 에러가 떨어집니다. 시스템 스키마들이 8.0에 맞게 변환되지 않았기 때문에 접속을 할 수 없는 것입니다.
* Upgrade Option이 MINIMAL인경우
Minimal로 업그레이드시 굉장히 많은 제약이 따름니다. 보면서 한번 확인해 보겠습니다.
shell > mysqld_safe --defaults-file=/etc/my.cnf --upgrade=MINIMAL &
MINIMAL로 업그레이드시 로그
2020-01-12T16:21:08.546552Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-01-12T16:21:08.546643Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) starting as process 3184
2020-01-12T16:21:08.556151Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2020-01-12T16:21:10.117289Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2020-01-12T16:21:10.648114Z 0 [Warning] [MY-013378] [Server] Server upgrade is required, but skipped by command line option '--upgrade=MINIMAL'.
2020-01-12T16:21:11.160589Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-01-12T16:21:11.168963Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component
' doesn't exist
2020-01-12T16:21:11.169015Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component
table is missing or has an incorrect definition.
2020-01-12T16:21:11.175574Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.user].
2020-01-12T16:21:11.175586Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.db].
2020-01-12T16:21:11.175593Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv].
2020-01-12T16:21:11.175600Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv].
2020-01-12T16:21:11.175607Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv].
2020-01-12T16:21:11.175614Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.proxies_priv].
2020-01-12T16:21:11.175995Z 0 [Warning] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 51, found 45. The table is probably corrupted
2020-01-12T16:21:11.176012Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.db. The table is probably corrupted!
2020-01-12T16:21:11.176035Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted!
2020-01-12T16:21:11.176048Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted!
2020-01-12T16:21:11.176073Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.columns_priv. The table is probably corrupted!
2020-01-12T16:21:11.176090Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted!
2020-01-12T16:21:11.176106Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted!
2020-01-12T16:21:11.176122Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.proxies_priv. The table is probably corrupted!
2020-01-12T16:21:11.176132Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.proxies_priv. The table is probably corrupted!
2020-01-12T16:21:11.176145Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.proxies_priv. The table is probably corrupted!
2020-01-12T16:21:11.176162Z 0 [Warning] [MY-010966] [Server] ACL table mysql.role_edges missing. Some operations may fail.
2020-01-12T16:21:11.176175Z 0 [Warning] [MY-010966] [Server] ACL table mysql.default_roles missing. Some operations may fail.
2020-01-12T16:21:11.176188Z 0 [Warning] [MY-010966] [Server] ACL table mysql.global_grants missing. Some operations may fail.
2020-01-12T16:21:11.176197Z 0 [Warning] [MY-010966] [Server] ACL table mysql.password_history missing. Some operations may fail.
2020-01-12T16:21:11.176658Z 0 [Warning] [MY-010965] [Server] The system table mysql.global_grants is missing. Please perform the MySQL upgrade procedure.
2020-01-12T16:21:11.180965Z 0 [Warning] [MY-010727] [Server] System table 'func' is expected to be transactional.
2020-01-12T16:21:11.182768Z 0 [Warning] [MY-010405] [Repl] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2020-01-12T16:21:11.182801Z 0 [ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.
2020-01-12T16:21:11.182811Z 0 [ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.
2020-01-12T16:21:11.182819Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2020-01-12T16:21:11.182831Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2020-01-12T16:21:11.184912Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.17' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
2020-01-12T16:21:11.423816Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060로그 중간에 보시면 스토리지 엔진을 제대로 사용할 수 없고 권한 테이블, 복제관련 옵션등을 사용할 수 없습니다 .한마디로 거의 모든 기능을 다 사용할 수 없다고 보셔도 될것 같습니다.
Minimal 업그레이드 후 접속 서버에 하여 Query 수행해보겠습니다.
shell > mysql -uroot -p
mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> use mysql
Database changed
mysql> show tables;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> select * from mysql.user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *BCDB46F9759BC3C7C2679D4E81145B53EE616059 | N | 2020-01-13 00:36:40 | NULL | N |
| localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2020-01-13 00:35:25 | NULL | Y |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2020-01-13 00:35:25 | NULL | Y |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
3 rows in set (0.00 sec)보시는바와 같이 기본적인 MySQL명령이 에러가 납니다. Admin명령을 제대로 수행하기 어려워 보입니다.
업그레이드 여부 테스트 프로그램에서도 에러가 납니다.
shell > mysqlcheck -u root -p --all-databases --check-upgrade
Enter password:
mysqlcheck: Error: Couldn't execute 'SHOW DATABASES': The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
* 자동(전체) 업그레이드
엔진을 수행하면 data디렉토리 안에 모든 스키마 정보를 자동으로 업그레이드 합니다.
shell > mysqld_safe --defaults-file=/etc/my.cnf -u root &
수행 후 로그를 확인해 보면 업그레이드를 완료했다는 메세지가 보입니다.
2020-01-12T17:02:40.483287Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-01-12T17:02:40.483418Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) starting as process 2139
2020-01-12T17:02:40.500253Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2020-01-12T17:02:42.702835Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2020-01-12T17:02:43.357800Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' started.
2020-01-12T17:02:47.945495Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' completed.
2020-01-12T17:02:48.488011Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-01-12T17:02:48.510621Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.17' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
2020-01-12T17:02:48.675674Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
업그레이드 후 테스트 프로그램을 통해 확인해봅니다.
shell > mysqlcheck -u root -p --all-databases --check-upgrade
Enter password:
employees.departments Table is already up to date
employees.dept_emp Table is already up to date
employees.dept_manager Table is already up to date
employees.employees Table is already up to date
employees.salaries Table is already up to date
employees.titles Table is already up to date
mysql.columns_priv Table is already up to date
mysql.component Table is already up to date
mysql.db Table is already up to date
mysql.default_roles Table is already up to date
mysql.engine_cost Table is already up to date
mysql.func Table is already up to date
mysql.general_log Table is already up to date
mysql.global_grants Table is already up to date
mysql.gtid_executed Table is already up to date
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.innodb_index_stats Table is already up to date
mysql.innodb_table_stats Table is already up to date
mysql.ndb_binlog_index Table is already up to date
mysql.password_history Table is already up to date
mysql.plugin Table is already up to date
mysql.procs_priv Table is already up to date
mysql.proxies_priv Table is already up to date
mysql.role_edges Table is already up to date
mysql.server_cost Table is already up to date
mysql.servers Table is already up to date
mysql.slave_master_info Table is already up to date
mysql.slave_relay_log_info Table is already up to date
mysql.slave_worker_info Table is already up to date
mysql.slow_log Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date
sys.sys_config Table is already up to date'Databases > MySQL' 카테고리의 다른 글
| [MySQL][Admin] 멀티 인스턴스 수행방법 (0) | 2020.01.27 |
|---|---|
| [MySQL][Admin] 로그종류와 특징 (0) | 2020.01.16 |
| [MySQL][Admin] - 기본으로 제공되는 프로그램들 (0) | 2020.01.04 |
| [MySQL][Install] - 8.0+CentOS 7.x - Source (2) | 2019.12.31 |
| [MySQL][Install] - 8.0+CentOS 7.x-RPM 설치 (0) | 2019.12.29 |