Download as PDF, PPTX
![SQL Mode The SQL Mode default has been changed Version Default SQL Mode 5.6 NO_ENGINE_SUBSTITUTION 5.7 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION 8.0 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by And be aware that some have been removed and might cause error: frameworks had Error: PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'") Copyright @ 2020 Oracle and/or its affiliates. 34 / 82](https://image.slidesharecdn.com/mysqldayvirtual-bestpracticestipsupgradingtomysql8-200729192526/75/MySQL-Day-Virtual-Best-Practices-Tips-Upgrading-to-MySQL-8-0-34-2048.jpg)
![In-place Upgrade (2) start the MySQL Service: $ sudo systemctl start mysqld 2020-01-27 21:46:42 21955 [ERROR] Column count of mysql.threads is wrong. Expected 14, found 3. Created with MySQL 50562, now running 50647. Please use mysql_upgrade to x this error. as the error log recommends it, we must also run mysql_upgrade: $ sudo mysql_upgrade and don't forget to restart again MySQL: $ sudo systemctl restart mysqld Copyright @ 2020 Oracle and/or its affiliates. 48 / 82](https://image.slidesharecdn.com/mysqldayvirtual-bestpracticestipsupgradingtomysql8-200729192526/75/MySQL-Day-Virtual-Best-Practices-Tips-Upgrading-to-MySQL-8-0-48-2048.jpg)
![Let's go to 🎱 ! stop the MySQL Service: $ sudo systemctl stop mysqld upgrade the binaries to 8.0: $ sudo yum upgrade mysql-community-* --disablerepo=mysql* --enablerepo=mysql80-community start the MySQL Service: $ sudo systemctl start mysqld Jan 27 22:55:13 mysql2 mysqld[23209]: 2020-01-27T22:55:13.783334Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data. Jan 27 22:55:14 mysql2 mysqld[23209]: 2020-01-27T22:55:14.432342Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80019' started. Jan 27 22:55:23 mysql2 mysqld[23209]: 2020-01-27T22:55:23.439943Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80019' completed. Copyright @ 2020 Oracle and/or its affiliates. 57 / 82](https://image.slidesharecdn.com/mysqldayvirtual-bestpracticestipsupgradingtomysql8-200729192526/75/MySQL-Day-Virtual-Best-Practices-Tips-Upgrading-to-MySQL-8-0-57-2048.jpg)
Uploaded byFrederic Descamps
MySQL Day Virtual: Best Practices Tips - Upgrading to MySQL 8.0
The document provides guidance on upgrading to MySQL 8.0, including reading release notes, verifying application compatibility, checking for removed configuration settings, ensuring the connector supports the new default authentication plugin, and using the MySQL Shell Upgrade Checker utility to check for upgrade readiness.
MySQL Day Virtual: Best Practices Tips - Upgrading to MySQL 8.0
- 1.
- 2. Frédéric Descamps Community Manager MySQL BestPractices Tips Upgrading to MySQL 8.0 2 / 82
- 3. Safe Harbor The followingis intended to outline our general product direction. It is intended for information purpose only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied up in making purchasing decisions. The development, release, timing and pricing of any features or functionality described for Oracle´s product may change and remains at the sole discretion of Oracle Corporation. Statement in this presentation relating to Oracle´s future plans, expectations, beliefs, intentions and ptospects are "forward-looking statements" and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that a ect our business is contained in Oracle´s Securities and Exchange Commission (SEC) lings, including our most recent reports on Form 10-K and Form 10-Q under the heading "Risk Factors". These lings are available on the SEC´s website or on Oracle´s website at h p://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Copyright @ 2020 Oracle and/or its affiliates. 3 / 82
- 4. about.me/lefred Who am I? Copyright @ 2020 Oracle and/or its affiliates. 4 / 82
- 5. Frédéric Descamps @lefred MySQL Evangelist ManagingMySQL since 3.20 devops believer living in Belgium 🇧🇪 h ps://lefred.be Copyright @ 2020 Oracle and/or its affiliates. 5 / 82
- 6. Migrating to MySQL8.0 Why ? Copyright @ 2020 Oracle and/or its affiliates. 6 / 82
- 7. DB-Engines 2020 Database Ranking Copyright@ 2020 Oracle and/or its affiliates. 7 / 82
- 8. DB-Engines 2020 Database Ranking MySQLis the most popular Open Source database Copyright @ 2020 Oracle and/or its affiliates. 8 / 82
- 9. MySQL is theDBMS of the Year 2019 ! Copyright @ 2020 Oracle and/or its affiliates. 9 / 82
- 10. MySQL Developer Popularity StackOverflow Developer Survey 2019 Copyright @ 2020 Oracle and/or its affiliates. 10 / 82
- 11. MySQL is themost popular database with developers MySQL Developer Popularity Stack Overflow Developer Survey 2019 Copyright @ 2020 Oracle and/or its affiliates. 11 / 82
- 12. "This is alandmark release as MySQL eventually evolved beyond SQL-92 and the purely relational dogma. Among a few other standard SQL features, MySQL now supports window functions (over) and common table expressions (with). Without a doubt, these are the two most important post-SQL-92 features.” MySQL 8.0: one giant leap for SQL Copyright @ 2020 Oracle and/or its affiliates. 12 / 82
- 13. and many more features! Copyright @ 2020 Oracle and/or its affiliates. 13 / 82
- 14. Happy 25th AnniversaryMySQL Copyright @ 2020 Oracle and/or its affiliates. 14 / 82
- 15. I want toupgrade to MySQL 8.0 First Steps Copyright @ 2020 Oracle and/or its affiliates. 15 / 82
- 16. Some Mandatory Literature... Forany upgrade (minor and major), you need to rst read the release notes: h ps://dev.mysql.com/doc/relnotes/mysql/8.0/en/ Copyright @ 2020 Oracle and/or its affiliates. 16 / 82
- 17. Some Mandatory Literature... Forany upgrade (minor and major), you need to rst read the release notes: h ps://dev.mysql.com/doc/relnotes/mysql/8.0/en/ Changes in MySQL 8: h ps://dev.mysql.com/doc/refman/8.0/en/upgrading-from- previous-series.html Copyright @ 2020 Oracle and/or its affiliates. 17 / 82
- 18. Some Mandatory Literature... Forany upgrade (minor and major), you need to rst read the release notes: h ps://dev.mysql.com/doc/relnotes/mysql/8.0/en/ Changes in MySQL 8: h ps://dev.mysql.com/doc/refman/8.0/en/upgrading-from- previous-series.html Verify your con guration le for removed se ings: h ps://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html Copyright @ 2020 Oracle and/or its affiliates. 18 / 82
- 19. Some Mandatory Literature... Forany upgrade (minor and major), you need to rst read the release notes: h ps://dev.mysql.com/doc/relnotes/mysql/8.0/en/ Changes in MySQL 8: h ps://dev.mysql.com/doc/refman/8.0/en/upgrading-from- previous-series.html Verify your con guration le for removed se ings: h ps://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html Take a quick look to this website from @tmtms : h ps://mysql- params.tmtms.net/mysqld/ Copyright @ 2020 Oracle and/or its affiliates. 19 / 82
- 20. New Parameters andnew defaults Copyright @ 2020 Oracle and/or its affiliates. 20 / 82
- 21. Is My ApplicationReady ? There are some points very important to verify before upgrading to MySQL 8.0: Copyright @ 2020 Oracle and/or its affiliates. 21 / 82
- 22. Is My ApplicationReady ? There are some points very important to verify before upgrading to MySQL 8.0: Is the driver recent enough and if not can it be upgraded ? Copyright @ 2020 Oracle and/or its affiliates. 22 / 82
- 23. Is My ApplicationReady ? There are some points very important to verify before upgrading to MySQL 8.0: Is the driver recent enough and if not can it be upgraded ? Is my application using a speci c SQL mode ? Copyright @ 2020 Oracle and/or its affiliates. 23 / 82
- 24. Is My ApplicationReady ? There are some points very important to verify before upgrading to MySQL 8.0: Is the driver recent enough and if not can it be upgraded ? Is my application using a speci c SQL mode ? Is my application using queries speci c to some SQL mode ? Copyright @ 2020 Oracle and/or its affiliates. 24 / 82
- 25. Is My ApplicationReady ? There are some points very important to verify before upgrading to MySQL 8.0: Is the driver recent enough and if not can it be upgraded ? Is my application using a speci c SQL mode ? Is my application using queries speci c to some SQL mode ? Does my application relies somehow to Information_Schema or other system (mysql.*) tables ? Copyright @ 2020 Oracle and/or its affiliates. 25 / 82
- 26. Connector and Authentication thenew default authentication plugin (caching_sha2_password) is more secure but not supported with old connectors. all the new connectors provided by MySQL are compatible. Copyright @ 2020 Oracle and/or its affiliates. 26 / 82
- 27. Connector and Authentication thenew default authentication plugin (caching_sha2_password) is more secure but not supported with old connectors. all the new connectors provided by MySQL are compatible. if your connector doesn't support the new authentication plugin, you should force the use of the old native one by adding in my.cnf: default-authentication-plugin = mysql_native_password Copyright @ 2020 Oracle and/or its affiliates. 27 / 82
- 28. mysql> select user,plugin from mysql.user limit 2; +-------+-----------------------+ | user | plugin | +-------+-----------------------+ | books | mysql_native_password | | dev | caching_sha2_password | +-------+-----------------------+ 2 rows in set (0.00 sec) 8.0 Connector and Authentication (2) be sure the credentials used by your application use the right plugin: Copyright @ 2020 Oracle and/or its affiliates. 28 / 82
- 29. Example of PHPSupport Copyright @ 2020 Oracle and/or its affiliates. 29 / 82
- 30. Example of PHPSupport (2) Copyright @ 2020 Oracle and/or its affiliates. 30 / 82
- 31. Example of PHPSupport (3) Copyright @ 2020 Oracle and/or its affiliates. 31 / 82
- 32. SQL Mode The SQLMode default has been changed Version Default SQL Mode 5.6 NO_ENGINE_SUBSTITUTION 5.7 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION 8.0 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION Copyright @ 2020 Oracle and/or its affiliates. 32 / 82
- 33. SQL Mode The SQLMode default has been changed Version Default SQL Mode 5.6 NO_ENGINE_SUBSTITUTION 5.7 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION 8.0 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Copyright @ 2020 Oracle and/or its affiliates. 33 / 82
- 34. SQL Mode The SQLMode default has been changed Version Default SQL Mode 5.6 NO_ENGINE_SUBSTITUTION 5.7 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION 8.0 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by And be aware that some have been removed and might cause error: frameworks had Error: PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'") Copyright @ 2020 Oracle and/or its affiliates. 34 / 82
- 35. Upgrade Checker Copyright @2020 Oracle and/or its affiliates. 35 / 82
- 36. Don't forget touse MySQL Shell Upgrade Checker Utility New tool in MySQL Shell Will check your MySQL 5.7 or 8.0 installation readiness for upgrade to latest version Check for legacy issues Run the tool on the 5.7 before upgrading! Always use latest version of MySQL Shell Copyright @ 2020 Oracle and/or its affiliates. 36 / 82
- 37. Don't forget touse MySQL Shell Upgrade Checker Utility New tool in MySQL Shell Will check your MySQL 5.7 or 8.0 installation readiness for upgrade to latest version Check for legacy issues Run the tool on the 5.7 before upgrading! Always use latest version of MySQL Shell Util.checkForServerUpgrade: Detected MySQL server version is 5.5.62, but this tool requires server to be at least at version 5.7 (LogicError) Copyright @ 2020 Oracle and/or its affiliates. 37 / 82
- 38. usage of oldtemporal type usage of new reserved keywords usage of utf8mb3 charset con icting name in mysql schema partitions with engine not supporting native partitioning foreign key constraint names longer than 64 characters usage of obsolete MAXDB SQL mode ag usage of orther obsolete SQL mode ags ENUM/SET column de nitions containing elements longer than 255 characters usage of partitioned tables in shared tablespaces MySQL Shell Upgrade Checker Utility Upgrade Checker performs the following checks: Copyright @ 2020 Oracle and/or its affiliates. 38 / 82
- 39. circular directory referencesin tablespace data le paths usage of removed functions usage of removed GROUP BY ASC/DESC syntax removed system variables for error logging to the system log con guration removed system variables system variables with new default values schema inconsistencies resulting from le removal or corruption table names containing 'FTS' issues reported by 'check table x for upgrade' command MySQL Shell Upgrade Checker Utility (2) Copyright @ 2020 Oracle and/or its affiliates. 39 / 82
- 40. MySQL Upgrade Checker:how to use it? From the system shell: $ mysqlsh -- util checkForServerUpgrade user@localhost:3306 --target-version=8.0.21 --output-format=JSON --con g-path=/etc/mysql/my.cnf From the New MySQL Shell: mysqlsh> util.checkForServerUpgrade('user@example.com:3306', {"password":"password", "targetVersion":"8.0.21", "con gPath":"/etc/my.cnf"}) mysqlsh> util.checkForServerUpgrade() Copyright @ 2020 Oracle and/or its affiliates. 40 / 82
- 41. I want toupgrade to MySQL 8.0 Let's do it ! Copyright @ 2020 Oracle and/or its affiliates. 41 / 82
- 42. From MySQL 5.5to MySQL 8.0 To migrate to a newer version we have 2 options: logical dump & restore in-place upgrade Copyright @ 2020 Oracle and/or its affiliates. 42 / 82
- 43. Logical Dump &Restore This is the easiest option if you don't have too much data or if you can a ord a long dump and import time: dump the data: $ mysqldump -A > dump.sql stop the MySQL Service: $ sudo systemctl stop mysqld upgrade the binaries: $ sudo yum upgrade mysql-community* Copyright @ 2020 Oracle and/or its affiliates. 43 / 82
- 44. Logical Dump &Restore (2) erase the content of the data directory: $ sudo rm -f /var/lib/mysql/* if you plan to load the system tables (like in this example), please change in my.cnf: default-authentication-plugin = mysql_native_password start the MySQL Service (that will initialize the service): $ sudo systemctl start mysqld A new password has been generated, you can nd it in the error log. You need to change it, it's a good time to use a new password for root ;-) Copyright @ 2020 Oracle and/or its affiliates. 44 / 82
- 45. Logical Dump &Restore (3) restore the data: $ mysql -u root -p < dump.sql as we restored system tables too, it's be er to restart mysqld and force and upgrade: $ sudo systemctl set-environment MYSQLD_OPTS="--upgrade=FORCE" $ sudo systemctl restart mysqld $ sudo systemctl unset-environment MYSQLD_OPTS Copyright @ 2020 Oracle and/or its affiliates. 45 / 82
- 46. Logical Dump &Restore (3) restore the data: $ mysql -u root -p < dump.sql as we restored system tables too, it's be er to restart mysqld and force and upgrade: $ sudo systemctl set-environment MYSQLD_OPTS="--upgrade=FORCE" $ sudo systemctl restart mysqld $ sudo systemctl unset-environment MYSQLD_OPTS mysql> s -------------- mysql Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL) Copyright @ 2020 Oracle and/or its affiliates. 46 / 82
- 47. In-place Upgrade When youhave a lot of data and that you consider that a single-thread logical dump and restore is not an option: 5.5 to 5.6 edit my.cnf and add for the full upgrade process: innodb_fast_shutdown = 0 stop the MySQL Service: $ sudo systemctl stop mysqld upgrade the binaries to 5.6: $ sudo yum upgrade mysql-community-* --disablerepo=mysql* --enablerepo=mysql56-community Copyright @ 2020 Oracle and/or its affiliates. 47 / 82
- 48. In-place Upgrade (2) startthe MySQL Service: $ sudo systemctl start mysqld 2020-01-27 21:46:42 21955 [ERROR] Column count of mysql.threads is wrong. Expected 14, found 3. Created with MySQL 50562, now running 50647. Please use mysql_upgrade to x this error. as the error log recommends it, we must also run mysql_upgrade: $ sudo mysql_upgrade and don't forget to restart again MySQL: $ sudo systemctl restart mysqld Copyright @ 2020 Oracle and/or its affiliates. 48 / 82
- 49. In-place Upgrade (3) Wecan perform some checks on InnoDB Formats: mysql> SELECT FILE_FORMAT, ROW_FORMAT, count(*) Tot FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES GROUP BY FILE_FORMAT, ROW_FORMAT; +-------------+------------+-----+ | FILE_FORMAT | ROW_FORMAT | Tot | +-------------+------------+-----+ | Antelope | Compact | 15 | | Antelope | Redundant | 4 | +-------------+------------+-----+ Let's check if we have old temporal columns: Copyright @ 2020 Oracle and/or its affiliates. 49 / 82
- 50. In-place Upgrade (4) mysql>SELECT t.TABLE_SCHEMA,t.ENGINE,t.TABLE_NAME,c.COLUMN_NAME,c.COLUMN_TYPE FROM INFORMATION_SCHEMA.TABLES t INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME LEFT OUTER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES ist ON ist.NAME = CONCAT(t.TABLE_SCHEMA,'/',t.TABLE_NAME) LEFT OUTER JOIN INFORMATION_SCHEMA.INNODB_SYS_COLUMNS isc ON isc.TABLE_ID = ist.TABLE_ID AND isc.NAME = c.COLUMN_NAME WHERE c.COLUMN_TYPE IN ('time', 'timestamp', 'datetime') AND t.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') AND t.TABLE_TYPE = 'base table' AND (t.ENGINE != 'innodb' OR (t.ENGINE = 'innodb' AND isc.MTYPE = 6)) ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME; +--------------+--------+-------------+---------------+-------------+ | TABLE_SCHEMA | ENGINE | TABLE_NAME | COLUMN_NAME | COLUMN_TYPE | +--------------+--------+-------------+---------------+-------------+ | sbtest_tpcc | InnoDB | customer1 | c_since | datetime | | sbtest_tpcc | InnoDB | history1 | h_date | datetime | | sbtest_tpcc | InnoDB | orders1 | o_entry_d | datetime | | sbtest_tpcc | InnoDB | orders2 | o_entry_d | datetime | | sbtest_tpcc | InnoDB | order_line1 | ol_delivery_d | datetime | +--------------+--------+-------------+---------------+-------------+ Copyright @ 2020 Oracle and/or its affiliates. 50 / 82
- 51. In-place Upgrade (5) 5.6to 5.7 stop the MySQL Service: $ sudo systemctl stop mysqld upgrade the binaries to 5.7: $ sudo yum upgrade mysql-community-* --disablerepo=mysql* --enablerepo=mysql57-community start the MySQL Service: $ sudo systemctl start mysqld Copyright @ 2020 Oracle and/or its affiliates. 51 / 82
- 52. In-place Upgrade (6) werun mysql_upgrade: $ sudo mysql_upgrade We can see that the process also repairs some tables (with the previous old time format): Checking databases. sbtest_tpcc.customer1 error : Table rebuild required. Please do "ALTER TABLE `customer1` FORCE" or dump/reload to x it! ... Repairing tables `sbtest_tpcc`.`customer1` Running : ALTER TABLE `sbtest_tpcc`.`customer1` FORCE status : OK ... Upgrade process completed successfully. Copyright @ 2020 Oracle and/or its affiliates. 52 / 82
- 53. In-place Upgrade (7) anddon't forget to restart again MySQL: $ sudo systemctl restart mysqld let's verify mysql> SELECT FILE_FORMAT, ROW_FORMAT, count(*) Tot FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES GROUP BY FILE_FORMAT, ROW_FORMAT; +-------------+------------+-----+ | FILE_FORMAT | ROW_FORMAT | Tot | +-------------+------------+-----+ | Antelope | Compact | 5 | | Antelope | Redundant | 5 | | Barracuda | Dynamic | 25 | +-------------+------------+-----+ Copyright @ 2020 Oracle and/or its affiliates. 53 / 82
- 54. In-place Upgrade (8) 5.7to 8.0 now we can use MySQL Upgrade Checker: $ mysqlsh -- util checkForServerUpgrade root@localhost --con g-path=/etc/my.cnf The MySQL server at localhost, version 5.7.29 - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.19... ... Errors: 0 Warnings: 28 Notices: 0 No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not signi cant before upgrading. Copyright @ 2020 Oracle and/or its affiliates. 54 / 82
- 55. MySQL Upgrade Checker: details 16) System variables with new default values Warning: Following system variables that are not de ned in your con guration le will have new default values. Please review if you rely on their current values and if so de ne them before performing upgrade. More information: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ back_log - default value will change character_set_server - default value will change from latin1 to utf8mb4 collation_server - default value will change from latin1_swedish_ci to utf8mb4_0900_ai_ci event_scheduler - default value will change from OFF to ON explicit_defaults_for_timestamp - default value will change from OFF to ON innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to 2 (interleaved) innodb_ ush_method - default value will change from NULL to fsync (Unix), unbuffered (Windows) innodb_ ush_neighbors - default value will change from 1 (enable) to 0 (disable) innodb_max_dirty_pages_pct - default value will change from 75 (%) 90 (%) innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10 (%) innodb_undo_log_truncate - default value will change from OFF to ON ... Copyright @ 2020 Oracle and/or its affiliates. 55 / 82
- 56. MySQL Upgrade Checker: details (2) 17) Zero Date, Datetime, and Timestamp values Warning: By default zero date/datetime/timestamp values are no longer allowed in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in SQL_MODE by default. These modes should be used with strict mode as they will be merged with strict mode in a future release. If you do not include these modes in your SQL_MODE setting, you are able to insert date/datetime/timestamp values that contain zeros. It is strongly advised to replace zero values with valid ones, as they may not work correctly in the future. More information: https://lefred.be/content/mysql-8-0-and-wrong-dates/ sbtest_tpcc.orders2.o_entry_d - column has zero default value: 0000-00-00 00:00:00 Copyright @ 2020 Oracle and/or its affiliates. 56 / 82
- 57. Let's go to🎱 ! stop the MySQL Service: $ sudo systemctl stop mysqld upgrade the binaries to 8.0: $ sudo yum upgrade mysql-community-* --disablerepo=mysql* --enablerepo=mysql80-community start the MySQL Service: $ sudo systemctl start mysqld Jan 27 22:55:13 mysql2 mysqld[23209]: 2020-01-27T22:55:13.783334Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data. Jan 27 22:55:14 mysql2 mysqld[23209]: 2020-01-27T22:55:14.432342Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80019' started. Jan 27 22:55:23 mysql2 mysqld[23209]: 2020-01-27T22:55:23.439943Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80019' completed. Copyright @ 2020 Oracle and/or its affiliates. 57 / 82
- 58. Woooohoooo o/ Copyright @2020 Oracle and/or its affiliates. 58 / 82
- 59. MySQL 8.0 is Great! Copyright @ 2020 Oracle and/or its affiliates. 59 / 82
- 60. logical dump andload for speed & fixes MySQL Shell Dump & Load Copyright @ 2020 Oracle and/or its affiliates. 60 / 82
- 61. MySQL Shell Dump& Load For MySQL < 5.7, mysqldump is used to perform logical dumps. Since 8.0.21, for MySQL 5.7 and 8.0, MySQL Shell can be used for amazing performance and to migrate to OCI using Object Store. CLOUD Copyright @ 2020 Oracle and/or its affiliates. 61 / 82
- 62. MySQL Shell Dump Copyright@ 2020 Oracle and/or its affiliates. 62 / 82
- 63. MySQL Shell Load Copyright@ 2020 Oracle and/or its affiliates. 63 / 82
- 64. MySQL Shell Load Pleasenote the SQL statements to increase performance ! Copyright @ 2020 Oracle and/or its affiliates. 64 / 82
- 65. MySQL Shell Load Pleasenote the SQL statements to increase performance ! Don't forget to enable back redo logs ! Copyright @ 2020 Oracle and/or its affiliates. 65 / 82
- 66. Dump & Loadto convert charset Since MySQL Shell 8.0.21, it's possible to use the dump and load to also migrate the data and convert the charset ! Example: 3) Usage of utf8mb3 charset Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. More information: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html nation.continents.name - column's default character set: utf8 nation.countries.name - column's default character set: utf8 nation.countries.country_code2 - column's default character set: utf8 nation.countries.country_code3 - column's default character set: utf8 nation.guests.name - column's default character set: utf8 nation.languages.language - column's default character set: utf8 ... Copyright @ 2020 Oracle and/or its affiliates. 66 / 82
- 67. Dump & Loadto convert charset (2) We can dump the data like previously but we will load it in 3 steps: load the schemes without data Copyright @ 2020 Oracle and/or its affiliates. 67 / 82
- 68. Dump & Loadto convert charset (2) We can dump the data like previously but we will load it in 3 steps: load the schemes without data convert the empty tables to uft8mb4 Copyright @ 2020 Oracle and/or its affiliates. 68 / 82
- 69. Dump & Loadto convert charset (2) We can dump the data like previously but we will load it in 3 steps: load the schemes without data convert the empty tables to uft8mb4 load the data Copyright @ 2020 Oracle and/or its affiliates. 69 / 82
- 70. Dump & Loadto convert charset (3) Load the schemes without data: Copyright @ 2020 Oracle and/or its affiliates. 70 / 82
- 71. Dump & Loadto convert charset (3) Load the schemes without data: Copyright @ 2020 Oracle and/or its affiliates. 71 / 82
- 72. Dump & Loadto convert charset (4) Prepare the changes: use information_schema; SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql FROM `TABLES` where table_schema not in ('sys','information_schema','mysql', 'performance_schema') group by table_schema into out le '/var/lib/mysql- les/01.sql'; SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `TABLES` where table_schema not in ('sys','information_schema','mysql', 'performance_schema') group by table_schema, table_name into out le '/var/lib/mysql- les/02.sql'; SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `", column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") as _sql FROM `COLUMNS` where table_schema not in ('sys','information_schema','mysql', 'performance_schema') and CHARACTER_SET_NAME IS NOT NULL into out le '/var/lib/mysql- les/03.sql'; Copyright @ 2020 Oracle and/or its affiliates. 72 / 82
- 73. Dump & Loadto convert charset (5) Apply the changes: mysql> . /var/lib/mysql- les/01.sql mysql> . /var/lib/mysql- les/02.sql mysql> . /var/lib/mysql- les/03.sql Copyright @ 2020 Oracle and/or its affiliates. 73 / 82
- 74. Dump & Loadto convert charset (6) Finally load the data: Copyright @ 2020 Oracle and/or its affiliates. 74 / 82
- 75. And now ? PostChecks Copyright @ 2020 Oracle and/or its affiliates. 75 / 82
- 76. Post Checks MySQL UpgradeChecker warned us about the invalid default date, but it doesn't scan the data. It's possible to write some queries to verify this... or use a plugin for the MySQL Shell: Copyright @ 2020 Oracle and/or its affiliates. 76 / 82
- 77. Post Checks (2) Let'sverify the authentication methods used on our system: Copyright @ 2020 Oracle and/or its affiliates. 77 / 82
- 78. Post Checks (3) ComparingResults from previous version is not a straight forward task, and even if you can rely on PFS tables, you cannot link the results between version relying on the digest as it's created during parsing tree fragments and it di ers accross versions. I you want to compare, I advise you to create a view like this one and link the results using the new common_hash: CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW `events_statements_summary_by_digest_hash` AS SELECT *, SHA2(TRIM(DIGEST_TEXT), 224) COMMON_HASH FROM performance_schema.events_statements_summary_by_digest; Copyright @ 2020 Oracle and/or its affiliates. 78 / 82
- 79. Thank you ! Copyright@ 2020 Oracle and/or its affiliates. 79 / 82
- 80. Don't forget thatin one year MySQL 5.6 is EOL ! Copyright @ 2020 Oracle and/or its affiliates. 80 / 82
- 81. Upgrade to MySQL8.0 It's time to upgrade to MySQL 8.0 ! Copyright @ 2020 Oracle and/or its affiliates. 81 / 82
- 82. Upgrade to MySQL8.0 It's time to upgrade to MySQL 8.0 ! Copyright @ 2020 Oracle and/or its affiliates. 82 / 82