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
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.82 GB
Created by: Imran Afzal
You Can See More Courses in the Developer >> Greetings from CourseDown.com