Complete MySQL Database Administration Course

MySQL DBA Training to get your Dream IT Job
Complete MySQL Database Administration Course
File Size :
9.99 GB
Total length :
27h 54m

Category

Instructor

Imran Afzal

Language

Last update

12/2022

Ratings

4.4/5

Complete MySQL Database Administration Course

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

Complete MySQL Database Administration Course

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 (CentOS8)

Lecture 17 Download and Install Linux Server (CentOS9 Stream)

Lecture 18 Virtual machine management

Section 3: Linux Basics for a DB Administrator

Lecture 19 Welcome to Linux basic administration

Lecture 20 Important Things to Remember in Linux

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

Lecture 22 What is root?

Lecture 23 Directory Listing Attributes

Lecture 24 Linux Command Syntax

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

Lecture 26 Changing Password

Lecture 27 Files and Directory Permissions (chmod)

Lecture 28 Help Commands

Lecture 29 Adding Text to Files

Lecture 30 Pipes ( | )

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

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

Lecture 33 Linux File Editor (vi)

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

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

Lecture 36 Process monitor commands (ps and top)

Lecture 37 crontab command

Lecture 38 Linux network files and commands

Lecture 39 SCP – Secure Copy Protocol

Lecture 40 System Updates and Repos (rpm, yum)

Section 4: Download, Install and Configure MySQL

Lecture 41 Weclome To Download, Install and Configure MySQL Section

Lecture 42 Brief History, Forks of MySQL and Cloud Computing

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

Lecture 44 Download, Install and Configure MySQL (CentOS8)

Lecture 45 Download, Install and Configure MySQL (CentOS9 Stream)

Lecture 46 Install MySQL Workbench (CentOS8)

Lecture 47 Install MySQL Workbench (CentOS 9 Stream)

Lecture 48 Download and restore sample Sakila DB

Section 5: Database Fundamentals and Design

Lecture 49 Welcome To Database Fundamentals and Design Section

Lecture 50 What is Data?

Lecture 51 What is Database?

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

Lecture 53 How is data stored ?

Lecture 54 How is Data Saved in MySQL (linux)

Lecture 55 MySQL Storage engine and Types

Lecture 56 What is a Table, COLUMN and ROW ?

Lecture 57 What is a Key?

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

Lecture 59 What is a Transaction and ACID properties?

Lecture 60 Database Normalization AND Different forms of Database Normalization

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

Section 6: Introduction to SQL Commands

Lecture 62 Welcome to Basic SQL Commands Section

Lecture 63 SQL Statement and Types

Lecture 64 DML Statement with examples

Lecture 65 DDL Statements with examples

Lecture 66 DCL Statement with examples

Lecture 67 TCL Statement with examples

Section 7: Query and Data Manipulation

Lecture 68 Welcome to Query and Data Manipulation Sections

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

Lecture 70 SELECT Statement in detail

Lecture 71 Operators, Expressions and Conditions

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

Lecture 73 Select from two tables – JOINS

Lecture 74 Different Types of JOINS

Lecture 75 What is a Sub Query?

Lecture 76 INSERT Statement in detail

Lecture 77 Load Data from a CSV file to MySQL

Lecture 78 Export data from MySQL to a CSV

Lecture 79 UPDATE Statement in detail

Lecture 80 DELETE Statement in detail

Lecture 81 What is a Stored Procedure

Lecture 82 What Is a View?

Lecture 83 What is a Function?

Lecture 84 What is a Trigger?

Lecture 85 What is an INDEX?

Lecture 86 Clustered ,NonClustered Indexes and Index Design considerations

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

Section 8: MySQL Architecture and Option Files

Lecture 88 Welcome to MySQL Architecture and Option Files Section

Lecture 89 MySQL Architecture Overview

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

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

Lecture 92 Options Scope and What options are in affect ?

Section 9: MySQL Backups and Recovery

Lecture 93 Welcome to MySQL Backups and Restore Section

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

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

Lecture 96 Binary Logs

Lecture 97 The mysqldump Utility

Lecture 98 Restore of database using mysql and Workbench

Lecture 99 Table level Backup and Restore

Lecture 100 Point In Time Recovery in MySQL and Lab

Lecture 101 XtraBackup Utility by Percona, Functionality and Installation

Lecture 102 Full Backup and Restore using Percona Xtrabackup

Lecture 103 Lab for mysqldump and Backups using MySQL Workbench

Lecture 104 Incremental Backup and Restore using Percona XtraBackup

Lecture 105 Schedule backups (cronjobs)

Section 10: Users and Privileges Management

Lecture 106 Welcome to Users and Privileges Management Section

Lecture 107 Understanding MySQL Security

Lecture 108 Root User in MySQL

Lecture 109 CREATE USER and ALTER USER Statement

Lecture 110 Create New User (MySQL Workbench)

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

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

Lecture 113 GRANT Tables and Checking Privileges

Lecture 114 REVOKE Statement and Revoking Privileges

Lecture 115 DROP USER Statement

Lecture 116 Roles in MySQL

Section 11: Monitoring MySQL Server

Lecture 117 Welcome to Monitoring MySQL Server Section

Lecture 118 MySQL Server Performance Monitoring Basics

Lecture 119 The General Query Log

Lecture 120 The Slow Query Log

Lecture 121 The Error Log

Lecture 122 Optimizing my.cnf for MySQL performance

Lecture 123 InnoDB Engine Status

Lecture 124 Query Optimization TIPS, EXPLAIN Statement and Execution Plans

Lecture 125 Query Optimization Demo

Section 12: MySQL Replication

Lecture 126 Welcome to MySQL Replication Sections

Lecture 127 MySQL Replication and Its Advantages

Lecture 128 MySQL Replication Methods , Synchronization Types and Replication Formats

Lecture 129 MySQL Replication Setup Demo

Lecture 130 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

Course Information:

Udemy | English | 27h 54m | 9.99 GB
Created by: Imran Afzal

You Can See More Courses in the Developer >> Greetings from CourseDown.com

New Courses

Scroll to Top