Automatic Upgrade and New Error Logging in MySQL 8.0

下载 0

知可追

发布于

1742

人观看

#信息技术

我们将从MySQL5.7升级到MySQL8.0（8.0.15），从旧的元数据存储升级到事务数据字典。我们将介绍自动升级的新可能性，升级速度和可靠性的主要进步，以及MySQL升级检查器中的新一致性检查。
MySQL8.0有一个可插入的错误日志。我们将讨论传统的错误记录器和JSON错误记录器，它们为用户提供高级过滤。

展开查看详情

1 .Automatic Upgrade and New Error Logging in MySQL 8.0 Ståle Deraas, Software Development Director Oracle, MySQL May 2019 Copyright Copyright©©2019, 2019,Oracle Oracleand/or and/orits itsaffiliates. affiliates.All Allrights rightsreserved. reserved.|

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 a 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. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 2

3 .Agenda 1 Introduction to upgrade 2 A straight forward upgrade to MySQL 8.0 3 Upgrade to MySQL 8.0 in detail 4 Smoother upgrade going forward 5 Improvements to error logging in MySQL 8.0 Copyright © 2019, Oracle and/or its affiliates. All rights reserved. |

5 .Upgrading in General • Why upgrade the MySQL installation? – Security concerns – Performance and Scalability – New functionality – Reduce tech debt for the MySQL installation • Multiple version upgrade is complex (5.6 -> 5.7 -> 8.0) • Eg. deprecated functionality in 5.7, removed in 8.0 Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 5

6 .Upgrading in General - Biting the Bullet • At some point upgrade needs to happen! • DBA feedback 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 adopt new version gradually – When switching, downtime should be minimal Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 6

8 .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.25 - 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. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 8

9 .MySQL upgrade 5.7 -> 8.0, smooth sailing! Con’t • Backup your data directory and install MySQL 8.0 • Start the MySQL 8.0 server – In-place upgrade, all upgrade processing happens automatically • Inspect the error log 2019-05-24T10:30:27.672011Z 0 [System] [MY-010116] [Server] /exp/mysqld (mysqld 8.0.17) starting as process 23158 2019-05-24T10:30:27.700032Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2019-05-24T10:30:34.341342Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data. 2019-05-24T10:30:36.438459Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' started. 2019-05-24T10:30:52.447935Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' completed. 2019-05-24T10:30:53.034429Z 0 [System] [MY-010931] [Server] /exp/mysqld: ready for connections. Version: '8.0.17' socket: '/tmp/mysql.sock' port: 3306 Source distribution. 2019-05-24T10:30:53.267799Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind- address: '::' port: 33060 • Verify that apps and services are working as expected Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 9

11 .Goals for the MySQL 8.0 upgrade experience • Upgrade to be faster and with lower risk – Eliminate legacy issues with metadata • Transition from legacy metadata handling to transactional data dictionary • The upgrade process will produce a consistent data dictionary • Help DBAs upgrading MySQL – Better support preparing for the upgrade • Added upgrade_checker to the MySQL Shell – Better support during the upgrade • Added upgrade checks to the MySQL server • Prohibit legacy issues from entering the MySQL 8.0 metadata store Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 11

12 .MySQL Data Dictionary before MySQL 8.0 Data Dictionary Files File system FRM TRG OPT SQL System Tables (mysql.â) MyISAM user events proc InnoDB System Tables InnoDB Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 12

14 .MySQL 8.0 highlights • Read release notes https://dev.mysql.com/doc/relnotes/mysql/8.0/en/ and https://dev.mysql.com/doc/refman/8.0/en/upgrading.html • Read blogs on https://mysqlserverteam.com • New features in 8.0, and there is a lot of them: • https://mysqlserverteam.com/the-complete-list-of-new-features-in-mysql-8-0/ • Deprecations and removals • Defaults changes • upgrade_checker in MySQL shell Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 14

15 .MySQL upgrade_checker – New tool part of MySQL shell – upgrade_checker checks your MySQL installation readiness for upgrade – Identify issues preventing upgrade • Run the tool on your MySQL installation and make necessary changes – upgrade_checker is in active development and more checks will be added • Part of MySQL update releases Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 15

16 .MySQL upgrade_checker, esp 5.7 -> 8.0 • Usage of old temporal type • Usage of partitioned tables in • Conflicting db object names and shared tablespaces reserved keywords • Usage of removed functions • Usage of utf8mb3 charset • Usage of removed GROUP BY • Reserved tablenames in mysql ASC/DESC schema • Removed system variables • FK names longer than 64 chars • System variable with new defaults • Usage of obsolete sql_mode • Inconsistencies after file removal • ENUM/SET column definitions • Issues reported by ”check table x containing elements longer than for upgrade” command 255 characters • New default auth plugin checks Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 16

17 .Issues detected by the upgrade_checker: Example • The Transactional Data Dictionary is stored as tables in the mysql schema, and can not conflict with user table names – Detect with SQL, by the upgrade_checker: SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql’ and LOWER(TABLE_NAME) IN ( 'catalogs’, ’character_sets’....); – ACTION: Rename tables with conflicting name Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 17

19 .Smoother upgrade going forward • How can we continue improving the upgrade? –We want to reduce time and risk even further –Reduce need for manual intervention • Bulk of time spent for in-place upgrade of MySQL: –Harvest metadata for analysis –Examine all user tables Copyright © 2019, Oracle and/or its affiliates. All rights reserved. |

21 .Upgrade MySQL 8.0 -> • MySQL now stores all metadata in InnoDB (GA) – Enables fast metadata analysis with SQL-queries • Added metadata for versioning (GA) – The new mysqld executable knows which version it is upgrading from • Improved protection of metadata, good for security reasons (Partly done) – Enforced metadata integrity • Remove need for mysql_upgrade client (8.0.16) – Move functionality to mysqld proper – Docker/container friendly • Update of help tables (8.0.16) Copyright © 2019, Oracle and/or its affiliates. All rights reserved. |

22 .Upgrade MySQL 8.0 -> the GREAT news • The traditional MySQL upgrade 1. Stop old MySQL server 2. Change binaries to new MySQL server version 3. Adjust config, my.cnf of new server version 4. Start new MySQL server 5. Run mysql_upgrade to possibly upgrade system tables and user tables • Potentially time consuming 6. Restart MySQL server • Adds downtime, container/docker unfriendly Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 22

23 .Upgrade MySQL 8.0 -> the GREAT news con’t • The MySQL 8.0 upgrade 1. Stop old MySQL server 2. Change binaries to new MySQL server version 3. Adjust config, my.cnf of new server version 4. Start new MySQL server (once) • Analyze metadata and automatically upgrade • Speeding up upgrade • Container/Docker friendly 5. Run mysql_upgrade to possibly upgrade system tables and user tables • Potentially time consuming 6. Restart MySQL server • Adds downtime, container/docker unfriendly Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 23

24 .Upgrade MySQL 8.0 – new option to mysqld • New option --upgrade • --upgrade=AUTO mysqld upgrades anything it determines to be out of date (default option) • --upgrade=NONE mysqld upgrades nothing, exits with an error if anything must be upgraded • Advanced option values • --upgrade=MINIMAL • --upgrade=FORCE – These exist because MySQL currently allows DDL on some system tables – We foresee that MINIMAL and FORCE will go away in a future release Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 24

25 .Summary: Upgrade MySQL 8.0 -> • Lowers risk – The upgrade_checker identifies potential issues • In active development – Metadata integrity • Faster upgrade process – Fast metadata analysis – Fast metadata upgrade – Removal of mysql_upgrade • Simplified upgrade process – Fewer steps – Automatic metadata upgrade – Help tables updated as part of upgrade Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 25

27 .Error logging – feedback from community pre MySQL 8.0 – Too verbose by default – Useful info left out – Hard to filter • Some special filtering options for certain messages – No identification of subsystem source for error message – No error codes, so parsing messages is needed to identify the error – Bootstrap messages might get lost – Fixed format Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 27

28 .Error logging – MySQL 5.7 startup and shutdown output 2019-04-25T14:53:40.062118Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use -- explicit_defaults_for_timestamp server option (see documentation for more details). 2019-04-25T14:53:40.062355Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled 2019-04-25T14:53:40.062726Z 0 [Note] D:\mysql\mysqld.exe (mysqld 5.7.25-debug) starting as process 8160 ... 2019-04-25T14:53:40.081310Z 0 [Note] InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!! 2019-04-25T14:53:40.082415Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2019-04-25T14:53:40.083751Z 0 [Note] InnoDB: Uses event mutexes 2019-04-25T14:53:40.084361Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier 2019-04-25T14:53:40.085487Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2019-04-25T14:53:40.089203Z 0 [Note] InnoDB: Number of pools: 1 2019-04-25T14:53:40.089872Z 0 [Note] InnoDB: Not using CPU crc32 instructions 2019-04-25T14:53:40.093530Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2019-04-25T14:53:40.269813Z 0 [Note] InnoDB: Completed initialization of buffer pool 2019-04-25T14:53:40.338223Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2019-04-25T14:53:40.363285Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1317206 2019-04-25T14:53:40.363758Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1317215 2019-04-25T14:53:40.364156Z 0 [Note] InnoDB: Database was not shutdown normally! 2019-04-25T14:53:40.364542Z 0 [Note] InnoDB: Starting crash recovery. 2019-04-25T14:53:40.725588Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2019-04-25T14:53:40.726076Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 28

29 .Error logging – MySQL 5.7 startup and shutdown output 2019-04-25T14:53:40.726636Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2019-04-25T14:53:40.864010Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB. 2019-04-25T14:53:40.878031Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2019-04-25T14:53:40.878520Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2019-04-25T14:53:40.889152Z 0 [Note] InnoDB: Waiting for purge to start 2019-04-25T14:53:40.940950Z 0 [Note] InnoDB: 5.7.25 started; log sequence number 1317215 2019-04-25T14:53:40.945300Z 0 [Note] InnoDB: Loading buffer pool(s) from D:\mysql\data\ib_buffer_pool 2019-04-25T14:53:40.945926Z 0 [Note] Plugin 'FEDERATED' is disabled. 2019-04-25T14:53:40.999322Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key 2019-04-25T14:53:41.002994Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2019-04-25T14:53:41.003573Z 0 [Note] IPv6 is available. 2019-04-25T14:53:41.003925Z 0 [Note] - '::' resolves to '::'; 2019-04-25T14:53:41.004402Z 0 [Note] Server socket created on IP: '::'. 2019-04-25T14:53:41.015464Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190425 16:53:41 2019-04-25T14:53:41.062328Z 0 [Note] Event Scheduler: Loaded 0 events 2019-04-25T14:53:41.064056Z 0 [Note] D:\mysql\mysqld.exe: ready for connections. Version: '5.7.25-debug' socket: '' port: 3306 Source distribution 2019-04-25T14:54:09.295492Z 0 [Note] D:\mysql\mysqld.exe: Normal shutdown 2019-04-25T14:54:09.296374Z 0 [Note] Giving 0 client threads a chance to die gracefully Copyright © 2019, Oracle and/or its affiliates. All rights reserved. | 29

0点赞

0收藏

0下载