Login

Complete MySQL Database Administration Course

MySQL DBA Training to get your Dream IT Job

  • 0.0 (0 Reviews)
  • 0 Students
  • 182 Lessons
  • Imran Afzal

Course Overview

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



See more

FAQ

FAQ area empty

Course curriculum

Requirment

  • Positive attitude and desire to learn

  • Passion to become SQL database administrator

  • A PC or MAC with at least 4-8G of RAM

Outcomes

  • You will be able to apply for SQL developer, Database administrator, Business Analyst, and Business Intelligence job opportunities

  • You will learn 150+ SQL queries

  • Administration of SQL database through Linux Operating System

  • You will manage SQL servers in a corporate environment

  • Troubleshoot everyday SQL database related issues

  • Know how to answer specific business questions by using SQL’s aggregate functions

Instructor

...
Imran Afzal

0.0

  • ... 0 Student
  • ... 2 Courses
  • ... 0 review

View Details

Reviews

Rate this course :

Remove all