LinuxCBT.com

Syllabus

Focus: MySQL DBMS Engine

Duration: 35-Hours

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

LinuxCBT MySQL Edition

  •  
DEMO