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 15. Load Balancing MySQL

There are different ways to connect to MySQL. For example, to perform a write test, a connection is created, the statement is executed, and then the connection is closed. To avoid the cost of opening a connection every time it is needed, the concept of the connection pool was developed. Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread of the application.

Extending the concept of high availability discussed in Chapter 13 to connections in order to improve a production system’s resilience, it is possible to use load balancers to connect to a database cluster. With load balancing and MySQL high availability, it is possible to keep the application running without interruption (or with only minor downtime). Basically, if the source server or one of the nodes of the database cluster fails, the client just needs to connect to another database node and it can continue to serve requests.

Load balancers were built to provide transparency for clients when connecting to MySQL infrastructure. In this way, the application does not need to be aware of the MySQL topology; whether you’re using a classic replication, Group Replication, or Galera Cluster does not matter. The load balancer will provide an online node where it will be possible to read and write queries. Having a robust MySQL architecture and a proper load balancer in place can help DBAs avoid sleepless nights.