Mysql Database Administration

Day 1

1 Introduction to MySQL

• Objectives
• Course Goals
• Introductions
• Classroom Environment
• MySQL Powers the Web
• MySQL Market Share: DB-Engines 2019
• MySQL Enterprise Edition
• Oracle Premier Support for MySQL
• MySQL and Oracle Integration
• MySQL Websites 1-13
• Community Resources 1-14

2 Installing and Upgrading MySQL

• Objectives 2-2
• Topics 2-3
• Installation Sequence 2-4
• Installing MySQL from Downloaded Packages 2-5
• MySQL RPM Installation Files for Linux 2-6
• MySQL RPM Installation Process 2-7
• Linux Distribution–Specific Repositories 2-9
• Installing MySQL by Using a Package Manager 2-10
• Adding a Yum Repository 2-11
• Configuring Yum Repository Versions 2-12
• Installing MySQL on Windows 2-16
• Installing on Windows: MySQL Installer 2-17
• Installing on Windows: Selecting Products and Features 2-18
• Installing on Windows: Product Configuration 2-19
• Installing MySQL as a Windows Service 2-20
• Linux MySQL Server Installation Directories 2-29
• Windows MySQL Server Installation Directory 2-30
• MySQL Programs 2-31
• mysqld: MySQL Server Process 2-32
• nstallation Programs 2-33
• Utility Programs 2-34
• mysql_config_editor 2-35
• .mylogin.cnf Format 2-36
• Login Paths 2-37
• Command-Line Client Programs 2-38
• Launching Command-Line Client Programs 2-39
• Configuring Mandatory Access Control 2-41
• SELinux Example 2-42
• Changing the root Password 2-44
• Using mysqladmin to Change the root Password 2-45
• Starting and Stopping MySQL 2-48
• Stopping MySQL with mysqladmin 2-49
• MySQL Service Files 2-50
• Starting and Stopping MySQL on Windows 2-51
• Starting and Stopping MySQL on Windows: MySQL Notifier 2-52
• Upgrading MySQL 2-54
• Reading Release Notes 2-55
• MySQL Shell Upgrade Checker Utility 2-56
• Using In-Place Upgrade Method 2-57
• Using Logical Upgrade Method 2-58
• mysql_upgrade 2-59
• Summary 2-60
• Practices 2-61

3 Understanding MySQL Architecture

• Objectives 3-2
• Topics 3-3
• Architecture 3-4
• Client/Server Connectivity 3-5
• MySQL Server Process 3-6
• Terminology 3-7
• Server Process 3-8
• Connection Layer 3-10
• Connection Protocols 3-11
• Local and Remote Connection Protocol: TCP/IP 3-12
• Local Connection Protocol in Linux: Socket 3-13
• MySQL and localhost 3-14
• Local Connection Protocols in Windows: Shared Memory and Named Pipes 3-15
• SSL by Default 3-16
• Connection Threads 3-17
• SQL Layer 3-20
• SQL Layer Components 3-21
• SQL Statement Processing 3-22
• Storage Layer 3-24
• Storage Engines Provided with MySQL 3-25
• Storage Engines: Function 3-26
• SQL and Storage Layer Interactions 3-27
• Features Dependent on Storage Engine 3-28
• InnoDB Storage Engine 3-30
• MyISAM Storage Engine 3-31
• MEMORY Storage Engine 3-32
• ARCHIVE Storage Engine 3-33
• NDBCluster Storage Engine 3-34
• BLACKHOLE Storage Engine 3-35
• Storage Engines Feature Summary 3-36
• How MySQL Uses Disk Space 3-38
• Data Directory 3-39
• What Is a Data Dictionary? 3-41
• Types of Metadata 3-42
• Data Dictionary in Earlier Versions of MySQL 3-43
• Transactional Data Dictionary in MySQL 8 3-44
• Transactional Data Dictionary: Features 3-45
• Serialization of the Data Dictionary 3-46
• Dictionary Object Cache 3-47
• InnoDB Tablespaces 3-49
• InnoDB System Tablespace 3-50
• File-per-Table Tablespaces 3-51
• General Tablespaces 3-52
• Choosing Between File-Per-Table and General Tablespaces 3-53
• Locating Tablespaces Outside the Data Directory 3-54
• Temporary Tablespaces 3-55
• Redo Logs 3-57
• Undo Logs 3-59
• Undo Tablespaces 3-61
• Temporary Table Undo Log 3-62
• How MySQL Uses Memory 3-65
• Global Memory 3-67
• Session Memory 3-68
• Log Files and Buffers 3-69
• InnoDB Buffer Pool 3-70
• Configuring the Buffer Pool 3-71
• MySQL Plugin Interface 3-73
• MySQL Component Interface 3-74
• Summary 3-75
• Practices 3-76

 

Day 2

4 Configuring MySQL

• Objectives 4-2
• Topics 4-3
• MySQL Configuration Options 4-4
• Deciding When to Use Options 4-5
• Displaying Configured Server Options 4-6
• Option Naming Convention 4-7
• Using Command-Line Options 4-8
• Reasons to Use Option Files 4-10
• Option File Locations 4-11
• Option Files That Each Program Reads 4-12
• Standard Option Files 4-13
• Option File Groups 4-14
• Option Groups That Each Program Reads 4-15
• Option Group Names 4-16
• Client Options: Examples 4-17
• Writing Option Files 4-18
• Option File Contents: Example 4-19
• Option Precedence in Option Files 4-20
• Loading or Ignoring Option Files from the Command Line 4-21
• Loading Option Files with Directives 4-22
• Displaying Options from Option Files 4-23
• Server System Variables 4-26
• System Variable Scope: GLOBAL and SESSION 4-27
• Dynamic System Variables 4-28
• Changing Variable Values 4-29
• Persisting Global Variables 4-30
• Displaying System Variables 4-31
• Viewing Variables with Performance Schema 4-32
• Launching Multiple Servers on the Same Host 4-35
• Settings That Must Be Unique 4-36
• mysqld_multi 4-37
• mysqld_multi: Example Configuration File 4-38
• systemd: Multiple MySQL Servers 4-39
• Summary 4-41
• Practices 4-42

5 Monitoring MySQL

• Objectives 5-2
• Topics 5-3
• Monitoring MySQL with Log Files 5-4
• Log File Characteristics 5-5
• Error Log 5-6
• Binary Log 5-7
• General Query Log 5-8
• General Query Log: Example 5-9
• Slow Query Log 5-10
• Slow Query Log: Logging Administrative and Replicated Statements 5-11
• Filtering Slow Query Log Events 5-12
• Slow Query Log: Example 5-13
• Viewing the Slow Query Log with mysqldumpslow 5-14
• mysqldumpslow: Example 5-15
• Specifying TABLE or FILE Log Output 5-16
• Log File Rotation 5-17
• Flushing Logs 5-18
• Status Variables 5-21
• Displaying Status Information 5-22
• Monitoring Status with mysqladmin 5-23
• Performance Schema 5-25
• Performance Schema Table Groups 5-26
• Configuring Performance Schema 5-27
• Performance Schema Setup Tables 5-28
• Performance Schema Instruments 5-29
• Top-Level Instrument Components 5-30
• Accessing Performance Schema Metrics 5-31
• The sys Schema 5-32
• Using the sys Schema Example 1 5-33
• Using the sys Schema: Example 2 5-36
• MySQL Enterprise Audit 5-38
• Installing MySQL Enterprise Audit 5-39
• Audit Log File Configuration 5-40
• Audit Log Filtering 5-41
• Audit Log Filter Definitions 5-42
• Audit Log File Format 5-43
• Audit Log File: New-Style XML Format 5-44
• Audit Log File: JSON Format 5-45
• Audit Record Values 5-46
• MySQL Enterprise Monitor 5-49
• Installing MySQL Enterprise Monitor 5-50
• Installing the Service Manager 5-51
• Post-Installation Configuration 5-53
• Installing Agents 5-54
• SHOW PROCESSLIST 5-60
• Performance Schema Threads Table 5-61
• Killing Processes 5-62
• Limiting User Activity 5-63
• Setting Resource Limits 5-64
• Resetting Limits to Default Values 5-65
• Summary 5-66
• Practices 5-67

6 Managing MySQL Users

• Objectives 6-2
• Topics 6-3
• Importance of User Management 6-4
• Authentication and Authorization 6-5
• User Connection and Query Process 6-6
• Viewing User Account Settings 6-7
• Pluggable Authentication 6-8
• Local Connection 6-9
• Remote Connection 6-10
• Account Names 6-12
• Host Name Patterns 6-13
• Creating a User Account 6-14
• Roles 6-15
• Creating a Role 6-16
• Manipulating User Accounts and Roles 6-17
• Setting the Account Password 6-19
• Expiring Passwords Manually 6-21
• Configuring Password Expiration 6-22
• Changing Expired Passwords 6-23
• Pluggable Authentication 6-26
• Cleartext Client-Side Authentication Plugin 6-27
• Loadable Authentication Plugins 6-28
• Enterprise Authentication Plugins 6-29
• PAM Authentication Plugin 6-30
• Configuring the PAM Authentication Plugin 6-31
• Creating Users that Authenticate with PAM 6-32
• Creating PAM Proxied Users 6-33
• Logging In with PAM Accounts 6-34
• Authorization 6-36
• Determining Appropriate User Privileges 6-37
• Privilege Scope 6-38
• Granting Administrative Privileges 6-39
• Dynamic Privileges 6-40
• Special Privileges 6-41
• GRANT Statement 6-42
• Granting Permissions on Columns 6-43
• Granting Roles to Users 6-44
• Displaying GRANT Privileges 6-45
• Displaying Privileges for Another User 6-46
• Displaying Privileges for a Role 6-47
• Revoking Account Privileges 6-48
• REVOKE: Examples 6-49
• Partial Revoke 6-51
• User Privilege Restrictions 6-52
• Using Role Privileges 6-55
• Activating Roles at Server Level 6-56
• Activating Roles at User Level 6-57
• Activating Roles at Session Level 6-58
• Mandatory Roles 6-59
• Grant Tables 6-61
• Grant Table Contents 6-62
• Use of Grant Tables 6-63
• Effecting Privilege Changes 6-64
• Summary 6-65

 

Day 3

7 Securing MySQL

• Objectives 7-2
• Topics 7-3
• Security Risks 7-4
• MySQL Installation Security Risks 7-5
• Securing MySQL from Public Networks 7-7
• Preventing Network Security Risks 7-8
• Securing MySQL in Private Networks 7-9
• Secure Connections 7-11
• Secure Connection: Overview 7-12
• Generating a Digital Certificate 7-13
• Server Security Defaults 7-14
• SSL Is Enabled by Default with MySQL Clients 7-15
• Disabling SSL on MySQL Server 7-16
• Setting Client Options for Secure Connections 7-17
• Client –ssl-mode Option: Example 7-18
• Setting the Permitted Versions for SSL/TLS for the Server 7-19
• Setting the Permitted Versions for SSL/TLS for the Client 7-20
• Setting the Cipher to Use for Secure Connections 7-21
• Global System Variable and Session Status Variables for Ciphers 7-22
• Cipher System and Status Variables: Example 1 7-23
• Cipher System and Status Variables: Example 2 7-24
• Setting Client SSL/TLS Options by User Account 7-25
• Generating a Digital Certificate 7-26
• SSL Server Variables for Digital Certificates 7-27
• SSL Client Options for Digital Certificates 7-28
• Securing a Remote Connection to MySQL 7-29
• Preventing MySQL Password Security Risks 7-32
• How Attackers Derive Passwords 7-33
• Password Validation Component 7-34
• Validate Password Component Variables 7-35
• Changing the Default Password Validation Variables 7-36
• Other Password Considerations 7-37
• Locking an Account 7-38
• Pluggable Authentication 7-39
• Preventing Application Password Security Risks 7-40
• Connection-Control Plugin 7-41
• Installing the Connection-Control Plugin 7-42
• Monitoring Connection Failures 7-43
• Limiting Operating System Usage 7-47
• Limiting Operating System Accounts 7-48
• Operating System Security 7-49
• File System Security 7-50
• Preventing File System Security Risks 7-51
• Key Management Functions 7-55
• Encrypting InnoDB Tablespaces 7-56
• Encrypting InnoDB Redo Logs and Undo Logs 7-57
• InnoDB Encryption Keys 7-58
• Encrypting Binary Log and Relay Log 7-59
• Binary Log Encryption Keys 7-60
• Protecting Your Data from SQL Injection Attacks 7-62
• SQL Injection: Example 7-63
• Detecting Potential SQL Injection Attack Vectors 7-64
• Preventing SQL Injection Attacks 7-65
• Practices 7-79

8 Maintaining a Stable System

• Objectives 8-2
• Topics 8-3
• Stable Systems 8-4
• Measuring What You Manage 8-5
• Establishing a Baseline 8-6
• Application Profiling 8-7
• Asking “What Could Go Wrong?” 8-9
• Components in a MySQL Server Installation 8-10
• Server Hardware 8-11
• Problems with Hardware 8-12
• Virtualized Environment 8-13
• Operating System 8-14
• Coexistent Applications 8-15
• Network Failures 8-16
• Application Failures 8-17
• Capacity Planning 8-20
• Monitoring Table Size 8-21
• Calculating Logical Size: Data and Indexes 8-22
• Calculating Physical Size: Querying Information Schema 8-23
• Calculating Physical Size: Reading the File System 8-24
• Scalability 8-25
• Scaling Up and Scaling Out 8-26
• Establishing the Nature of a Problem 8-29
• Identifying the Problem 8-30
• Common Problems 8-31
• Resolving Problems 8-32
• Identifying the Causes of Server Slowdowns 8-34
• Investigating Slowdowns 8-35
• How MySQL Locks Rows 8-38
• Identifying Lock Contention 8-39
• InnoDB Table Locks 8-40
• InnoDB Row Locks 8-41
• Troubleshooting Locks by Using SHOW PROCESSLIST 8-42
• Monitoring Data Locks with Information Schema and Performance Schema 8-43
• Information Schema INNODB_TRX View 8-44
• Performance Schema data_locks Table 8-45
• Performance Schema data_lock_waits Table 8-47
• innodb_lock_waits View 8-48
• innodb_lock_waits: Example Query 8-49
• Performance Schema metadata_locks Table 8-50
• schema_table_lock_waits View 8-51
• InnoDB Recovery 8-53
• Using –innodb_force_recovery 8-54
• Summary 8-55
• Practices 8-56

 

Day 4

9 Optimizing Query Performance

• Objectives 9-2
• Topics 9-3
• Identifying Slow Queries 9-4
• Choosing What to Optimize 9-5
• Using EXPLAIN to See Optimizer’s Choice of Index 9-7
• EXPLAIN: Example 9-8
• EXPLAIN Output 9-9
• Common type Values 9-11
• Displaying Query Rewriting and Optimization Actions 9-12
• EXPLAIN Example: Table Scan 9-13
• EXPLAIN Example: Primary Key 9-14
• EXPLAIN Example: Non-unique Index 9-15
• EXPLAIN and Complex Queries 9-16
• EXPLAIN Example: Simple Join 9-17
• Explanation of Simple Join Output 9-18
• EXPLAIN FORMAT 9-19
• EXPLAIN FORMAT: JSON Example 9-20
• EXPLAIN ANALYZE 9-21
• Hash Join Optimization 9-22
• Index Types 9-24
• Creating Indexes to Improve Query Performance 9-25
• Creating Indexes on Existing Tables 9-26
• Dropping Indexes on Existing Tables 9-27
• Displaying Indexes Metadata 9-28
• Invisible Indexes 9-29
• Maintaining InnoDB Index Statistics 9-31
• Automatically Updating Index Statistics 9-32
• Using ANALYZE TABLE 9-33
• Rebuilding Indexes 9-34
• mysqlcheck Client Program 9-35
• Summary 9-45
• Practices 9-46

10 Choosing a Backup Strategy

• Objectives 10-2
• Topics 10-3
• Reasons to Back Up 10-4
• Backup Types 10-5
• Hot Backups 10-6
• Cold Backups 10-7
• Warm Backups 10-8
• Backup Techniques 10-11
• Logical Backups 10-12
• Logical Backup Conditions 10-14
• Logical Backup Performance 10-15
• Physical Backups 10-16
• Physical Backup Files 10-17
• Physical Backup Conditions 10-18
• Online Disk Copies 10-19
• Snapshot-Based Backups 10-20
• Performing a Snapshot 10-21
• Replication-Based Backups 10-22
• Binary Log Backups 10-23
• Binary Logging and Incremental Backups 10-24
• Comparing Backup Methods 10-27
• Deciding a Backup Strategy 10-28
• Backup Strategy: Decision Chart 10-29
• More Complex Strategies 10-30
• Summary 10-31
• Practices 10-32

11 Performing Backups

• Objectives 11-2
• Topics 11-3
• Backup Tools: Overview 11-4
• MySQL Enterprise Backup 11-6
• Percona Xtrabackup
• Full Backups 11-10
• Single-File Backups 11-11
• Backup Process 11-12
• Incremental Backups 11-13
• Differential Backups 11-14
• Validate Operations 11-15
• Restore Operations 11-16
• Restore Commands 11-17
• Restoring Incremental Backups 11-18
• Update Operations 11-19
• Single-File Operations 11-20
• Basic Privileges Required for MySQL Enterprise Backup 11-21
• Granting Required Privileges 11-22
• mysqldump and mysqlpump 11-25
• mysqldump 11-26
• Ensuring Data Consistency with mysqldump 11-27
• mysqldump Options for Creating Objects 11-28
• mysqldump Options for Dropping Objects 11-29
• mysqldump General Options 11-30
• Restoring mysqldump Backups 11-31
• Using mysqlimport 11-32
• Privileges Required for mysqldump 11-33
• Privileges Required for Reloading Dump Files 11-34
• mysqlpump 11-35
• Specifying Objects to Back Up with mysqlpump 11-36
• Parallel Processing with mysqlpump 11-37
• Physical InnoDB Backups: Overview 11-40
• Portability of Physical Backups 11-41
• Physical InnoDB Backup Procedure 11-42
• Recovering from Physical InnoDB Backups 11-43
• Using Transportable Tablespaces for Backup 11-44
• Transportable Tablespaces: Copying a Table to Another Instance 11-45
• Physical MyISAM and ARCHIVE Backups 11-46
• Physical MyISAM and ARCHIVE Backup Procedure 11-47
• Recovering from Physical MyISAM or Archive Backups 11-48
• Replication as an Aid to Backup 11-53
• Backing Up from a Replication Slave 11-54
• Processing Binary Log Contents 11-57
• Selective Binary Log Processing 11-58
• Point-in-Time Recovery 11-59
• Summary 11-62
• Practices 11-63

Day 5:

12 Configuring a Replication Topology

• Objectives 12-2
• Topics 12-3
• MySQL Replication 12-4
• Replication Masters and Slaves 12-5
• Relay Slaves 12-6
• Complex Topologies 12-7
• Replication Conflicts 12-10
• Replication Conflicts: Example Scenario with No Conflict 12-11
• Replication Conflicts: Example Scenario with Conflict 12-12
• Replication Use Cases 12-14
• Replication for Geographic Data Distribution 12-17
• Replicating with the BLACKHOLE Storage Engine 12-18
• Replication for High Availability 12-19
• Configuring Replication 12-21
• Configuring Replication Masters 12-22
• Configuring Replication Slaves 12-23
• CHANGE MASTER TO 12-24
• Finding Log Coordinates 12-25
• Global Transaction Identifiers (GTIDs) 12-26
• Identifying the Source Server 12-27
• Logging Transactions 12-28
• Replication with GTIDs 12-29
• Replication Filtering Rules 12-30
• Applying Filtering Rules 12-31
• Binary Log Formats 12-34
• Row-Based Binary Logging 12-35
• Statement-Based Binary Logging 12-36
• Mixed Format Binary Logging 12-37
• Replication Logs 12-38
• Crash-Safe Replication 12-39
• Asynchronous Replication 12-41
• Semisynchronous Replication 12-42
• Advantages and Disadvantages of Semisynchronous Replication 12-43
• Enabling Semisynchronous Replication 12-44
• Multi-Source Replication 12-45
• Configuring Multi-Source Replication for a GTID-Based Master 12-46
• Configuring Multi-Source Replication for a Binary Log Based Master 12-47
• Controlling Slaves in a Multi-Source Replication Topology 12-48
• Summary 12-57
• Practices 12-58

13 Administering a Replication Topology

• Objectives 13-2
• Topics 13-3
• Failover with Log Coordinates 13-4
• Potential Problems When Executing a Failover with Log Coordinates 13-5
• Avoiding Problems When Executing a Failover with Log Coordinates 13-6
• Failover with GTIDs 13-7
• Topics 13-8
• Replication Threads 13-9
• The Master’s Binlog Dump Thread 13-10
• Single-Threaded Slaves 13-11
• Multithreaded Slaves 13-12
• Controlling Slave Threads 13-13
• Resetting the Slave 13-14
• Quiz 13-15
• Topics 13-16
• Monitoring Replication 13-17
• Slave Thread Status 13-18
• Master Log Coordinates 13-19
• Relay Log Coordinates 13-20
• Replication Slave I/O Thread States 13-21
• Replication Slave SQL Thread States 13-24
• Monitoring Replication by Using Performance Schema 13-26
• Replication Tables in Performance Schema 13-27
• MySQL Enterprise Monitor Replication Dashboard 13-28
• Topics 13-29
• Troubleshooting MySQL Replication 13-30
• Examining the Error Log 13-32
• SHOW SLAVE STATUS Error Details 13-34
• Checking I/O Thread States 13-35
• Monitoring Multi-Source Replication 13-36
• Summary 13-37
• Practices 13-38

14 Achieving High Availability with MySQL InnoDB Cluster

• Objectives 14-2
• Topics 14-3
• What Is MySQL InnoDB Cluster? 14-4
• Architecture 14-5
• MySQL Group Replication Plugin 14-6
• How Group Replication Works 14-7
• Single-Primary Mode 14-8
• Multi-Primary Mode 14-9
• Conflict Resolution 14-10
• Consensus and Quorum 14-11
• Use Cases 14-12
• Group Replication: Requirements and Limitations 14-13
• MySQL Shell (mysqlsh) 14-16
• Using MySQL Shell to Execute a Script 14-17
• MySQL Router (mysqlrouter) 14-18
• Deployment Scenarios 14-20
• Deploying Sandbox Instances and Creating the Cluster 14-21
• Production Deployment 14-22
• Distributed Recovery 14-23
• Connecting Clients to the Cluster 14-24
• Managing Sandbox Instances 14-27
• Checking the Status of a Cluster 14-28
• Viewing the Structure of a Cluster 14-29
• Checking the State of an Instance 14-30
• Updating a Cluster Metadata 14-31
• Removing Instances from the Cluster 14-32
• Rejoining an Instance to the Cluster 14-33
• Restoring Quorum Loss 14-34
• Recovering the Cluster from a Major Outage 14-35
• Dissolving a Cluster 14-36
• Disabling super_read_only 14-37
• Customizing a MySQL InnoDB Cluster 14-38
• Customizing an Instance 14-39
• Configuring Secure Connection in a Cluster 14-40
• Creating a Server Whitelist 14-41
• Summary 14-42
• Practices 14-43

15 Conclusion

• Course Goals 15-2
• Introduction To Mysql on Azure Cloud
• Migration from Local Instance to Azure Mysql
• MySQL Websites 15-5
• Your Evaluation 15-6
• Thank You 15-7
• Q&A Session 15-8