Book Description

Get a comprehensive overview on how to set up and design an effective database with MySQL. This thoroughly updated edition covers MySQL’s latest version, including its most important aspects. Whether you’re deploying an environment, troubleshooting an issue, or engaging in disaster and recovery, this practical guide provides the insights and tools necessary to take full advantage of this powerful RDBMS.

Authors Vinicius Grippa and Sergey Kuzmichev from Percona show developers and DBAs methods for minimizing costs and maximizing availability and performance. You’ll learn how to perform basic and advanced querying, monitoring and troubleshooting, database management and security, backup and recovery, and tuning for improved efficiency. This edition includes new chapters on high availability, load balancing, and using MySQL in the cloud.

Learning MySQL will help you:

  • Deploy MySQL databases on bare metal, on virtual machines, and in the cloud
  • Design database infrastructures
  • Code highly efficient queries
  • Monitor and troubleshoot MySQL databases
  • Execute efficient backup and restore operations
  • Optimize database costs in the cloud
  • Understand database concepts, especially those pertaining to MySQL

Table of Contents

  1. 1. Installing MySQL
    1. MySQL Forks
      1. MySQL community
      2. Percona Server for MySQL
      3. MariaDB Server
      4. MySQL Enterprise
    2. Installation Choices and Platforms
    3. Installing Linux
      1. Installing MySQL 8 on Centos 7 using yum
      2. Installing MySQL 8 on Centos 8
      3. Installing MySQL 8 on Ubuntu 20.04 LTS (Focal Fossa)
      4. Installing Percona Server 8 on Centos 7
      5. Installing Percona Server 8 on Ubuntu 20.04 LTS (Focal Fossa)
      6. Installing MariaDB 10.5 on Centos 7
      7. Installing MariaDB 10.5 on Ubuntu 20.04 LTS (Focal Fossa)
    4. The Contents of the MySQL Directory
      1. MySQL 5.7 default files
      2. MySQL 8 default files
    5. Using the Command-Line Interface
    6. Using Docker
      1. Installing Docker
      2. Using Sandboxes
    7. Upgrading the MySQL Server
  2. 2. Modeling and Designing Databases
    1. How Not to Develop a Database
    2. The Database Design Process
    3. The Entity Relationship Model
      1. Representing Entities
      2. Representing Relationships
      3. Partial and Total Participation
      4. Entity or Attribute?
      5. Entity or Relationship?
      6. Intermediate Entities
      7. Weak and Strong Entities
    4. Database Normalization
    5. Normalizing an example table
    6. Entity Relationship Modeling Examples
      1. The Music Database
      2. The University Database
      3. The Flight Database
    7. Using the Entity Relationship Model
      1. Mapping Entities and Relationships to Database Tables
      2. Converting the Music Database ER Model to a Database Schema
    8. Using Tools for Database Design
  3. 3. Basic SQL
    1. Using the Sakila Database
    2. The SELECT Statement and Basic Querying Techniques
      1. Single Table SELECTs
      2. Choosing Columns
      3. Choosing Rows with the WHERE Clause
      4. ORDER BY Clauses
      5. The LIMIT Clause
      6. Joining Two Tables
    3. The INSERT Statement
      1. INSERT Basics
      2. Alternative Syntaxes
    4. The DELETE Statement
      1. DELETE Basics
      2. Using WHERE, ORDER BY, and LIMIT
      3. Removing All Rows with TRUNCATE
    5. The UPDATE Statement
      1. Examples
      2. Using WHERE, ORDER BY, and LIMIT
    6. Exploring Databases and Tables with SHOW and mysqlshow
  4. 4. Working with Database Structures
    1. Creating and Using Databases
    2. Creating Tables
      1. Basics
      2. Collation and Character Sets
      3. Other Features
      4. Column Types
      5. Keys and Indexes
      6. The AUTO_INCREMENT Feature
    3. Altering Structures
      1. Adding, Removing, and Changing Columns
      2. Adding, Removing, and Changing Indexes
      3. Renaming Tables and Altering Other Structures
    4. Deleting Structures
      1. Dropping Databases
      2. Removing Tables