Upgrading to MySQL 8.0 and a More Automated Experience

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.