MySQL 8 Cookbook

MySQL 8 Cookbook

by Karthik Appigatla
Released January 2018
Publisher(s): Packt Publishing
ISBN: 9781788395809

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

Design and administer enterprise-grade MySQL 8 solutions

About This Book

  • Store, retrieve, and manipulate your data using the latest MySQL 8 features
  • Practical recipes on effective administration in MySQL, with a focus on security, performance tuning, troubleshooting, and more
  • Contains tips, tricks, and best practices for designing, developing, and administering your MySQL 8 database solution without any hassle

Who This Book Is For

If you are a MySQL developer or administrator looking for quick, handy solutions to solve the most common and not-so-common problems in MySQL, this book is for you. MySQL DBAs looking to get up-to-speed with the latest MySQL 8 development and administration features will also find this book very useful. Prior knowledge of Linux and RDBMS is desirable.

What You Will Learn

  • Install and configure your MySQL 8 instance without any hassle
  • Get to grips with new features of MySQL 8 like CTE, Window functions and many more
  • Perform backup tasks, recover data and set up various replication topologies for your database
  • Maximize performance by using new features of MySQL 8 like descending indexes, controlling query optimizer and resource groups
  • Learn how to use general table space to suit the SaaS or multi-tenant applications
  • Analyze slow queries using performance schema, sys schema and third party tools
  • Manage and monitor your MySQL instance and implement efficient performance-tuning tasks

In Detail

MySQL is one of the most popular and widely used relational databases in the World today. The recently released MySQL 8 version promises to be better and more efficient than ever before.

This book contains everything you need to know to be the go-to person in your organization when it comes to MySQL. Starting with a quick installation and configuration of your MySQL instance, the book quickly jumps into the querying aspects of MySQL. It shows you the newest improvements in MySQL 8 and gives you hands-on experience in managing high-transaction and real-time datasets. If you've already worked with MySQL before and are looking to migrate your application to MySQL 8, this book will also show you how to do that. The book also contains recipes on efficient MySQL administration, with tips on effective user management, data recovery, security, database monitoring, performance tuning, troubleshooting, and more.

With quick solutions to common and not-so-common problems you might encounter while working with MySQL 8, the book contains practical tips and tricks to give you the edge over others in designing, developing, and administering your database effectively.

Style and approach

This book takes a recipe-based approach to tackling the pain points of SQL developers. It is a comprehensive book full of solutions to common problems faced by SQL administrators and developers alike.

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Conventions used
    4. Sections
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
      5. See also
    5. Get in touch
      1. Reviews
  2. MySQL 8 - Installing and Upgrading
    1. Introduction
    2. Installing MySQL using YUM/APT
      1. How to do it...
        1. Using YUM repositories
        2. Using APT repositories
    3. Installing MySQL 8.0 using RPM or DEB files
      1. How to do it...
        1. Using the RPM bundle
        2. Using the APT bundle
    4. Installing MySQL on Linux using Generic Binaries
      1. How to do it...
      2. There's more...
    5. Starting or Stopping MySQL 8
      1. How to do it...
        1. Starting the MySQL 8.0 server
        2. Stopping the MySQL 8.0 server
        3. Checking the status of the MySQL 8.0 server
    6. Uninstalling MySQL 8
      1. How to do it...
        1. On YUM-based systems
        2. On APT-based systems
        3. Uninstalling Binaries
    7. Managing the MySQL Server with systemd
      1. How to do it...
    8. Downgrading from MySQL 8.0
      1. How to do it...
        1. In-place Downgrades
          1. Using YUM repositories
          2. Using APT Repositories
          3. Using the RPM or APT bundle
          4. Using Generic Binaries
        2. Logical Downgrades
          1. Using YUM Repositories
          2. Using APT Repositories
          3. Using RPM or APT bundles
          4. Using Generic Binaries
    9. Upgrading to MySQL 8.0
      1. Getting ready
      2. How to do it...
        1. In-place upgrades
          1. YUM-based systems
          2. APT-based systems
          3. Using RPM or APT bundles
          4. Using Generic Binaries
        2. Logical Upgrades
    10. Installing MySQL utilities
      1. How to do it...
        1. On YUM-based systems
        2. On APT-based systems
  3. Using MySQL
    1. Introduction
    2. Connecting to MySQL using the command-line client
      1. Getting ready
      2. How to do it...
      3. See also
    3. Creating databases
      1. How to do it...
      2. See also
    4. Creating tables
      1. How to do it...
        1. Cloning table structure
      2. See also
    5. Inserting, updating, and deleting rows
      1. How to do it...
        1. Inserting
        2. Updating
        3. Deleting
        4. REPLACE, INSERT, ON DUPLICATE KEY UPDATE
        5. Truncating tables
    6. Loading sample data
      1. How to do it...
    7. Selecting data
      1. How to do it...
        1. Selecting columns
        2. Count
        3. Filter based on condition
        4. Operators
        5. Simple pattern matching
        6. Regular expressions
        7. Limiting results
        8. Using the table alias
    8. Sorting results
      1. How to do it...
    9. Grouping results (aggregate functions)
      1. How to do it...
        1. COUNT
        2. SUM
        3. AVERAGE
        4. DISTINCT
        5. Filtering using HAVING
      2. See also
    10. Creating users
      1. How to do it...
      2. See also
    11. Granting and revoking access to users
      1. How to do it...
        1. Granting privileges
        2. Checking grants
        3. Revoking grants
        4. Modifying the mysql.user table
        5. Setting password expiry for users
        6. Locking users
        7. Creating roles for users
    12. Selecting data into a file and table
      1. How to do it...
        1. Saving as a file
        2. Saving as a table
    13. Loading data into a table
      1. How to do it...
    14. Joining tables
      1. How to do it...
        1. Identifying Duplicates using SELF JOIN
        2. Using SUB queries
        3. Finding mismatched rows between tables
    15. Stored procedures
      1. How to do it...
      2. There's more...
      3. See also
    16. Functions
      1. How to do it...
        1. Inbuilt functions
      2. See also
    17. Triggers
      1. How to do it...
      2. See also
    18. Views
      1. How to do it...
    19. Events
      1. How to do it...
        1. Access control
      2. See also
    20. Getting information about databases and tables
      1. How to do it...
        1. TABLES
        2. COLUMNS
        3. FILES
        4. INNODB_SYS_TABLESPACES
        5. INNODB_TABLESTATS
        6. PROCESSLIST
      2. See also
  4. Using MySQL (Advanced)
    1. Introduction
    2. Using JSON
      1. How to do it...
        1. Insert JSON
        2. Retrieve JSON
        3. JSON functions
          1. Pretty view
        4. Searching
        5. Modifying
        6. Removing
        7. Other functions
      2. See also
    3. Common table expressions (CTE)
      1. How to do it...
        1. Non-recursive CTE
        2. Recursive CTE
    4. Generated columns
      1. How to do it...
    5. Window functions
      1. How to do it...
        1. Row number
        2. Partition results
        3. Named windows
        4. First, last, and nth values
  5. Configuring MySQL
    1. Introduction
    2. Using config file
      1. How to do it...
    3. Using global and session variables
      1. How to do it...
    4. Using parameters with startup script
      1. How to do it...
    5. Configuring the parameters
      1. How to do it...
        1. data directory
        2. innodb_buffer_pool_size
        3. innodb_buffer_pool_instances
        4. innodb_log_file_size
    6. Changing the data directory
      1. How to do it...
  6. Transactions
    1. Introduction
    2. Performing transactions
      1. How to do it...
        1. Autocommit
    3. Using savepoints
      1. How to do it...
    4. Isolation levels
      1. How to do it...
        1. Read uncommitted
        2. Read committed
        3. Repeatable read
        4. Serializable
    5. Locking
      1. How to do it...
        1. Locking queue
  7. Binary Logging
    1. Introduction
    2. Using binary logging
      1. How to do it...
        1. Enabling binary logs
        2. Disabling binary logs for a session
        3. Move to the next log
        4. Expire binary logs
    3. Binary log format
      1. How to do it...
      2. See also
    4. Extracting statements from a binary log
      1. Getting ready
      2. How to do it...
        1. Observations
        2. Extracting based on time and position
        3. Extracting based on the database
        4. Extracting a row event display
        5. Rewriting a database name
        6. Disabling a binary log for recovery
        7. Displaying events in a binary log file
    5. Ignoring databases to write to a binary log
      1. How to do it...
        1. Example 1
        2. Example 2
    6. Relocating binary logs
      1. How to do it...
  8. Backups
    1. Introduction
    2. Taking backups using mysqldump
      1. How to do it...
        1. Full backup of all databases
        2. Point-in-time recovery
        3. Dumping master binary coordinates
        4. Specific databases and tables
        5. Ignore tables
        6. Specific rows
        7. Backup from a remote server
        8. Backup to rebuild another server with a different schema
        9. Only schema and no data
        10. Only data and no schema
        11. Backup for merging data with other server
          1. REPLACE with new data
          2. IGNORE data
    3. Taking backups using mysqlpump
      1. How to do it...
        1. Parallel processing
        2. Exclude/include database objects using regex
        3. Backup users
        4. Compressed backups
        5. Faster reload
    4. Taking backups using mydumper
      1. How to do it...
        1. Installation
        2. Full backup
        3. Consistent backup
        4. Backup of a single table
        5. Backup of specific databases using regex
        6. Taking backup of a big table using mydumper
        7. Non-blocking backup
        8. Compressed backups
        9. Backing up only data
    5. Taking backups using flat files
      1. How to do it...
    6. Taking backups using XtraBackup
      1. How to do it...
        1. Installation
          1. On CentOS/Red Hat/Fedora
          2. On Debian/Ubuntu
    7. Locking instances for backup
      1. How to do it...
    8. Binary log backup
      1. How to do it...
  9. Restoring Data
    1. Introduction
    2. Recovering from mysqldump and mysqlpump
      1. How to do it...
      2. There's more...
    3. Recovering from mydumper using myloader
      1. How to do it...
        1. Recovering full database
        2. Recover a single database
        3. Recovering a single table
    4. Recovering from flat file backup
      1. How to do it...
    5. Performing point-in-time recovery
      1. How to do it...
        1. mysqldump or mysqlpump
        2. mydumper
  10. Replication
    1. Introduction
    2. Setting up replication
      1. How to do it...
    3. Setting up master-master replication
      1. How to do it...
    4. Setting up multi-source replication
      1. How to do it...
    5. Setting up replication filters
      1. How to do it...
        1. Replicate a database only
        2. Replicate specific tables
        3. Ignore a database
        4. Ignore specific tables
      2. See also
    6. Switching slave from master-slave to chain replication
      1. How to do it...
    7. Switching the slave from chain replication to master-slave
      1. How to do it...
    8. Setting up delayed replication
      1. How to do it...
    9. Setting up GTID replication
      1. How to do it...
    10. Setting up semi-synchronous replication
      1. How to do it...
  11. Table Maintenance
    1. Introduction
    2. Installing Percona Toolkit
      1. How to do it...
        1. On Debian/Ubuntu
        2. On CentOS/Red Hat/Fedora
    3. Altering tables
      1. How to do it...
    4. Moving tables across databases
      1. How to do it...
    5. Altering tables using an online schema change tool
      1. How it works...
      2. How to do it...
    6. Archiving tables
      1. How to do it...
        1. Purging data
        2. Archiving data
        3. Copying data
      2. See also
    7. Cloning tables
      1. How to do it...
    8. Partitioning tables
      1. How to do it...
        1. RANGE partitioning
        2. Removing partitioning
        3. RANGE COLUMNS partitioning
        4. LIST and LIST COLUMNS partitioning
        5. HASH and LINEAR HASH partitioning
        6. KEY and LINEAR KEY partitioning
        7. Subpartitioning
    9. Partition pruning and selection
      1. How to do it...
        1. Partition pruning
        2. Partition selection
    10. Partition management
      1. How to do it...
        1. ADD partitions
        2. Reorganizing partitions
        3. DROP partitions
        4. TRUNCATE partitions
        5. Managing HASH and KEY partitions
        6. Other operations
    11. Partition information
      1. How to do it...
        1. Using SHOW CREATE TABLE
        2. Using SHOW TABLE STATUS
        3. Using EXPLAIN
        4. Querying the INFORMATION_SCHEMA.PARTITIONS table
    12. Efficiently managing time to live and soft delete rows
      1. How it works...
      2. How to do it...
  12. Managing Tablespace
    1. Introduction
    2. Changing the number or size of InnoDB redo log files
      1. How to do it...
    3. Resizing the InnoDB system tablespace
      1. How to do it...
        1. Increasing the InnoDB system tablespace
        2. Shrinking the InnoDB system tablespace
    4. Creating file-per-table tablespaces outside the data directory
      1. How to do it...
    5. Copying file-per-table tablespaces to another instance
      1. How to do it...
        1. Copy full table
        2. Copying individual partitions of a table
      2. See also
    6. Managing UNDO tablespace
      1. How to do it...
    7. Managing general tablespace
      1. How to do it...
        1. Create a general tablespace
        2. Adding tables to a general tablespace
        3. Moving non-partitioned tables between tablespaces
        4. Managing partitioned tables in a general tablespace
        5. Dropping general tablespace
    8. Compressing InnoDB tables
      1. How to do it...
        1. Enabling Compression for file_per_table Tables
        2. Disabling Compression for file_per_table Tables
        3. Enabling Compression for General Tablespace
  13. Managing Logs
    1. Introduction
    2. Managing the error log
      1. How to do it...
        1. Configuring the error log
        2. Rotating the error log
        3. Using the system log for logging
        4. Error logging in JSON format
    3. Managing the general query log and slow query log
      1. How to do it...
        1. General query log
        2. Slow query log
        3. Selecting  query log output destinations
    4. Managing the binary logs
      1. How to do it...
  14. Performance Tuning
    1. Introduction
    2. The explain plan
      1. How to do it...
        1. Using EXPLAIN
          1. Using EXPLAIN JSON
          2. Using EXPLAIN for connection
    3. Benchmarking queries and the server
      1. How to do it...
    4. Adding indexes
      1. Primary key (clustered index) and secondary indexes
      2. How to do it...
        1. Add index
        2. UNIQUE index
        3. Prefix index
        4. Drop index
        5. Index on generated columns
    5. Invisible index
      1. How to do it...
    6. Descending index
      1. How to do it...
    7. Analyzing slow queries using pt-query-digest
      1. How to do it...
        1. Slow query log
        2. General query log
        3. Process list
        4. Binary log
        5. TCP dump
      2. See also
    8. Optimizing datatypes
      1. How to do it...
    9. Removing duplicate and redundant indexes
      1. How to do it...
        1. pt-duplicate-key-checker
        2. mysqlindexcheck
    10. Checking index usage
      1. How to do it...
    11. Controlling the query optimizer
      1. How to do it...
        1. optimizer_search_depth
          1. How to know that the query is spending time in evaluating plans?
        2. optimizer_switch
        3. Optimizer hints
        4. Adjusting the optimizer cost model
    12. Using index hints
      1. How to do it...
    13. Indexing for JSON using generated columns
      1. How to do it...
    14. Using resource groups
      1. How to do it...
        1. Alter and drop resource group
    15. Using performance_schema
      1. How to do it...
        1. Enable/disable performance_schema
        2. Enable/disable consumers and instruments
        3. performance_schema tables
    16. Using the sys schema
      1. How to do it...
        1. Statement by type (INSERT and SELECT) from each host
        2. Statement by type from each user
        3. Redundant indexes
        4. Unused indexes
        5. Statements executed from each host
        6. Table statistics
        7. Table statistics with buffer
        8. Statement analysis
  15. Security
    1. Introduction
    2. Securing installation
      1. How to do it...
        1. The FILE privilege
    3. Restricting networks and users
      1. How to do it...
    4. Password-less authentication using mysql_config_editor
      1. How to do it...
    5. Resetting the root password
      1. How to do it...
        1. Using init-file
        2. Using --skip-grant-tables
    6. Setting up encrypted connections using X509
      1. How to do it...
    7. Setting up SSL replication
      1. How to do it...

Product information

  • Title: MySQL 8 Cookbook
  • Author(s): Karthik Appigatla
  • Release date: January 2018
  • Publisher(s): Packt Publishing
  • ISBN: 9781788395809