MySQL is a well-known open source structured database because of its performance, easiness to use, and reliability. This is the most common choice of web applications for a relational database. In the current market, thousands of web-based applications rely on MySQL including giant industries such as Facebook, Twitter, and Wikipedia. It has also proven to be the database choice for Software as a Service (SaaS) based applications such as Twitter, YouTube, SugarCRM, Supply Dynamics, Workday, RightNow, Omniture, Zimbra, and many more. We will discuss this in detail in the use cases of MySQL section later in the chapter. MySQL was developed by MySQL AB, a Swedish company, and now it is distributed and supported by Oracle Corporation. MySQL carries a valuable history with it.

MySQL has continued to improve in order to become an enterprise-level database management system. MySQL 8 is expected to be a game-changer as today we are in the age of digitization. MySQL 8 is all tuned to serve many new use cases that in prior versions were difficult to achieve. Some of the use cases an enormous amount of data is produced are social networking, e-commerce, bank/credit card transactions, emails, data stored on the cloud, and so on. Analysis of all such structured, unstructured, or semi-structured ubiquitous data helps to discover hidden patterns, market trends, correlations, personal preferences.

Let's take an in-depth look at MySQL 8 new features, benefits, use cases along with a few limitations of MySQL 8 after we have an overview of MySQL. This is going to be exciting, let's get prepared.

Structured Query Language (SQL) is used to manipulate, retrieve, insert, update, and delete data in relational database management system (RDBMS). To make it simpler, SQL tells the database what to do and exactly what it needs. SQL is a standard language that all RDBMS systems such as MySQL, MS Access, MS SQL, Oracle, Postgres, and others use.

SQL allows users to access data from MySQL and define and manipulate the data. To embed within other languages, you can leverage SQL modules, libraries, and precompilers, which can help you create/drop databases and tables, allow users to create the view, and stored procedures, functions, and so on, in a database. It can do various other operations such as allowing users to set permissions on tables, procedures, and views.

Data in a relational database is stored in an organized format so that information can be retrieved easily. Data will be stored in different tables made up of rows and columns. However, the relationship can also be built between different tables that efficiently store huge data and effectively retrieve the selected data. This provides database operations with tremendous speed and flexibility.

As a relational database, MySQL has capabilities to establish relationships with different tables such as one to many, many to one, and one to one by providing primary keys, foreign keys, and indexes. It can also perform joins between tables to retrieve exact information such as inner joins and outer joins.

SQL is used as an interface to interact with the relational data in MySQL. SQL is an American National Standard Institute (ANSI) standard language which we can operate with data such as creation, deletion, updating, and retrieval.

Many industries prefer open source technology because of the technology's flexibility and cost-saving features, while MySQL has put its footprint in the market by becoming the most popular relational database for web applications. Open source means that you can view the source of MySQL and customize it based on your needs without any cost. You can download the source or binary files from its site and use them accordingly.

The MySQL server is covered under the General Public License (GNU), which means that we can freely use it for web applications, study its source code, and modify it to suit our needs. It also has the Enterprise Edition as well with advanced features included. Many enterprises still purchase the support contract from MySQL to get assistance on various issues.

MySQL has great reliability to perform well without requiring extensive troubleshooting due to bottlenecks or other slowdowns. It also incorporates a number of performance enhanced mechanisms such as index support, load utilities, and memory caches. MySQL uses InnoDB as a storage engine, which provides highly efficient ACID compliant transactional capabilities that assure high performance and scalability. To handle the rapidly growing database, MySQL Replication and cluster help scale out the database.

MySQL has great cross-platform availability that makes it more popular. It is flexible to run on major platforms such as RedHat, Fedora, Ubuntu, Debian, Solaris, Microsoft Windows, and Apple macOS. It also provides Application Programming Interface (APIs) to interconnect with various programming languages such as C, C++, C#, PHP, Java, Ruby, Python, and Perl.

Here is a list of major releases of MySQL so far:

• Version 5.0 GA was released on 19th October, 2005
• Version 5.1 GA was released on 14th November, 2008
• Version 5.5 GA was released on 3rd December, 2010
• Version 5.6 GA was released on 5th February, 2013
• Version 5.7 GA was released on 21st October, 2015

Now it's time for the major version release--MySQL 8--which was announced on 12th September, 2016 and is still in the development milestone mode.

Let's look back and quickly glance through some of the core features in MySQL. We will be discussing various features throughout the book in detail as we progress.

Structured databases are traditional databases that have been used by many enterprises for more than 40 years. However, in the modern world, data volume is becoming bigger and bigger and a common need has taken its place--data analytics. Analytics is becoming difficult with structured databases as the volume and velocity of digital data grow faster by the day; we need to find a way to achieve such needs in an effective and efficient way. The most common database that is used as a structured database in the open source world is MySQL.

Many organizations use a structured database to store their data in an organized way with the formatted repository. Basically, data in a structured database has a fixed field, a predefined data length, and defines what kind of data is to be stored such as numbers, dates, time, addresses, currencies, and so on. In short, the structure is already defined before data gets inserted, which gives a clearer idea of what data can reside there. The key advantage of using a structured database is that data being easily stored, queried, and analyzed.

An unstructured database is the opposite of this; it has no identifiable internal structure. It can have a massive unorganized agglomerate or various objects. Mainly, the source of structured data is machine-generated, which means information is generated from the machine and without human intervention, whereas unstructured data is human-generated data. Organizations use structured databases for data such as ATM transactions, airline reservations, inventory systems, and so on. In the same way, some organizations use unstructured data such as emails, multimedia content, word processing documents, web pages, business documents, and so on.

Let's now look at an overview of different MySQL storage engines. This is an important section that gives a brief of different database storage engines; we will be discussing this in detail in Chapter 6, MySQL 8 Storage Engines. MySQL stores data in the database as a subdirectory. In each database, data is stored as tables. When you create a table, MySQL stores the table definition in .frm with the same name as the table name. You can use the SHOW TABLE STATUS command to show information about your table:

mysql> SHOW TABLE STATUS LIKE 'admin_user' \G;
*************************** 1. row ***************************
 Name: admin_user
 Engine: InnoDB
 Version: 10
 Row_format: Dynamic
 Rows: 2
 Avg_row_length: 8192
 Data_length: 16384
 Max_data_length: 0
 Index_length: 16384
 Data_free: 0
 Auto_increment: 3
 Create_time: 2017-06-19 14:46:49
 Update_time: 2017-06-19 15:15:08
 Check_time: NULL
 Collation: utf8_general_ci
 Checksum: NULL
 Create_options:
 Comment: Admin User Table
1 row in set (0.00 sec)

This command shows that this is an InnoDB table with the column name Engine. There is additional information that you can refer to for other purposes such as the number of rows, index length, and so on.

The storage engine is the way to handle SQL operations for different table types. Each storage engine has its own advantages and disadvantages. It is important to understand each storage engine's features and choose the most appropriate one for your tables to maximize the performance of the database. InnoDB is the default storage engine when we create a new table in MySQL 8.

The MySQL server uses a plug-and-play storage engine architecture. You can load the required storage engine and unload unnecessary storage engines from the MySQL server with the help of the SHOW ENGINES command as follows:

mysql> SHOW ENGINES \G;
*************************** 1. row ***************************
 Engine: InnoDB
 Support: YES
 Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
 XA: YES
 Savepoints: YES
*************************** 2. row ***************************
 Engine: MRG_MYISAM
 Support: YES
 Comment: Collection of identical MyISAM tables
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 3. row ***************************
 Engine: MEMORY
 Support: YES
 Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 4. row ***************************
 Engine: BLACKHOLE
 Support: YES
 Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 5. row ***************************
 Engine: MyISAM
 Support: DEFAULT
 Comment: MyISAM storage engine
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 6. row ***************************
 Engine: CSV
 Support: YES
 Comment: CSV storage engine
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 7. row ***************************
 Engine: ARCHIVE
 Support: YES
 Comment: Archive storage engine
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 8. row ***************************
 Engine: PERFORMANCE_SCHEMA
 Support: YES
 Comment: Performance Schema
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 9. row ***************************
 Engine: FEDERATED
 Support: NO
 Comment: Federated MySQL storage engine
Transactions: NULL
 XA: NULL
 Savepoints: NULL
9 rows in set (0.00 sec)

InnoDB is the default storage engine broadly used out of all other available storage engines. It was released with MySQL 5.1 as a plugin in 2008. MySQL 5.5 and later has InnoDB as a default storage engine. It has been taken over by Oracle Corporation in October 2005, from the Innobase Oy, which is a Finland-based company.

InnoDB tables support ACID-compliant commits, rollback, and crash recovery capabilities to protect user data. It also supports row-level locking, which helps with better concurrency and performance. It stores data in clustered indexes to reduce I/O operations for all SQL select queries based on the primary key. It also supports FOREIGN KEY constraints that allow better data integrity for the database. The maximum size of an InnoDB table can scale up to 64 TB, which should be good enough to serve many real-world use cases.

MyISAM was the default storage engine for MySQL prior to 5.5 1. MyISAM storage engine tables do not support ACID-compliant as opposed to InnoDB. MyISAM tables support table-level locking only, so MyISAM tables are not transaction-safe; however, they are optimized for compression and speed. It is generally used when you need to have primarily read operations with minimal transaction data. The maximum size of a MyISAM table can grow up to 256 TB, which helps in use cases such as data analytics. MyISAM supports full-text indexing, which can help in complex search operations. Using full-text indexes, we can index data stored in BLOB and TEXT data types.

A memory storage engine is generally known as a heap storage engine. It is used to access data extremely quickly. This storage engine stores data in the RAM so it wouldn't need I/O operation. As it stores data in the RAM, all data is lost upon server restart. This table is basically used for temporary tables or the lookup table. This engine supports table-level locking, which limits high write concurrency.

Important notes about memory tables are as follows:

• Because memory table stores data in the RAM, which has a very limited storage capacity; if you try to write too much data into the memory table, it will start swapping data into the disk and then you lose the benefits of the memory storage engine
• These tables don't support TEXT and BLOB data types, and it is not even required as it has limited storage capacity
• This storage engine can be used to cache the results; lookup tables, for example, or postal codes and the names of states
• Memory tables support B-tree indexes and Hash indexes

This storage engine is used to store large amounts of historical data without any indexes. Archive tables do not have any storage limitations. The archive storage engine is optimized for high insert operations and also supports row-level locking. These tables store data in a compressed and small format. The archive engine does not support DELETE or UPDATE operations; it only allows INSERT, REPLACE, and SELECT operations.

This storage engine accepts data but does not store it. It discards data after every INSERT instead of storing it.

Now, what is the use of this storage engine; why would anybody use it? Why would we run an INSERT query that doesn't insert anything into the table?

This engine is useful for replication with large number of servers. A BLACKHOLE storage engine acts as a filter server between the master and slave server, which do not store any data, but only apply replicate-do-* and replicate-ignore-* rules and write a binlogs. These binlogs are used to perform replication in slave servers. We will discuss this in detail in Chapter 8, Replication in MySQL 8.

The comma separated values (CSV) engine stores data in the .csv file type using the comma-separated values format. This engine extracts data from the database and copies it to .csv out of the database. If you create a CSV file from the spreadsheet and copy it into the MYSQL data folder server, it can read the data using the select query. Similarly, if you write data in the table, an external program can read it from the CSV file. This storage engine is used for the exchange of data between software or applications. A CSV table does not support indexing and partitioning. All columns in the CSV storage engine need to be defined with the NOT NULL attribute to avoid errors during table creation.

This storage engine is also known as an MRG_MyISAM storage engine. This storage engine merges a MyISAM table and creates it to be referred to a single view. For a merge table, all columns are listed in the same order. These tables are good for data warehousing environments.

The table is used to manage log-related tables, generally. You can create different months of logs in separate MyISAM tables and merge these tables using the merge storage engine.

MyISAM tables have storage limit for the operating system, but a collection of MyISAM (merge) tables do not have storage limits. So using a merge table would allow you to split data into multiple MyISAM tables, which can help in overcoming storage limits.

Merge tables do not support partitioning. Also, you cannot partition a merge table or any of a merge table's underlying MyISAM tables in a different partition.

This storage engine allows you to create a single database on a multiple physical server. It opens a client connection to another server and executes queries against a table there, retrieving and sending rows as needed. It was originally marketed as a competitive feature that supported many enterprise-grade proprietary database servers, such as Microsoft SQL Server and Oracle, but that was always a stretch, to say the least. Although it seemed to enable a lot of flexibility and neat tricks, it has proven to be a source of many problems and is disabled by default. This storage engine is disabled by default in MySQL; to enable it, you need to start the MySQL server binary using the federated option.

NDB cluster (also known as NDB) is an in-memory storage engine offering high availability and data persistence features.

The NDB cluster storage engine can be configured with a range of failover and load balancing options, but it is easiest to start with the storage engine at the cluster level. NDB cluster uses the NDB storage engine that contains a complete set of data, which is dependent only on other datasets available within the cluster.

The cluster portion of the NDB cluster is configured independently of the MySQL servers. In an NDB cluster, each part of the cluster is considered to be a node.

Each storage engine has its own advantage and usability, as follows:

• Search Engine: NDBCluster
• Transactions data: InnoDB
• Session data: MyISAM or NDBCluster
• Localized calculations: Memory
• Dictionary: MyISAM

The following diagram will help you understand which store engine you need to use for your requirement:

Now you have a better idea about various storage engines along with different use cases, which will help you to make a decision based on your needs.

It's time to move on to our next topic where we will look at delightful new features available in MySQL 8.

The MySQL database development team has recently announced its major release as MySQL 8 Development Milestone Release (DMR). It contains significant updates and fixes for problems that were much needed.

You might be wondering why it's 8 after 5.7! Were the intermediate versions, that is, 6 and 7, miss out? Of course not! Actually, 6.0 was preserved as part of the changeover to a more frequent and timely release, while 7.0 for the clustering version of MySQL.

Let's see some exciting features that have been introduced in this latest version, as depicted in the following diagram:

It's time to look at MySQL 8 features in detail, which makes us excited and convinced about the reasons for a major version upgrade of MySQL.

Up until the previous version, the MySQL data dictionary was stored in different metadata files and non-transactional tables, but from this version onwards, it will have a transactional data dictionary to store the information about the database. No more .frm, .trg, or .par files. All information will be stored in the database, which removes the cost of performing heavy file operations. There were numerous issues with filesystem metadata storage such as the vulnerability of the filesystem, exorbitant file operations, difficult to handle crash recovery failures, or replication; it was also difficult to add new feature-related metadata. Now this upgrade has made it simple by storing information in a centralized manner, and will have improved performance as this data dictionary object can be cached in memory, similar to other database objects.

This data dictionary will have data that is needed for SQL query execution such as catalog information, character sets, collations, column types, indexes, database information, tables, stored procedures, functions and triggers, and so on.

In MySQL 8, the privileges module has been improved by introducing roles, which means a collection of permissions. Now we can create roles with a number of privileges and assign them to multiple users.

The problem with the previous version was that we were not able to define generic permissions for a group of users and each user has individual privileges. Suppose if there are 1,000 users already existing that have common privileges, and you want to remove the write permissions for these 1,000 users, what would you have done in the previous version? You would have had to take the time-consuming approach of updating each user, right? Arrgh! That's a long, long task.

Now with MySQL 8, it is easy to update any change in privileges. Roles will define all the required privileges and this role will be assigned to those 1,000 users. We just need to make any privilege changes in the role and all users will automatically inherit the respective privileges.

Roles can be created, deleted, grant or revoke permission, grant or revoke from the user account, and can specify the default role within the current session.

MySQL 8 has changed the auto-increment counter value store mechanism. Previously, it was stored in the memory, which was quite difficult to manage during server restarts or server crashes. However, now the auto-increment counter value is written into the redo log whenever the value gets changed and, on each checkpoint, it will be saved in the system table, which makes it persistent across the server restart.

With the previous version, update of the auto-increment value may have caused duplicate entry errors. Suppose if you updated the value of auto-increment in the middle of the sequence with a larger than the current maximum value, but then subsequent insert operations could not identify the unused values, which could cause a duplicate entry issue. This has been prevented by persisting the auto-increment value, hence subsequent insert operations can get the new value and allocate it properly.

If server restart happened, the auto-increment value was lost with the previous version as it was stored in memory and InnoDB needed to execute a query to find out the maximum used value. This has been changed, as the newer version has the capability to persist its value across the server restart. During the server restart, InnoDB initializes the counter value in memory using the maximum value stored in the data dictionary table. In case of server crashes, InnoDB initializes the auto-increment counter value that is bigger than the data dictionary table and the redo log.

MySQL 8 provides you with a feature to make indexes invisible. These kinds of indexes cannot be used by the optimizer. In case you want to test the query performance without indexes, using this feature you can do so by making them invisible rather than dropping and re-adding an index. This is a handy feature when indexing is supposed to be dropped and recreated on huge datasets.

All indexes are visible by default. To make them invisible or visible, INVISIBLE and VISIBLE keywords are used respectively, as described in the following code snippet:

ALTER TABLE table1 ALTER INDEX ix_table1_col1 INVISIBLE;
ALTER TABLE table1 ALTER INDEX ix_table1_col1 VISIBLE;

Descending indexes existed in version 5.7 too, but they were scanned in reverse order, which caused performance barriers. To improve performance, MySQL 8 has optimized this and scanned descending indexes in forward order, which has drastically improved performance. It also brings multiple column indexes for the optimizer when the most efficient scan order has ascending order for some columns, and descending order for other columns.

Server variables can be configured globally and dynamically while the server is running. There are numerous system variables that we can set using SET GLOBAL:

SET GLOBAL max_connections = 1000;

However, such settings will be lost after server restart. To avoid this, MySQL 8 has introduced the SET PERSIST variant, which preserves variables across a server restart:

SET PERSIST max_connections = 1000; 

Until the previous version, it supported only one coordinate system, a unitless 2D place that was not referenced to a position on earth. Now MySQL 8 has added support for a Spatial Reference System (SRS) with geo-referenced ellipsoids and 2D projections. SRS helps assign coordinates to a location and establishes relationships between sets of such coordinates. This spatial data can be managed in data dictionary storage as the ST_SPATIAL_REFERENCE_SYSTEMS table.

The default character set has been changed from latin1 to UTF8. UTF8 is the dominating character set, though it hadn't been a default one in previous versions of MySQL. Along with the character set default, collation has been changed from latin1_swedish_ci to utf8mb4_800_ci_ai. With these changes globally accepted, character sets and collations are now based on UTF8; one of the common reasons is because there are around 21 different languages supported by UTF8, which makes systems provide multilingual support.

In MySQL 5.7, bit-wise operations and functions were working for BIGINT (64-bit integer) data types only. We needed to pass BIGINT as an argument and it would return the result as BIGINT. In short, it had maximum range up to 64 bits to perform operations. A user needs to do conversion to the BIGINT data type in case they want to perform it on other data types. This typecasting was not feasible for data types larger than 64 bits as it would truncate the actual value, which resulted in inaccuracy.

MySQL 8 has improved bit-wise operations by enabling support for other binary data types such as Binary, VarBinary, and BLOB. This makes it possible to perform bit-wise operations on larger than 64-bit data. No more typecasting needed! This allows the taking of arguments and returning results larger than 64 bits.

Multiple get operations are now possible with the InnoDB memcached plugin, which will really help in improving the read performance. Now, multiple key value pairs can be fetched in a single memcached query. Frequent communication traffic has also been minimized as we can get multiple data in a single shot.

Range queries are also supported by the InnoDB Memcached plugin. It simplifies range searches by specifying a particular range and retrieves values within this range.

When rows are locked by other transactions that you are trying to access, then you need to wait for that transaction to release the lock on the same row so that you can access it accordingly. To avoid waiting for the other transaction, InnoDB has added support of the NOWAIT and SKIP LOCKED options. NOWAIT will return immediately with an error in case the requested row is locked rather than going into the waiting mode, and SKIP LOCKED will skip the locked row and never wait to acquire the row lock. Hence, SKIP LOCKED will not consider the locked row in the resulting set:

SELECT * FROM table1 WHERE id = 5 FOR UPDATE NOWAIT;
SELECT * FROM table1 FOR UPDATE SKIP LOCKED;

JSON support had been implemented in MySQL 5.7; it was well-acknowledged feature. In MySQL 8 it has added various functions that would allow us to get dataset results in JSON data format, virtual columns, and tentatively 15 SQL functions that allow you to search and use JSON data on server side. In MySQL8 there are additional aggregation functions added that can be used in JSON objects/arrays to represent loaded data in a further optimized way. The following are the two JSON aggregation functions that were introduced in MySQL8:

• JSON_OBJECTAGG()
• JSON_ARRAYAGG()

In MySQL 8 a new option is introduced innodb_dedicated_server, which would be helpful for vertical scaling of the servers. It actually automatically detects the memory allocated to the virtual server and appropriately set MySQL 8 without any need to change configuration files. These would be very handy features considering the adoption of virtualization and cloud is there. In fact with this configuration, you might not even need to get shell access of server to edit the configuration files. You can do this with the new SET PERSIST feature that can set relevant configuration from the MySQL command line itself, which can enhance security further as you almost wouldn't need shell access of the server.

MySQL 8 has come up with a wonderful resource management feature that will allow you to allocate resource to threads running on a server, which would be executed based on the resources configured for the group. Currently, CPU time is a resource that can be configured for a group. With this, you can tweak your workloads with virtual resource management within MySQL itself. MySQL will identify on startup numbers of virtual CPUs available and after that users with appropriate privileges can map the virtual CPUs with resource group and align thread management to these groups.

We expect to see more features by the time MySQL 8 is available for general use. Let us now look at benefits of using MySQL 8.

Whether you are a developer or an enterprise, you would obviously choose one that provides good benefits and results when compared to other related products. MySQL provides numerous advantages as the first choice in this competitive market. It has various powerful features available that make it a more comprehensive database. Let's now go through some benefits of using MySQL.

The first thing that comes to mind is securing data because nowadays data has become precious and can impact business continuity if legal obligations are not met; in fact, it can be so bad that it can close down your business in no time. MySQL is the most secure and reliable database management system used by many well-known enterprises such as Facebook, Twitter, and Wikipedia. It really provides a good security layer that protects sensitive information from intruders. MySQL gives access control management so that granting and revoking required access from the user is easy. Roles can also be defined with a list of permissions that can be granted or revoked for the user. All user passwords are stored in an encrypted format using plugin-specific algorithms.

Day by day, the mountain of data is growing because of extensive use of technology in numerous ways. Because of this, load average is going through the roof. In some cases, it is unpredictable that data cannot exceed up to some limit or number of users will not go out of bounds. Scalable databases would be a preferable solution so that, at any point, we can meet unexpected demands to scale. MySQL is a rewarding database system for its scalability, which can scale horizontally and vertically; in terms of data, spreading database and load of application queries across multiple MySQL servers is quite feasible. It is pretty easy to add horsepower to the MySQL cluster to handle the load.

MySQL is an open source database management system that makes debugging, upgrading, and enhancing the functionality fast and easy. You can view the source and make the changes accordingly and use it in your own way. You can also distribute an extended version of MySQL, but you will need to have a license for this.

MySQL gives high-speed transaction processing with optimal speed. It can cache the results, which boosts read performance. Replication and clustering make the system scalable for more concurrency and manages the heavy workload. Database indexes also accelerate the performance of SELECT query statements for substantial amount of data. To enhance performance, MySQL 8 has included indexes in performance schema to speed up data retrieval.

Today, in the world of competitive marketing, an organization's key point is to have their system up and running. Any failure or downtime directly impacts business and revenue; hence, high availability is a factor that cannot be overlooked. MySQL is quite reliable and has constant availability using cluster and replication configurations. Cluster servers instantly handle failures and manage the failover part to keep your system available almost all the time. If one server gets down, it will redirect the user's request to another node and perform the requested operation.

MySQL provides cross-platform flexibility that can run on various platforms such as Windows, Linux, Solaris, OS2, and so on. It has great API support for the all major languages, which makes it very easy to integrate with languages such as PHP, C++, Perl, Python, Java, and so on. It is also part of the Linux Apache MySQL PHP (LAMP) server that is used worldwide for web applications.

It's now time to get our hands dirty and look at MySQL 8; let's start with the installation of MySQL 8 on a Linux platform in our case. We prefer MySQL 8 on a Linux operating system as that has been a common use case across many organizations. We will be discussing more installation in Chapter 2, Installing and Upgrading MySQL 8. You can use it on other platforms that MySQL supports, such as Windows, Solaris, HP-UNIX, and so on. Linux provides various ways to install the MySQL server, as follows:

• RPM package
• YUM repository
• APT repository
• SLES repository
• Debian package
• TAR package
• Compiling and installing from the source code

A coin has two sides; similarly, benefits of also using MySQL 8 would come along with a few limitations. Let us walk through a few areas of MySQL 8 now.

The number of databases or tables are not a limitation for MySQL 8; however, the operating system file limit can be a limitation for MySQL 8. Storage Engine InnoDB is allowed to scale up to four billion tables as its peak number.

You may hit maximum table size limit, which is not restricted from MySQL 8; however, it may be because of operating system filesystem limits.

In a single join, one can use 61 tables, which can be referred. It is also applicable to the tables that are referenced in view definition. Joins that are part of subqueries and views are also considered to be part of the limitation.

There are few limitations when you have MySQL 8 used on the Windows platform:

• Memory: 32-bit architecture has limitation to use only 2 GB of RAM for a process.
• Ports: In case you have a high number of concurrency you might come across Windows platform limitation of having 4000 ports available for client connections in total.
• Case-insensitivity: The Windows platform doesn't have case sensitivity, which is why tables and databases need to be deliberately managed for case-insensitivity.
• Pipes: |, generally referred as pipe signs, they are not fully supported in Windows. You might come across them in a few scenarios while doing database administration activities.
• Pathname separator: MySQL 8 escape character is \, which is the pathname separator for Windows. Hence while using path separator you can double slash as "\\" as an alternative for a pathname separator.

The table column for each table in MySQL 8 has a limit of 4096 columns. It might vary based on a few other factors for columns count limit, as stated in the following section.

MySQL tables have a limit of 65,535 bytes for a row, although storage engines such as InnoDB are capable of supporting larger chunks.

Limitations on InnoDB storage engine are what we will talk about a bit more specifically as InnoDB now with MySQL 8 will play a prominent role.

We will have a quick glance at a few of the limitations of InnoDB storage engine:

• The number of indexes supported can be maximum 64 for a table
• For tables that use compressed or dynamic row format; 3072 is the index key prefix length limit
• For tables that use compact or redundant row format; 767 is the index key prefix length limit
• Total columns in a table, which includes virtual generated columns, are limited to a maximum of 1,017
• 16 columns is the maximum permitted for multi-column indexes
• The combined InnoDB log file size cannot exceed 512 GB
• Maximum table size supported by InnoDB is 256 TB
• AdminAPI is not supported while using unix socket connections
• Multi-byte characters might give you unreliable aligned columns while formatting of results in InnoDB clusters

We will now have a quick glance at a few of the restrictions of the InnoDB storage engine:

• Delete from tablename: It doesn't actually delete the complete table, instead it deletes each row of the table one after another.
• Show table status: It wouldn't provide you accurate data all the time; it provides estimates.
• When counting rows, the number of rows provided by count(*) is not accurate because of concurrency; it would count only those counts visible to transactions currently available.
• If there is multiple analyze table queries executed, later one will be blocked until the first one gets completed.
• InnoDB keeps an exclusive lock on the index at the end associated with the auto_increment column.
• In a case the auto_increment integer runs out of the value; the following insert operations would show us duplicate-key errors.
• Foreign keys that are cascaded cannot activate triggers.
• There are a few column names reserved by MySQL that InnoDB uses for internal purposes. The following are a few such column names:
  • DB_ROW_ID
  • DB_TRX_ID
  • DB_ROLL_PTR
  • DB_MIX_ID

We might come across output shown in the following example in case of such reserved column names used:

        mysql> CREATE TABLE chintan (c1 INT, db_row_id INT) 
         ENGINE=INNODB;
        ERROR 1166 (42000): Incorrect column name 'db_row_id'

• InnoDB locks are released immediately after the transaction is aborted or committed, which is held by a transaction.
• The addition of table locks are not supported, as locks are implicit to commit and unlock tables

Let us have a look at a few known limitations of data dictionary:

• Individual MyISAM tables for backup and restore are not supported by merely copying the files.
• Manually created directories for databases are not supported by MySQL 8. For instance, using mkdir would have no impact on MySQL server data dictionary.
• DDL operations would take more time than expected because such operations are written to storage, undo logs and redo instead of .frm files as what we would have seen in prior versions of MySQL.

It's now time to discuss a few limitations of group replication in MySQL 8:

• Large transactions: Transactions that result to GTID contents cannot be replicated between the rest of the members of the group if they're too large. It is suggested to use smaller chunks of data that cannot be replicated in around five seconds to group members to avoid failures.
• Cluster from a group: If you try to create clusters from an existing group replication setup it will result in an error as the instance would already be part of a replication group. This is noticed currently only in MySQL's wizard mode only; an alternative solution for the issue is to disable wizard mode.
• Serializable isolation level: Serializable isolation level is not supported when multi-primary groups are used, which is the default configuration.
• DDL and DML operations: If there is concurrent DDL and DML operations executed against the same data object but on different servers is not supported when multi-primary group mode is used.
• Replication checksum: Currently MySQL design limitations create restrictions of having replication event checksums.

We will be discussing limitations of partitioning in this section.

The following are the constructs that are not allowed in expressions of partitions:

• Declared variables
• User variables
• Stored procedures
• Stored functions
• UDFs
• Plugins

There are a few operators that are not permitted in partition expressions such as << , >> , | , & , ~ and ^ . Results for arithmetic operators such as +, -, and * must have an integer value or NULL.

The following are a few specific areas that show us limitations of partitioning on tables:

• The maximum number of partitions supported by MySQL 8 for a table is 8192. This limit also considers sub-partitions.
• Fulltext index and search is not supported on partitioned tables.
• Tables that are temporary cannot be partitioned.
• Log tables can't be partitioned.
• Foreign keys are not supported on partitioned InnoDB storage engine.
• The data type of partition keys should be an integer column or can be an expression to an integer. Expression or column values may be NULL; however, expressions that include ENUM are not supported.
• Upgrading partitioned tables that have been partitioned by KEY would have to be reloaded, which stands true other than the InnoDB storage engine.

We have so far discussed overview, features, benefits, and a few limitations of MySQL. Let us now walk through the wonderful use cases of MySQL.

MySQL has many advantages because it has its foot in many industries and various use cases across the globe. The importance of MySQL doesn't depend only on how much data you have, it's rather what you are going to do with the data. Data can be sourced and analyzed from unpredictable sources and can be used to address many things.

Let's now look at use cases with real-life importance made on renowned scenarios with the help of MySQL:

The preceding figure helps us understand where MySQL is serving various industries. Though it's not an extensive list of industries where MySQL has been playing a prominent role in business decisions, let's now discuss a few of the industries.

Social media content is information, and so are engagements such as views, likes, demographics, shares, follows, unique visitors, comments, and downloads. At the end of the day, what matters is how your social media-related efforts contribute to the business.

One notable example is Facebook, where MySQL had been used extensively. On top of MySQL where petabytes of data was used to serve likes, shares, and comments. Facebook has developed the RocksDB storage engine on top of the MySQL InnoDB storage engine, which leverages many advantages of InnoDB storage engine as Facebook wanted to primarily focus on storage optimization. Though currently MySQL is still used largely for other common applications.

The era of MySQL has been playing a significant role in government too; government bodies have been using MySQL extensively because of splendid return on investments and promoting open source. In fact, the government sector is carrying out a huge number of implementations of MySQL worldwide.

This may come as a surprise to you; US Navy uses MySQL for its critical flight planning activities. There are various activities such as weather conditions, flight plans, fuel efficiency, maintenance of flights, and many more that are being tracked with the help of MySQL as the database. It's a no-brainer that it needs to run 24x7 with full redundancy; MySQL was able to achieve this serving US Navy aircraft across the globe.

YouTube is also one of the prominent users of MySQL. Anytime you watch a video on YouTube it gets data from a relational database or a blob store using MySQL. YouTube also uses Vitess; a project that was released by YouTube to frontend MySQL. Vitess helps to do lots of optimization and acts as a proxy to serve each database request using MySQL. MySQL replicas are heavily used in YouTube's implementation; leveraging MySQL caching was one of the other prominent factors for YouTube.

When it comes to security, fraud detection, or compliance, and precisely if your solution helps you in identifying and preventing issues before they strike, then it becomes a sweet spot for business. Most of the time, fraud detection takes place a long time after the fraud has occurred, when you might have already suffered loss. The next steps would be obviously to minimize the impact of fraud and improve areas that could help you prevent this from being repeated.

Many companies who are into any type of transaction processing or claims use fraud detection techniques extensively. MySQL helps to analyze transactions, claims, and so on in real time, along with trends or anomalous behavior to prevent fraudulent activities.

PayPal is one of such use cases that has built fraud detection system using MySQL. PayPal has more than 100 million active users, which is distributed to US, Japanese, and European data centers. High-availability for such use cases is a key criteria along with performance, which MySQL has been able to deliver as expected.

Netflix has millions of subscribers; it uses MySQL for running its billing systems. The core billing system of Netflix on MySQL is a prominent backbone for any business. Netflix has billions of rows of data concurrently updated and of consisting data since its inception two decades ago. Compliance was one of the key factors along with migration from Oracle with minimal downtime; both of these were achieved with MySQL and has been expanding tremendously every other day.

Uber is one of the other well-known customers of MySQL. Uber had been growing enormously worldwide, and scalability, high-availability, and return on investments were a few of the important criteria to be worked upon. Uber uses MySQL as its primary database for its known private car transportation service. Uber heavily uses schema less database architecture as its backend as a layer on MySQL.

There are many real-world MySQL use cases that have changed humanity, technology, predictions, health, science and research, law and order, sports, e-commerce, power and energy, financial trading, robotics, and many more. MySQL is an integral part of our daily routine, which is not evident all the time, but yes, it plays a significant role in what we do in many ways.

In this chapter, we started with an overview of MySQL along with major features of the MySQL database and explored the newly added features in MySQL 8. After this, we took a deep dive into exciting new features of MySQL 8 along with benefits of using MySQL for your business applications. We understood MySQL 8's current limitations and restrictions, which is important for us when performing the implementations. Finally, we glanced through a few impressive use cases from the real world that play prominent roles in our daily routine, and they all use MySQL as their database.

In the next chapter, we will learn detailed steps for installing MySQL 8 on different platforms. The chapter also covers methods to upgrade or downgrade from MySQL 8, and they will all be discussed in detail.