MySQL Administration Training

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.

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.

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

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

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

Optimizing Databases
Improving Application Design
Precomputing Queries
Materialized Views
Transactions
Indexing
- Types Of Indexes
- Indexes are not used when ...
- Client-Server Interactions
MyISAM Index Collecting Statistics

Query Execution Plan
Analyzing the output from EXPLAIN Statement
- Explain Table
Join Optimization
- Cartesian Product
- Match Elimination: Filtering Joins
- Using Joins in Aggregation
- Optimizing Joins
- Driving Tables
When to Index
- Index Types
SQL Queries: Other Optimizations
- Optimizing Data Retrieval
- Cardinality of a Table
- Using the OPTIMIZE TABLE Statement
The Slow Query Log

System Characteristics
- Disk I/O Speed
- Memory
- How MySQL Uses Memory
- Processor Speed
- Network Bandwidth
Tuning Server Parameters
MySQL Query Cache
The MyISAM Key Cache
Examining Thread Information

Replicating MySQL Databases
MySQL Replication Model
- Availability
- Load Balancing
Setting up Replication
- Replication Files on the Slave
- Replication Chaining
Implementing Replication - Details
- Set up Replication User
- Making Initial Backup
- Configuration Changes on Master
- Configuration Changes on the Slave
- Restore Backup on Slave
- Set up Connection to Master
- Start Replication on Slave
- Verifying Replication
Managing Replication
- Managing the Master Server
- Managing the Slave Server
Replication Configuration Options