Description

MySQL Server continues to be one of the world’s most popular, FREE and top databases.  Database is a heart of IT which is needed to stored all critical data and information.

MySQL is a DBMS, or database management system. It is developed, supported and distributed by Oracle, but since it is open-source it is freely available to anyone.  MySQL databases are relational, meaning that the data is split up between tables. MySQL is very fast and lightweight so you can run it alongside your other applications on a desktop or laptop. It can also be scaled up to take advantage of all the CPU power and memory available on a dedicated machine

If you want to become an expert in SQL database administration then this is the course for YOU.

This training course will provide you all the necessary skills you need to be a successful database administrator.

Here is the course outline:

Course Syllabus

Section 1:

   Introduction to Database
   RDBMS
   What is SQL?
   Top 5 RDBMS
   Introduction to MySQL Server
   Different editions of MySQL

Section 2:

   MySQL Server pre-requisite
   Lab design
   What is virtualization?
   Download and Install VMWare Workstation Player
   Optional – Download and Install Oracle VirtualBox
   Create a Virtual Machine
   Download and Install Linux Server
   Virtual Machine Management

Section 3:

   Important things to remember in Linux
   Linux navigation commands
   What is root?
   Directory listing attributes
   Linux command syntax
   Creating files and directories and its permissions
   Changing password
   Help commands
   Adding text to files
   Pipes (|)
   File maintenance commands
   File display commands
   Linux file editor
   User account management and sudo access
   Process monitor commands
   Crontab
   Linux network files and commands
   Secure copy (SCP)
   System updates and repos (rpm and yum).

Section 4:

   Forks of MySQL and Cloud Computing
   MySQL installation
   Download, Install and Configure MySQL
   Install MySQL Workbench
   Download and restore sample Sakila DB
   Quiz

Section 5:

   What is Data and Database and RDBMS?
   How is data stored?
   How is data saved in MySQL (Linux)
   MySQL Storage engine and Types
   What is a Table, COLUMN and ROW ?
   What is a Key?
   What is Primary Key, Foreign Key, Unique Key etc.
   What is a Transaction and ACID properties?
   Database Normalization AND its different forms
   Create your first Database, Table and Populate table with data
   Quiz

Section 6:

   SQL Statement and Types
   DML Statement with examples
   DDL Statements with examples
   DCL Statements with examples
   TCL Statements with examples
   Quiz

Section 7:

   Create TABLE(s) and Temp Table(s)
   SELECT Statement in detail
   Operators, Expressions and Conditions
   WHERE Clause, ORDER BY, HAVING BY, GROUP BY Clause
   Select from two tables – JOINS and different Types of JOINS
   What is a Sub Query?
   INSERT, UPDATE, and DELETE statements
   Load data from a CSV file to MySQL and vice versa
   Stored procedure
   What is view, function, trigger and index
   Clustered, non-clustered Indexes and Index Design considerations
   INDEX Cardinality, Table Fragmentation, USE INDEX, FORCE INDEX and LAB
   Quiz

Section 8:

   MySQL Architecture Overview
   MySQL Programs and invocation
   Specifying Program Options and Option Files
   How to edit the configuration file (.cnf)
   Options scope
   What options are in affect?
   Quiz

Section 9:

   MySQL Backups, why we need backup, RTO and RPO
   Type of Backups in MySQL, offsite storage, encryption and restore testing
   Binary logs
   The mysqldump utility
   Restore of database using mysql and Workbench
   Table level Backup and Restore
   Point In Time Recovery in MySQL
   XtraBackup Utility by Percona, Functionality and Installation
   Full Backup and Restore using Percona XtraBackup
   Lab for mysqldump and Backups using MySQL Workbench
   Incremental Backup and Restore using Percona XtraBackup
   Schedule backups (cronjobs)
   Quiz

Section 10:

   Understanding MySQL Security
   Root User in MySQL
   CREATE USER and ALTER USER Statement
   Create New User (MySQL Workbench)
   MySQL Privileges , Privileges Control and Some important Privileges in MySQL
   GRANT Statement , WITH GRANT Option and Granting Privileges to MySQL User
   GRANT Tables and Checking Privileges
   REVOKE Statement and Revoking Privileges
   DROP USER Statement
   Roles in MySQL
   Quiz

Section 11:

   MySQL Server Performance Monitoring Basics
   The General Query Log
   The Slow Query Log
   The Error Log
   Optimizing configuration for MySQL performance
   DB Engine Status
   Query Optimization TIPS, EXPLAIN Statement and Execution Plans
   Query Optimization Demo
   Quiz

Section 12:

   MySQL Replication and its Advantages
   MySQL Replication Methods
   Synchronization Types
   Replication Formats
   MySQL Replication Setup Demo
   Quiz

Who this course is for:

   Anyone who wants to start a career in SQL database administration
   Anyone who wants to have complete SQL database training to get a job in IT
   Anyone who wants to advance his/her career
   Anyone who wants to master the SQL command line skills
   College students
   IT professionals, teachers and instructors

Requirements

   Positive attitude and desire to learn
   Passion to become SQL database administrator
   A PC or MAC with at least 4-8 GB of RAM

Last Updated 7/2022