MySQL Administration Training (3 days)

MySQL Administration Training Overview

This MySQL training course teaches the administration, configuration, backups, security and management of MySQL databases. The My SQL course is very hands on and teaches the extensive suite of options in using various MySQL programs.

MySQL Administration Training Course Goals

• Understand the MySQL Architecture.
• General characteristics and resources used.
• Common Database Administration tasks.
• Utilize the various MySQL administration programs.
• Learn the MySQL Administrator Graphical User Interface.
• Use the INFORMATION_SCHEMA database to access metadata.
• Install and Upgrade MySQL 5.0 for the most common operating systems.
• Start and shutdown MySQL.
• Configure MySQL server options at runtime.
• Setup and manage numerous server logs.
• Learn various data types and constraints for performance issues.
• Understand data locking concepts at different levels in MySQL.
• Understand the features of various MySQL storage engines.
• Create, Maintain and Manage MySQL tables.
• Perform backup and restore operations utilizing multiple MySQL tools.
• Maintain security of a MySQL installation via user management and access rights.
• Improve performance through query optimization.
• Optimize at various levels - Installation, Databases and statements.
• Understand multiple MySQL options.

MySQL Administration Training Course Prerequisites

Experience in the following areas is required:

• Experience with a MySQL Server instance and database
• Working knowledge of MySQL architecture
• General SQL knowledge and principles
• Understanding of DDL objects - tables, Indexes, constraints and others
• Understanding of database installation and deployment
• Basic knowledge hardware and OS

Experience in the following areas would be beneficial.

• Some experience with applications and performance issues.

MySQL Administration Training Course Outline

1. Installing MySQL
   1. Installing MySQL
   2. Operating System Selection
   3. Downloading and Installing MySQL On Windows
      1. Running the MySQL Configuration Wizard
      2. Starting and stopping the MySQL server
   4. Installing MySQL On Linux and Unix
      1. Using RPM (Linux only)
      2. MySQL Packaged RPMs
      3. Using a Tar File to Install MySQL
      4. Starting the Server Automatically
   5. Installing MySQL On Mac
   6. Verifying a Downloaded File
   7. Verifying a MySQL Installation
      1. mysqladmin: Obtain Server Status
   8. Launching the MySQL Server
   9. Installing From Source Files
2. Setting up MySQL
   1. Versions of the MySQL Server
      1. The mysqld_safe Wrapper
   2. Mysql Directory Structure
   3. Securing the MySQL Server: Set root Password
   4. Delete Anonymous Accounts
   5. Creating an Account for Basic Use
   6. General Information using MySQL Monitor
   7. Configuring MySQL
3. Configuring MySQL and Specifying Options
   1. Configuration Options
   2. Viewing MySQL's Configuration Parameters
   3. Some Examples of Option Usage
      1. Managing Connection Loads
      2. Setting the Data Directory Location
      3. Setting the Default Storage Engine
      4. Executing SQL Commands Automatically
      5. Logging Potentially Nonoptimal Queries
      6. Logging Slow Queries
      7. Setting the Maximum Allowable Simultaneous Connections
      8. Setting MySQL's Communication Port
   4. Rules for Specifying Options
      1. Session Specific Options
      2. Turning Options On or Off
      3. --loose Option
      4. Memory Specifications
      5. Precedence of Options
   5. Setting Options in Configuration Files
   6. Configuration Templates
   7. Location of Configuration Files
      1. Unix Configuration Files
      2. Windows Configuration Files
   8. Options in Environment/System Variables
   9. Main Configuration Groups
   10. Key Configuration Options
   11. Client Configuration Options
   12. Server Configuration Options
       1. Logging Configuration Options
   13. Engine-Specific Configuration Options
   14. Buffer and Cache Allocation Configuration Options
4. Securing MySQL Installation
   1. First Things First
      1. Apply Latest Trusted Patches
      2. Cleanup restaged boxes
      3. Audit OS User Accounts
      4. Disable Unused System Services
   2. Network
      1. Add Firewall to Shutdown Access
      2. Restrict host connections
      3. Use IP addresses if your DNS is unreliable
      4. Disable Networking
   3. Securing the mysqld Daemon
   4. Files, Directories, and Processes
      1. Run MySQL as a non-root user
      2. Protect the socket file
      3. Set Data File Permissions
      4. Consider an Encrypted File System
      5. Avoid Symbolic Links
   5. Securing Accounts
      1. Set a good MySQL root user password
      2. User Access
      3. Requiring Passwords
      4. Controlling Account Privileges
      5. If you Forget the Root Password!
      6. Limiting User Resources
      7. Controlling Connections
      8. Application Access
   6. Data Storage and Encryption
      1. One-Way Encryption
      2. Signing Your Data
      3. Two-Way Encryption
      4. Don't Store Sensitive Data
5. Managing Users in MySQL
   1. Managing Security and Access Control
   2. The Two Stages of Access Control
   3. Granting Privileges
   4. Revoking Privileges
   5. Managing Users
      1. Adding Users to MySQL Access Control
      2. Adding Privileges to an Existing User
      3. Deleting Users
   6. MySQL Privilege System
      1. Tracing A Connection Request
   7. Privilege Scope Levels - Where Is Access Information Stored?
   8. The user Table - Global Privilege Scope
   9. Database Privilege Scope - The db Table
      1. The host Table
      2. Working with DB Permissions
   10. Table Privilege Scope - The tables_priv Table
       1. Revoking Table-Level Permissions
   11. Column Privilege Scope - The columns_priv Table
       1. Revoking Column-Level Permissions
   12. The Routine scope: The procs_priv Table
   13. Tools for Setting the Access Privileges
   14. Refreshing Grant Caches
       1. Insert into user Table
   15. Reviewing User Privileges
       1. Using SHOW GRANTS
       2. Listing Conflicts
   16. The GRANT OPTION Privilege
       1. Grant to All Users from a Host
6. Administering MySQL
   1. View Information
      1. Using MySQLshow
   2. Using MySQLadmin Tool
      1. Databases
      2. View Server Status
   3. Flush Caches and Settings
   4. View and Kill Threads
   5. Server Management
   6. MySQL Administrator User Interface
7. Data Backup and Restore in MySQL
   1. Managing Backup and Recovery
   2. Backing up or Exporting Databases Using mysqldump
      1. Copying Data Directory
      2. Portability
   3. Using mysqldump
   4. mysqldump - Options
      1. Formatting Options
   5. Backing up a Single Database
      1. Backing up the Entire Database
   6. Contents of the Backup SQL Script
      1. Set Variables
      2. Enclosing Characters /*! and */
      3. Set Names
      4. Tables
      5. Ignoring Foreign Key Constraints
      6. Flush Logs
      7. Backing up Individual Tables
      8. Backing up Multiple Databases
      9. Database Information in the Backup File
      10. Backing up all Databases
   7. Restoring Your Database
      1. Reloading Your Database
   8. Updating the Restored Database From Binary Log Files
      1. Restoring Data Directly From a Binary Log
      2. Selective Changes from Log Files
      3. Restoring Binary Log Data From a Text File
      4. Enabling and Disabling Binary Logging
   9. Recovering Corrupt MyISAM Tables
      1. mysqlhotcopy - A Database Backup Program
   10. Backing Up and Recovering an InnoDB Database
       1. Stop Background Processes - Forced Recovery
       2. Recovery using Checkpoints
       3. InnoDB Hot Backup
8. Optimizing Databases and Their Objects
   1. Optimizing Databases
   2. Improving Application Design
   3. Precomputing Queries
   4. Materialized Views
   5. Transactions
   6. Indexing
      1. Types Of Indexes
      2. Indexes are not used when ...
      3. Client-Server Interactions
   7. MyISAM Index Collecting Statistics
9. Optimizing and Tuning Queries
   1. Query Execution Plan
   2. Analyzing the output from EXPLAIN Statement
      1. Explain Table
   3. Join Optimization
      1. Cartesian Product
      2. Match Elimination: Filtering Joins
      3. Using Joins in Aggregation
      4. Optimizing Joins
      5. Driving Tables
   4. When to Index
      1. Index Types
   5. SQL Queries: Other Optimizations
      1. Optimizing Data Retrieval
      2. Cardinality of a Table
      3. Using the OPTIMIZE TABLE Statement
   6. The Slow Query Log
10. Tuning MySQL for Performance
    1. System Characteristics
       1. Disk I/O Speed
       2. Memory
       3. How MySQL Uses Memory
       4. Processor Speed
       5. Network Bandwidth
    2. Tuning Server Parameters
    3. MySQL Query Cache
    4. The MyISAM Key Cache
    5. Examining Thread Information
11. Replication in MySQL
    1. Replicating MySQL Databases
    2. MySQL Replication Model
       1. Availability
       2. Load Balancing
    3. Setting up Replication
       1. Replication Files on the Slave
       2. Replication Chaining
    4. Implementing Replication - Details
       1. Set up Replication User
       2. Making Initial Backup
       3. Configuration Changes on Master
       4. Configuration Changes on the Slave
       5. Restore Backup on Slave
       6. Set up Connection to Master
       7. Start Replication on Slave
       8. Verifying Replication
    5. Managing Replication
       1. Managing the Master Server
       2. Managing the Slave Server
    6. Replication Configuration Options

MySQL Administration Training Course Materials

In addition to a comprehensive set of materials, including course notes and all the programming examples, each student will also receive a one-year subscription to Webucator's online reference library, which contains hundreds of the most current electronic technology books - a $149.95 per student value.

MySQL Administration Training Technical Requirements

1. MySQL 5.0
   • Download
2. MySQL GUI Tools
   • Download

Class Files for MySQL Administration Training

1. Download the class files.
2. After downloading the files, click here for further instructions.