0%

For MySQL, the price of popularity comes with a flood of questions from users on how to solve specific data-related issues. That's where this cookbook comes in. When you need quick solutions or techniques, this handy resource provides scores of short, focused pieces of code, hundreds of worked-out examples, and clear, concise explanations for programmers who don't have the time (or expertise) to resolve MySQL problems from scratch.

In this updated fourth edition, authors Sveta Smirnova and Alkin Tezuysal provide more than 200 recipes that cover powerful features in both MySQL 5.7 and 8.0. Beginners as well as professional database and web developers will dive into topics such as MySQL Shell, MySQL replication, and working with JSON.

You'll learn how to:

  • Connect to a server, issue queries, and retrieve results
  • Retrieve data from the MySQL Server
  • Store, retrieve, and manipulate strings
  • Work with dates and times
  • Sort query results and generate summaries
  • Assess the characteristics of a dataset
  • Write stored functions and procedures
  • Use stored routines, triggers, and scheduled events
  • Perform basic MySQL administration tasks
  • Understand MySQL monitoring fundamentals

Table of Contents

  1. 1. Using the mysql Client Program
    1. 1.0. Introduction
    2. 1.1. Setting Up a MySQL User Account
    3. 1.2. Creating a Database and a Sample Table
    4. 1.3. What to Do if mysql Cannot Be Found
    5. 1.4. Specifying mysql Command Options
    6. 1.5. Executing SQL Statements Interactively
    7. 1.6. Executing SQL Statements Read from a File or Program
    8. 1.7. Controlling mysql Output Destination and Format
    9. 1.8. Using User-Defined Variables in SQL Statements
    10. 1.9. Customizing mysql Prompt
    11. 1.10. Using External Programs
    12. 1.11. Filter and process output
  2. 2. MySQL Replication
    1. 2.0. Introduction
    2. 2.1. Configuration of the Basic Replication between One Source and One Replica
    3. 2.2. Position-Based Replication in the New Installation Environment
    4. 2.3. Position-Based Replication for the Source Server that is Already in Use
    5. 2.4. GTID-based Replication
    6. 2.5. Binary Log Format
    7. 2.6. Replication Filters
    8. 2.7. Rewriting Database on the Replica
    9. 2.8. Multithreaded Replica
    10. 2.9. Circular Replication
    11. 2.10. Multisource Replication
    12. 2.11. Semisynchronous Replication
    13. 2.12. Group Replication
    14. 2.13. Storing Replication Credentials Securely
    15. 2.14. Using TLS (SSL) for Replication
    16. 2.15. Replication Troubleshooting
    17. 2.16. Use Processlist to Understand Replication Performance
    18. 2.17. Replication Automation
  3. 3. Selecting Data from Tables
    1. 3.0. Introduction
    2. 3.1. Specifying Which Columns and Rows to Select
    3. 3.2. Naming Query Result Columns
    4. 3.3. Sorting Query Results
    5. 3.4. Removing Duplicate Rows
    6. 3.5. Working with NULL Values
    7. 3.6. Writing Comparisons Involving NULL in Programs
    8. 3.7. Using Views to Simplify Table Access
    9. 3.8. Selecting Data from Multiple Tables
    10. 3.9. Selecting Rows from the Beginning, End, or Middle of Query Results
    11. 3.10. What to Do When LIMIT Requires the Wrong Sort Order
    12. 3.11. Calculating LIMIT Values from Expressions
    13. 3.12. Combining Two or More SELECT Results
    14. 3.13. Selecting Results of Subqueries
  4. 4. Table Management
    1. 4.0. Introduction
    2. 4.1. Cloning a Table
    3. 4.2. Saving a Query Result in a Table
    4. 4.3. Creating Temporary Tables
    5. 4.4. Generating Unique Table Names
    6. 4.5. Checking or Changing a Table Storage Engine
    7. 4.6. Copying a Table Using mysqldump
    8. 4.7. Copying an InnoDB Table Using Transportable Tablespaces