MySQL 8 Database Management

In the previous chapter, we learned about MySQL 8 data types, explaining in detail which data types are available and how they are categorized. There are various properties associated with each of these data types, and the storage capacity varies with each type. The previous chapter also provided you with an in-depth understanding of MySQL 8 data types. Now its time to get some practical knowledge on MySQL 8 administrative features. Isn't it interesting to know more about the administrative features of MySQL 8, how configuration will be done for it, and much more? It's extremely important for an administrator to have detailed knowledge on how MySQL 8 works for globalization, how logs are maintained, and how to enhance capability of the server. Now, let's start with some fundamental concepts.

We will cover the follow topics in this chapter...

MySQL 8 server administration

There are many operating parameters that available with MySQL 8, and among them all the required parameters are set by default during the installation process. After installation, you are allowed to change the option file by removing or adding a comment sign (#) at the start of the line of a specific parameter setting. The user is also allowed to set parameters at runtime by using command line arguments or the option file.

Server options and different types of variables

In this section, we are going to cover server options, system variable, and status variables available in MySQL 8 on startup.

• Server option: As described in the previous chapter MySQL 8 uses the option file and command line arguments...

Data directory

The data directory is the place where MySQL 8 stores all the information that is managed by itself. Each sub-directory of the data directory represents a database directory and its related data. All the MySQL installations have the following standard databases:

• The sys directory: This represents the sys schema, which contains the objects useful for the Performance Schema information interpretation.
• The performance schema directory: This directory is used to observe the internal execution of the MySQL server at run-time.
• The mysql directory: This directory is related to the MySQL system database, which contains the data dictionary table and the system tables. It contains the information that is required by the MySQL server once it is running.

The system database

The system database mainly contains the data dictionary tables that stores the object's metadata and system tables for other operational purposes. The system database contains a number of system tables. We will learn more about them in the coming sections.

Data dictionary tables

The data dictionary tables contains the metadata about data objects. Tables of this directory are invisible and are not read by general SQL queries such as SELECT, SHOW TABLES, INFORMATION_SCHEMA.TABLES, and so on. MySQL mainly exposes the metadata using the INFORMATION_SCHEMA option.

Grant system tables

...

Running multiple instances on a single machine

There might be some situations where you are required to install multiple instances on a single machine. It may be to check the performance of two different versions, or perhaps there is a need to manage two separate databases on different MySQL instances. The reason can be anything, but MySQL allows user to execute multiple instances on the same machine by providing different configuration values. MySQL 8 allows users to configure parameters by making use of the command line, option file, or by setting environment variables. The primary resource used by MySQL 8 for this is the data directory and it must be unique for the two instances. We can define the value for the same using the --datadir=dir_name function. Apart from the data directory, we will also configure unique values for the following options as well:

• --port=port_num
• ...

Components and plugin management

The component based structure is supported by MySQL server, to extend the server capabilities. MySQL 8 uses the INSTALL COMPONENT and UNINSTALL COMPONENT SQL statements to load and unload components at runtime. MySQL 8 manages component details into the mysql.component system table. So, every time a new component is installed, MySQL 8 server performs the following tasks:

• Load components into server to make available instantly
• Load service registered component into the mysql.component system table.

When we uninstall any component, MySQL server will perform the same steps, but in the reverse order. To see which components are available, execute the following query:

SELECT * FROM mysql.component;

MySQL 8 server plugins

...

Roles and permissions

To put it simply, a role is a collection of privileges. To create a role in MySQL 8, you must have the global CREATE ROLE or CREATE USER privilege. MySQL 8 provides various privileges to attach to roles and users. Refer to https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html for more details on the available privileges.

Now, let's take an example to understand the role creation and privileges assignment. Assume we have a hr_employee table already created in the current database and we want to give access of this table to the hrdepartment role. This dilemma can be resolved by making use of the following code:

CREATE ROLE hrdepartment;
grant all on hr_employee to hrdepartment;

The above code will help us to create the hrdepartment role and grant all the necessary access to it. This topic will be covered in detailed in Chapter 11, Security.

...

Caching techniques

Cache is a mechanism used to improve performance. MySQL uses several strategies to cache information in the buffer. MySQL 8 make use of the cache at the storage engine level to handle its operations. It also applies the cache in prepared statements and stored programs to improve performance. MySQL 8 has introduced various system level variables to manage cache, such as binlog_stmt_cache_size, daemon_memcached_enable_binlog, daemon_memcached_w_batch_size, host_cache_size, and many more. We will cover caching in detail in Chapter 12, Optimizing MySQL 8.

Globalization

Globalization is a feature which provides multi-language support for an application, such as enabling the use of native languages. It is much easier to understand messages in our own native language than other languages, right? To achieve this, globalization comes into the picture. Using globalization a user can store, retrieve and update data into many languages. There are certain parameters that are to be considered in globalization. We will discuss them in detail in the following sections.

Character sets

Before going into detail about character sets it is required to understand what a character set actually is, as well as its related terms, right? Let's start with the term itself; the character set is...

MySQL 8 server logs

MySQL 8 server provides the following different type of logs that enable users to track the activity of the server in various situations:

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 master 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

You can learn more about the different type of logs at https://dev.mysql.com/doc/refman/8.0/en/server-logs.html.

MySQL 8 will not generate the logs in MySQL 8, except in error logs in Windows, unless we enable...

Summary

This was an interesting chapter for any MySQL 8 user, wasn't it? In this chapter we understood how MySQL 8 manages different log files and which log file to use at what time. At the same time we also covered many of the administrative features, such as globalization, system data database, and component and plugin configuration, and explained how to run multiple instances on a single machine. The later part of the chapter covered log maintenance.

Moving on to the next chapter, we will provide you with information about storage engines, such as what the different types of storage engine are, which one to use for your application, and how to create our own custom storage engine for MySQL 8.