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
