MySQL Database

What is MySQL Database?

MySQL is an open-source relational database management system based on SQL – Structured Query Language. The application is used for a wide range of purposes, including data warehousing, e-commerce, and logging applications.

Open Source means that it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything. If you wish, you may study the source code and change it to suit your needs. Most of the social media platforms run on MySQL Database

It is developed, distributed, and supported by Oracle Corporation.

MySQL is an integral part of all major organizations including popular internet organizations such as Facebook, YouTube, Twitter, Instagram, and Wikipedia. 

In fact, MySQL has a market share of 42.00% in the relational database market.

For more interesting posts on MySQL, please click on INDEX

Why use MySQL?

Compatibility: MySQL was originally designed to ensure maximum compatibility. This includes not only web services and applications, but several other technologies and architectures. The relational database system is widely compatible and runs on all major operating systems including Unix-based operating systems such as macOS and Windows.

Easy-to-use: The tabular model of MySQL allows for an easy-to-use experience despite the relational nature of the databases and their storage structure. From installation to database server configuration of workloads, we can implement it easily. The variety of data structures that MySQL uses include standard data types such as numeric, date, time, alphanumeric, and more advanced data types such as JSON. 

Data organization: Because MySQL is a relational database, it stands out from other database management systems since the data is stored in an organized set of individual tables, unlike other databases where the data is often unstructured. This simple process of organizing the data optimizes data processing in processes such as data retrieval, updating information, and even indices establishing the relation between two different tables.

Open-source environment: Coming to why we started this whole conversation, being a popular open source database product makes MySQL a relied-upon solution for major organizations. Being open-source allows any individual or enterprise unrestricted use of the MySQL code base enabling them to modify, publish, and expand. 

 

Editions

MySQL is offered under two different editions: the open source MySQL Community Server and the proprietary Enterprise Server.

MySQL Open-Source Community Edition

Being an open-source product, MySQL offers the MySQL Community Server that is freely available on over 20 platforms that include Windows, Linux, Unix, and Mac OS to name a few.

 Multiple storage engines offered in the community edition include InnoDB, MyISAM, and memory and Network Database (NDB). The databases are also modeled, developed, and administered by the Oracle MySQL workbench community tools.

The community edition uses the General Public License (GPL) that allows the user to access and modify the source code. The updated code can be distributed as a part of the tool. When using the community edition, you, as a user of the version must understand and adhere to the terms and conditions of the GPL.

If you are embedding the community edition in your application, the source will have to be made available under the General Public License. This is exactly the reason you need to ensure that you understand the terms.

Before moving further, we need to know what the GPL or the GNU General Public License is. In simple terms, GPL is a list of terms and conditions used for copying, modifying, and distributing free software.

Oracle also offers a Cluster edition which is a variation of the community edition and is freely available. The Generally Available (GA) cluster edition offers the same basic features as the community edition but adds a distributed, multi-master architecture. This enables the scalability of MySQL across multiple systems and ensures fault tolerance.

 

MySQL Enterprise Edition

If you have the need for the latest features and 24×7 support from Oracle, the MySQL Enterprise edition is the best bet. The enterprise edition is built on the standard edition of MySQL. 

The standard edition, in addition to the offerings of the community edition, offers several other add-on high-performance features. Unlike the MySQL workbench community edition, you get a MySQL workbench standard edition that supports more features such as automation of database documentation and MySQL router.

The enterprise edition builds upon this and focuses on features that are specific to the enterprise. Moreover, with the enterprise edition, Oracle offers the MySQL workbench enterprise edition tool. Along with the tool come several advanced features such as the tools to monitor MySQL servers, data backup facility, and implementation of enterprise security. The tools focus on scalability and increased availability.

 

MySQL Client server Architecture

The Architecture of MYSQL contain following major layer’s :

• Client
• Server
• Storage Layer

Client Layer:
This layer is the topmost layer in the above diagram. The Client give request instructions to the Serve with the help of Client Layer .The Client make request through  Command Prompt or through GUI  screen by using valid MYSQL commands and expressions .If the Expressions and commands are valid then the output is obtained on the screen.  Some important services of client layer are :

• Connection Handling. – The default port is 3306
• Authentication
• Security

 

Server Layer:  

Mysql Server is the central program that manages Database contents.

Mysqld is the Mysql Server Process. It will listen for incoming server requests.

The second layer of MYSQL architecture is responsible for all logical functionalities of relational database management system of MYSQL. This Layer of MYSQL System is also known as “Brain of MYSQL Architecture”. When the Client give request instructions to the Server and the server gives the output as soon as the the instruction is matched.  The various sub components of MYSQL server are:

• Thread Handling –
  When a client send request to the server and server will accept the request and the client is connected .. When Client is connected to the server at that time , a client get it’s own thread for it’s connection. This thread is provided by  thread handling  of Server Layer. Also the queries of  client side  which is executed by the thread  is also handled by Thread Handling module.
• Parser –
  A Parser is a type of Software Component that  built a data structure(parse tree)  of  given input . Before parsing lexical  analysis is done i.e. input is broken into number of tokens . After the  data is available in the smaller elements parser perform Syntax Analysis , Semantics  Analysis after that parse tree is generated as output.

So it validates the query’s Syntax and semantics and converts it to a standard form

• Authorization – Verifies that the connected user is allowed to run the query and has enough permissions on the objects the query refers to
• Optimizer –
  As soon as the parsing is done ,  various types of optimization techniques are applied at Optimizer Block. These techniques may include rewriting the query, order of scanning of tables and choosing the right indexes to use etc.

Creates and optimal execution plan for each query. This involves deciding which indexes to use and in which order to process the tables.

• Query Cache –
  Query Cache stores the complete result set for inputted query statement. Eve before Parsing , MYSQL Server consult query cache . When client write a query , if the query written by client is identical in the cache then the server simply skip the parsing, optimization and even execution, it just simply display the output from the cache.
• Buffer and Cache –
  Cache and will buffer store the previous query or problem asked by user. When User write a query then it firstly goes to Query Cache then query cache will check that the same query or problem is available in the cache. If the same query is available then it will provide output without interfering Parser, Optimizer.
• Table Metadata Cache –
  The metadata cache is a reserved area of memory used for tracking information on databases, indexes, or objects. The greater the number of open databases, indexes, or objects, the larger the metadata cache size.
• Key Cache –
  A  key cache  is an index entry that uniquely identifies an object in a cache. By default, edge servers cache content based on the entire resource path and a query string.

SQL Statement Processing

Storage Layer: 
This Storage Engine Layer of MYSQL Architecture make it’s unique and most preferable for developer’s . Due to this Layer MYSQL layer is counted as the mostly used RDBMS and is widely used. In MYSQL server , for different situations and requirement’s different types of storage engines are used which are InnoDB ,MyISAM , NDB ,Memory etc. These storage engines are used as pluggable  storage enginer where tables created by user are plugged with them.

 

Storage Engines

Storage Engines are server components that act as handlers for different table types

MySQL delegates the task of handling data rows to these storage engines, which stores the data on disk, memory or other components on the network and provide indexes and other row optimizations.

Storage engines are MySQL components that handle the SQL operations for different table types. 

The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM

To determine which storage engines your server supports, use the SHOW ENGINES statement. The value in the Support column indicates whether an engine can be used. A value of YES, NO, or DEFAULT indicates that an engine is available, not available, or available and currently set as the default storage engine.

MyISAM manages non transactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations. MyISAM is supported in all MySQL configurations, and is the default storage engine until Version 5.4

InnoDB is the default  engine from version 5.5

InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

 

The main difference between MyISAM and INNODB are :

• MyISAM does not support transactions by tables while InnoDB supports.
• There are no possibility of row-level locking, relational integrity in MyISAM but with InnoDB this is possible. MyISAM has table-level locking.
• InnoDB does not support FULLTEXT index while MyISAM supports.
• InnoDB is better option while you are dealing with larger database because it supports transactions, volume while MyISAM is suitable for small project.
• As InnoDB supports row-level locking which means inserting and updating is much faster as compared with MyISAM.
• InnoDB supports ACID (Atomicity, Consistency, Isolation and Durability) property while MyISAM does not support.
• MyISAM does not support FOREIGN-KEY referential-integrity constraints while InnoDB supports.

	INNODB	MYISAM
1	It’s New Engine	It’s Old Engine
2	InnoDB is more complex	MYISAM is Simpler
3	InnoDB is more strict in data integrity	MYISAM is loose in data integrity
4	InnoDB implements row-level lock for inserting and updating	MyISAM implements table-level lock.
5	InnoDB has foreign keys and relationship constraints	MyISAM does not.
6	InnoDB has better crash recovery	MyISAM is poor at recovering data integrity at system crashes.
7	InnoDB does not have full-text search index.	MyISAM has full-text search index
8	ACID compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys	Not ACID compliant and non-transactional

 

MySQL Server Logs

MySQL Server has several logs that can help you find out what activity is taking place.

Log Type	Information Written to Log
Error log	Problems encountered starting, running, or stopping mysqld
General query log	Established client connections and statements received from clients
Binary log	Statements that change data (also used for replication)
Relay log	Data changes received from a replication source server
Slow query log	Queries that took more than long_query_time seconds to execute
DDL log (metadata log)	Metadata operations performed by DDL statements

By default, no logs are enabled, except the error log on Windows.

By default, the server writes files for all enabled logs in the data directory. You can force the server to close and reopen the log files (or in some cases switch to a new log file) by flushing the logs. You can control the general query and slow query logs during runtime. You can enable or disable logging, or change the log file name. You can tell the server to write general query and slow query entries to log tables, log files, or both.

Error Log : The error log contains a record of mysqld startup and shutdown times. It also contains diagnostic messages such as errors, warnings, and notes that occur during server startup and shutdown, and while the server is running. For example, if mysqld notices that a table needs to be automatically checked or repaired, it writes a message to the error log.

If used to start mysqld, mysqld_safe may write messages to the error log. For example, when mysqld_safe notices abnormal mysqld exits, it restarts mysqld and writes a mysqld restarted message to the error log.

General Query Log : The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

When the general query log is enabled, the server writes output to any destinations specified by the log_output system variable. If you enable the log, the server opens the log file and writes startup messages to it. However, further logging of queries to the file does not occur unless the FILE log destination is selected. If the destination is NONE, the server writes no queries even if the general log is enabled. Setting the log file name has no effect on logging if the log destination value does not contain FILE.

Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). 

Binary Log : The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

• For replication, the binary log on a replication source server provides a record of the data changes to be sent to replicas. The source sends the events contained in its binary log to its replicas, which execute those events to make the same data changes that were made on the source.
• Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

The binary log is not used for statements such as SELECT or SHOW that do not modify data. To log all statements (for example, to identify a problem query), use the general query log.

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

mysqld appends a numeric extension to the binary log base name to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new file in the series each time any of the following events occurs:

• The server is started or restarted
• The server flushes the logs.
• The size of the current log file reaches max_binlog_size.

A binary log file may become larger than max_binlog_size if you are using large transactions because a transaction is written to the file in one piece, never split between files.

If you are using replication, you should not delete old binary log files on the source until you are sure that no replica still needs to use them. 

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

If the server is unable to write to the binary log, flush binary log files, or synchronize the binary log to disk, the binary log on the source can become inconsistent and replicas can lose synchronization with the source. 

By default, the binary log is synchronized to disk at each write (sync_binlog=1). If sync_binlog was not enabled, and the operating system or machine (not only the MySQL server) crashed, there is a chance that the last statements of the binary log could be lost. To prevent this, enable the sync_binlog system variable to synchronize the binary log to disk after every N commit groups. 

 

Relay Log : The relay log is used only on replicas, to hold data changes from the replication source server that must also be made on the replica.

MySQL Socket File

A socket file doesn’t actually contain data, it transports it. It is a special  type of file created with special system calls/commands. It is not an ordinary file.

It is like a pipe the server and the clients can use to connect and exchange requests and data. Also, it is only used locally. Its significance is merely as an agreed rendezvous location in the filesystem.

When you specify host=localhost, mysql client will try to login to mysql server using unix named pipe which requires a .sock file.

This can be bypassed by specifying host=127.0.0.1. This will make mysql client use TCP to connect to the server.

Socket-file connections are secure by default. They can also be encrypted, but encrypting a socket-file connection makes it no more secure and increases CPU load.

 

Client – Server Connection Ports

Port 3306 is the default port for the classic MySQL protocol (port), which is used by the mysql client, MySQL Connectors, and utilities such as mysqldump and mysqlpump. The port for X Protocol (mysqlx_port), supported by clients such as MySQL Shell, MySQL Connectors and MySQL Router, is calculated by multiplying the port used for classic MySQL protocol by 10. For example if the classic MySQL protocol port is the default value of 3306 then the X Protocol port is 33060.

As of MySQL 8.0.14, the server permits a TCP/IP port to be configured specifically for administrative connections. This provides an alternative to the single administrative connection that is permitted on the network interfaces used for ordinary connections.  The default port being 33062

Default Port/Protocol	Description	SSL or other Encryption	Required	Direction
3306/TCP	MySQL clients to the MySQL server (classic MySQL protocol)	Yes	Yes, unless you are only using X Protocol	From the MySQL client to the MySQL server
33060/TCP	MySQL clients to the MySQL server (X Protocol)	Yes	Yes, unless you are only using port 3306	From the MySQL client to the MySQL server
33062/TCP (default)	A port configured specifically for MySQL administrative connections (classic MySQL protocol)	Yes	No	From the MySQL client to the MySQL server

 

Data Directory

Information managed by the MySQL server is stored under a directory known as the data directory. 

Data directory subdirectories. Each subdirectory of the data directory is a database directory and corresponds to a database managed by the server. All MySQL installations have certain standard databases:

The mysql directory corresponds to the mysql system schema, which contains information required by the MySQL server as it runs. This database contains data dictionary tables and system tables. 

The performance_schema directory corresponds to the Performance Schema, which provides information used to inspect the internal execution of the server at runtime. 

The sys directory corresponds to the sys schema, which provides a set of objects to help interpret Performance Schema information more easily. 

The ndbinfo directory corresponds to the ndbinfo database that stores information specific to NDB Cluster (present only for installations built to include NDB Cluster).

Other subdirectories correspond to databases created by users or applications

Log files written by the server

InnoDB tablespace and log files

• The server process ID file (while the server is running).

The mysqld-auto.cnf file that stores persisted global system variable settings.

 

MySQL uses /var/lib/mysql directory as default data directory for Linux based systems.

To identify the current data directory use the following command.

SELECT @@datadir

 

InnoDB System Tablespace

The system tablespace is the storage area for the InnoDB data dictionary, the doublewrite buffer, the change buffer, and undo logs.

Change Buffer : The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

 

Double Write Buffer : The doublewrite buffer is a storage area where InnoDB writes pages flushed from the buffer pool before writing the pages to their proper positions in the InnoDB data files. If there is an operating system, storage subsystem, or unexpected mysqld process exit in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.

Although data is written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations. Data is written to the doublewrite buffer in a large sequential chunk, with a single fsync() call to the operating system (except in the case that innodb_flush_method is set to O_DIRECT_NO_FSYNC).

The doublewrite buffer is enabled by default in most cases. To disable the doublewrite buffer, set innodb_doublewrite to 0.

It may also contain table and index data if tables are created in the system tablespace rather than file-per-table tablespaces.

The system tablespace can have one or more data files. By default, a single system tablespace data file, named ibdata1, is created in the data directory. The size and number of system tablespace data files is defined by the innodb_data_file_path startup option.

The easiest way to increase the size of the system tablespace is to configure it to be auto-extending. To do so, specify the autoextend attribute for the last data file in the innodb_data_file_path setting, and restart the server. For example:

innodb_data_file_path=ibdata1:10M:autoextend

 

Suppose that the data file has grown to 988MB over time. This is the innodb_data_file_path setting after modifying the size attribute to reflect the current data file size, and after specifying a new 50MB auto-extending data file:

 

innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:80M;/disk2/ibdata2:50M:autoextend

 

 

 

File-Per-Table Tablespaces

A file-per-table tablespace contains data and indexes for a single InnoDB table, and is stored on the file system in a single data file.

File-Per-Table Tablespace Advantages

File-per-table tablespaces have the following advantages over the shared system tablespace.

• Disk space is returned to the operating system after truncating or dropping a table created in a file-per-table tablespace. Truncating or dropping a table stored in the system tablespace creates free space within the system tablespace, which can only be used for InnoDB In other words, a system tablespace does not shrink in size after a table is truncated or dropped.
• A table-copying ALTER TABLEoperation on a table that resides in the system tablespace can increase the amount of disk space occupied by the tablespace. Such operations may require as much additional space as the data in the table plus indexes. This space is not released back to the operating system as it is for file-per-table tablespaces.
• TRUNCATE TABLEperformance is better when executed on tables that reside in file-per-table tablespaces.
• Tables created in file-per-table tablespaces use the Barracuda file format. The Barracuda file format enables features associated with DYNAMICand COMPRESSED row formats.

• Tables stored in individual tablespace data files can save time and improve chances for a successful recovery when data corruption occurs, when backups or binary logs are unavailable, or when the MySQL server instance cannot be restarted.

• Tables in the shared system tablespace, which contains other structures such as the InnoDBdata dictionary and undo logs, are limited in size by the 64TB tablespace size limit. By comparison, each file-per-table tablespace has a 64TB size limit, which provides plenty of room for individual tables to grow in size.

 

Undo Tablespaces

Undo tablespaces contain undo logs, which are collections of records containing information about how to undo the latest change by a transaction to a clustered index record.

Undo logs are stored in the system tablespace by default but can be stored in one or more undo tablespaces instead. Using undo tablespaces can reducing the amount of space required for undo logs in any one tablespace. The I/O patterns for undo logs also make undo tablespaces good candidates for SSD storage.

The number of undo tablespaces used by InnoDB is controlled by the innodb_undo_tablespaces option. This option can only be configured when initializing the MySQL instance. It cannot be changed afterward.

Undo tablespaces and individual segments inside those tablespaces cannot be dropped

 

 

Schemas in Mysql

 

1. Information_schema

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

2. Performance Schema

The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level.

The Performance Schema provides a way to inspect internal execution of the server at runtime. The Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata.

The Performance Schema monitors server events. An “event” is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements. Event collection provides access to information about synchronization calls (such as for mutexes) file and table I/O, table locks, and so forth for the server and for several storage engines.

Performance Schema events are specific to a given instance of the MySQL Server. Performance Schema tables are considered local to the server, and changes to them are not replicated or written to the binary log.

Performance Schema configuration can be modified dynamically by updating tables in the performance_schema database through SQL statements. Configuration changes affect data collection immediately.

3. SYS Schema

MySQL 8.0 includes the sys schema, a set of objects that helps DBAs and developers interpret data collected by the Performance Schema. SYS schema objects can be used for typical tuning and diagnosis use cases. Objects in this schema include:

• Views that summarize Performance Schema data into more easily understandable form.
• Stored procedures that perform operations such as Performance Schema configuration and generating diagnostic reports.
• Stored functions that query Performance Schema configuration and provide formatting services.

 

Features Added in MySQL 8.0

The following features have been added to MySQL 8.0:

• Data dictionary. MySQL now incorporates a transactional data dictionary that stores information about database objects. In previous MySQL releases, dictionary data was stored in metadata files and nontransactional tables. 
• Atomic data definition statements (Atomic DDL). An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction.
• Upgrade procedure. Previously, after installation of a new version of MySQL, the MySQL server automatically upgrades the data dictionary tables at the next startup, after which the DBA is expected to invoke mysql_upgrade manually to upgrade the system tables in the mysql schema, as well as objects in other schemas such as the sys schema and user schemas.

As of MySQL 8.0.16, the server performs the tasks previously handled by mysql_upgrade. After installation of a new MySQL version, the server now automatically performs all necessary upgrade tasks at the next startup and is not dependent on the DBA invoking mysql_upgrade. In addition, the server updates the contents of the help tables (something mysql_upgrade did not do). A new –upgrade server option provides control over how the server performs automatic data dictionary and server upgrade operations. 

 

• Character set support. The default character set has changed from latin1 to utf8mb4. The utf8mb4 character set has several new collations, including utf8mb4_ja_0900_as_cs, the first Japanese language-specific collation available for Unicode in MySQL. 

 

•  Error logging was rewritten to use the MySQL component architecture. Traditional error logging is implemented using built-in components, and logging using the system log is implemented as a loadable component. In addition, a loadable JSON log writer is available. To control which log components to enable, use the log_error_services system variable.

 

• Backup lock. A new type of backup lock permits DML during an online backup while preventing operations that could result in an inconsistent snapshot. The new backup lock is supported by LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE The BACKUP_ADMIN privilege is required to use these statements.

 

• Connection management. MySQL Server now permits a TCP/IP port to be configured specifically for administrative connections. This provides an alternative to the single administrative connection that is permitted on the network interfaces used for ordinary connections even when max_connections connections are already established. 

•  The maximum permitted length of host names throughout MySQL has been raised to 255 ASCII characters, up from the previous limit of 60 characters. This applies to, for example, host name-related columns in the data dictionary, mysql system schema, Performance Schema, INFORMATION_SCHEMA, and sys schema; the MASTER_HOST value for the CHANGE MASTER TO statement; the Host column in SHOW PROCESSLIST statement output; host names in account names (such as used in account-management statements and in DEFINER attributes); and host name-related command options and system variables.

•  Previously, MySQL plugins could be written in C or C++. MySQL header files used by plugins now contain C++ code, which means that plugins must be written in C++, not C. 

• Redo Log Archiving. As of MySQL 8.0.17, InnoDB supports redo log archiving. Backup utilities that copy redo log records may sometimes fail to keep pace with redo log generation while a backup operation is in progress, resulting in lost redo log records due to those records being overwritten. The redo log archiving feature addresses this issue by sequentially writing redo log records to an archive file. Backup utilities can copy redo log records from the archive file as necessary, thereby avoiding the potential loss of data.

• Security and account management. 
  • The grant tables in the mysqlsystem database are now InnoDB (transactional) tables. Previously, these were MyISAM (nontransactional) tables. The change of grant table storage engine underlies an accompanying change to the behavior of account-management statements. Previously, an account-management statement (such as CREATE USER or DROP USER) that named multiple users could succeed for some users and fail for others. Now, each statement is transactional and either succeeds for all named users or rolls back and has no effect if any error occurs. The statement is written to the binary log if it succeeds, but not if it fails; in that case, rollback occurs and no changes are made. 
• A new caching_sha2_passwordauthentication plugin is available. Like the sha256_password plugin, caching_sha2_password implements SHA-256 password hashing, but uses caching to address latency issues at connect time. It also supports more transport protocols and does not require linking against OpenSSL for RSA key pair-based password-exchange capabilities. 

The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is now the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password. 

 

• The MySQL Enterprise Edition SASL LDAP authentication plugin now supports GSSAPI/Kerberos as an authentication method for MySQL clients and servers on Linux. This is useful in Linux environments where applications access LDAP using Microsoft Active Directory, which has Kerberos enabled by default. 

• MySQL Enterprise Edition now supports an authentication method that enables users to authenticate to MySQL Server using Kerberos, provided that appropriate Kerberos tickets are available or can be obtained.  

• MySQL now supports roles, which are named collections of privileges. Roles can be created and dropped. Roles can have privileges granted to and revoked from them. Roles can be granted to and revoked from user accounts. The active applicable roles for an account can be selected from among those granted to the account, and can be changed during sessions for that account. 

• MySQL now incorporates the concept of user account categories, with system and regular users distinguished according to whether they have the SYSTEM_USER

• Previously, it was not possible to grant privileges that apply globally except for certain schemas. This is now possible if the partial_revokessystem variable is enabled.
• MySQL now sets the access control granted to clients on the named pipe to the minimum necessary for successful communication on Windows. Newer MySQL client software can open named pipe connections without any additional configuration. If older client software cannot be upgraded immediately, the new named_pipe_full_access_groupsystem variable can be used to give a Windows group the necessary permissions to open a named pipe connection. Membership in the full-access group should be restricted and temporary.

• Resource management. MySQL now supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group. Group attributes enable control over its resources, to enable or restrict resource consumption by threads in the group. DBAs can modify these attributes as appropriate for different workloads. Currently, CPU time is a manageable resource, represented by the concept of “virtual CPU” as a term that includes CPU cores, hyperthreads, hardware threads, and so forth. The server determines at startup how many virtual CPUs are available, and database administrators with appropriate privileges can associate these CPUs with resource groups and assign threads to groups.

Table encryption management.  Table encryption can now be managed globally by defining and enforcing encryption defaults. The default_table_encryption variable defines an encryption default for newly created schemas and general tablespace. The encryption default for a schema can also be defined using the DEFAULT ENCRYPTION clause when creating a schema. By default, a table inherits the encryption of the schema or general tablespace it is created in. Encryption defaults are enforced by enabling the table_encryption_privilege_check variable. The privilege check occurs when creating or altering a schema or general tablespace with an encryption setting that differs from the default_table_encryption setting, or when creating or altering a table with an encryption setting that differs from the default schema encryption. The TABLE_ENCRYPTION_ADMIN privilege permits overriding default encryption settings when table_encryption_privilege_check is enabled. 

InnoDB enhancements.  These InnoDB enhancements were added:

• The current maximum auto-increment counter value is written to the redo log each time the value changes, and saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts. Additionally:
  • A server restart no longer cancels the effect of the AUTO_INCREMENT = Ntable option. If you initialize the auto-increment counter to a specific value, or if you alter the auto-increment counter value to a larger value, the new value is persisted across server restarts.
  • A server restart immediately following a ROLLBACKoperation no longer results in the reuse of auto-increment values that were allocated to the rolled-back transaction.
  • If you modify an AUTO_INCREMENTcolumn value to a value larger than the current maximum auto-increment value (in an UPDATE operation, for example), the new value is persisted, and subsequent INSERT operations allocate auto-increment values starting from the new, larger value.

For more information, see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”, and InnoDB AUTO_INCREMENT Counter Initialization.

• When encountering index tree corruption, InnoDBwrites a corruption flag to the redo log, which makes the corruption flag crash safe. InnoDB also writes in-memory corruption flag data to an engine-private system table on each checkpoint. During recovery, InnoDB reads corruption flags from both locations and merges results before marking in-memory table and index objects as corrupt.
• The InnoDBmemcached plugin supports multiple get operations (fetching multiple key-value pairs in a single memcached query) and range queries. See Section 15.20.4, “InnoDB memcached Multiple get and Range Query Support”.
• A new dynamic variable, innodb_deadlock_detect, may be used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeoutsetting for transaction rollback when a deadlock occurs.
• The new INNODB_CACHED_INDEXEStable reports the number of index pages cached in the InnoDB buffer pool for each index.
• InnoDBtemporary tables are now created in the shared temporary tablespace, ibtmp1.
• The InnoDBtablespace encryption feature supports encryption of redo log and undo log data. See Redo Log Encryption, and Undo Log Encryption.
• InnoDBsupports NOWAIT and SKIP LOCKED options with SELECT … FOR SHARE and SELECT … FOR UPDATE locking read statements. NOWAIT causes the statement to return immediately if a requested row is locked by another transaction. SKIP LOCKED removes locked rows from the result set. See Locking Read Concurrency with NOWAIT and SKIP LOCKED.

SELECT … FOR SHARE replaces SELECT … LOCK IN SHARE MODE, but LOCK IN SHARE MODE remains available for backward compatibility. The statements are equivalent. However, FOR UPDATE and FOR SHARE support NOWAIT, SKIP LOCKED, and OF tbl_name options. See Section 13.2.10, “SELECT Statement”.

OF tbl_name applies locking queries to named tables.

• ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REORGANIZE PARTITION, and REBUILD PARTITIONALTER TABLE options are supported by native partitioning in-place APIs and may be used with ALGORITHM={COPY|INPLACE} and LOCK

DROP PARTITION with ALGORITHM=INPLACE deletes data stored in the partition and drops the partition. However, DROP PARTITION with ALGORITHM=COPY or old_alter_table=ON rebuilds the partitioned table and attempts to move data from the dropped partition to another partition with a compatible PARTITION … VALUES definition. Data that cannot be moved to another partition is deleted.

• The InnoDBstorage engine now uses the MySQL data dictionary rather than its own storage engine-specific data dictionary. For information about the data dictionary, see Chapter 14, MySQL Data Dictionary.
• mysqlsystem tables and data dictionary tables are now created in a single InnoDB tablespace file named ibd in the MySQL data directory. Previously, these tables were created in individual InnoDB tablespace files in the mysql database directory.
• The following undo tablespace changes are introduced in MySQL 8.0:
  • By default, undo logs now reside in two undo tablespaces that are created when the MySQL instance is initialized. Undo logs are no longer created in the system tablespace.
  • As of MySQL 8.0.14, additional undo tablespaces can be created in a chosen location at runtime using CREATE UNDO TABLESPACE

CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE ‘file_name.ibu’;

Undo tablespaces created using CREATE UNDO TABLESPACE syntax can be dropped at runtime using DROP UNDO TABLESPACE syntax.

DROP UNDO TABLESPACE tablespace_name;

ALTER UNDO TABLESPACE syntax can be used to mark an undo tablespace as active or inactive.

ALTER UNDO TABLESPACE tablespace_name SET {ACTIVE|INACTIVE};

A STATE column that shows the state of a tablespace was added to the INFORMATION_SCHEMA.INNODB_TABLESPACES table. An undo tablespace must be in an empty state before it can be dropped.

• The innodb_undo_log_truncatevariable is enabled by default.
• The innodb_rollback_segmentsvariable defines the number of rollback segments per undo tablespace. Previously, innodb_rollback_segments specified the total number of rollback segments for the MySQL instance. This change increases the number of rollback segments available for concurrent transactions. More rollback segments increases the likelihood that concurrent transactions use separate rollback segments for undo logs, resulting in less resource contention.

• Default values for variables that affect buffer pool preflushing and flushing behavior were modified:
  • The innodb_max_dirty_pages_pct_lwmdefault value is now 10. The previous default value of 0 disables buffer pool preflushing. A value of 10 enables preflushing when the percentage of dirty pages in the buffer pool exceeds 10%. Enabling preflushing improves performance consistency.
  • The innodb_max_dirty_pages_pctdefault value was increased from 75 to 90. InnoDB attempts to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value. The increased default value permits a greater percentage of dirty pages in the buffer pool.
• The default innodb_autoinc_lock_modesetting is now 2 (interleaved). Interleaved lock mode permits the execution of multi-row inserts in parallel, which improves concurrency and scalability. The new innodb_autoinc_lock_mode default setting reflects the change from statement-based replication to row based replication as the default replication type in MySQL 5.7. Statement-based replication requires the consecutive auto-increment lock mode (the previous default) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of SQL statements, whereas row-based replication is not sensitive to the execution order of SQL statements. For more information, see InnoDB AUTO_INCREMENT Lock Modes.

For systems that use statement-based replication, the new innodb_autoinc_lock_mode default setting may break applications that depend on sequential auto-increment values. To restore the previous default, set innodb_autoinc_lock_mode to 1.

• Renaming a general tablespace is supported by ALTER TABLESPACE … RENAME TO
• The new innodb_dedicated_servervariable, which is disabled by default, can be used to have InnoDB automatically configure the following options according to the amount of memory detected on the server:
  • innodb_buffer_pool_size
  • innodb_log_file_size
  • innodb_flush_method

This option is intended for MySQL server instances that run on a dedicated server. For more information, see Section 15.8.12, “Enabling Automatic Configuration for a Dedicated MySQL Server”.

• The new INNODB_TABLESPACES_BRIEFview provides space, name, path, flag, and space type data for InnoDB tablespaces.
• The zlib libraryversion bundled with MySQL was raised from version 1.2.3 to version 1.2.11. MySQL implements compression with the help of the zlib library.

If you use InnoDB compressed tables, see Section 2.11.4, “Changes in MySQL 8.0” for related upgrade implications.

• Serialized dictionary information (SDI) is present in all InnoDBtablespace files except for global temporary tablespace and undo tablespace files. SDI is serialized metadata for table and tablespace objects. The presence of SDI data provides metadata redundancy. For example, dictionary object metadata may be extracted from tablespace files if the data dictionary becomes unavailable. SDI extraction is performed using the ibd2sdi SDI data is stored in JSON format.

The inclusion of SDI data in tablespace files increases tablespace file size. An SDI record requires a single index page, which is 16KB in size by default. However, SDI data is compressed when it is stored to reduce the storage footprint.

• The InnoDBstorage engine now supports atomic DDL, which ensures that DDL operations are either fully committed or rolled back, even if the server halts during the operation. For more information, see Section 13.1.1, “Atomic Data Definition Statement Support”.
• Tablespace files can be moved or restored to a new location while the server is offline using the innodb_directories For more information, see Section 15.6.3.6, “Moving Tablespace Files While the Server is Offline”.
• The following redo logging optimizations were implemented:
  • User threads can now write concurrently to the log buffer without synchronizing writes.
  • User threads can now add dirty pages to the flush list in a relaxed order.
  • A dedicated log thread is now responsible for writing the log buffer to the system buffers, flushing system buffers to disk, notifying user threads about written and flushed redo, maintaining the lag required for the relaxed flush list order, and write checkpoints.
  • System variables were added for configuring the use of spin delay by user threads waiting for flushed redo:
    • innodb_log_wait_for_flush_spin_hwm: Defines the maximum average log flush time beyond which user threads no longer spin while waiting for flushed redo.
    • innodb_log_spin_cpu_abs_lwm: Defines the minimum amount of CPU usage below which user threads no longer spin while waiting for flushed redo.
    • innodb_log_spin_cpu_pct_hwm: Defines the maximum amount of CPU usage above which user threads no longer spin while waiting for flushed redo.
  • The innodb_log_buffer_sizevariable is now dynamic, which permits resizing of the log buffer while the server is running.