DBA Tutorial Kai Voigt Senior MySQL Instructor Sun Microsystems - - PowerPoint PPT Presentation

dba tutorial kai voigt senior mysql instructor sun
SMART_READER_LITE
LIVE PREVIEW

DBA Tutorial Kai Voigt Senior MySQL Instructor Sun Microsystems - - PowerPoint PPT Presentation

DBA Tutorial Kai Voigt Senior MySQL Instructor Sun Microsystems kai@sun.com Santa Clara, April 12, 2010 Donnerstag, 8. April 2010 Certification Details http://www.mysql.com/certification/ Registration at Conference Closed Book


slide-1
SLIDE 1

DBA Tutorial Kai Voigt Senior MySQL Instructor Sun Microsystems kai@sun.com Santa Clara, April 12, 2010

Donnerstag, 8. April 2010

slide-2
SLIDE 2

Certification Details

  • http://www.mysql.com/certification/
  • Registration at Conference
  • Closed Book Exams
  • Non-Disclosure and Logo Usage Agreements
  • 70 multiple-choice questions
  • 90 minutes

Donnerstag, 8. April 2010

slide-3
SLIDE 3

MySQL Certification Study Guide

  • http://www.mysql.com/certification/studyguides

Donnerstag, 8. April 2010

slide-4
SLIDE 4

New DBA 5.1 Hands On Exams

  • Online real life tasks
  • Need to pass 5 out of 5 mandatory tasks
  • Need to pass 5 out of 10 optional tasks

Donnerstag, 8. April 2010

slide-5
SLIDE 5

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-6
SLIDE 6

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-7
SLIDE 7

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-8
SLIDE 8

Client/Server Overview

  • OS independancy
  • C Client Library
  • Connectors

Donnerstag, 8. April 2010

slide-9
SLIDE 9

Communication Protocols

  • Windows Only: Named Pipes, Shared Memory
  • --skip-networking
  • Unix Sockets vs TCP/IP

Donnerstag, 8. April 2010

slide-10
SLIDE 10

SQL Parser & Storage Engines

  • Two Tiers
  • Storage Engines Independance

Donnerstag, 8. April 2010

slide-11
SLIDE 11

How MySQL uses Disk Space

  • Data Directory
  • InnoDB Table Space

Donnerstag, 8. April 2010

slide-12
SLIDE 12

How MySQL Uses Memory

  • One Connection = One Thread
  • Grant Table Buffer
  • Key Buffer Cache
  • Table Cache

Donnerstag, 8. April 2010

slide-13
SLIDE 13

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-14
SLIDE 14

Types of MySQL Distributions

  • tar & RPM
  • Binaries vs self-built

Donnerstag, 8. April 2010

slide-15
SLIDE 15

Starting and Stopping, Windows

  • Pre-initialized Data Directory
  • Multiple mysqld Binaries
  • --console Option
  • Running mysql command line client
  • Install mysqld as a service

Donnerstag, 8. April 2010

slide-16
SLIDE 16

Starting and Stopping, Unix

  • RPM: user, group, data directory
  • Data Directory: /var/lib/mysql
  • mysqld, mysqld_safe, mysql.server
  • How to stop a MySQL server
  • mysqld writes to stdout

Donnerstag, 8. April 2010

slide-17
SLIDE 17

Runtime MySQL Configuration

  • my.cnf / my.ini
  • Option File vs Command Line
  • General Startup Options

Donnerstag, 8. April 2010

slide-18
SLIDE 18

Log and Status Files

  • Problems with Logging
  • General Log
  • Slow Query Log
  • Queries not using indexes

Donnerstag, 8. April 2010

slide-19
SLIDE 19

Loading Time Zone Tables

  • mysql_tzinfo_to_sql script

Donnerstag, 8. April 2010

slide-20
SLIDE 20

Security-Related Configuration

  • Passwords
  • Filesystem Access

Donnerstag, 8. April 2010

slide-21
SLIDE 21

SQL Mode

  • Use
  • How to configure

Donnerstag, 8. April 2010

slide-22
SLIDE 22

Upgrading MySQL

  • Manual and Release Notes
  • Basic Procedure

Donnerstag, 8. April 2010

slide-23
SLIDE 23

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-24
SLIDE 24

Client Programs

  • mysql Command Line Client
  • mysqladmin
  • mysqlimport
  • mysqldump

Donnerstag, 8. April 2010

slide-25
SLIDE 25

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-26
SLIDE 26

Character Set Support

  • Performance Issues
  • Disk Usage, Disk I/O, Memory Usage
  • CHAR vs VARCHAR

Donnerstag, 8. April 2010

slide-27
SLIDE 27

Break

Donnerstag, 8. April 2010

slide-28
SLIDE 28

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-29
SLIDE 29

Locking Concepts

  • Read vs Write Locks
  • Explicit vs Implicit Locks
  • Advisory Locks
  • Table vs Row Level Locks
  • Deadlocks

Donnerstag, 8. April 2010

slide-30
SLIDE 30

Explicit Table Locking

  • LOCK TABLE
  • READ LOCAL
  • Releasing Table Locks
  • Locking multiple tables

Donnerstag, 8. April 2010

slide-31
SLIDE 31

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-32
SLIDE 32

MySQL Storage Engines

  • Things to consider
  • Default Engine
  • Determine the storage engine
  • Required Engines: MyISAM, Memory

Donnerstag, 8. April 2010

slide-33
SLIDE 33

The MyISAM Engine

  • MERGE, FULLTEXT features
  • LOW_PRIORITY, HIGH_PRIORITY
  • Out of Disk Space
  • No Deadlocks
  • Fixed Format
  • Compressed Format

Donnerstag, 8. April 2010

slide-34
SLIDE 34

The MERGE Engine

  • Table Locking
  • No Deadlocks

Donnerstag, 8. April 2010

slide-35
SLIDE 35

The InnoDB Engine

  • Tablespace
  • Multiversioning
  • Deadlocks
  • Auto-Recovery
  • Foreign Keys
  • Per-Table Tablespace
  • Transactions
  • SAVEPOINT
  • Implicit Commits
  • Isolation Levels

Donnerstag, 8. April 2010

slide-36
SLIDE 36

The MEMORY Engine

  • General Characteristics
  • BTREE vs HASH Indexes

Donnerstag, 8. April 2010

slide-37
SLIDE 37

The FEDERATED Engine

  • Concepts of FEDERATED Engine
  • How to define

Donnerstag, 8. April 2010

slide-38
SLIDE 38

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-39
SLIDE 39

SQL Statements

  • CHECK TABLE
  • REPAIR TABLE
  • ANALYZE TABLE
  • OPTIMIZE TABLE
  • Table Statistics

Donnerstag, 8. April 2010

slide-40
SLIDE 40

Client and Utility Programs

  • myisamchk
  • mysqlcheck

Donnerstag, 8. April 2010

slide-41
SLIDE 41

MyISAM Auto-Repair

  • --myisam-recover

Donnerstag, 8. April 2010

slide-42
SLIDE 42

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-43
SLIDE 43

INFORMATION_SCHEMA

  • List of Tables
  • Read-Only
  • INFORMATION_SCHEMA vs SHOW
  • Limitations

Donnerstag, 8. April 2010

slide-44
SLIDE 44

Exam Content DBA 1

  • MySQL Architecture (10%)
  • Starting, Stopping, and Configuring MySQL (20%)
  • Client Programs for DBA Work (5%)
  • Character Set Support (5%)
  • Locking (10%)
  • Storage Engines (20%)
  • Data (Table) Maintenance (10%)
  • The INFORMATION_SCHEMA Database (5%)
  • Data Backup and Recovery Methods (15%)

Donnerstag, 8. April 2010

slide-45
SLIDE 45

Introduction

  • Recovery = Backup + Binary Log
  • Principles of MySQL Backup
  • Binary vs Text Backup

Donnerstag, 8. April 2010

slide-46
SLIDE 46

Making Binary Backups

  • MyISAM Tables
  • InnoDB Tables
  • Binary Portability

Donnerstag, 8. April 2010

slide-47
SLIDE 47

Making Text Backups

  • SELECT INTO OUTFILE
  • mysqldump

Donnerstag, 8. April 2010

slide-48
SLIDE 48

Replication as an Aid to Backup

  • Hot Backup
  • How to Backup a Slave

Donnerstag, 8. April 2010

slide-49
SLIDE 49

Data Recovery

  • Load mysqldump File
  • Binary Log
  • mysqlbinlog
  • --start-position
  • --stop-position

Donnerstag, 8. April 2010

slide-50
SLIDE 50

End of Part 1

Donnerstag, 8. April 2010

slide-51
SLIDE 51

DBA Tutorial

Kai Voigt Senior MySQL Instructor Sun Microsystems kai@sun.com Santa Clara, April 20, 2009

Donnerstag, 8. April 2010

slide-52
SLIDE 52

Certification Details

  • http://www.mysql.com/certification/
  • Registration at Conference
  • Closed Book Exams
  • Non-Disclosure and Logo Usage Agreements
  • 70 multiple-choice questions
  • 90 minutes

Donnerstag, 8. April 2010

slide-53
SLIDE 53

MySQL Certification Study Guide

  • http://www.mysql.com/certification/studyguides

Donnerstag, 8. April 2010

slide-54
SLIDE 54

New DBA 5.1 Hands On Exams

  • Online real life tasks
  • Need to pass 5 out of 5 mandatory tasks
  • Need to pass 5 out of 10 optional tasks

Donnerstag, 8. April 2010

slide-55
SLIDE 55

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-56
SLIDE 56

Stored Procedures

  • DEFINER/INVOKER
  • Check incoming Data
  • Restrict incoming Data
  • Reduce Amount of Data Exchange

Donnerstag, 8. April 2010

slide-57
SLIDE 57

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-58
SLIDE 58

User Account Management

  • Username + Hostname
  • Wildcards
  • Grant Tables in mysql Database
  • Global, Database, Table, Column, Routine Level
  • ALL PRIVILEGES
  • USAGE Privilege
  • Grants Cache
  • GRANT/REVOKE
  • CREATE/DROP USER
  • IDENTIFIED BY
  • WITH GRANT OPTION
  • SHOW GRANTS
  • SET PASSWORD

Donnerstag, 8. April 2010

slide-59
SLIDE 59

Client Access Control

  • Two Stages
  • Wildcards
  • Hostnames, then Usernames

Donnerstag, 8. April 2010

slide-60
SLIDE 60

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-61
SLIDE 61

Filesystem Security

  • Don‘t run mysql as root
  • Log Files should be kept secret

Donnerstag, 8. April 2010

slide-62
SLIDE 62

Network Security

  • MySQL user names != OS user names
  • mysql_secure_installation Script
  • mysql Database
  • FILE Privilege
  • SHOW PROCESSLIST
  • SUPER Privilege
  • --skip-networking
  • --bind-address

Donnerstag, 8. April 2010

slide-63
SLIDE 63

FEDERATED Table Security

  • Connect String Storage
  • Prevent Users from seeing password

Donnerstag, 8. April 2010

slide-64
SLIDE 64

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-65
SLIDE 65

Upgrade-Related Security Issues

  • Upgrading Privilege Tables
  • Strict SQL Mode
  • Error for Division by Zero
  • Zero Dates, Null Dates

Donnerstag, 8. April 2010

slide-66
SLIDE 66

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-67
SLIDE 67

Identifying Candidates for Analysis

  • Binary Log
  • Slow Query Log
  • SHOW PROCESSLIST

Donnerstag, 8. April 2010

slide-68
SLIDE 68

EXPLAIN

  • columns: possible_keys, key, ref, id
  • Joins: Multiply row numbers
  • Order of Tables
  • type: eq_ref, range, const
  • Multi-Column Indexes
  • STRAIGHT JOIN

Donnerstag, 8. April 2010

slide-69
SLIDE 69

MyISAM Index Caching

  • Global vs Table Level Key Buffer
  • SET, CACHE INDEX, LOAD INDEX INTO

Donnerstag, 8. April 2010

slide-70
SLIDE 70

Break

Donnerstag, 8. April 2010

slide-71
SLIDE 71

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-72
SLIDE 72

General Table Optimization

  • PROCEDURE ANALYSE()
  • Normalization vs Denormalization

Donnerstag, 8. April 2010

slide-73
SLIDE 73

MyISAM-Specific Optimizations

  • Compressed Tables
  • Dynamic vs Fixed Row Format
  • Split Table into Dynamic and Fixed Parts

Donnerstag, 8. April 2010

slide-74
SLIDE 74

InnoDB-Specific Optimizations

  • Size of Primary Keys
  • SELECT COUNT(*)
  • Group Statements into Transactions
  • OPTIMIZE TABLE

Donnerstag, 8. April 2010

slide-75
SLIDE 75

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-76
SLIDE 76

Tuning Memory Parameters

  • Table Cache
  • Small Default Values
  • MyISAM Key Buffer
  • InnoDB Buffer Pool
  • Disabling InnoDB
  • read_buffer_size
  • Sort Buffer
  • Join Buffer
  • max_allowed_packet

Donnerstag, 8. April 2010

slide-77
SLIDE 77

Using the Query Cache

  • Characteristics
  • query_cache_size
  • query_cache_limit
  • SHOW STATUS LIKE „qcache_%“

Donnerstag, 8. April 2010

slide-78
SLIDE 78

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-79
SLIDE 79

Diagnostics

  • Error Log
  • long_query_time
  • Slow Query Log
  • mysqldumpslow

Donnerstag, 8. April 2010

slide-80
SLIDE 80

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-81
SLIDE 81

Memory and Disks

  • Memory is important
  • Disk Seek Time is important
  • RAID
  • Seperate Log Files and Data Directory
  • Using Symlinks
  • Filesystem Selection

Donnerstag, 8. April 2010

slide-82
SLIDE 82

Network and OS

  • Latency and Throughput
  • Process Limits
  • max_connections

Donnerstag, 8. April 2010

slide-83
SLIDE 83

Exam Content DBA 2

  • Stored Routines (5%)
  • User Management (20%)
  • Securing the Server (10%)
  • Upgrade-Related Security Issues (5%)
  • Optimizing Queries (15%)
  • Optimizing Schemas (15%)
  • Optimizing the Server (10%)
  • Interpreting Error Messages (5%)
  • Optimizing the Environment (5%)
  • Scaling MySQL (10%)

Donnerstag, 8. April 2010

slide-84
SLIDE 84

Using Multiple Servers

  • Requirements
  • Shared InnoDB tables

Donnerstag, 8. April 2010

slide-85
SLIDE 85

Replication

  • General Features
  • Master-Slave
  • MASTER_LOG_FILE, MASTER_LOG_POS
  • master.info
  • Relay Log
  • Binary Log
  • IO Thread, SQL Thread
  • Unique Server IDs

Donnerstag, 8. April 2010

slide-86
SLIDE 86

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any

  • contract. It is not a commitment to deliver any

material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features

  • r functionality described for Oracle’s products

remains at the sole discretion of Oracle.

Donnerstag, 8. April 2010

slide-87
SLIDE 87

Good Luck!

Donnerstag, 8. April 2010