LinuxCBT DBMS Edition feat. MySQL 5 focuses on the ubiquitous, high-performance, low-maintenance, highly-available and scaleable MySQL 5 Database Management System (DBMS).

LinuxCBT DBMS Edition establishes the foundation for successful deployments and administration of MySQL 5 by exploring many of its features including but not limited to DML & DDL Statements, Terminal Monitor, shell-based clients, graphical clients, MyODBC, storage engines, data imports & exports, and PHP integration.

LinuxCBT DBMS Edition is unparalleled in content, depth and expertise. It entails 30-hours, or 4+ days of classroom training. LinuxCBT DBMS Edition prepares you for successfully deploying MySQL-based DBMS solutions.

Let LinuxCBT DBMS Edition cost-effectively sharpen your MySQL 5 DBMS skills!

Recommended Prerequisites for:

• Any LinuxCBT Operating System Course /Classic|EL-4|SUSE|Debian Editions/
  • Open mind & determination to master Linux and related open-source applications
  • Basic understanding of networking concepts
  • Access to a PC to follow the exercises

Modules:

• 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
• top