Complete Mysql Training For Data Analysis On Linux

Become MySQL Database Expert to get your Dream IT Job

What you'll learn
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
Requirements
Positive attitude and desire to learn
Passion to become SQL database administrator
A PC or MAC with at least 4-8G of RAM
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 machineIf 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 SyllabusSection 1:Introduction to DatabaseRDBMSWhat is SQL?Top 5 RDBMSIntroduction to MySQL ServerDifferent editions of MySQLSection 2:MySQL Server pre-requisiteLab designWhat is virtualization?Download and Install VMWare Workstation PlayerOptional - Download and Install Oracle VirtualBoxCreate a Virtual MachineDownload and Install Linux ServerVirtual Machine ManagementSection 3:Important things to remember in LinuxLinux navigation commandsWhat is root?Directory listing attributesLinux command syntaxCreating files and directories and its permissionsChanging passwordHelp commandsAdding text to filesPipes (|)File maintenance commandsFile display commandsLinux file editorUser account management and sudo accessProcess monitor commandsCrontabLinux network files and commandsSecure copy (SCP)System updates and repos (rpm and yum).Section 4:Forks of MySQL and Cloud ComputingMySQL installationDownload, Install and Configure MySQLInstall MySQL WorkbenchDownload and restore sample Sakila DBQuizSection 5:What is Data and Database and RDBMS?How is data stored?How is data saved in MySQL (Linux)MySQL Storage engine and TypesWhat 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 formsCreate your first Database, Table and Populate table with dataQuizSection 6:SQL Statement and TypesDML Statement with examplesDDL Statements with examplesDCL Statements with examplesTCL Statements with examplesQuizSection 7:Create TABLE(s) and Temp Table(s)SELECT Statement in detailOperators, Expressions and ConditionsWHERE Clause, ORDER BY, HAVING BY, GROUP BY ClauseSelect from two tables – JOINS and different Types of JOINSWhat is a Sub Query?INSERT, UPDATE, and DELETE statementsLoad data from a CSV file to MySQL and vice versaStored procedureWhat is view, function, trigger and indexClustered, non-clustered Indexes and Index Design considerationsINDEX Cardinality, Table Fragmentation, USE INDEX, FORCE INDEX and LABQuizSection 8:MySQL Architecture OverviewMySQL Programs and invocationSpecifying Program Options and Option FilesHow to edit the configuration file (.cnf)Options scopeWhat options are in affect?QuizSection 9:MySQL Backups, why we need backup, RTO and RPOType of Backups in MySQL, offsite storage, encryption and restore testingBinary logsThe mysqldump utilityRestore of database using mysql and WorkbenchTable level Backup and RestorePoint In Time Recovery in MySQLXtraBackup Utility by Percona, Functionality and InstallationFull Backup and Restore using Percona XtraBackupLab for mysqldump and Backups using MySQL WorkbenchIncremental Backup and Restore using Percona XtraBackupSchedule backups (cronjobs)QuizSection 10:Understanding MySQL SecurityRoot User in MySQLCREATE USER and ALTER USER StatementCreate New User (MySQL Workbench)MySQL Privileges , Privileges Control and Some important Privileges in MySQLGRANT Statement , WITH GRANT Option and Granting Privileges to MySQL UserGRANT Tables and Checking PrivilegesREVOKE Statement and Revoking PrivilegesDROP USER StatementRoles in MySQLQuizSection 11:MySQL Server Performance Monitoring BasicsThe General Query LogThe Slow Query LogThe Error LogOptimizing configuration for MySQL performanceDB Engine StatusQuery Optimization TIPS, EXPLAIN Statement and Execution PlansQuery Optimization DemoQuizSection 12:MySQL Replication and its AdvantagesMySQL Replication MethodsSynchronization TypesReplication FormatsMySQL Replication Setup DemoQuiz

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Course overview

Lecture 3 Introduction to database

Lecture 4 What is RDBMS?

Lecture 5 What is SQL?

Lecture 6 Top 5 RDBMS using SQL

Lecture 7 Introduction to MySQL Server

Lecture 8 Editions of MySQL Server

Section 2: Lab design and setup

Lecture 9 Welcome to the lab design and setup

Lecture 10 Lab Setup Pre-requisite

Lecture 11 Lab design

Lecture 12 What is Virtualization?

Lecture 13 Download and Install VMWare Workstation Player

Lecture 14 Optional - Download and Install Oracle Virtualbox

Lecture 15 Create a Virtual Machine

Lecture 16 Download and Install Linux server

Lecture 17 Virtual machine management

Section 3: Linux Basics for a DB Administrator

Lecture 18 Welcome to Linux basic administration

Lecture 19 Important Things to Remember in Linux

Lecture 20 Linux navigation commands (cd, pwd and ls)

Lecture 21 What is root?

Lecture 22 Directory Listing Attributes

Lecture 23 Linux Command Syntax

Lecture 24 Creating Files and Directories (touch, cp, vi, mkdir)

Lecture 25 Changing Password

Lecture 26 Files and Directory Permissions (chmod)

Lecture 27 Help Commands

Lecture 28 Adding Text to Files

Lecture 29 Pipes ( | )

Lecture 30 File Maintenance Commands (cp, rm, mv, mkdir, rmdir)

Lecture 31 File Display Commands (cat, less, more, head, tail)

Lecture 32 Linux File Editor (vi)

Lecture 33 User Account Management (useradd, groupadd, usermod, userdel, groupdel)

Lecture 34 Switch Users and Sudo Access (su, sudo)

Lecture 35 Process monitor commands (ps and top)

Lecture 36 crontab command

Lecture 37 Linux network files and commands

Lecture 38 SCP - Secure Copy Protocol

Lecture 39 System Updates and Repos (rpm, yum)

Section 4: Download, Install and Configure MySQL

Lecture 40 Weclome To Download, Install and Configure MySQL Section

Lecture 41 Brief History, Forks of MySQL and Cloud Computing

Lecture 42 MySQL installation steps (prereq, distribution, versions etc)

Lecture 43 Download, Install and Configure MySQL

Lecture 44 Install MySQL Workbench

Lecture 45 Download and restore sample Sakila DB

Section 5: Database Fundamentals and Design

Lecture 46 Welcome To Database Fundamentals and Design Section

Lecture 47 What is Data?

Lecture 48 What is Database?

Lecture 49 What is Relational Database and Relational Database Management System (RDBMS) ?

Lecture 50 How is data stored ?

Lecture 51 How is Data Saved in MySQL (linux)

Lecture 52 MySQL Storage engine and Types

Lecture 53 What is a Table, COLUMN and ROW ?

Lecture 54 What is a Key?

Lecture 55 What is Primary Key , Foreign Key, Unique Key etc ?

Lecture 56 What is a Transaction and ACID properties?

Lecture 57 Database Normalization AND Different forms of Database Normalization

Lecture 58 Create your first Database, Table and Populate table with data

Section 6: Introduction to SQL Commands

Lecture 59 Welcome to Basic SQL Commands Section

Lecture 60 SQL Statement and Types

Lecture 61 DML Statement with examples

Lecture 62 DDL Statements with examples

Lecture 63 DCL Statement with examples

Lecture 64 TCL Statement with examples

Section 7: Query and Data Manipulation

Lecture 65 Welcome to Query and Data Manipulation Sections

Lecture 66 Create TABLE(s) and Temp Table(s)

Lecture 67 SELECT Statement in detail

Lecture 68 Operators, Expressions and Conditions

Lecture 69 WHERE Clause, ORDER BY Clause , HAVING By Clause, GROUP BY Clause

Lecture 70 Select from two tables - JOINS

Lecture 71 Different Types of JOINS

Lecture 72 What is a Sub Query?

Lecture 73 INSERT Statement in detail

Lecture 74 Load Data from a CSV file to MySQL

Lecture 75 Export data from MySQL to a CSV

Lecture 76 UPDATE Statement in detail

Lecture 77 DELETE Statement in detail

Lecture 78 What is a Stored Procedure

Lecture 79 What Is a View?

Lecture 80 What is a Function?

Lecture 81 What is a Trigger?

Lecture 82 What is an INDEX?

Lecture 83 Clustered ,NonClustered Indexes and Index Design considerations

Lecture 84 INDEX Cardinality, Table Fragmentation, USE INDEX, FORCE INDEX and LAB

Section 8: MySQL Architecture and Option Files

Lecture 85 Welcome to MySQL Architecture and Option Files Section

Lecture 86 MySQL Architecture Overview

Lecture 87 MySQL Programs and invocation, Specifying Program Options and Option Files

Lecture 88 my.cnf file and How to edit my.cnf (LAB)

Lecture 89 Options Scope and What options are in affect ?

Section 9: MySQL Backups and Recovery

Lecture 90 Welcome to MySQL Backups and Restore Section

Lecture 91 MySQL Backups, why we need backup, RTO and RPO

Lecture 92 Type of Backups in MySQL, Offsite Storage, Encryption and Restore Testing

Lecture 93 Binary Logs

Lecture 94 The mysqldump Utility

Lecture 95 Restore of database using mysql and Workbench

Lecture 96 Table level Backup and Restore

Lecture 97 Point In Time Recovery in MySQL and Lab

Lecture 98 XtraBackup Utility by Percona, Functionality and Installation

Lecture 99 Full Backup and Restore using Percona Xtrabackup

Lecture 100 Lab for mysqldump and Backups using MySQL Workbench

Lecture 101 Incremental Backup and Restore using Percona XtraBackup

Lecture 102 Schedule backups (cronjobs)

Section 10: Users and Privileges Management

Lecture 103 Welcome to Users and Privileges Management Section

Lecture 104 Understanding MySQL Security

Lecture 105 Root User in MySQL

Lecture 106 CREATE USER and ALTER USER Statement

Lecture 107 Create New User (MySQL Workbench)

Lecture 108 MySQL Privileges , Privileges Control and Some important Privileges in MySQL

Lecture 109 GRANT Statement , WITH GRANT Option and Granting Privileges to MySQL User

Lecture 110 GRANT Tables and Checking Privileges

Lecture 111 REVOKE Statement and Revoking Privileges

Lecture 112 DROP USER Statement

Lecture 113 Roles in MySQL

Section 11: Monitoring MySQL Server

Lecture 114 Welcome to Monitoring MySQL Server Section

Lecture 115 MySQL Server Performance Monitoring Basics

Lecture 116 The General Query Log

Lecture 117 The Slow Query Log

Lecture 118 The Error Log

Lecture 119 Optimizing my.cnf for MySQL performance

Lecture 120 InnoDB Engine Status

Lecture 121 Query Optimization TIPS, EXPLAIN Statement and Execution Plans

Lecture 122 Query Optimization Demo

Section 12: MySQL Replication

Lecture 123 Welcome to MySQL Replication Sections

Lecture 124 MySQL Replication and Its Advantages

Lecture 125 MySQL Replication Methods , Synchronization Types and Replication Formats

Lecture 126 MySQL Replication Setup Demo

Lecture 127 Congratulations

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