Advanced MySQL DBA Workshop - draft

With a partner we are planning and Advanced MySQL DBA workshop. As the name says it should be a workshop. Its planned duration is 2 days. So within 2 days we can cover 4 to 8 topics more deeply.

Requirements: VirtualBox, VMware, own Laptop?

Possible exercises during the workshop

• Set-up a Master-Master replication with 2 Slaves
• Load balance on master with MySQL Proxy and on Slaves with LVS.
• Design a little schema and load with data from foodmart
• Do a backup with XtraDB and LVM
• Do a PITR and and InnoDB crash recovery (from some samples)
• Find some discrepancies between master/slave
• Run some synthetic benchmark and monitor

Planned contents:

Contents

Introduction
  Admin
  Who we are?
  Tasks of a DBA
  Discussion for focus

Planning and evaluating
  Collecting information
    Business plan, amount of users, expected traffic,
    peak and average traffic, requirements, amount of data,
    in what time range
    Information Life Cycle Management
  Plan architecture
    DB vs. NO-SQL vs. no database
    Storage Engines
      MyISAM/Aria
      InnoDB/XtraDB
      MySQL Cluster (NDB)
      Spider SE / InfiniDB / Infobright / ...
    Architectures
      Scale-out
      High availability (HA)
  Evaluate OS and Hardware
    Operating System
    Server (CPU, RAM, Network)
      single-thread performance
    I/O systems
      RAID
      SAN
    Discussion about your personal evaluation / experience
  Implement logical model into physical modelling
    UML to ER
      MySQL-Workbench
    Normalization vs de-normalization
    Schema optimizing
    Database standards
      Processes
      Naming conventions

Maintenance and Operations
  Installing the Software
    The database
      Package types
      Compile your self
      Multi instance set-up's (myenv?)
    HA / failover components
      MySQL - Proxy
      LVS Load Balancer
      Heartbeat/DRBD/Corosync ???
  Upgrade / downgrade
    Dump
    Binary upgrade
    Replication
  Configure and reconfigure the database
    Changeable and not changeable parameters
    Change and impact: how to measure
  Managing the database storage structure
    InnoDB
      File per table
      Log files
      The new barracuda file format
    Log files
      Binary Logs
      Other logs
  Interacting with the software vendor
    Report a bug with MySQL
    Report a bug with other providers
    Open a support case with MySQL
  Migration from/to MySQL
    Migration tool kit
    mysqldump
    JDBC/ODBC
    Stored Programs
  Load and unload data (ETL)
    Tools?
    SELECT INTO OUTFILE
    LOAD DATA INFILE
    INSERT vs multi row INSERT vs. LOAD DATA INFILE
  Operating a MySQL Database
    Automate Everything
    Backup/Restore (see further down)
    Maintenance task
    Monitoring (see further down)

Data protection
  Backup/restore/recovery
    Point-in-Time-Recovery (PITR)
    LVM snapshots
    InnoDB crashes
      Recovery with the Percona recovery tools?
    Xtrabackup
  Maintaining database changes from development to test to production
    repository (VCS)
    mysqldump --nodata
    FromDual Backup/Restore/Development/Production cycle
  Managing users and security
    Role concepts
    Scripts to monitor users/security
  Data Integrity
    Master/Slave - find discrepancies with the Percona tools

Monitoring
  Health / Availability
    Key indicators
    Parse the error log
    Some scripts which assist you in monitoring
  Performance
    vmstat / iostat / top
    mytop / innotop
    MySQL Enterprise Monitoring
    FromDual MySQL performance monitor
  Load simulation/generation
    Apache J/Meter

Performance Management and Tuning
  First measure, then act, then measure again.
  Predicting trends
  Plan growth and changes (capacity planning)
  Profiling is everything
  The FromDual Performance Tuning Balance
  The FromDual Database Health Check
  Application tuning
  The slow query log / profile
  Execution plans
  SESSION status diff

The first run of this workshop should take place in November. Please let us know what you think about the contents, so we can adapt it to your needs...