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 10. Backups and Recovery

The most important task for any DBA is backing up the data. Correct and tested backup and recovery procedures can save a company and thus a job. Mistakes happen, disasters happen, and errors happen. MySQL is a robust piece of software, but it’s not completely free of bugs or crashes. Thus, it is crucial to understand why and how to perform backups.

Apart from preserving database contents, most backup methods can also be used for another important purpose: copying the contents of the database between separate systems. Though probably not as important as saving the day when corruption happens, this copying is a routine operation for the vast majority of database operators. Developers will often need to use downstream environments, which should be similar to production. QA staff may need a volatile environment with a lifespan of an hour. Analytics may be run on a dedicated host. Some of these tasks can be solved by replication, but any replica starts from a restored backup.

This chapter first briefly reviews two major types of backups and discusses their fundamental properties. It then looks at some of the tools available in the MySQL world for the purpose of backup and recovery. Covering each and every tool and their parameters would be beyond the scope of this book, but by the end of the chapter you should know your way around backing up and recovering MySQL data. We’ll also explore some basic data transfer scenarios. Finally, the chapter outlines ...