You are using an outdated browser. For a faster, safer browsing experience, upgrade for free today.

Modules

Details

Release Info

Long baked-into the L[AE]MP stack is MySQL for Relational DBMS services. MySQL, is feature-filled, reliable and scalable. Interfaces abound to allow you to be productive ASAP. It's CLI access control is very useful, offering very easy control of your data. MySQL is a must-study. Spend some time on the basics of MySQL in this release.

Release Syllabus

MySQL DBMS Engine

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
  •  

Tokyo Time

16:9

Rate

1.25x

Watched

1

Completed

1 of 5

Recent