MySQL 8.0 Upgrade 방법 및 참고사항

MySQL 5.7에서 8.0으로의 업그레이드는 주요한 데이터베이스 시스템 변경을 수반하며, 이 과정은 몇 가지 핵심 단계와 고려해야 할 중요한 포인트로 구성됩니다. 아래에서는 이 업그레이드 과정의 기본 배경, 주요 단계, 예상되는 도전 과제 및 이를 해결하기 위한 방안에 대해 상세하게 설명합니다.

배경

MySQL 8.0은 여러 새로운 기능과 개선사항을 제공합니다. 이는 보안 강화, 쿼리 실행 최적화, 새로운 데이터 타입의 지원, JSON 확장 및 성능 개선을 포함합니다. 또한, MySQL 8.0은 새로운 기본 문자 세트인 utf8mb4로의 전환을 포함하여, 국제화 및 다양한 언어 지원 면에서도 개선을 제공합니다.

업그레이드 준비 단계

업그레이드 필요성 인식: MySQL 5.7의 공식 지원 종료와 보안, 성능, 기능 개선을 위해 업그레이드가 필요합니다.
테스트 및 계획 수립: 충분한 테스트 환경을 구축하고, 업그레이드 전에 테스트를 수행하여 호환성 및 성능 이슈를 사전에 파악합니다.
백업: 업그레이드 전에 데이터베이스의 완전한 백업을 수행하여 데이터 손실에 대비합니다.

도전 과제 및 해결책

롤백 능력 유지: 문제 발생 시 이전 버전으로 쉽게 롤백할 수 있도록 전략을 마련합니다.
문자 세트 및 정렬 규칙 호환성: MySQL 8.0에서는 utf8mb4가 기본 문자 세트로 사용됩니다. 업그레이드 전에 데이터베이스의 문자 세트 호환성을 확인하고 필요한 경우 조정합니다.
시스템 변수 변경 대응: MySQL 8.0에서 변경되거나 제거된 시스템 변수를 확인하고, 설정 파일을 적절히 조정합니다.

업그레이드 실행

점진적 롤아웃: 복제본을 먼저 업그레이드하여 초기 문제를 식별하고 해결한 후, 주 데이터베이스를 업그레이드합니다.
데이터베이스 관리 도구 사용: Vitess와 같은 도구를 사용하여 데이터베이스 관리 및 샤딩을 용이하게 합니다.

문제 해결 및 최적화

복제 지연 및 버그 대응: 업그레이드 후 복제 지연이나 버그가 발견되면 적절한 조치를 취합니다.
생산 환경에서의 쿼리 테스트: 개발 환경과 달리, 실제 생산 환경에서 쿼리 성능을 모니터링하고 최적화합니다.

학습 및 결론

관찰 가능성의 중요성: 강력한 모니터링 및 로깅 도구를 사용하여 시스템의 성능과 상태를 지속적으로 관찰합니다.
업그레이드 도구의 활용: MySQL Shell과 같은 공식 도구를 사용하여 업그레이드 전 호환성 검사를 수행하고, 문제가 발생할 경우 적절한 조치를 취합니다.

MySQL 5.x에서 8.x로의 업그레이드 과정은 계획적이고 체계적으로 접근해야 하는 복잡한 과정입니다. 충분한 준비, 테스트 및 모니터링을 통해 성공적인 업그레이드를 달성할 수 있습니다. 기술적 세부 사항을 포함하여 성공적으로 수행하기 위해서는 다음과 같은 코드, 명령어 및 로그 내용이 중요합니다.

업그레이드 사전지식

MySQL에서 사용되는 시스템 스키마는 크게 두가지로 나뉩니다.

- Database Object 메타데이터가 저장되는 Data dictionary Table

- 다른 운영 목적으로 사용되는 시스템 테이블

서버용으로 사용되는 스키마는 다음과 같습니다.

- performance_schema, sys, information_schema

- user schema

업그레이드 작업은 크게 위의 스키마들을 업그레이드 하는 작업이라고 생각하시면 됩니다. 즉 모든 데이터베이스가 해당이 됩니다.

1. 준비 단계

버전 확인: 현재 MySQL 버전을 확인합니다.
```
mysql --version
```

백업 수행: 데이터베이스의 완전한 백업을 생성합니다.

mysqldump --all-databases --user=root --password --single-transaction > all_databases.sql

2. 호환성 검사

MySQL 업그레이드 체크: MySQL 8.0으로의 업그레이드 전에 호환성을 검사하는 도구를 사용합니다.
```
mysqlcheck --all-databases --check-upgrade --user=root --password
```

시스템 변수 조정: MySQL 8.0에서 변경되거나 제거된 시스템 변수에 대응하여 
my.cnf 또는 my.ini 파일을 수정합니다. 
예를 들어, --skip-host-cache 옵션이 MySQL 8.0에서 지원되지 않으므로, 
해당 옵션이 설정 파일에 있다면 제거해야 합니다.

------------------------ ------------------------ ------------------------ ------------------------ ------------------------

■ 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

예상되는 로그 메시지

업그레이드 과정 중, 다음과 같은 로그 메시지가 나타날 수 있습니다:

업그레이드 시작

[Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.x started.

시스템 테이블 업그레이드

[System] [MY-013576] [InnoDB] InnoDB initialization has started.
[System] [MY-013577] [InnoDB] InnoDB initialization has ended.

경고 및 오류 처리

[Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
[ERROR] [MY-000068] [Server] unknown option '--skip-host-cache'.

주의사항

롤백 계획: 업그레이드 전에 롤백 계획을 준비해야 합니다. 만약 업그레이드가 실패하거나 예상치 못한 문제가 발생하면, 백업에서 데이터를 복원하여 이전 버전으로 롤백할 수 있어야 합니다.
문서 및 릴리스 노트 참조: 업그레이드 전에 반드시 MySQL 8.0의 릴리스 노트와 공식 문서를 참조하여, 변경 사항 및 새로운 기능을 숙지해야 합니다.

이 지침을 따르면 MySQL 5.7에서 8.0으로의 업그레이드 과정을 체계적으로 진행할 수 있습니다.

업그레이드 이후 내부적으로 고려해야할 항목들

1) character_set, collation 변경

character_set
- character_set_client, character_set_connection, character_set_database, character_set_filesystem, character_set_results, character_set_server
- utf8 -> utf8mb4
- utf8(utf8mb3)은 MySQL 8.0에서 더 이상 지원되지 않아 utf8mb4로 변경해줘야한다.
collation
- collation_connection, collation_server
- utf8mb3_general_ci -> utf8mb4_general_ci
- character_set과 같은 이유로 변경

utf8mb4_unicode_ci방식이 utf8mb4_general_ci보다 더 복잡한 알고리즘을 사용하여 더 나은 정확도나 다국어 지원에 좋다.
복잡한 알고리즘때문에 속도 차이가 있지만 요새 하드웨어 성능이 많이 올라 큰 차이가 없다.

2) lower_case_table_names 변경

테이블명 대소문자 구분 여부
1(구분 안함) -> 0(구분 함, default value)
MySQL 8.0 버전은 쿼리 작성시 테이블명의 대소문자를 무조건 구분해야한다.
기존 시스템은 구분 안함으로 사용하였어서.. 대문자로 작성된 테이블명들 전부 수기로 변경해주었다...

2. DB 레벨에서 character, collation 변경

CHARACTER_SET : utf8 -> utf8mb4
COLLATION : utf8_general_ci -> utf8mb4_general_ci

-- 변경
ALTER DATABASE (DB NAME)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 확인
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;

3. Table과 Column 레벨에서 CHARACTER, COLLATE 변경

CHARACTER: utf8 -> utf8mb4
COLLATE: utf8_general_ci -> utf8mb4_general_ci

-- table, column 모두 변경
ALTER TABLE (table_name)
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 확인
SHOW TABLE STATUS FROM (table_name);

4. Procedure, Function DDL 변경

기존에 사용중인 프로시저나 함수의 DDL을 살펴보면 입출력값의 Character나 Collate가 utf8로 되어있다면 utf8mb4로 변경해주어야 함.

CHARACTER: utf8 -> utf8mb4
COLLATE: utf8_general_ci -> utf8mb4_general_ci

5. MySQL 5.7의 내장 함수 'password()'가 8.0에서 deprecated

아래와 같이 old_password() function을 생성하여 사용 예정

DELIMITER //
CREATE DEFINER=`db_name`@`%` FUNCTION `db_name`.`old_password`(pwd TEXT) RETURNS varchar(41) CHARSET utf8mb4
	NO SQL
	COMMENT 'password() 함수는 MySQL8.0에서 deprecated 되었음.'
BEGIN
	DECLARE result VARCHAR(41);
	SET result = CONCAT('*', UPPER(SHA1(UNHEX(SHA1(pwd)))));
	RETURN result;
END;
DELIMITER ;

6. illegal mix of collations Error 해결방법

connection_collation과 DB collation 불일치로 생기는 에러
업그레이드할 DB(MySQL 8.0.34)의 collation은 utf8mb4_general_ci로 설정되어 있음.
해당 에러가 발생한다면 아래와 같이 jdbc url parameter 옵션을 설정해줘야 함.

?characterEncoding=UTF-8&connectionCollation=utf8mb4_general_ci

7. 문법 오류

MySQL5.7에서는 정상적으로 작동하지만 8.0버전에서는 문법 오류 발생

1. group by 절에 정렬 조건 작성 불가**

SELECT *
FROM table
GROUP BY column DESC

-- 아래와 같이 ORDER BY절 필요
SELECT *
FROM table t
GROUP BY t.column
ORDER BY t.column DESC

2. NULLIF(expr1, expr2)

두 인자 expr1의 타입과 expr2타입이 일치해야함

-- t.date는 date타입, ''는 text타입으로 불일치
SELECT NULLIF(t.date, '') FROM table t

-- IFNULL(), CONCAT()는 타입이 일치하지 않아도 문법 오류가 발생하지 않음
SELECT IFNULL(t1.date. '') FROM table t1;
SELECT CONCAT(t2.date. 'text') FROM table t2;

8. 사용자 인증 방식 변경

MySQL 5.7 기본 인증 방식: Native Authentication
MySQL 8.0 기본 인증 방식: Caching SHA-2 Authentication
필자는 정상적으로 접근이 가능하여 따로 작업하지 않음.

9. 외래키 이름의 길이

MySQL 8.0에서는 외래키의 제약조건이 64글자로 제한된다.
현재 사용중인 DB에서 64글자 이상의 외래키 제약조건 이름은 없는 것으로 확인.

-- 확인 쿼리
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME IS NOT NULL
AND CHAR_LENGTH(CONSTRAINT_NAME) >= 64;

10. Index

MySQL 5.x에서 사용되던 인덱스가 있다면 MySQL 8.0로 업그레이드 후 성능 테스트를 수행해야 한다.

옵티마이저가 변경되었기 때문에 같은 쿼리라도 실행계획이 달라질 수 있다.

MySQL 5.x에서는 성능 향상에 도움이 됐지만 MySQL 8.x에서는 오히려 성능 저하를 유발할 수도 있다.

테스트 결과 지금까지는 성능이 저하된 쿼리는 없는 것으로 확인.

11. 파티션을 위한 공용 테이블스페이스

MySQL 8.x에서는 파티션의 각 테이블스페이스를 공용 테이블스페이스에 저장할 수 없다. 그래서 파티션의 테이블스페이스가 공용 테이블스페이스에 저장된 것이 있는지 먼저 확인하고, 있다면 ALTER TABLE ... REORGANIZE 명령을 실행해 개별 테이블스페이스를 사용하도록 변경해야 한다.

아래 쿼리로 공용 테이블스페이스에 저장된 게 없는 것으로 확인.

SELECT table_name, partition_name, subpartition_name, tablespace_name 
FROM information_schema.partitions 
WHERE table_schema = 'your_database_name'
AND tablespace_name = 'innodb_system';

원본 참고

https://myinfrabox.tistory.com/9

https://blog.pages.kr/2921

https://velog.io/@wujin/DB-AWS-RDS-MySQL-5.7-to-8.0-%EB%A7%88%EC%9D%B4%EA%B7%B8%EB%A0%88%EC%9D%B4%EC%85%98

참고

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html#USER_UpgradeDBInstance.MySQL.57to80Prechecks

https://dev.mysql.com/blog-archive/inplace-upgrade-from-mysql-5-7-to-mysql-8-0/

https://dev.mysql.com/blog-archive/upgrading-to-mysql-8-0-here-is-what-you-need-to-know/

https://dev.mysql.com/blog-archive/new-defaults-in-mysql-8-0/

https://sungwookkang.com/entry/MySQL-MySQL-57%EC%97%90%EC%84%9C-80%EC%9C%BC%EB%A1%9C-%EC%97%85%EA%B7%B8%EB%A0%88%EC%9D%B4%EB%93%9C%EC%8B%9C-%EB%B3%80%EA%B2%BD%EB%90%98%EB%8A%94-%EC%84%A4%EC%A0%95-%EB%B0%8F-%EC%98%B5%EC%85%98-%EC%A0%95%EB%A6%AC

https://wonsjung.tistory.com/582

https://kimdubi.github.io/mysql/mysql8to57_collation/

저작자표시

기쁨3배 & 행복3배 = DATABASE & MYSQL & MARIADB

MySQL 8.0 Upgrade 방법 및 참고사항

배경

업그레이드 준비 단계

도전 과제 및 해결책

업그레이드 실행

문제 해결 및 최적화

학습 및 결론

업그레이드 사전지식

1. 준비 단계

2. 호환성 검사

■ MySQL 8.0 업그레이드 방법.

예상되는 로그 메시지

주의사항

업그레이드 이후 내부적으로 고려해야할 항목들

1) character_set, collation 변경

2) lower_case_table_names 변경

2. DB 레벨에서 character, collation 변경

3. Table과 Column 레벨에서 CHARACTER, COLLATE 변경

4. Procedure, Function DDL 변경

5. MySQL 5.7의 내장 함수 'password()'가 8.0에서 deprecated

6. illegal mix of collations Error 해결방법

7. 문법 오류

1. group by 절에 정렬 조건 작성 불가**

2. NULLIF(expr1, expr2)

8. 사용자 인증 방식 변경

9. 외래키 이름의 길이

10. Index

11. 파티션을 위한 공용 테이블스페이스

티스토리툴바

MySQL 8.0 Upgrade 방법 및 참고사항

배경

업그레이드 준비 단계

도전 과제 및 해결책

업그레이드 실행

문제 해결 및 최적화

학습 및 결론

업그레이드 사전지식

1. 준비 단계

2. 호환성 검사

■ MySQL 8.0 업그레이드 방법.

예상되는 로그 메시지

주의사항

업그레이드 이후 내부적으로 고려해야할 항목들

1) character_set, collation 변경

2) lower_case_table_names 변경

2. DB 레벨에서 character, collation 변경

3. Table과 Column 레벨에서 CHARACTER, COLLATE 변경

4. Procedure, Function DDL 변경

5. MySQL 5.7의 내장 함수 'password()'가 8.0에서 deprecated

6. illegal mix of collations Error 해결방법

7. 문법 오류

1. group by 절에 정렬 조건 작성 불가**

2. NULLIF(expr1, expr2)

8. 사용자 인증 방식 변경

9. 외래키 이름의 길이

10. Index

11. 파티션을 위한 공용 테이블스페이스

관련글

티스토리툴바