MySQL 8 Administrator's Guide

MySQL 8 Administrator's Guide

by Chintan Mehta, Ankit Bhavsar, Hetal Oza, Subhash Shah
Released February 2018
Publisher(s): Packt Publishing
ISBN: 9781788395199

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Step by step guide to monitor, manage, and secure your database engine

About This Book

  • 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

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.

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

In Detail

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.

Style and approach

A Step by Step guide to understand all new features of MySQL 8 and leverage it while administering your MySQL 8 solution

Table of contents

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

Product information

  • Title: MySQL 8 Administrator's Guide
  • Author(s): Chintan Mehta, Ankit Bhavsar, Hetal Oza, Subhash Shah
  • Release date: February 2018
  • Publisher(s): Packt Publishing
  • ISBN: 9781788395199