MySQL 8.0 Architecture and Enhancement

下载 0

寒冰

发布于

1760

人观看

#信息技术

与以前的版本相比，MySQL8.0版本为用户提供了更多的功能。在体系结构方面有重大的变化，也增加了差异化的特性，并改进了管理数据库的效率。
在我们的讨论中，我们将介绍MySQL8.0体系结构
在磁盘上 内存中
将共享示例和用例，展示MySQL8.0中引入的新特性。

展开查看详情

4 . Buffer Pool [In-Memory] ● Caches table and index data as it is accessed. ● Permits frequently used data to be processed directly from memory. ● Configuration variable: Innodb_buffer_pool_size ●4 ●© 2018 Percona

5 . Buffer Pool [In-Memory] ● Monitoring the Buffer Pool: ○ SHOW ENGINE INNODB STATUS; ○ INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS ● Configuration to improve Performance: ○ InnoDB Buffer Pool Size ○ Multiple Buffer Pool Instances ○ InnoDB Buffer Pool Flushing ●5 ●© 2018 Percona

6 . Change Buffer [In-Memory] ● Caches changes to secondary index 3 pages. ● Configuration variable: 1 innodb_change_buffering innodb_change_buffer_max_size ● Monitoring the Buffer Pool: 2 SHOW ENGINE INNODB STATUS\G INSERT BUFFER AND ADAPTIVE HASH INDEX ●6 ●© 2018 Percona

7 . Adaptive Hash Index [In-Memory] ● Act like in-memory database on systems. ● Configuration variable: Innodb_adaptive_hash_index innodb_adaptive_hash_index_parts ● Monitoring the Buffer Pool: SHOW ENGINE INNODB STATUS\G ----> “SEMAPHORES” ●7 ●© 2018 Percona

9 . Tablespace [On-Disk] Example: innodb_data_file_path = /data/ibdata1:1G;/data/ibdata2:500M:autoextend Example: CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB; CREATE TABLE t1 (c1 int PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB; ●9 ●© 2018 Percona

10 . On-Disk Structure [On-Disk] server-uuid=b0d8f682-4c09-11e9-b6ac-38dead00d7d2 Binlog: By default Enable in MySQL 8.0 Log Buffer System tablespace Default Redo logs Global Temporary Tablespace Session Temporary Tablespace Tablespace for data dictionary tables. Default Undo Tablespace ●© 2018 Percona ●1 0

11 . Undo Tablespace & Logs [On-Disk] ● Undo tablespaces contain undo logs ● Automated and Manual truncation ● Add/Drop Undo Tablespaces at runtime [MySQL 8.0.14] Innodb_undo_directory Innodb_undo_log_truncate Example: CREATE UNDO TABLESPACE undo03 ADD DATAFILE 'undo03.ibu'; Tables: INFORMATION_SCHEMA.INNODB_TABLESPACES ●11 ●© 2018 Percona

12 . Temporary Tablespace [On-Disk] ● Global temporary tablespace User-created temporary tables ● session temporary innodb_temp_data_file_path tablespaces innodb_temp_tablespaces_dir - User-created temporary tables. - Internal temporary tables created by the optimizer. ●© 2018 Percona ●12

14 . Data Dictionary [MySQL 8.0] 5.7 8.0 Metadata files Native data dictionary based on InnoDB. .FRM, .PAR, .OPT, .TRN and .TRG files Transactional data dictionary. SQL SQL Data dictionary Data dictionary .FRM, .PAR, .OPT, .TRN and .TRG File System files DD Tables System Tables [mysql.*] MyISAM Innodb system tables InnoDB InnoDB ●14 ●© 2018 Percona

15 .MySQL 8.0 Data-at-rest encryption ● Each individual tablespace has its own encryption key ● Each tablespace key is encrypted by the Global Master Key ● Each time a tablespace is moved a new key is generated. This is called a transfer key. Tablespace Key Tablespace Key Tablespace Key Tablespace Key ●© 2018 Percona

16 . InnoDB Encryption [MySQL 8.0] ● File-Per-Table Tablespace [ Introduced in 5.7] ● System Tablespaces (ibdata) ALTER TABLESPACE mysql ENCRYPTION = 'Y/N'; ● General Tablespaces CREATE/ALTER …. ENCRYPTION = 'Y/N'; ●16 ●© 2018 Percona

17 . InnoDB Encryption [MySQL 8.0] ● UNDO Tablespaces [ innodb_undo_log_encrypt ● REDO Log [ innodb_redo_log_encrypt conf ] ● Binary Log Files and Relay Log Files [ binlog_encryption] ●17 ●© 2018 Percona

18 . Configuration and Logging [MySQL 8.0] ● Persisted System Variables set persist innodb_redo_log_encrypt=ON set persist innodb_undo_log_encrypt=ON; set persist binlog_encryption=ON; ●18 ●© 2018 Percona

19 . Logging in MySQL 8.0 ● Defaults change: log_error_verbosity=2 ● Suppress error logs of type warning or note Configuration: [mysqld] log_error_verbosity=2 # error and warning messages only log_error_suppression_list='ER_PARSER_TRACE,MY-010001,10002' ●19 ●© 2018 Percona

20 . Replication [MySQL 8.0] ● Multi-source Replication Per Channel Filters M1 M2 M3 Channel_1 Channel_2 Channel_3 Example: CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1) FOR CHANNEL channel_1; Slave ● --replicate-do-db=channel_1:db1 ● --replicate-ignore-db=channel_1:db2 ●20 ●© 2018 Percona

21 . Security [MySQL 8.0] ● SQL Roles : It is a collection of privileges Users Roles + Privileges dba_access ALL CREATE, DROP, SELECT,INSERT, dev_access UPDATE,DELETE SELECT,INSERT, app_access UPDATE,DELETE ●21 ●© 2018 Percona

22 . Security [MySQL 8.0] ● Automatic assignment and granting of default roles when new users are created [ mandatory_roles] ● Password rotation policy enforcement [default_password_lifetime] ● Old password required for SET PASSWORD for some users ●22 ●© 2018 Percona

24 . Percona Server: MySQL improved ●Patch (not fork) MySQL to add: ▪ Enterprise features for free (threadpool, PAM auth) ▪ Instrumentation ▪ Performance/scalability ▪ Selected new features ●24 ●© 2018 Percona

0点赞

0收藏

0下载