Introduction to MySQL Development and Administration Training

Delivery Options

• Customized Onsite Training (5 days): Request a Proposal

Class Description

Class Overview

Class Goals

• Understand the basics of Relational Databases.
• Learn the features and benefits of MySQL.
• Use various Data types and database design.
• Discover a database design structure/content.
• Use expressions in SQL statements for more functional and flexible retrieval.
• Learn bulk data import and export operations.
• Understand MySQL storage engines, transactions and features of the common engines.
• Create views to reuse SELECT statements
• Learn data retrieval using SELECT statement.
• Troubleshoot typical warnings and errors.
• Change or add data.
• Underdstand MySQL data validation.
• Delete data from tables.
• Generate aggregated query data using various criteria.
• Connect data from multiple table rows using various types of JOIN constructs.
• Use several different types of sub-queries.
• Extensive coverage of MySQL Functions and expressions.
• Use expressions in SQL statements for more functional and flexible retrieval.
• Learn to export and import data.
• 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.

Class Outline

1. MySQL Programs and Executables
   1. MySQL Programs and Executables
   2. Common Options to Commands
   3. Specifying Options
   4. Options on the Command Line
   5. my_print_defaults: Display options from files
   6. perror: Explain Error Codes
2. User Interfaces MySQL
   1. Overview of User Interfaces
   2. Establishing a Connection
   3. MySQL Query Browser
      1. Keyboard Shortcuts
      2. Main Query Window
      3. Query Toolbar
      4. Result Area
      5. Advanced Toolbar
      6. SQL Commands with Mouse Clicks
      7. History and Bookmarks for SQL Commands
      8. Using Scripts: Executing Several Commands
      9. MySQL Help - Information Browser
      10. Object Browser
      11. The Script Editor
      12. Changing Data in SELECT Results
      13. Stored Procedures
   4. MySQL Administrator
   5. MySQL Migration Toolkit
      1. A Brief Overview of the Migration Process
   6. phpMyAdmin
3. MySQL Monitor
   1. Using MySQL Monitor (SQL Command Interpreter)
   2. Some Useful Options
   3. Formatting and Output Options
   4. Commands for Interactive Mode
   5. Executing a Script from the Monitor
   6. Using the mysql Command to run a Script
   7. See Database Status
4. Designing Databases
   1. Relational Database Concepts
      1. Brief History of SQL
      2. Relational Database Management System
      3. Popular Databases
      4. Tables
      5. Rows
      6. Columns
      7. Relationships
      8. Datatypes
      9. Primary Keys
      10. Foreign Keys
      11. Valid Object References
      12. SQL Statements
   2. Database Design Principles
   3. Film Rentals - sakila
   4. Basic Modeling Process: Creating a Data Model
      1. Logical versus Physical Model
      2. The Entity-Relationship Model
      3. Entities And Entity Sets
      4. Attributes
      5. Relationships
      6. Relationship Types
      7. Graphical Representation of a Model
      8. Physical Model: Mapping The ER Model To A Relational Database
   5. Database Normalization
      1. First Normal Form
      2. Warning: Abnormal Normal Form!
      3. Second Normal Form
      4. Third Normal From
5. Creating Databases and Components
   1. Creating Databases
      1. Deleting Databases
   2. Creating Tables
      1. Dropping Tables
      2. Defining Table Types
      3. Altering Existing Table Structures
      4. Add a column
      5. Renaming Tables
      6. Temporary Tables
   3. Creating Column Definitions
   4. Data Types in MySQL
      1. Numeric Data Types
      2. String Data Types
      3. Character Set Fundamentals
      4. Set Types
      5. Date/Time Data Types
      6. Options and Attributes
   5. Constraints
      1. NOT NULL Constraint: Defining a Column's Nullability
      2. UNIQUE Constraint
      3. PRIMARY KEY Constraint: Defining a Master Key
      4. Defining Auto-Increment Columns
      5. Referential Integrity Constraints: Defining Foreign Keys
      6. Constraint Checking on a Transaction
      7. Deferred Checking
      8. Defining Default Values
   6. Managing Indexes
      1. Defining Indexes when Creating Tables
      2. CREATE INDEX Statement
      3. Full-Text Indexes
      4. Deleting Indexes
6. Identifiers in MySQL
   1. Identifiers in MySQL
      1. Identifier Names
   2. Schema Object Names
      1. Using Double Quotes
      2. Using Quote Within Names
      3. Using Digits
   3. Some Restrictions
   4. Identifier Qualifiers
   5. Mapping of Identifiers to Filenames
   6. Identifier Case Sensitivity
      1. lower_case_table_names Setting
      2. Avoiding Data Transfer Problems
7. Show Database Information
   1. SHOW and DESCRIBE Statements
      1. Using Table-Related SHOW Statements
      2. Using DESCRIBE Statement
   2. INFORMATION_SCHEMA Tables
      1. Showing Databases
8. MySQL Table Types
   1. MySQL Table Types (Storage Engines)
   2. Setting/Changing Engine
   3. MyISAM Tables
      1. Full-Text Search on MyISAM Tables
      2. Creating a Full-Text Index
      3. Using Full-Text Search
   4. InnoDB Tables
      1. InnoDB - Limitations and Drawbacks
      2. InnoDB Disk Management
      3. Preventing Problems
      4. Moving Files
   5. FOREIGN KEY Constraints in InnoDB
      1. Checking Foreign Keys
   6. Choosing - MyISAM or InnoDB?
   7. HEAP or MEMORY Tables
   8. Temporary Tables
   9. MERGE Tables
   10. Other Table Types
       1. ARCHIVE or Compressed Tables (since MySQL 4.1)
       2. CSV Type Tables(since MySQL 4.1)
       3. NDB or Cluster Tables (since MySQL 4.1)
       4. FEDERATED or External Tables (since MySQL 5.0)
       5. BDB Tables
   11. Table Files Storage
9. Basic Select Statements
   1. Basic SQL Syntax Rules
      1. Comments in SQL
      2. Whitespace and Semi-colons
      3. Case Sensitivity
   2. SELECT Basics
   3. Introduction to the sakila Database
   4. Basic Select Examples
      1. SELECTing All Columns in All Rows
   5. SELECTing Specific Columns
   6. The WHERE Clause and Basic Operator Symbols
      1. Checking for NULL
   7. WHERE and ORDER BY
   8. Using Aliases
   9. Selecting Distinct Records
   10. Sorting Records
   11. The LIMIT Clause
       1. Processing the First N or Last N Records
10. Using Expressions in MySQL Statements
    1. MySQL Expressions
       1. Basic Elements of MySQL Expressions
    2. Operators in MySQL
       1. Calculated Fields
    3. Concatenation
    4. Arithmetic Operators
    5. Operator Precedence
    6. Comparison Operators
    7. The WHERE Clause and Operator Words
       1. The BETWEEN Operator
       2. The IN Operator
       3. The LIKE Operator
       4. The REGEXP Operator
       5. Use of BINARY with Strings
       6. The NOT Operator
    8. Using CASE
    9. Logical Operators
       1. Order of Evaluation
       2. Summary
    10. Using MySQL Functions
    11. Functions to Compare Data
    12. Control Flow Functions
        1. Exercise :Using Control Flow Functions in a SELECT Statement
        2. Data Conversion Functions
    13. String Functions
        1. Numeric Functions
    14. Date/Time Functions
        1. Current Date/Time
        2. Date Addition/Subtraction Functions
        3. Date Extraction Methods
        4. Date Formatting Methods
11. Inserting, Updating and Deleting Records in MySQL
    1. Inserting Data in a MySQL Database
       1. <VALUES option> of INSERT
       2. Add Multiple Rows
       3. Maintaining Relationships
       4. <set option> Alternative of INSERT
    2. Using REPLACE Statement to Add Data
    3. INSERT with ON DUPLICATE KEY UPDATE Syntax
    4. Updating Data in MySQL
       1. ORDER BY and LIMIT Clauses in UPDATE
    5. Using DELETE Statement in MySQL
       1. Some Advanced MySQL modifiers to DELETE statement
       2. ORDER BY and LIMIT in DELETE Statement
    6. Using TRUNCATE Statement to Delete Data
    7. Joining Tables in an UPDATE Statement
       1. Updating Multiple Tables using Joins
    8. Joining Tables in a DELETE Statement
       1. <FROM JOIN DELETE> Alternative
       2. <using join delete> Clause
       3. Deleting from multiple tables
12. Data Validation in MySQL
    1. Data Validation
    2. MySQL SQL Modes
       1. Setting SQL mode
       2. Some important SQL Modes
    3. Strict SQL Mode
       1. Strict Mode in Default Values
    4. Special Combination Modes
    5. Data Validation for Temporal Types
       1. SHOW WARNINGS
13. Advanced Queries
    1. More on The SELECT Statement
    2. Using Variables in a SELECT Statement
    3. Creating Joins in Your SQL Statements
    4. Inner Joins and Cross Joins
       1. Multi-table Joins
    5. Outer Joins
       1. Left Joins
       2. Right Joins
       3. Full Outer Joins
       4. Creating Full Joins
       5. Creating Basic Joins
       6. Creating Straight Joins
       7. Creating Natural Joins
    6. Unions
       1. UNION ALL
       2. UNION Rules
    7. Creating Views
    8. Dropping Views
    9. Benefits of Views
    10. The SELECT Statement Options
    11. Subqueries
        1. SubQuery Variants
        2. Nesting JOINS
        3. SubSELECT Limitations
        4. SubSelects as Join
14. Exporting and Importing Data in MySQL
    1. EXPORTING: Data Out of a Table
    2. Use Field and Line Separators
    3. Dump a Row to a File
    4. CTAS: Creating a New Table Using a ResultSet
    5. Copying Data into an Existing Table
    6. Using the LOAD DATA to Import Data
       1. General Loading Rules
       2. More Loading Examples
    7. Using the mysqlimport Utility to Import Data
       1. Using Separators with mysqlimport Utility
15. 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
       2. mysql: Is Server Running?
       3. Query on Base Tables
    8. Launching the MySQL Server
    9. Installing From Source Files
16. 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
17. 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
18. 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
19. 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
20. 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
21. 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

Class Materials

All students receive a course manual or book and all the class examples.

Students in private onsite classes will also receive:

• Technical Reference Library

Class Prerequisites

Experience in the following areas is required:

• General RDBMS and 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:

• General administration of database installations.
• Understanding of database installation and deployment
• Basic knowledge hardware and OS

Technical Requirements

Our computer technical requirements and setup process is easy, with support just a click away.

• Course technical requirements and setup instructions.

• MySQL ^TM and all MySQL-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. or its subsidiaries in the U.S. and other countries.