Introduction to MySQL Development and Administration Training

This is an intensive MySQL training course with a solid coverage of various MySQL programs, development, technologies, concepts and administration.

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

MySQL Programs and Executables
Common Options to Various Commands
Specifying Options
Options on the Command Line
my_print_defaults: Display options from files
perror: Explain Error Codes

Overview of User Interfaces
Establishing a Connection
MySQL Query Browser
- Keyboard Shortcuts
- Main Query Window
- Query Toolbar
- Result Area
- Advanced Toolbar
- SQL commands with Mouse Clicks
- History and Bookmarks for SQL Commands
- Using Scripts: Executing Several Commands
- MySQL Help - Information Browser
- Object Browser
- The Script Editor
- Changing Data in SELECT Results
- Stored Procedures
MySQL Administrator
MySQL Migration Toolkit
- A Brief Overview of the Migration Process
phpMyAdmin

Using MySQL Monitor (SQL Command Interpreter)
Some Useful Options
Formatting and Output Options
Commands for Interactive Mode
Executing a Script from the Monitor
Using the mysql Command to run a Script
See Database Status

Relational Database Concepts
- Brief History of SQL
- Relational Database Management System
- Popular Databases
- Tables
- Rows
- Columns
- Relationships
- Datatypes
- Primary Keys
- Foreign Keys
- Valid Object References
- SQL Statements
Database Design Principles
Film Rentals - sakila
Basic Modeling Process: Creating a Data Model
- Logical versus Physical Model
- The Entity-Relationship Model
- Entities And Entity Sets
- Attributes
- Relationships
- Relationship Types
- Graphical Representation of a Model
- Physical Model: Mapping The ER Model To A Relational Database
Database Normalization
- First Normal Form
- Warning: Abnormal Normal Form!
- Second Normal Form
- Third Normal From

Creating Databases
- Deleting Databases
Creating Tables
- Dropping Tables
- Defining Table Types
- Altering Existing Table Structures
- Add a column
- Renaming Tables
- Temporary Tables
Creating Column Definitions
Data Types in MySQL
- Numeric Data Types
- String Data Types
- Character Set Fundamentals
- Set Types
- Date/Time Data Types
- Options and Attributes
Constraints
- NOT NULL Constraint: Defining a Column`s Nullability
- UNIQUE Constraint
- PRIMARY KEY Constraint: Defining a Master Key
- Defining Auto-Increment Columns
- Referential Integrity Constraints: Defining Foreign Keys
- Constraint Checking on a Transaction
- Deferred Checking
- Defining Default Values
Managing Indexes
- Defining Indexes when Creating Tables
- CREATE INDEX Statement
- Full-Text Indexes
- Deleting Indexes

Identifiers in MySQL
- Identifier Names
Schema Object Names
- Using Double Quotes
- Using Quote Within Names
- Using Digits
Some Restrictions
Identifier Qualifiers
Mapping of Identifiers to Filenames
Identifier Case Sensitivity
- lower_case_table_names Setting
- Avoiding Data Transfer Problems

SHOW and DESCRIBE Statements
- Using Table-Related SHOW Statements
- Using DESCRIBE Statement
INFORMATION_SCHEMA Tables
- Showing Databases

MySQL Table Types (Storage Engines)
Setting/Changing Engine
MyISAM Tables
- Full-Text Search on MyISAM Tables
- Creating a Full-Text Index
- Using Full-Text Search
InnoDB Tables
- InnoDB - Limitations and Drawbacks
- InnoDB Disk Management
- Preventing Problems
- Moving Files
FOREIGN KEY Constraints in InnoDB
- Checking Foreign Keys
Choosing - MyISAM or InnoDB?
HEAP or MEMORY Tables
Temporary Tables
MERGE Tables
Other Table Types
- ARCHIVE or Compressed Tables (since MySQL 4.1)
- CSV Type Tables(since MySQL 4.1)
- NDB or Cluster Tables (since MySQL 4.1)
- FEDERATED or External Tables (since MySQL 5.0)
- BDB Tables
Table Files Storage

Basic SQL Syntax Rules
Comments in SQL
Whitespace and Semi-colons
- Case Sensitivity
- SELECT Basics
- Introduction to the sakila Database
Basic Select Examples
- SELECTing All Columns in All Rows
SELECTing Specific Columns
The WHERE Clause and Basic Operator Symbols
- Checking for NULL
WHERE and ORDER BY
Using Aliases
Selecting Distinct Records
Sorting Records
The LIMIT Clause
- Processing the First N or Last N Records

MySQL Expressions
- Basic Elements of MySQL Expressions
Operators in MySQL
- Calculated Fields
Concatenation
Arithmetic Operators
Operator Precedence
Comparison Operators
The WHERE Clause and Operator Words
- The BETWEEN Operator
- The IN Operator
- The LIKE Operator
- The REGEXP Operator
- Use of BINARY with Strings
- The NOT Operator
Using CASE
Logical Operators
- Order of Evaluation
- Summary
Using MySQL Functions
Functions to Compare Data
Control Flow Functions
- Exercise :Using Control Flow Functions in a SELECT Statement
- Data Conversion Functions
String Functions
- Numeric Functions
Date/Time Functions
- Current Date/Time
- Date Addition/Subtraction Functions
- Date Extraction Methods
- Date Formatting Methods

Inserting Data in a MySQL Database
- of INSERT
- Add Multiple Rows
- Maintaining Relationships
- Alternative of INSERT
Using REPLACE Statement to Add Data
INSERT with ON DUPLICATE KEY UPDATE Syntax
Updating Data in MySQL
- ORDER BY and LIMIT Clauses in UPDATE
Using DELETE Statement in MySQL
- Some Advanced MySQL modifiers to DELETE statement
- ORDER BY and LIMIT in DELETE Statement
Using TRUNCATE Statement to Delete Data
Joining Tables in an UPDATE Statement
- Updating Multiple Tables using Joins
Joining Tables in a DELETE Statement
- Alternative
- Clause
- Deleting from multiple tables

Data Validation
MySQL SQL Modes
- Setting SQL mode
- Some important SQL Modes
Strict SQL Mode
- Strict Mode in Default Values
Special Combination Modes
Data Validation for Temporal Types
- SHOW WARNINGS

More on The SELECT Statement
Using Variables in a SELECT Statement
Creating Joins in Your SQL Statements
Inner Joins and Cross Joins
- Multi-table Joins
Outer Joins
- Left Joins
- Right Joins
- Full Outer Joins
- Creating Full Joins
- Creating Basic Joins
- Creating Straight Joins
- Creating Natural Joins
Unions
- UNION ALL
- UNION Rules
Creating Views
Dropping Views
Benefits of Views
The SELECT Statement Options
Subqueries
- SubQuery Variants
- Nesting JOINS
- SubSELECT Limitations
- SubSelects as Join

EXPORTING: Data Out of a Table
Use Field and Line Separators
Dump a Row to a File
CTAS: Creating a New Table Using a ResultSet
Copying Data into an Existing Table
Using the LOAD DATA to Import Data
- General Loading Rules
- More Loading Examples
Using the mysqlimport Utility to Import Data
- Using Separators with mysqlimport Utility

Installing MySQL
Operating System Selection
Downloading and Installing MySQL On Windows
- Running the MySQL Configuration Wizard
- Starting and stopping the MySQL server
Installing MySQL On Linux and Unix
- Using RPM (Linux only)
- MySQL Packaged RPMs
- Using a Tar File to Install MySQL
- Starting the Server Automatically
Installing MySQL On Mac
Verifying a Downloaded File
Verifying a MySQL Installation
- mysqladmin: Obtain Server Status
Launching the MySQL Server
Installing From Source Files

Configuration Options
Viewing MySQL`s Configuration Parameters
Some Examples of Option Usage
- Managing Connection Loads
- Setting the Data Directory Location
- Setting the Default Storage Engine
- Executing SQL Commands Automatically
- Logging Potentially Nonoptimal Queries
- Logging Slow Queries
- Setting the Maximum Allowable Simultaneous Connections
- Setting MySQL`s Communication Port
Rules for Specifying Options
- Session Specific Options
- Turning Options On or Off
- --loose Option
- Memory Specifications
- Precedence of Options
Setting Options in Configuration Files
Configuration Templates
Location of Configuration Files
- Unix Configuration Files
- Windows Configuration Files
Options in Environment/System Variables
Main Configuration Groups
Key Configuration Options
Client Configuration Options
Server Configuration Options
- Logging Configuration Options
Engine-Specific Configuration Options
Buffer and Cache Allocation Configuration Options

First Things First
- Apply Latest Trusted Patches
- Cleanup restaged boxes
- Audit OS User Accounts
- Disable Unused System Services
Network
- Add Firewall to Shutdown Access
- Restrict host connections
- Use IP addresses if your DNS is unreliable
- Disable Networking
Securing the mysqld Daemon
Files, Directories, and Processes
- Run MySQL as a non-root user
- Protect the socket file
- Set Data File Permissions
- Consider an Encrypted File System
- Avoid Symbolic Links
Securing Accounts
- Set a good MySQL root user password
- User Access
- Requiring Passwords
- Controlling Account Privileges
- If you Forget the Root Password!
- Limiting User Resources
- Controlling Connections
- Application Access
Data Storage and Encryption
- One-Way Encryption
- Signing Your Data
- Two-Way Encryption
- Don`t Store Sensitive Data

Managing Security and Access Control
The Two Stages of Access Control
Granting Privileges
Revoking Privileges
Managing Users
- Adding Users to MySQL Access Control
- Adding Privileges to an Existing User
- Deleting Users
MySQL Privilege System
- Tracing A Connection Request
Privilege Scope Levels - Where Is Access Information Stored?
The user Table - Global Privilege Scope
Database Privilege Scope - The db Table
- The host Table
- Working with DB Permissions
Table Privilege Scope - The tables_priv Table
- Revoking Table-Level Permissions
Column Privilege Scope - The columns_priv Table
- Revoking Column-Level Permissions
The Routine scope: The procs_priv Table
Tools for Setting the Access Privileges
Refreshing Grant Caches
- Insert into user Table
Reviewing User Privileges
- Using SHOW GRANTS
- Listing Conflicts
The GRANT OPTION Privilege
- Grant to All Users from a Host

View Information
- Using MySQLshow
Using MySQLadmin Tool
- Databases
- View Server Status
Flush Caches and Settings
View and Kill Threads
Server Management
MySQL Administrator User Interface

Managing Backup and Recovery
Backing up or Exporting Databases Using mysqldump
- Copying Data Directory
- Portability
Using mysqldump
mysqldump - Options
- Formatting Options
Backing up a Single Database
- Backing up the Entire Database
Contents of the Backup SQL Script
- Set Variables
- Enclosing Characters /*! and */
- Set Names
- Tables
- Ignoring Foreign Key Constraints
- Flush Logs
- Backing up Individual Tables
- Backing up Multiple Databases
- Database Information in the Backup File
- Backing up all Databases
Restoring Your Database
- Reloading Your Database
Updating the Restored Database From Binary Log Files
- Restoring Data Directly From a Binary Log
- Selective Changes from Log Files
- Restoring Binary Log Data From a Text File
- Enabling and Disabling Binary Logging
Recovering Corrupt MyISAM Tables
- mysqlhotcopy - A Database Backup Program
Backing Up and Recovering an InnoDB Database
- Stop Background Processes - Forced Recovery
- Recovery using Checkpoints
- InnoDB Hot Backup

Versions of the MySQL Server
- The mysqld_safe Wrapper
Mysql Directory Structure
Securing the MySQL Server: Set root Password
Delete Anonymous Accounts
Creating an Account for Basic Use
General Information using MySQL Monitor
Configuring MySQL