fbpx
  • LOGIN
  • No products in the cart.

MySQL is the most popular open source database. This training is your first step towards gaining a strong foundation and hands-on experience in using and administering this open source relational databases.

You will gain an understanding of the core concepts and advanced tools and techniques to manage data and administer the MySQL Database. Through this training you will gain the ability to create and administer your own MySQL Database and manage data.

 

Course Objective

After completing this course, you should be able to:
• Identify features of MySQL database
• Understand benefits of an open source database
• Explain the basics of relational databases
• Create an effective MySQL database
• Create database objects using SQL
• Use MySQL command-line client to populate and manipulate data
• Access a database from MySQL client tools or applications
• Use SELECT statements to query data
• Use various data types based on requirement
• Use SQL to modify structure of your database
• Create indexes and keys
• Use Join operation to query data from multiple tables
• Explain database transactions
• Use built-in MySQL functions
• Export and import database data
• Configure MySQL server and client programs
• Configure simple and complex replication topologies
• Create and manage users
• Define and implement a backup strategy
• Perform physical and logical backups of your data
• Protect your data from common security risks
• Use server logs and other tools to monitor database activity

Prerequisites

  • Basic computing knowledge
  • Working knowledge of any command-line program
  • Basic understanding of relational database systems is an added advantage

Who should attend

This course can be beneficial for people having the below professional background:

• Database Developers

• Application Developers

• Database Designers

• Database Administrators

 

 

 

Program Duration: 30 hours

Program Language: English / Arabic

Location: EPSILON TRAINING CENTER | Head Office

 

Participants will be granted a completion certificate from Epsilon Training Institute, USA if they attend a minimum of 80 percent of the direct contact hours of the Program and after fulfilling program requirements (passing both Final Exam and Project to obtain the Certificate)

 

Course Outline

Getting Started with MySQL and Installing MySQL

Objectives: At the end of this module, you will be able to:

• Understand MySQL products, professional services and support for your operating system.

• Define a relational database management system (RDBMS) and describe its structure.

• Explain the use of SQL and MySQL with relational databases

• Define data definition language (DDL) and data manipulation language (DML)

• Understand MySQL client/server model and work with MySQL connectors.

• Install the MySQL server on Linux, start and stop MySQL server

• Interact with the server using the MySQL command-line client.

• Log your MySQL command-line client session to a text file.

• Use MySQL Workbench to interact with the server.

Topics:

• MySQL Overview

• Basics of Relational Databases

• Entities and Relationships

• SQL Language and MySQL

• SQL data definition language and data manipulation language

• MySQL Client/Server Model

• MySQL Connectors

• Installation of the MySQL server

• Starting and stopping MySQL server

• Using the MySQL client

• MySQL Workbench

Hands On

• Install and Configure MySQL Server, Client and MySQL workbench

• Using MySQL Workbench

• Creating the ‘world’ Database & Explore the Schema

Designing Your Database and Understand Data Types

Objectives: At the end of this module, you will be able to:

• Describe database modeling

• Use primary keys and foreign keys to achieve referential integrity

• Normalize your data model

• Design your new database

• Model a database using MySQL workbench and view the structure.

• Evaluate a database design

• Explain the use of different data types in database design

• Choose the correct data types while modeling data

• Describe the considerations for selecting data types

Topics:

• Database Modeling

• Keys and Normalization

• Database Design

• Viewing and Evaluating a Database

• Data Types and Database Design

• Numeric, Temporal and String Data Types

• Character Set and Collation Support

• Data Type Considerations

• Meaning of NULL

Hands On:

• Explore an Existing Database

• Designing a New Database

• Selecting Data Types for your New Database

Creating Databases and Tables

Objectives: At the end of this module, you will be able to:

• Create a database and add tables

• Use the SHOW CREATE TABLE statement

• Set column and table options

• Create indexes, keys, and constraints

• Delete a database.

• Create and delete a table.

• Add and remove table columns

• Modify table columns.

Topics:

• Creating a Database

• Creating a Table

• Showing How a Table Was Created

• Table Options

• Column Options

• Indexes, Keys, and Constraints

• Deleting database and tables

• Creating New Table Using an Existing Table

• Creating a Temporary Table

• Copying an Existing Table Structure

• Adding, removing and modifying table columns and indexes

Hands On:

• Creating a Database

• Deleting a Database

• Creating and Deleting a Table

• Altering Table Columns

Querying and Modifying Table Data

Objectives: At the end of this module, you will be able to:

• Retrieve database data using the SELECT statement from your database.

• Use the SELECT statement clauses: FROM, DISTINCT, WHERE, ORDER BY, and LIMIT

• Create views

• Query data from application code

• Export and import a delimited file

Topics:

• The SELECT Statement

• Creating Views

• Querying Data from an Application

• Exporting and Importing a Delimited File

• Troubleshooting: Authorization Levels

• Manipulating Data and inserting records

• Replacing and updating existing records

• Deleting records

• Exporting and importing a script

• Multistatement Transactions

• Modifying Data from an Application

Hands On:

• Executing Basic Queries

• Importing a Delimited File

• Performing Basic Queries on Imported Data

• Performing Basic Queries from a PHP Application

• Exporting Table Data for MySQL Database

• Inserting, Modifying and Deleting Data

• Exporting a Script

Joining Tables and Functions

Objectives: At the end of this module, you will be able to:

• Explain the concept of a join

• Use the JOIN keyword to query multiple tables

• Execute inner and outer joins

• Use the UNION keyword to combine multiple queries

• Use built-in functions in MySQL

• Describe and use: string functions, Date and time functions, Numeric functions.

• Use aggregate functions with the SELECT statement

Topics:

• Querying Multiple Tables

• Joining Tables with SELECT

• Inner Joins

• Outer Joins

• Table Name Aliases

• Functions in MySQL Expressions

• Using Functions

• String Functions

• Date and time functions

• Numeric Functions

• Aggregate Functions

• Spaces in Function Names

Hands On:

• Using Joins in Queries

• Using Unions in Queries

• Using Built-In, String, and Date and Time Functions

• Using Numeric Functions

• Using Aggregate Functions

MySQL Architecture and Administration Basics

Objectives: At the end of this module, you will be able to:

• Explain how MySQL processes, stores, and transmits data

• Configure InnoDB tablespaces

• Explain how MySQL uses memory

• Configure the InnoDB buffer pool

• List some of the available plug-ins

Topics:

• Architectural Overview

• Understand how MySQL Transmits Data and Processes Requests

• Understand how MySQL Stores Data and Uses Memory

• Tablespaces, Redo and Undo Logs

• Plug-in Interface

Hands On:

• Configuring Tablespaces

• Configuring the Buffer Pool

Configuring & Monitoring MySQL Server

Objectives: At the end of this module, you will be able to:

• Describe database modelling.

• Use primary and foreign keys to achieve referential integrity.

• Normalize your data model.

• Design your new database.

• Model a database using MySQL workbench and view the structure.

• Evaluate a database design.

• Explain the use of different data types in database design.

• Choose the correct data types while modelling data.

Topics:

• Server Configuration Options

• Option Files and System Variables

• Launching Multiple Servers on the Same Host

• Monitoring Tools & Techniques

• MySQL Enterprise Audit

• MySQL Enterprise Monitor

• Monitoring User Activity

Hands On:

• Modifying the Configuration File

• Changing Dynamic Settings

• Configuring the Slow Query Log

• Using Performance Schema

User Management

Objectives: At the end of this module, you will be able to:

• Create user accounts.

• Design a permission structure.

• Control user permissions

• Grant access to system operations

• Authenticate by using plug-ins

• Expire accounts manually and automatically.

• Reset a forgotten root password.

Topics:

• Creating and Modifying User Accounts

• Configuring Passwords

• Authentication Plug-Ins

• Granting Permissions & Grant Tables

Hands On:

• Creating Users

• Granting Permissions

Security

Objectives: At the end of this module, you will be able to:

• Recognize common security risks

• List security problems and counter-measures for networks, passwords, operating systems, file systems and applications.

• Protect your data from interception and access.

• Use SSL for secure MySQL server connections.

• Use SSH to create a secure remote connection to MySQL.

Topics:

• Understanding Security Risks

• Network Security & Secure Connections

• Password & Operating System Security

Hands On:

• Enable SSL Connections on the MySQL Server

Backup Tools & Technique

Objectives: At the end of this module, you will be able to:

• Distinguish between the different types of backup

• State advantages and disadvantages of the various backup techniques

• Implement a backup strategy

• Use MySQL Enterprise Backup to perform consistent backups.

• Use the mysqldump and mysqlpump utilities to perform logical backups

• Understand when and how to use raw file backups

• Back up the binary log

Topics:

• Understanding Backups & Backup Techniques

• Creating a Backup Strategy

• MySQL Backup Tools

• Raw Backup Methods

Hands On:

• Backing up using mysqldump and mysqlpump

Course Curriculum

Getting Started with MySQL and Installing MySQL
Designing Your Database and Understand Data Types
Creating Databases and Tables
Querying and Modifying Table Data
Joining Tables and Functions
MySQL Architecture and Administration Basics
Configuring & Monitoring MySQL Server
User Management
Security
Backup Tools & Technique

Drop Us A Query

6878 STUDENTS ENROLLED

    Related Courses Widget

    Top Rated Course

    Copyright © 2018 Epsilon Registered in Egypt with company no. 118268
    X