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/
o Open mind & determination to master Linux and related open-source applications

Read More

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/
o Open mind & determination to master Linux and related open-source applications
o Basic understanding of networking concepts
o Access to a PC to follow the exercises

Modules:

* MySQL Installation
o Introduction to MySQL 5
o Discuss DBMS system specifications
o Identify existing MySQL-installed components
o Explore file-system layout
o Identify & discuss the various downloadable formats /source|binary|packaged/
o Download MySQL packages to a local repository
o Discuss the key DBMS components
o Confirm md5sums of components
o Install MySQL server package and examine defaults
o Identify key server-side administrative utilities
o Inspect the contents of the default DBMS data-directory
o Install MySQL Client package and explore its contents
*
* Terminal Monitor Shell Client
o Discuss Terminal Monitor concepts
o Explain the default privileges configuration
o Connect to the DBMS using anonymous privileges
o Explore Terminal Monitor's help facility and useful escape sequences
o Identify the default schemas
o Execute useful SQL functions
o Demonstrate how to execute SQL queries from the shell in non-interactive mode
*
* User Administration
o Identify the currently logged-in user
o Connect to MySQL DBMS as root and explore the interface
o Attempt to connect to the DBMS as invalid users and discuss the results
o Attempt to connect from a remote host and evaluate results
o Discuss the primary methods of tightening the default security scheme
o Tighten the default privileges for the root user
o Explore the global 'user' grant table & discuss attributes
o Secure anonymous user accounts
o Remove anonymous privileges and confirm enhanced security policy
o Discuss and explore the grant tables and applicable attributes
o Identify the ancillary grant tables that permit granular filtration
o Create new DBMS users and confirm connectivity
o Remove superfluous user accounts
o Discuss permissible host values when defining users
o Create remote super users and test connectivity
o Discuss privileges scopes concepts & applications
o Identify grant tables related to privileges scopes
o Use GRANT to create and manipulate user-privileges
o Create unprivileged accounts and explore capabilities
o Use REVOKE to remove privileges
o Use DROP to discard superfluous accounts
o Maintain user passwords using SET and UPDATE
*
* Show Commands & Options Files
o Explain the usages of key SHOW commands
o Expose DBMS privileges for currently authenticated user
o Expose DBMS privileges for other defined users
o List all databases available on the system
o Show the DDL statements used to create various MySQL-managed databases
o Examine the default supported character sets
o Reveal the structure of defined tables
o Show the DDL statements used to create various tables
o List the supported Storage Engines by the current binary-build of MySQL
o Examine the MyISAM Storage Engine tables on the file system & discuss key file types
o Show the schemas of various tables and discuss the results
o Examine the currently executing processes, including connected users
o Initiate & KILL connections listed in the process-list table
o Examine key status variables on the running server
o Show table status information, revealing used-space, length, create-time, etc.
o Discuss the default search-path for options files by MySQL clients
o Examine and secure the MySQL history file to prevent superfluous disclosure of queries
o Define a personal MySQL options file
o Explain the supported section headers in MySQL options files
o Define useful run-time directives for MySQL clients and examine results
o Define a useful, custom MySQL prompt on a global and per-user basis & examine usefulness
o Set default database variable in per-user options files and examine results
*
* Key MySQL Client Utilities
o Focus on mysql terminal monitor utility
o Discuss & demonstrate mysql client modes
o Discuss command-line option types
o Output queries to standard /HTML|XML|TXT/ formats
o Exclude column-headers from output for easy import into other applications
o Use options file to control startup variables
o Focus on mysqladmin utility
o Compare & contrast mysqladmin batch-mode to mysql interactive mode
o Confirm the status of DBMS servers using mysqladmin
o Ascertain remote DBMS process list
o Create & drop remote databases using mysqladmin
o Administer remote user credentials using mysqladmin
o Focus on mysqldump utility
o Discuss mysqldump applications, features & benefits
o Dump all databases and related objects to an ASCII text backup file
o Examine the resulting dump file
o Dump select databases and examine resulting file
o Dumpy DBMS databases to a remote MySQL instance using mysqldump
o Focus on mysqlimport utility
o Create database container and table structure for subsequent imports
o Create matching data-feed for import by mysqlimport
o Import data-feed using default delimiters and record-separators
o Discuss mysqlimport defaults regarding data-directory and table names
o Examine importing duplicate records
*
* Key Data Manipulation Language (DML) Statements
o Discuss common DML statements
o Focus on SELECT DML statement
o Optimize SELECT statements with WHERE clauses to define criteria
o Discuss SELECT operators /=|=|!=|etc/
o Perform pattern matching using WHERE clause with LIKE operator & wildcards
o Influence the order of SELECT output using ORDER BY
o Extend criteria definition and optimize queryies using AND clause
o Return result-set INTO output ASCII text file using SELECT INTO
o Redirect result-set to AWK and extract interesting fields
o Use LIMIT with SELECT to return n number of rows
o Return total number of rows including LIMIT value
o Alias column headers using SELECT
o Concatenate results returned using SELECT
o Focus on INSERT DML statement
o Explain supported INSERT statements /VALUES|SET|SELECT/
o Describe table structure to determine column constraints
o INSERT VALUES specifying column names
o INSERT VALUES one-to-one without defined column headers
o Use SET with INSERT to indicate interesting columns
o Use VALUES to INSERT multiple rows in one statement
o Discuss INSERT SELECT applications
o Create table structure for using INSERT SELECT
o Populate newly-created table with data
o Use SELECT INSERT to move data between tables & evaluate results
o Focus on UPDATE DML statement
o Perform global table UPDATE with simple DML statement
o Specify columns to be updated and evaluate affected rows
o UPDATE interesting rows using criteria
o UPDATE n number of records using the LIMIT criteria operator
o Focus on DELETE DML statement
o Remove interesting records using WHERE clause and applicable critieria
o Intro to the ALTER Data Definition Language (DDL) statement
*
* Key Data Types
o Focus on Numeric data-types
o Use describe to expose defined data-types for columns
o Discuss unsigned and auto incremented column attributes
o Discuss /TINYINT|SMALLINT|MEDIUMINT|INT|BIGINT/ capacity and applications
o Discuss FLOAT capacity and applications
o Discuss DOUBLE capacity and applications
o Discuss DECIMAL capacity and applications
o Discuss BIT capacity and applications
o Define a table with TINYINT column
o Populate TINYINT column with INSERT and exceed its limits and evaluate resutls
o Use ALTER to increase the capacity of the TINYINT column to SMALLINT
o Populate SMALLINT column and evaluate results
o ALTER table and add columns with greater numeric capacity
o Define a DECIMAL column and store values
o Focus on String data-types
o Explain features and limitations of CHAR type fields
o INSERT strings with unpreserved characters and evaluate results
o Compare & contrast VARCHAR & CHAR type fields; fixed vs. varaible-length
o ALTER table to convert interesting fields from CHAR to VARCHAR
o Create Perl script to generate values for VARCHAR columns and evaluate results
o Discuss /TEXT|TINYTEXT|MEDIUMTEXT|LONGTEXT/ column types
o Discuss /BLOB|TINYBLOB|MEDIUMBLOB|LONGBLOB/ column types
o ALTER table to support LONGTEXT and import data and evaluate results
o Discuss /BINARY|VARBINARY/ usages and limits
o Explain the usages of ENUM column types
o Explore predefined ENUM column types in system tables
o Define ENUM fields, insert data and evaluate DBMS behavior
o Focus on Date data-types
o Discuss DATE column type features (resolution & range)
o ALTER table structure to support DATE-based column
o INSERT invalid date and evaluate results
o Discuss DATETIME column type features (resolution & range)
o ALTER table structure to support DATETIME-based column
o INSERT valid date & time value and evaluate results
o Discuss how MySQL implements dummy dates
o ALTER table structure to support TIMESTAMP-based column
o Discuss TIMESTAMP column type features (resolution & range)
o Discuss NULL-enabled auto-timestamp (dummy-time) invocated columns
o Explain MySQL's default date syntax
o Insert Date & Time values into MySQL columns using mixed delimiters
o Insert Date & Time values into MySQL columns without delimiters
o Recap supported data-types
*
* Key Data Definition Language (DDL) Statements
o Focus on CREATE DDL statement
o Describe how MySQL maps database containers to the file system
o CREATE DATABASE container to house new tables
o Define the requirements /auto_increment|primary key|etc/ of new base tables
o Define the CREATE TABLE syntax for new base tables based on selected data types
o Execute statement and evaluate results of recently-created tables
o Use SHOW CREATE TABLE to examine DDL syntax for reference
o CREATE TABLE with NOT-NULL column enforcements
o Populate tables with useful values and evaluate results
o INSERT VALUES with DEFAULT for auto-populated columns and evaluate results
o Duplicate table structure using CREATE TABLE and evaluate results
o Populate destination table with data from the source table using INSERT
o Discuss Temporary tables features and applications
o CREATE TEMPORARY TABLES and populate with useful data
o Rename tables using RENAME TABLE as an alternative to ALTER TABLE
o Focus on DROP DDL statement
o Describe the objects that are affected using DROP
o DROP DBMS objects and evaluate file system implications
o Focus on CREATE INDEX DDL statement
o Discuss the importance of indexes to improved data-retrieval
o Discuss MyISAM storage engine per-index limits
o Identify default MyISAM index files
o Explore defined indices
o Define UNIQUE indices
o Compare & contrast table & index storage requirements
o Generate data-set to be imported into an indexed column
o Compare & contrast query-execution times with and without indices
*
* JOINS
o Discuss the features & benefits of using JOINS to access normalized data
o Normalize the data structure, using ALTER TABLE, to prepare for using joins
o Use seq to generate normalized data and import using mysqlimport
o Discuss the various types of supported JOINS /EQUI|NATURAL|LEFT|RIGHT|INNER|OUTER/
o Define and execute EQUIJOINS
o Define and execute NATURAL JOINS
o Define JOINS using fully-justified DBMS object names
o Use table and column aliases with JOINS
*
* VIEWS & TRIGGERS
o Discuss the features & benefits of using VIEWS
o Use DESCRIBE to identify interesting VIEW candidate-columns
o Explain VIEW-creation syntax
o Create useful VIEWS and evaluate results
o Create VIEWS using column-aliases
o Update VIEWS using CREATE OR REPLACE VIEW & ALTER VIEW
o Create VIEWS based on EQUIJOINS
o Demonstrate how to UPDATE base-table data using VIEWS
o Remove superfluous VIEWS using DROP VIEW
o Focus on TRIGGERS
o Discuss MySQL's TRIGGER implementation rules
o Discuss TRIGGER t