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

Content preview from Learning MySQL, 2nd Edition

Chapter 1. Installing MySQL

Let’s begin our learning path by installing MySQL and accessing it for the first time.

Note that we do not rely on a single version of MySQL for this book. Instead, we have drawn on our collective knowledge of MySQL in the real world. The book’s core is focused on Linux operating systems (mostly Ubuntu/Debian and CentOS/RHEL or its derivatives) and on MySQL 5.7 and MySQL 8.0, because those are what we consider the “current” versions capable of production workloads. The MySQL 5.7 and 8.0 series are still under development, which means that newer versions with bug fixes and new features will continue to be released.

With MySQL becoming the most popular open source database (Oracle, which ranks first, is not open source), the demand for having a fast installation process has increased. You can think of installing MySQL from scratch as similar to baking a cake: the source code is the recipe. But even with the source code available, the recipe for building software is not easy to follow. It takes time to compile, and usually, it is necessary to install additional development libraries that expose production environments to risk. Say you want a chocolate cake; even if you have the instructions for how to make it yourself, you may not want to mess up your kitchen, or you may not have the time to bake it, so you go to a bakery and buy one instead. For MySQL, when you want it ready to use without the effort involved in compiling it, you can use the distribution ...