Upgrading to MySQL 8.0 and a More Automated Experience

在此会话中,了解MySQL5.7到MySQL8.0升级过程中会发生什么。通过识别数据库中的旧问题,收集5.7元数据并在统一存储的MySQL8.0数据字典中重新建立,了解升级预检查工具如何帮助您为升级做准备。其他主题包括从8.0升级到更高版本,提供自动升级体验,以及为什么自动降级具有挑战性。

展开查看详情

1.Upgrading to MySQL 8.0+: a More Automated Upgrade Experience Dmitry Lenev, Software Developer Oracle/MySQL, November 2018

2.Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

3.Agenda ● Introduction ● A straightforward upgrade to MySQL 8.0 ● Upgrade to MySQL 8.0 in detail ● A better future post MySQL 8.0

4.Introduction Upgrading in general

5.Why upgrade MySQL installation? ● Security concerns ● Bug fixes ● Performance and Scalability ● New features ● Reduce tech debt for installation ━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

6.Why upgrade MySQL installation? ● Security concerns ● Bug fixes ● Performance and Scalability ● New features ● Reduce tech debt for installation ━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

7.Why upgrade MySQL installation? ● Security concerns ● Bug fixes ● Performance and Scalability ● New features ● Reduce tech debt for installation ━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

8.Why upgrade MySQL installation? ● Security concerns ● Bug fixes ● Performance and Scalability ● New features ● Reduce tech debt for installation ━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

9.Why upgrade MySQL installation? ● Security concerns ● Bug fixes ● Performance and Scalability ● New features ● Reduce tech debt for installation ━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

10.Why do we postpone upgrades? ● Do we have required knowledge? ● Do we have required resources? ● Do we have time? ● Cost for doing the actual work necessary to upgrade ━ For DBAs and possibly consultants ● Can we afford it? ━ Cost for lost business during the switchover, depending on downtime

11.Why do we postpone upgrades? ● Do we have required knowledge? ● Do we have required resources? ● Do we have time? ● Cost for doing the actual work necessary to upgrade ━ For DBAs and possibly consultants ● Can we afford it? ━ Cost for lost business during the switchover, depending on downtime

12.Why do we postpone upgrades? ● Do we have required knowledge? ● Do we have required resources? ● Do we have time? ● Cost for doing the actual work necessary to upgrade ━ For DBAs and possibly consultants ● Can we afford it? ━ Cost for lost business during the switchover, depending on downtime

13.Why do we postpone upgrades? ● Do we have required knowledge? ● Do we have required resources? ● Do we have time? ● Cost for doing the actual work necessary to upgrade ━ For DBAs and possibly consultants ● Can we afford it? ━ Cost for lost business during the switchover, depending on downtime

14.Why do we postpone upgrades? ● Do we have required knowledge? ● Do we have required resources? ● Do we have time? ● Cost for doing the actual work necessary to upgrade ━ For DBAs and possibly consultants ● Can we afford it? ━ Cost for lost business during the switchover, depending on downtime

15.Biting the Bullet ● At some point upgrade needs to happen! ● DBA input regarding upgrading MySQL: ━ Reducing risk and cost is key ━ Total duration of upgrade should be short ━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

16.Biting the Bullet ● At some point upgrade needs to happen! ● DBA input regarding upgrading MySQL: ━ Reducing risk and cost is key ━ Total duration of upgrade should be short ━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

17.Biting the Bullet ● At some point upgrade needs to happen! ● DBA input regarding upgrading MySQL: ━ Reducing risk and cost is key ━ Total duration of upgrade should be short ━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

18.Biting the Bullet ● At some point upgrade needs to happen! ● DBA input regarding upgrading MySQL: ━ Reducing risk and cost is key ━ Total duration of upgrade should be short ━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

19.Biting the Bullet ● At some point upgrade needs to happen! ● DBA input regarding upgrading MySQL: ━ Reducing risk and cost is key ━ Total duration of upgrade should be short ━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

20.Biting the Bullet ● At some point upgrade needs to happen! ● DBA input regarding upgrading MySQL: ━ Reducing risk and cost is key ━ Total duration of upgrade should be short ━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

21.Upgrade to MySQL 8.0 The straightforward case

22.MySQL upgrade 5.7 → 8.0, smooth sailing! ● Read release notes: Conclusion – No problems ● Run upgrade_checker me@siv20$ ./mysqlsh root:@localhost:3307 -e "util.checkForServerUpgrade();” The MySQL server at localhost:3307 will now be checked for compatibility issues for upgrade to MySQL 8.0... MySQL version: 5.7.24 - Source distribution   1) Usage of db objects with names conflicting with reserved keywords in 8.0   No issues found   2) Usage of utf8mb3 charset   No issues found   …… No known compatibility errors or issues for upgrading the target server to MySQL 8 were found.

23.MySQL upgrade 5.7 → 8.0, smooth sailing! ● Read release notes: Conclusion – No problems ● Run upgrade_checker me@siv20$ ./mysqlsh root:@localhost:3307 -e "util.checkForServerUpgrade();” The MySQL server at localhost:3307 will now be checked for compatibility issues for upgrade to MySQL 8.0... MySQL version: 5.7.24 - Source distribution   1) Usage of db objects with names conflicting with reserved keywords in 8.0   No issues found   2) Usage of utf8mb3 charset   No issues found   …… No known compatibility errors or issues for upgrading the target server to MySQL 8 were found.

24.MySQL upgrade 5.7 → 8.0, smooth sailing! ● Read release notes: Conclusion – No problems ● Run upgrade_checker me@siv20$ ./mysqlsh root:@localhost:3307 -e "util.checkForServerUpgrade();” The MySQL server at localhost:3307 will now be checked for compatibility issues for upgrade to MySQL 8.0... MySQL version: 5.7.24 - Source distribution   1) Usage of db objects with names conflicting with reserved keywords in 8.0   No issues found   2) Usage of utf8mb3 charset   No issues found   …… No known compatibility errors or issues for upgrading the target server to MySQL 8 were found.

25.MySQL upgrade 5.7 → 8.0, smooth sailing! ● Backup your data directory ● Install and start MySQL 8.0 ━ In-place upgrade ● Run mysql_upgrade me@siv20$ ./mysql_upgrade  --socket=/me/mysql/mysql.sock --port=3307 --user=root Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv                                 OK mysql.component                                    OK mysql.db                                           OK ... Upgrade process completed successfully.

26.MySQL upgrade 5.7 → 8.0, smooth sailing! ● Backup your data directory ● Install and start MySQL 8.0 ━ In-place upgrade ● Run mysql_upgrade me@siv20$ ./mysql_upgrade  --socket=/me/mysql/mysql.sock --port=3307 --user=root Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv                                 OK mysql.component                                    OK mysql.db                                           OK ... Upgrade process completed successfully.

27.MySQL upgrade 5.7 → 8.0, smooth sailing! ● Backup your data directory ● Install and start MySQL 8.0 ━ In-place upgrade ● Run mysql_upgrade me@siv20$ ./mysql_upgrade  --socket=/me/mysql/mysql.sock --port=3307 --user=root Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv                                 OK mysql.component                                    OK mysql.db                                           OK ... Upgrade process completed successfully.

28.MySQL upgrade 5.7 → 8.0, smooth sailing! ● Backup your data directory ● Install and start MySQL 8.0 ━ In-place upgrade ● Run mysql_upgrade me@siv20$ ./mysql_upgrade  --socket=/me/mysql/mysql.sock --port=3307 --user=root Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv                                 OK mysql.component                                    OK mysql.db                                           OK ... Upgrade process completed successfully.

29.MySQL upgrade 5.7 → 8.0, smooth sailing! ● Restart the server and look at messages in the error log 2018-10-06T09:11:28.167169Z 0 [System] [MY-010116] [Server] /me/mysqld (mysqld 8.0.12) starting as process 27147 2018-10-06T09:11:30.261139Z 0 [System] [MY-010931] [Server] /me/mysqld: ready for connections. Version: '8.0.12'  socket: ’/me/mysql/mysql.sock'  port: 3307  Source distribution ● Verify that apps and services are working as expected ━ Simple installation – we assume that can be reactive about app issues.