MySQL 8 Administrator's Guide

Description

Step by step guide to monitor, manage, and secure your database engine Key Features Your companion to master all the administration-related tasks in MySQL 8 Ensure high performance and high availability of your MySQL solution using effective replication and backup techniques A comprehensive guide to performing query optimization, security and a whole host of other administrative tasks in MySQL 8 Book Description MySQL is one of the most popular and widely used relational databases in the world today. The recently released version 8.0 brings along some major advancements in the way your MySQL solution can be administered. This handbook will be your companion to understand the newly introduced features in MySQL and how you can leverage them to design a high-performance MySQL solution for your organization. This book starts with a brief introduction to the newly introduced features in MySQL 8, followed by quickly jumping onto the crucial administration topics that you will find useful in your day to day work. Topics such as migrating to MySQL 8, MySQL benchmarking, achieving high performance by implementing the indexing techniques, and optimizing your queries are covered in this book. You will also learn how to perform replication, scale your MySQL solution and implement effective security techniques. A special section on the common and not so common troubleshooting techniques for effective MySQL administration is also covered in this book. By the end of this highly practical book, you will have all the knowledge you need to tackle any problem you might encounter while administering your MySQL solution. What you will learn Understanding different MySQL 8 data types based on type of contents and storage requirements Best practices for optimal use of features in MySQL 8 Explore globalization configuration and caching techniques to improve performance Create custom storage engine as per system requirements Learn various ways of index implementation for flash memory storages Configure and implement replication along with approaches to use replication as solution Understand how to make your MySQL 8 solution highly available Troubleshoot common issues and identify error codes while using MySQL 8 Who this book is for This book is intended for MySQL administrators who are looking for a handy guide covering all the MySQL administration-related tasks. If you are a DBA looking to get started with MySQL administration, this book will also help you. Knowledge of the basic database concepts is required to get started with this book. Cover Copyright and Credits Packt Upsell Contributors Table of Contents Preface Chapter 1: An Introduction to MYSQL 8 Overview of MySQL MySQL as a relational database management system License requirements of MySQL8 Reliability and scalability Platform compatibility Releases Core features in MySQL Structured database Database storage engines and types Overview of InnoDB Overview of MyISAM Overview of memory Overview of archive Overview of BLACKHOLE as a storage engine Overview of CSV Overview of merge Overview of federated Overview of the NDB cluster Improved features in MySQL 8 Transactional data dictionary Roles InnoDB auto increment Invisible indexes Improving descending indexes The SET PERSIST variant Expanded GIS support Default character set Extended bit-wise operations InnoDB Memcached NOWAIT and SKIP LOCKED JSON Cloud Resource management Benefits of using MySQL 8 Security Scalability An open source relational database management system High performance High availability Cross-platform capabilities Limitations of MySQL 8 Number of tables or databases Table size Joins Windows platform Table column count Row size InnoDB storage engine Limitations of InnoDB storage engine Restrictions Data dictionary Limitations of group replication in MySQL8 Limitations of partitioning Constructs prohibition Operators Tables Use cases of MySQL Social media Government Media and entertainment Fraud detection Business mapping E-commerce Summary Chapter 2: Installing and Upgrading MySQL 8 The MySQL 8 installation process General installation guide Downloading MySQL 8 Verifying the package integrity Using MD5 checksums Using cryptographic signatures Installing MySQL 8 on Microsoft Windows Windows-specific considerations MySQL 8 installation layout Choosing the right installation package The MySQL 8 installer Initial setup information Installation workflow InnoDB cluster sandbox test setup Server configuration MySQL installer product catalog and dashboard MySQL installer console MySQL 8 installation using a ZIP file Installing MySQL 8 on Linux Installation using the Yum repository Installation using the RPM package Installation using the Debian package Post-installation setup for MySQL 8 Data directory initialization Securing the initial MySQL account Starting and troubleshooting MySQL 8 services Executing commands to test the server Upgrading MySQL 8 Upgrading methods In-place upgrade of MySQL Logical upgrade for MySQL 8 Upgrading prerequisites for MySQL 5.7 MySQL 8 downgrading Downgrading methods Logical downgrade Manual changes required before downgrading Summary Chapter 3: MySQL 8 – Using Programs and Utilities Overview of MySQL 8 programs MySQL programs in brief Startup programs Installation/upgradation programs Client programs Administrative and utilities programs  Environment variables MySQL GUI tool MySQL 8 command-line programs Executing programs from the command line Executing MySQL programs Connecting to the MySQL server Specifying options for programs Options on the command line Modifying program options Modifying options with files group opt_name opt_name=value Include directives Command-line options affecting option file handling Setting program variables with options Setting environment variables Server and server-startup programs mysqld - the MySQL server program Options mysqld_safe - MySQL server startup script mysql.server - MySQL Server startup script mysqld_multi - managing multiple MySQL servers Installation programs comp_err - compiling the MySQL error msg file mysql_secure_installation - improving MySQL installation security mysql_ssl_rsa_setup - creating SSL/RSA files mysql_tzinfo_to_sql - loading the timezone tables mysql_upgrade - checking and upgrading MySQL tables MySQL 8 client programs mysql - the command-line tool mysql options mysql commands help [arg], \h [arg],\? [arg], ? [arg] charset charset_name, \C charset_name clear, \c connect [db_name host_name], \r [db_name host_name] edit, \e exit, \q prompt [str], \R [str] quit, \q status, \s use db_name, \u db_name mysql logging mysql server-side help Executing sql from text files mysqladmin - client for administering a MySQL server mysqlcheck - a table maintenance program mysqldump - a database backup program Performance and scalability mysqlimport - a data import program mysqlpump - a database backup program mysqlsh - the MySQL Shell mysqlshow - showing database, table, and column information mysqlslap - load emulation client MySQL 8 administrative programs ibdsdi - InnoDB tablespace SDI extraction utility innochecksum - offline InnoDB file checksum utility myisam_ftdump - displaying full-text index utility myisamchk - MyISAM table-maintenance utility myisamlog - displaying MyISAM log file content myisampack - generating compressed, read-only MyISAM tables mysql_config_editor - MySQL configuration utility mysqlbinlog - utility for processing binary log files mysqldumpslow - summarizing slow query log files. MySQL 8 environment variables MySQL GUI tools MySQL Workbench MySQL Notifier MySQL Notifier usage Summary Chapter 4: MySQL 8 Data Types Overview of MySQL 8 data types Numeric data types Integer types Fixed point types Floating point types Problems with floating point values Bit value type Bit value literals Practical uses of BIT Type attributes Overflow handling Date and time data types DATE, DATETIME, and TIMESTAMP types MySQL DATETIME functions TIME type Time functions YEAR type Migrating YEAR(2) to YEAR(4) String data types CHAR and VARCHAR data types BINARY and VARBINARY data types BLOB and TEXT data types ENUM data type SET data type JSON data type Partial updates of JSON values Storage requirements for data types Choosing the right data type for column Summary Chapter 5: MySQL 8 Database Management MySQL 8 server administration Server options and different types of variables Server SQL modes Setting the SQL mode The available SQL modes Combination SQL modes Strict SQL mode The IGNORE keyword IPv6 support         Server side help The server shutdown process  Data directory The system database Data dictionary tables Grant system tables Object information system tables Log system tables The server-side help system tables Time zone system tables Replication system tables Optimizer system tables Other miscellaneous system tables Running multiple instances on a single machine Setting up multiple data directories Running multiple MySQL instances on Windows Components and plugin management MySQL 8 server plugins Installing the plugins Activate plugin Uninstall plugin Getting information about the installed plugins Roles and permissions Caching techniques Globalization Character sets Character set support Adding the character set Configuring the character sets Language selection Time zone settings for MySQL8 Locale support MySQL 8 server logs The error log Component configuration Default error log destination configuration  Default error log destination on Windows Default error log destination on Unix and  Unix-Like systems The general query log The binary log The slow query log The DDL log Server log maintenance Summary Chapter 6: MySQL 8 Storage Engines Overview of storage engines MySQL storage engine architecture Several types of storage engine Overview of the InnoDB storage engine Custom storage engine Several types of storage engines Pluggable storage engine architecture The common database server layer Setting the storage engine The MyISAM storage engine The MEMORY storage engine The CSV storage engine The ARCHIVE storage engine The BLACKHOLE storage engine The MERGE storage engine The FEDERATED storage engine The EXAMPLE storage engine The InnoDB storage engine ACID model Multiversioning Architecture Locking and transaction model Configuration Tablespaces Tables and indexes INFORMATION_SCHEMA tables  Memcached plugin Creating a custom storage engine Creating storage engine source files Adding engine-specific variables and parameters Creating the handlerton Handling handler installation Defining filename extensions Creating tables Opening a table Implementing basic table scanning Closing a table Reference for advanced custom storage engine Summary Chapter 7: Indexing in MySQL 8 An overview on indexing Uses of indexes in MySQL 8 SQL commands related to indexes Creating an INDEX command Spatial index characteristics Non-spatial index characteristics Drop index command SPATIAL index creation and optimization InnoDB and MyISAM index statistics collection Column-level indexing Column indexes Index prefixes FULLTEXT indexes Spatial Indexes Indexes in the MEMORY storage engine Multiple-column indexes B-Tree index Hash index Index extension Using an optimizer for indexes Invisible and descending indexes Invisible index Descending index Summary Chapter 8: Replication in MySQL 8 Overview of replication What is MySQL replication? Advantages of MySQL replication Configuring replication Binary log file based replication Replication master configuration REPLICATION SLAVE configuration Adding slaves to replication Global transaction identifiers based replication MySQL multi-source replication Replication administration tasks Implementing replication Replication formats Statement-based versus row-based replication Replication implementation details Replication channels Replication relay and status logs Evaluating replication filtering rules Group replication Primary-secondary replication versus group replication Group replication configuration Group replication use cases Replication solutions Summary Chapter 9: Partitioning in MySQL 8 Overview of partitioning Types of partitioning Partitioning management Partition selection and pruning Restrictions and limitations in partitioning Types of partitioning RANGE partitioning LIST partitioning COLUMNS partitioning RANGE COLUMN partitioning LIST COLUMN partitioning HASH partitioning LINEAR HASH partitioning KEY partitioning Subpartitioning Handling NULL in partitioning Partition management RANGE and LIST partition management HASH and KEY partition management Partition maintenance Obtain partition information Partition selection and pruning Partition pruning Partition selection Restrictions and limitations in partitioning Partitioning keys, primary keys, and unique keys Partitioning limitations relating to storage engines Partitioning limitations relating to functions Summary Chapter 10: MySQL 8 – Scalability and High Availability Overview of scalability and high availability in MySQL 8 MySQL replication MySQL cluster Oracle MySQL cloud service MySQL with the Solaris cluster Scaling MySQL 8 Scaling using cluster Client node Application node Management node Data node Data storage and management of disk-based and in-memory data Automatic and user-defined partitioning of tables or sharding of tables Synchronous data replication between data nodes Data retrieval and transactions Automatic fail over Automatic re-synchronization for self-healing after failure Scaling using memcached in MySQL 8 NoSQL APIs Scaling using replication Single server dependancy Performance Backup and recovery Load distribution Asynchronous data replication Geographical data distribution GTID replication ZFS replication Challenges in scaling MySQL 8 Business type and flexibility Understand server workload Read-write operation limit Maintenance Master server failure Synchronization Database security Cross node transaction Growing team for development Manage change request Scale-up and scale-out Achieving high availability Purpose of high availability Data availability Security of data Synchronization of data Backup of the data Competitive market Performance Updates in the system Choosing the solution Advantages of high availability Summary Chapter 11: MySQL 8 – Security Overview of security for MySQL 8 Common security issues General guidelines  Guidelines for a secure password Guidelines for end users Guidelines for administrators Password and logging Secure MYSQL 8 against attackers Security options and variables provided by MySQL 8 Security guidelines for client programming Access control in MySQL 8 Privileges provided by MySQL 8 Grant tables Verification of access control stages Stage 1 - Connection verification Stage 2 - Request verification Account management in MySQL 8 Add and remove user accounts Security using roles SET ROLE CREATE ROLE DROP ROLE  GRANT REVOKE SET DEFAULT ROLE SHOW GRANTS Password management Encryption in MySQL 8 Configuring MySQL 8 to use encrypted connections Server-side configuration for encrypted connections Client-side configuration for encrypted connections Command options for encrypted connections Connect with MySQL 8 remotely from Windows with SSH Security plugins Authentication plugins SHA-2 pluggable authentication Client-side cleartext pluggable authentication No-login pluggable authentication Socket peer-credential pluggable authentication Test pluggable authentication The connection-control plugins CONNECTION_CONTROL Plugin installation Variables related to CONNECTION-CONTROL The password validation plugin Install password validation plugin Variables and options related to the password validation plugin MySQL 8 keyring Install keyring plugin System variables related to keyring plugin Summary Chapter 12: Optimizing MySQL 8 Overview of MySQL 8 optimization Optimizing the database Optimizing the hardware Optimizing MySQL 8 servers and clients Optimizing disk I/O Using NFS with MySQL Optimizing the use of memory Optimizing use of the network Optimizing locking operations Performance benchmarking Examining thread information Optimizing database structure Optimizing data size Table columns Row format Indexes Joins Normalization Optimizing MySQL data types Optimizing for many tables Use of an internal temporary table in MySQL Optimizing queries Optimizing SQL statements Optimizing indexes Query execution plan Optimizing tables Optimization for InnoDB tables Optimization for MyISAM tables Optimization for MEMORY tables Leveraging buffering and caching InnoDB buffer pool optimization MyISAM key cache Summary Chapter 13: Extending MySQL 8 An overview of extending MySQL 8 MySQL 8 internals MySQL 8 plugin API  MySQL 8 services for components and plugins Adding new functions to MySQL 8 Debugging and porting MySQL 8 Extending plugins and using services to call them Writing plugins Component and plugin services The locking service The keyring service Adding new functions Features of a user-defined function interface Adding a new user-defined function Adding a new native function Debugging and porting Debugging MySQL server Debugging MySQL client The DBUG package Summary Chapter 14: MySQL 8 Best Practices and Benchmarking MySQL benchmarking and tools Resource utilization Stretching your benchmarking timelines Replicating production settings Consistency of throughput and latency Sysbench can do more Virtualization world Concurrency Hidden workloads Nerves of your query Benchmarks Best practices for memcached Resource allocation Operating system architecture Default configurations Max object size Backlog queue limit Large pages support Sensitive data Restricting exposure Failover Namespaces Caching mechanism Memcached general statistics Best practices for replication Throughput in group replication Infrastructure sizing Constant throughput Contradictory workloads Write scalability Best practices for data partitioning Horizontal partitioning Vertical partitioning Pruning partitions in MySQL Best practices for queries and indexing Data types Not null Indexing Search fields index Data types and joins Compound index Shortening up primary keys Indexing everything Fetching all data Letting the application do the job Existence of data Limiting yourself Analyzing slow queries Query cost Summary Chapter 15: Troubleshooting MySQL 8 MySQL 8 common problems Most common MySQL errors Access denied Can't connect to [local] MySQL server Lost connection to MySQL server Password fails when entered incorrectly Host host_name is blocked Too many connections Out of memory Packet too large The table is full Can't create/write to file Commands out of sync Ignoring user Table tbl_name doesn't exist MySQL 8 server errors Issues with file permissions Resetting the root password MySQL crashes prevention Handling MySQL full disk MySQL temporary files storage MySQL Unix socket file Time zone problems MySQL 8 client errors Case sensitivity in string searches Problems with DATE columns Problems with NULL values MySQL 8 troubleshooting approach Analyzing queries Real-world scenario Summary Other Books You May Enjoy Index