book

Learning MySQL, 2nd Edition

by Vinicius M. Grippa, Sergey Kuzmichev

September 2021

Beginner

629 pages

17h 9m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Who This Book Is ForHow This Book Is OrganizedStarting with MySQLUsing MySQLMySQL in ProductionMiscellaneous TopicsConventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgmentsFrom Vinicius GrippaFrom Sergey Kuzmichev
MySQL ForksMySQL Community EditionPercona Server for MySQLMariaDB ServerMySQL Enterprise EditionInstallation Choices and Platforms1. Download the Distribution that You Want to Install2. Install the Distribution3. Perform Any Necessary Post-Installation Setup4. Run BenchmarksInstalling MySQL on LinuxInstalling MySQL on CentOS 7Installing MySQL on CentOS 8Installing MySQL on Ubuntu 20.04 LTS (Focal Fossa)Installing MySQL on macOS Big SurInstalling MySQL on Windows 10The Contents of the MySQL DirectoryMySQL 5.7 Default FilesMySQL 8.0 Default FilesUsing the Command-Line InterfaceUsing DockerInstalling DockerUsing SandboxesInstalling DBdeployerUsing DBdeployerUpgrading MySQL Server
How Not to Develop a DatabaseThe Database Design ProcessThe Entity Relationship ModelRepresenting EntitiesRepresenting RelationshipsPartial and Total ParticipationEntity or Attribute?Entity or Relationship?Intermediate EntitiesWeak and Strong EntitiesDatabase NormalizationNormalizing an Example TableFirst Normal Form: No Repeating GroupsSecond Normal Form: Eliminate Redundant DataThird Normal Form: Eliminate Data Not Dependent on KeyEntity Relationship Modeling ExamplesUsing the Entity Relationship ModelMapping Entities and Relationships to Database TablesCreating a Bank Database ER ModelConverting the EER to a MySQL Database Using Workbench
Using the sakila DatabaseThe SELECT Statement and Basic Querying TechniquesSingle-Table SELECTsChoosing ColumnsSelecting Rows with the WHERE ClauseThe ORDER BY ClauseThe LIMIT ClauseJoining Two TablesThe INSERT StatementINSERT BasicsAlternative SyntaxesThe DELETE StatementDELETE BasicsUsing WHERE, ORDER BY, and LIMITRemoving All Rows with TRUNCATEThe UPDATE StatementExamplesUsing WHERE, ORDER BY, and LIMITExploring Databases and Tables with SHOW and mysqlshow
Creating and Using DatabasesCreating TablesBasicsCollation and Character SetsOther FeaturesColumn TypesKeys and IndexesThe AUTO_INCREMENT FeatureAltering StructuresAdding, Removing, and Changing ColumnsAdding, Removing, and Changing IndexesRenaming Tables and Altering Other StructuresDeleting StructuresDropping DatabasesRemoving Tables
AliasesColumn AliasesTable AliasesAggregating DataThe DISTINCT ClauseThe GROUP BY ClauseThe HAVING ClauseAdvanced JoinsThe Inner JoinThe UnionThe Left and Right JoinsThe Natural JoinConstant Expressions in JoinsNested QueriesNested Query BasicsThe ANY, SOME, ALL, IN, and NOT IN ClausesThe EXISTS and NOT EXISTS ClausesNested Queries in the FROM ClauseNested Queries in JOINsUser Variables
Isolation LevelsREPEATABLE READREAD COMMITTEDREAD UNCOMMITTEDSERIALIZABLELockingMetadata LocksRow LocksDeadlocksMySQL Parameters Related to Isolation and Locks
Inserting Data Using QueriesLoading Data from Comma-Delimited FilesWriting Data into Comma-Delimited FilesCreating Tables with QueriesPerforming Updates and Deletes with Multiple TablesDeletionUpdatesReplacing DataThe EXPLAIN StatementAlternative Storage EnginesInnoDBMyISAM and AriaMyRocks and TokuDBOther Table Types
Understanding Users and PrivilegesThe root UserCreating and Using New UsersGrant TablesUser Management Commands and LoggingModifying and Dropping UsersModifying a UserDropping a UserPrivilegesStatic Versus Dynamic PrivilegesThe SUPER PrivilegePrivilege Management CommandsChecking PrivilegesThe GRANT OPTION PrivilegeRolesChanging root’s Password and Insecure StartupSome Ideas for Secure Setup
Structure of the Option FileScope of OptionsSearch Order for Option FilesSpecial Option FilesLogin Path Configuration FilePersistent System Variables Configuration FileDetermining the Options in Effect

Physical and Logical BackupsLogical BackupsPhysical BackupsOverview of Logical and Physical BackupsReplication as a Backup ToolInfrastructure FailureDeployment BugThe mysqldump ProgramBootstrapping Replication with mysqldumpLoading Data from a SQL Dump Filemysqlpumpmydumper and myloaderCold Backup and Filesystem SnapshotsPercona XtraBackupBacking Up and RecoveringAdvanced FeaturesIncremental Backups with XtraBackupOther Physical Backup ToolsMySQL Enterprise BackupmariabackupPoint-in-Time RecoveryTechnical Background on Binary LogsPreserving Binary LogsIdentifying a PITR TargetPoint-in-Time-Recovery Example: XtraBackupPoint-in-Time-Recovery Example: mysqldumpExporting and Importing InnoDB TablespacesTechnical BackgroundExporting a TablespaceImporting a TablespaceXtraBackup Single-Table RestoreTesting and Verifying Your BackupsDatabase Backup Strategy Primer
The MySQL Server DaemonMySQL Server VariablesChecking Server SettingsBest Practices
Operating System MetricsCPUDiskMemoryNetworkMySQL Server ObservabilityStatus VariablesBasic Monitoring RecipesThe Slow Query LogInnoDB Engine Status ReportInvestigation MethodsThe USE MethodRED MethodMySQL Monitoring ToolsIncident/Diagnostic and Manual Data CollectionGathering System Status Variable Values PeriodicallyUsing pt-stalk to Collect MySQL and OS MetricsExtended Manual Data Collection
Asynchronous ReplicationBasic Parameters to Set on the Source and the ReplicaCreating a Replica Using PerconaXtraBackupCreating a Replica Using the Clone PluginCreating a Replica Using mysqldumpCreating a Replica Using mydumper and myloaderGroup ReplicationSynchronous ReplicationGalera/PXC Cluster
Database-as-a-Service (DBaaS)Amazon RDS for MySQL/MariaDBGoogle Cloud SQL for MySQLAzure SQLAmazon AuroraMySQL Cloud InstancesMySQL in KubernetesDeploying Percona XtraDB Cluster in Kubernetes
Load Balancing with Application DriversProxySQL Load BalancerInstalling and Configuring ProxySQLHAProxy Load BalancerInstalling and Configuring HAProxyMySQL Router
MySQL ShellInstalling MySQL ShellInstalling MySQL Shell on Ubuntu 20.04 Focal FossaInstalling MySQL Shell on CentOS 8Deploying a Sandbox InnoDB Cluster with MySQL ShellMySQL Shell UtilitiesFlame GraphsBuilding MySQL from SourceBuilding MySQL for Ubuntu Focal Fossa and ARM ProcessorsAnalyzing a MySQL Crash

Overview

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 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.