LinuxCBT MySQL Edition

MySQL - DBMS

MySQL Installation
Introduction to MySQL 5
Discuss DBMS system specifications
Identify existing MySQL-installed components
Explore file-system layout
Identify & discuss the various downloadable formats /source|binary|packaged/
Download MySQL packages to a local repository
Discuss the key DBMS components
Confirm md5sums of components
Install MySQL server package and examine defaults
Identify key server-side administrative utilities
Inspect the contents of the default DBMS data-directory
Install MySQL Client package and explore its contents

Terminal Monitor Shell Client
Discuss Terminal Monitor concepts
Explain the default privileges configuration
Connect to the DBMS using anonymous privileges
Explore Terminal Monitor's help facility and useful escape sequences
Identify the default schemas
Execute useful SQL functions
Demonstrate how to execute SQL queries from the shell in non-interactive mode

User Administration
Identify the currently logged-in user
Connect to MySQL DBMS as root and explore the interface
Attempt to connect to the DBMS as invalid users and discuss the results
Attempt to connect from a remote host and evaluate results
Discuss the primary methods of tightening the default security scheme
Tighten the default privileges for the root user
Explore the global 'user' grant table & discuss attributes
Secure anonymous user accounts
Remove anonymous privileges and confirm enhanced security policy
Discuss and explore the grant tables and applicable attributes
Identify the ancillary grant tables that permit granular filtration
Create new DBMS users and confirm connectivity
Remove superfluous user accounts
Discuss permissible host values when defining users
Create remote super users and test connectivity
Discuss privileges scopes concepts & applications
Identify grant tables related to privileges scopes
Use GRANT to create and manipulate user-privileges
Create unprivileged accounts and explore capabilities
Use REVOKE to remove privileges
Use DROP to discard superfluous accounts
Maintain user passwords using SET and UPDATE

Show Commands & Options Files
Explain the usages of key SHOW commands
Expose DBMS privileges for currently authenticated user
Expose DBMS privileges for other defined users
List all databases available on the system
Show the DDL statements used to create various MySQL-managed databases
Examine the default supported character sets
Reveal the structure of defined tables
Show the DDL statements used to create various tables
List the supported Storage Engines by the current binary-build of MySQL
Examine the MyISAM Storage Engine tables on the file system & discuss key file types
Show the schemas of various tables and discuss the results
Examine the currently executing processes, including connected users
Initiate & KILL connections listed in the process-list table
Examine key status variables on the running server
Show table status information, revealing used-space, length, create-time, etc.
Discuss the default search-path for options files by MySQL clients
Examine and secure the MySQL history file to prevent superfluous disclosure of queries
Define a personal MySQL options file
Explain the supported section headers in MySQL options files
Define useful run-time directives for MySQL clients and examine results
Define a useful, custom MySQL prompt on a global and per-user basis & examine usefulness
Set default database variable in per-user options files and examine results

Key MySQL Client Utilities
Focus on mysql terminal monitor utility
Discuss & demonstrate mysql client modes
Discuss command-line option types
Output queries to standard /HTML|XML|TXT/ formats
Exclude column-headers from output for easy import into other applications
Use options file to control startup variables
Focus on mysqladmin utility
Compare & contrast mysqladmin batch-mode to mysql interactive mode
Confirm the status of DBMS servers using mysqladmin
Ascertain remote DBMS process list
Create & drop remote databases using mysqladmin
Administer remote user credentials using mysqladmin
Focus on mysqldump utility
Discuss mysqldump applications, features & benefits
Dump all databases and related objects to an ASCII text backup file
Examine the resulting dump file
Dump select databases and examine resulting file
Dumpy DBMS databases to a remote MySQL instance using mysqldump
Focus on mysqlimport utility
Create database container and table structure for subsequent imports
Create matching data-feed for import by mysqlimport
Import data-feed using default delimiters and record-separators
Discuss mysqlimport defaults regarding data-directory and table names
Examine importing duplicate records

Key Data Manipulation Language (DML) Statements
Discuss common DML statements
Focus on SELECT DML statement
Optimize SELECT statements with WHERE clauses to define criteria
Discuss SELECT operators /=|<=|>=|!=|etc/
Perform pattern matching using WHERE clause with LIKE operator & wildcards
Influence the order of SELECT output using ORDER BY
Extend criteria definition and optimize queryies using AND clause
Return result-set INTO output ASCII text file using SELECT INTO
Redirect result-set to AWK and extract interesting fields
Use LIMIT with SELECT to return n number of rows
Return total number of rows including LIMIT value
Alias column headers using SELECT
Concatenate results returned using SELECT
Focus on INSERT DML statement
Explain supported INSERT statements /VALUES|SET|SELECT/
Describe table structure to determine column constraints
INSERT VALUES specifying column names
INSERT VALUES one-to-one without defined column headers
Use SET with INSERT to indicate interesting columns
Use VALUES to INSERT multiple rows in one statement
Discuss INSERT SELECT applications
Create table structure for using INSERT SELECT
Populate newly-created table with data
Use SELECT INSERT to move data between tables & evaluate results
Focus on UPDATE DML statement
Perform global table UPDATE with simple DML statement
Specify columns to be updated and evaluate affected rows
UPDATE interesting rows using criteria
UPDATE n number of records using the LIMIT criteria operator
Focus on DELETE DML statement
Remove interesting records using WHERE clause and applicable critieria
Intro to the ALTER Data Definition Language (DDL) statement

Key Data Types
Focus on Numeric data-types
Use describe to expose defined data-types for columns
Discuss unsigned and auto incremented column attributes
Discuss /TINYINT|SMALLINT|MEDIUMINT|INT|BIGINT/ capacity and applications
Discuss FLOAT capacity and applications
Discuss DOUBLE capacity and applications
Discuss DECIMAL capacity and applications
Discuss BIT capacity and applications
Define a table with TINYINT column
Populate TINYINT column with INSERT and exceed its limits and evaluate resutls
Use ALTER to increase the capacity of the TINYINT column to SMALLINT
Populate SMALLINT column and evaluate results
ALTER table and add columns with greater numeric capacity
Define a DECIMAL column and store values
Focus on String data-types
Explain features and limitations of CHAR type fields
INSERT strings with unpreserved characters and evaluate results
Compare & contrast VARCHAR & CHAR type fields; fixed vs. varaible-length
ALTER table to convert interesting fields from CHAR to VARCHAR
Create Perl script to generate values for VARCHAR columns and evaluate results
Discuss /TEXT|TINYTEXT|MEDIUMTEXT|LONGTEXT/ column types
Discuss /BLOB|TINYBLOB|MEDIUMBLOB|LONGBLOB/ column types
ALTER table to support LONGTEXT and import data and evaluate results
Discuss /BINARY|VARBINARY/ usages and limits
Explain the usages of ENUM column types
Explore predefined ENUM column types in system tables
Define ENUM fields, insert data and evaluate DBMS behavior
Focus on Date data-types
Discuss DATE column type features (resolution & range)
ALTER table structure to support DATE-based column
INSERT invalid date and evaluate results
Discuss DATETIME column type features (resolution & range)
ALTER table structure to support DATETIME-based column
INSERT valid date & time value and evaluate results
Discuss how MySQL implements dummy dates
ALTER table structure to support TIMESTAMP-based column
Discuss TIMESTAMP column type features (resolution & range)
Discuss NULL-enabled auto-timestamp (dummy-time) invocated columns
Explain MySQL's default date syntax
Insert Date & Time values into MySQL columns using mixed delimiters
Insert Date & Time values into MySQL columns without delimiters
Recap supported data-types

Key Data Definition Language (DDL) Statements
Focus on CREATE DDL statement
Describe how MySQL maps database containers to the file system
CREATE DATABASE container to house new tables
Define the requirements /auto_increment|primary key|etc/ of new base tables
Define the CREATE TABLE syntax for new base tables based on selected data types
Execute statement and evaluate results of recently-created tables
Use SHOW CREATE TABLE to examine DDL syntax for reference
CREATE TABLE with NOT-NULL column enforcements
Populate tables with useful values and evaluate results
INSERT VALUES with DEFAULT for auto-populated columns and evaluate results
Duplicate table structure using CREATE TABLE and evaluate results
Populate destination table with data from the source table using INSERT
Discuss Temporary tables features and applications
CREATE TEMPORARY TABLES and populate with useful data
Rename tables using RENAME TABLE as an alternative to ALTER TABLE
Focus on DROP DDL statement
Describe the objects that are affected using DROP
DROP DBMS objects and evaluate file system implications
Focus on CREATE INDEX DDL statement
Discuss the importance of indexes to improved data-retrieval
Discuss MyISAM storage engine per-index limits
Identify default MyISAM index files
Explore defined indices
Define UNIQUE indices
Compare & contrast table & index storage requirements
Generate data-set to be imported into an indexed column
Compare & contrast query-execution times with and without indices

JOINS
Discuss the features & benefits of using JOINS to access normalized data
Normalize the data structure, using ALTER TABLE, to prepare for using joins
Use seq to generate normalized data and import using mysqlimport
Discuss the various types of supported JOINS /EQUI|NATURAL|LEFT|RIGHT|INNER|OUTER/
Define and execute EQUIJOINS
Define and execute NATURAL JOINS
Define JOINS using fully-justified DBMS object names
Use table and column aliases with JOINS

VIEWS & TRIGGERS
Discuss the features & benefits of using VIEWS
Use DESCRIBE to identify interesting VIEW candidate-columns
Explain VIEW-creation syntax
Create useful VIEWS and evaluate results
Create VIEWS using column-aliases
Update VIEWS using CREATE OR REPLACE VIEW & ALTER VIEW
Create VIEWS based on EQUIJOINS
Demonstrate how to UPDATE base-table data using VIEWS
Remove superfluous VIEWS using DROP VIEW
Focus on TRIGGERS
Discuss MySQL's TRIGGER implementation rules
Discuss TRIGGER timing & events
Discuss TRIGGER statements
Define a TRIGGER to update a secondary table upon INSERT INTO primary table
INSERT INTO primary table and examine TRIGGER response
Log table updates using TRIGGERS
Use SHOW to reveal defined TRIGGERS ON tables
Create & evaluate multiple statement triggers using BEGIN .. .END
Redefine MySQL statement delimiters to support multiple statement triggers

Stored Routines (Functions & Procedures)
Discuss Stored Routines components and applications
Focus on Stored Procedures
Discuss required privileges
Explain how to invoke stored procedure
Define useful stored procedures
Use SHOW to reveal stored procedures and important attributes
Create multiple statement stored procedures
Redirect stored procedure output to memory for easy reuse
Focus on Functions
Discuss ideal uses for functions
Describe function syntax
Define functions based on date calculations and evaluate results

Replication Configuration
Explore uses of replication
Illustrate MySQL Master-Slave replication model
Discuss binary logging requirements
Updated configuration on Master & Slave servers to reflect replicaiton requirements
Create replication accounts on Master & Slave servers
Create a snapshot of the master server's databases
Transport the snapshot to slave servers using SCP
Record replication position
Configure server IDs on Master & Slave servers
Use DDL to enable Master & Slave servers
Confirm replication status via DDLs, the process list and system table
Committ updates to the master server and confirm replication to the slave servers
Configure an additional slave server and confirm replication

Logging
Explain MySQL logging /ERROR|QUERY|BINARY|SLOW/
Discuss the default log file nomenclature
Explain & examine the contents of the ERROR log file
Restart MySQL and examine log entries
Enable the QUERY log file
Explain & examine the contents of the QUERY log file
Discuss the BINARY log file
Discuss the SLOW log file
Enable the SLOW log file
Use mysqlbinlog to examine the contents of binary logs

Win32 Integration
Connect to Windows 2003 host and download MySQL Administrator
Install MySQL Administrator
Connect to Linux-based MySQL instance and explore key variables
Download and install MySQL Query Browser
Explore Query Browser interface & features
Execute remote queries with Query Browser
Download & install MyODBC
Connect MS Access to MySQL using MyODBC
Import data into MS Access from MySQL
Link Tables in MS Access to MySQL using MyODBC

Storage Engines
Discuss the MyISAM storage engine
Explain the usages and limitations of the Memory storage engine
Confirm Memory storage engine support
Create memory-based tables using the Memory storage engine
Focus on the CSV storage engine
Confirm support for the CSV storage engine
Define CSV-based tables and evaluate results
Focus on the Federated storage engine
Explain the applications of remote tables using the Federated storage engine
Implement Federated tables and evaluate results
Focus on the Archive storage engine
Confirm support for the Archive storage engine
Describe the applications of compressed tables using the Archive storage engine
Implement compressed tables and evaluate results
Import data-set into compressed table and evaluate storage requirements
Focus on the InnoDB storage engine
Discuss the benefits /Transactional|Scalability/ of InnoDB-based tables
Confirm support for InnoDB tables
Explain InnoDB storage architecture and identify default data & log files
Update /etc/my.cnf configuration file to support InnoDB tables
Load large data-set and examine resulting data & log files
Define custom InnoDB data & log files
Extend the data & log files and evaluate results
Distribute data & log files across multiple partitions
Create a raw device for InnoDB data & log files
Define InnoDB data & log files on a RAW device

phpMyAdmin
Discuss the benefits and applications of phpMyAdmin
Install & configure Apache HTTPD
Download & install phpMyAdmin
Secure phpMyAdmin's access to MySQL DBMS
Explore phpMyAdmin's interface
Perform many shell-based queries, graphically

PHP5 Integration
Uninstall PHP 4.x
Download & install PHP 5.x packages & relevant modules /MySQL|SSL|etc./
Explore the PHP Command Line Interface (CLI)
Interface PHP CLI to MySQL & return results
Implement PHP->MySQL error handling using conditions & OO-functions
Execute useful PHP->MySQL methods
Define HTML forms
Process HTML forms using PHP
Store data in MySQL using PHP

Explore Additional MySQL Utilities
Use MySQLShow to return useful data
Use MyISAMCheck & MySQLCheck
Compare and contrast both utilities

MySQL with SSH Tunnel
Discuss benefits
Implement SSH tunnel
Use MySQL utilities across secure tunnel
Evaluate results

MySQL on Windows Server
Install MySQL
Explore configuration
Communicate between Linux & Windows
Transfer data between Linux & Windows
Implement MySQL Workbench
Evaluate results

MySQL Instances
Discuss features & benefits
Configure multiple instances
Transfer data
Evaluate results