book

MySQL Cookbook, 4th Edition

by Sveta Smirnova, Alkin Tezuysal

August 2022

Intermediate to advanced

974 pages

26h 5m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Who This Book Is ForWhat’s in This BookMySQL APIs Used in This BookVersion and Platform NotesConventions Used in This BookThe MySQL Cookbook Companion GitHub RepositoryObtaining MySQL and Related SoftwareUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
1.0. Introduction1.1. Setting Up a MySQL User Account1.2. Creating a Database and a Sample Table1.3. Finding mysql Client1.4. Specifying mysql Command Options1.5. Executing SQL Statements Interactively1.6. Executing SQL Statements Read from a File or Program1.7. Controlling mysql Output Destination and Format1.8. Using User-Defined Variables in SQL Statements1.9. Customizing a mysql Prompt1.10. Using External Programs1.11. Filtering and Processing Output
2.0. Introduction2.1. Connecting to MySQL Server with MySQL Shell2.2. Selecting the Protocol2.3. Selecting SQL, JavaScript, or Python Mode2.4. Running SQL Session2.5. Running SQL in JavaScript Mode2.6. Running SQL in Python Mode2.7. Working with Tables in JavaScript Mode2.8. Working with Tables in Python Mode2.9. Working with Collections in JavaScript Mode2.10. Working with Collections in Python Mode2.11. Controlling the Output Format2.12. Running Reports with MySQL Shell2.13. Using MySQL Shell Utilities2.14. Using the Admin API to Automate Replication Management2.15. Working with JavaScript Objects2.16. Filling Test Data Using Python’s Data Science Modules2.17. Reusing Your Scripts for MySQL Shell
3.0. Introduction3.1. Configuring Basic Replication Between One Source and One Replica3.2. Position-Based Replication in the New Installation Environment3.3. Setting Up a Position-Based Replica of a MySQL Installation that Is Already in Use3.4. Setting Up GTID-Based Replication3.5. Configuring a Binary Log Format3.6. Using Replication Filters3.7. Rewriting a Database on the Replica3.8. Using a Multithreaded Replica3.9. Setting Up Circular Replication3.10. Using Multisource Replication3.11. Using a Semisynchronous Replication Plug-In3.12. Using Group Replication3.13. Storing Replication Credentials Securely3.14. Using TLS (SSL) for Replication3.15. Replication Troubleshooting3.16. Using Processlist to Understand Replication Performance3.17. Setting Up Automated Replication
4.0. Introduction4.1. Connecting, Selecting a Database, and Disconnecting4.2. Checking for Errors4.3. Writing Library Files4.4. Executing Statements and Retrieving Results4.5. Handling Special Characters and NULL Values in Statements4.6. Handling Special Characters in Identifiers4.7. Identifying NULL Values in Result Sets4.8. Obtaining Connection Parameters4.9. Resetting the profile Table
5.0. Introduction5.1. Specifying Which Columns and Rows to Select5.2. Naming Query Result Columns5.3. Sorting Query Results5.4. Removing Duplicate Rows5.5. Working with NULL Values5.6. Writing Comparisons Involving NULL in Programs5.7. Using Views to Simplify Table Access5.8. Selecting Data from Multiple Tables5.9. Selecting Rows from the Beginning, End, or Middle of Query Results5.10. What to Do When LIMIT and the Final Result Require a Different Sort Order5.11. Calculating LIMIT Values from Expressions5.12. Combining Two or More SELECT Results5.13. Selecting Results of Subqueries
6.0. Introduction6.1. Cloning a Table6.2. Saving a Query Result in a Table6.3. Creating Temporary Tables6.4. Generating Unique Table Names6.5. Checking or Changing a Table Storage Engine6.6. Copying a Table Using mysqldump6.7. Copying an InnoDB Table Using Transportable Tablespaces6.8. Copying a MyISAM Table Using an sdi File
7.0. Introduction7.1. String Properties7.2. Choosing a String Data Type7.3. Setting the Client Connection Character Set7.4. Writing String Literals7.5. Checking or Changing a String’s Character Set or Collation7.6. Converting the Lettercase of a String7.7. Comparing String Values7.8. Converting Between Decimal, Octal, and Hexadecimal Formats7.9. Converting Between ASCII, BIT, and Hexadecimal Formats 7.10. Pattern Matching with SQL Patterns7.11. Pattern Matching with Regular Expressions7.12. Reversing the String Content7.13. Searching for Substrings7.14. Breaking Apart or Combining Strings7.15. Using Full-Text Searches7.16. Using a Full-Text Search with Short Words7.17. Requiring or Prohibiting Full-Text Search Words7.18. Performing Full-Text Phrase Searches
8.0. Introduction8.1. Choosing a Temporal Data Type8.2. Using Fractional Seconds Support8.3. Changing MySQL’s Date Format8.4. Setting the Client Time Zone8.5. Setting the Server Time Zone8.6. Shifting Temporal Values Between Time Zones8.7. Determining the Current Date or Time8.8. Using TIMESTAMP or DATETIME to Track Row-Modification Times8.9. Extracting Parts of Dates or Times8.10. Synthesizing Dates or Times from Component Values8.11. Converting Between Temporal Values and Basic Units8.12. Calculating Intervals Between Dates or Times8.13. Adding Date or Time Values8.14. Calculating Ages8.15. Finding the First Day, Last Day, or Length of a Month8.16. Finding the Day of the Week for a Date8.17. Finding Dates for Any Weekday of a Given Week8.18. Canonizing Not-Quite-ISO Date Strings8.19. Selecting Rows Based on Temporal Characteristics

9.0. Introduction9.1. Using ORDER BY to Sort Query Results9.2. Using Expressions for Sorting9.3. Displaying One Set of Values While Sorting by Another9.4. Controlling Case Sensitivity of String Sorts9.5. Sorting in Temporal Order9.6. Sorting by Substrings of Column Values9.7. Sorting by Fixed-Length Substrings9.8. Sorting by Variable-Length Substrings9.9. Sorting Hostnames in Domain Order9.10. Sorting Dotted-Quad IP Values in Numeric Order9.11. Floating Values to the Head or Tail of the Sort Order9.12. Defining a Custom Sort Order9.13. Sorting ENUM Values
10.0. Introduction10.1. Summarizing with COUNT()10.2. Summarizing with MIN() and MAX()10.3. Summarizing with SUM() and AVG()10.4. Using DISTINCT to Eliminate Duplicates10.5. Creating a View to Simplify Using a Summary10.6. Finding Values Associated with Minimum and Maximum Values10.7. Controlling String Case Sensitivity for MIN() and MAX()10.8. Dividing a Summary into Subgroups10.9. Handling NULL Values with Aggregate Functions10.10. Selecting Only Groups with Certain Characteristics10.11. Using Counts to Determine Whether Values Are Unique10.12. Grouping by Expression Results10.13. Summarizing Noncategorical Data10.14. Finding Smallest or Largest Summary Values10.15. Producing Date-Based Summaries10.16. Working with Per-Group and Overall Summary Values Simultaneously10.17. Generating a Report that Includes a Summary and a List10.18. Generating Summaries from Temporary Result Sets
11.0. Introduction11.1. Creating Compound-Statement Objects11.2. Using Stored Functions to Simplify Calculations11.3. Using Stored Procedures to Produce Multiple Values11.4. Using Triggers to Log Changes to a Table11.5. Using Events to Schedule Database Actions11.6. Writing Helper Routines for Executing Dynamic SQL11.7. Detecting “No More Rows” Conditions Using Condition Handlers11.8. Catching and Ignoring Errors with Condition Handlers11.9. Raising Errors and Warnings11.10. Logging Errors by Accessing the Diagnostic Area11.11. Using Triggers to Preprocess or Reject Data
12.0. Introduction12.1. Determining the Number of Rows Affected by a Statement12.2. Obtaining Result Set Metadata12.3. Listing or Checking the Existence of Databases or Tables12.4. Listing or Checking the Existence of Views12.5. Accessing Table Column Definitions12.6. Getting ENUM and SET Column Information12.7. Getting Server Metadata12.8. Writing Applications That Adapt to the MySQL Server Version12.9. Getting Child Tables That Reference a Specific Table via Foreign Key Constraints12.10. Listing Triggers12.11. Listing Stored Routines and Scheduled Events12.12. Listing Installed Plug-Ins12.13. Listing Character Sets and Collations12.14. Listing CHECK Constraints
13.0. Introduction13.1. Importing Data with LOAD DATA and mysqlimport13.2. Specifying Column and Line Delimiters13.3. Dealing with Quotes and Special Characters13.4. Handling Duplicate Key Values13.5. Obtaining Diagnostics About Bad Input Data13.6. Skipping Datafile Lines13.7. Specifying Input Column Order13.8. Preprocessing Input Values Before Inserting Them13.9. Ignoring Datafile Columns13.10. Importing CSV Files13.11. Exporting Query Results from MySQL13.12. Importing and Exporting NULL Values13.13. Exporting Data in SQL Format13.14. Importing SQL Data13.15. Exporting Query Results as XML13.16. Importing XML into MySQL13.17. Importing Data in JSON Format13.18. Importing Data from MongoDB13.19. Exporting Data in JSON Format13.20. Guessing Table Structure from a Datafile
14.0. Introduction14.1. Using the SQL Mode to Reject Bad Input Values14.2. Using CHECK Constraints to Reject Invalid Values14.3. Using Triggers to Reject Input Values14.4. Writing an Input-Processing Loop14.5. Putting Common Tests in Libraries14.6. Using Pattern Matching to Validate Data14.7. Using Patterns to Match Broad Content Types14.8. Using Patterns to Match Numeric Values14.9. Using Patterns to Match Dates or Times14.10. Using Patterns to Match Email Addresses or URLs14.11. Using Table Metadata to Validate Data14.12. Using a Lookup Table to Validate Data14.13. Converting Two-Digit Year Values to Four-Digit Form14.14. Performing Validity Checking on Date or Time Subparts14.15. Writing Date-Processing Utilities14.16. Importing Non-ISO Date Values14.17. Exporting Dates Using Non-ISO Formats14.18. Preprocessing and Importing a File
15.0. Introduction15.1. Generating a Sequence with AUTO_INCREMENT Columns15.2. Choosing the Data Type for a Sequence Column15.3. Deleting Rows Without Changing a Sequence15.4. Retrieving Sequence Values15.5. Renumbering an Existing Sequence15.6. Extending the Range of a Sequence Column15.7. Reusing Values at the Top of a Sequence15.8. Ensuring That Rows Are Renumbered in a Particular Order15.9. Sequencing an Unsequenced Table15.10. Managing Multiple Auto-Increment Values Simultaneously15.11. Using Auto-Increment Values to Associate Tables15.12. Using Sequence Generators as Counters15.13. Generating Repeating Sequences15.14. Using Custom Increment Values15.15. Using Window Functions to Number Rows in the Result Set15.16. Generating Series with Recursive CTEs15.17. Creating and Storing Custom Sequences
16.0. Introduction16.1. Finding Matches Between Tables16.2. Finding Mismatches Between Tables16.3. Identifying and Removing Mismatched or Unattached Rows16.4. Comparing a Table to Itself16.5. Producing Candidate-Detail Lists and Summaries16.6. Enumerating a Many-to-Many Relationship16.7. Finding Per-Group Minimum or Maximum Values16.8. Using a Join to Fill or Identify Holes in a List16.9. Using a Join to Control Query Sort Order16.10. Joining Results of Multiple Queries16.11. Referring to Join Output Column Names in Programs
17.0. Introduction17.1. Calculating Descriptive Statistics17.2. Calculating Descriptive Statistics for Groups17.3. Generating Frequency Distributions17.4. Counting Missing Values17.5. Calculating Linear Regressions or Correlation Coefficients17.6. Generating Random Numbers17.7. Randomizing a Set of Rows17.8. Selecting Random Items from a Set of Rows17.9. Calculating Successive-Row Differences17.10. Finding Cumulative Sums and Running Averages17.11. Assigning Ranks17.12. Computing Team Standings
18.0. Introduction18.1. Preventing Duplicates from Occurring in a Table18.2. Having More Than One Unique Key in the Table18.3. Dealing with Duplicates When Loading Rows into a Table18.4. Counting and Identifying Duplicates18.5. Eliminating Duplicates from a Table
19.0. Introduction19.1. Choosing the Right Data Type19.2. Inserting JSON Values19.3. Validating JSON19.4. Formatting JSON Values19.5. Extracting Values from JSON19.6. Searching Inside JSON19.7. Inserting New Elements into a JSON Document19.8. Updating JSON19.9. Removing Elements from JSON19.10. Merging Two or More JSON Documents into One19.11. Creating JSON from Relational Data19.12. Converting JSON into Relational Format19.13. Investigating JSON19.14. Working with JSON in MySQL as a Document Store
20.0. Introduction20.1. Choosing a Transactional Storage Engine20.2. Performing Transactions Using SQL20.3. Performing Transactions from Within Programs20.4. Performing Transactions in Perl Programs20.5. Performing Transactions in Ruby Programs20.6. Performing Transactions in PHP Programs20.7. Performing Transactions in Python Programs20.8. Performing Transactions in Go Programs20.9. Using Context-Aware Functions to Handle Transactions in Go20.10. Performing Transactions in Java Programs
21.0. Introduction21.1. Creating Indexes21.2. Creating a Surrogate Primary Key21.3. Maintaining Indexes21.4. Deciding When a Query Can Use an Index21.5. Deciding the Order for Multiple Column Indexes21.6. Using Ascending and Descending Indexes21.7. Using Function-Based Indexes21.8. Using Indexes on Generated Columns with JSON Data21.9. Using Full Text Indexes21.10. Utilizing Spatial Indexes and Geographical Data21.11. Creating and Using Histograms21.12. Writing Performant Queries
22.0. Introduction22.1. Configuring the Server22.2. Managing the Plug-In Interface22.3. Controlling Server Logging22.4. Rotating or Expiring Logfiles22.5. Rotating Log Tables or Expiring Log Table Rows22.6. Configuring Storage Engines
23.0. Introduction23.1. Why Monitor the MySQL Server?23.2. Discovering Sources of MySQL Monitoring Information23.3. Checking Server Uptime and Progress23.4. Troubleshooting Server Start Problems23.5. Determining the IO Utilization of the MySQL Server23.6. Determining MySQL Thread’s CPU Utilization23.7. Determining if MySQL Has Reached Its Connection Limits23.8. Verifying That the Buffer Pool Is Sized Properly23.9. Finding Information About the Storage Engine23.10. Using the Error Log File to Troubleshoot MySQL Server Crashes23.11. Slow Query Log File23.12. Monitoring with the General Query Log23.13. Using the Binary Log to Identify Changes
24.0. Introduction24.1. Understanding the mysql.user Table24.2. Managing User Accounts24.3. Implementing a Password Policy24.4. Checking Password Strength24.5. Expiring Passwords24.6. Assigning Yourself a New Password24.7. Resetting an Expired Password24.8. Finding and Removing Anonymous Accounts24.9. Modifying “Any Host” and “Many Host” Accounts24.10. Using TLS (SSL)24.11. Using Roles24.12. Using Views to Secure Data Access24.13. Using Stored Routines to Secure Data Modifications

Content preview from MySQL Cookbook, 4th Edition

Chapter 3. MySQL Replication

3.0 Introduction

MySQL replication provides a way to set up a copy (replica) server of the active (source) database, then automatically continuously update such a copy applying all of the changes the source server receives.

Replica is useful in many situations, particularly the following:

Hot Standby: A server, normally idle, will replace an active one in case of a failure.
Read scale: Multiple servers, replicating from the same source, can process more parallel read requests than a single machine.
Geographical distribution: When an application serves users in different regions, having a local database server can help users retrieve data faster.
Analytics server: Complicated analytics queries may take hours to run, set plenty of locks, and use a lot of resources. Running them on the replica minimizes the impact on other parts of the application.
Backup server: Taking backups from a live database involves high-IO resource usage and locking, which is necessary to avoid data inconsistencies between the backup and active dataset. Taking backups from the dedicated replica reduces the impact on production.
Delayed copy: A replica, applying updates with a delay, configured by the SOURCE_DELAY (MASTER_DELAY) option, allows for rolling back human errors, such as the removal of an important table.

Note

Historically, the source server was called a “master,” and the replica server was called a “slave.” Recently, it was discovered that the terminology master and slave do not correctly ...