captcharefresh

Powered by ChronoForms - ChronoEngine.com

ENQUIRY
LIVE CHAT

Mysql

Duration: 5 Days

What you will learn

The MySQL for Database Administrators training is designed for DBAs and other database professionals. Expert Oracle University instructors will teach you how to install and configure the MySQL Server, set up replication and security, perform database backups and performance tuning and protect MySQL databases.

  • Learn To
    • Describe the MySQL Architecture.
    • Install and upgrade MySQL.
    • Configure MySQL server options at runtime.
    • Evaluate data types and character sets for performance issues.
    • Understand the use of the InnoDB storage engine with MySQL.
    • Perform backup and restore operations.
  • Benefits to You
    • After taking this course, you'll have a greater understanding of new enhancements made available in MySQL to improve performance, scalability, reliability and manageability for your Web, Cloud and embedded application requirements.
  • Live Virtual Class Format
    • A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.
  • Audience
    • Database Administrators
    • Developer
  • Related Training
    • Required Prerequisites
    • Have some experience with relational databases and SQL
    • MySQL Fundamentals Ed 1
  • Course Objectives
    • Describe the MySQL Architecture
    • Install and Upgrade MySQL
    • Use the INFORMATION_SCHEMA database to access metadata
    • Perform the MySQL start and shutdown operations
    • Configure MySQL server options at runtime
    • Use available tools including MySQL Workbench
    • Evaluate data types and character sets for performance issues
    • Understand data locking in MySQL
    • Understand the use of the InnoDB storage engine with MySQL
    • Maintain integrity of a MySQL installation
    • Use triggers for administration tasks
    • Use Enterprise Audit and Pluggable Authentication
    • Configure advanced replication topologies for high availability
    • Describe introductory performance tuning techniques
    • Perform backup and restore operations
    • Automate administrative tasks with scheduled events

Course Topics

  • Introduction
    • MySQL Overview, Products, Services
    • MySQL Services and Support
    • Supported Operating Services
    • MySQL Certification Program
    • Training Curriculum Paths
    • MySQL Documentation Resources
  • MySQL Architecture
    • The client/server model
    • Communication protocols
    • The SQL Layer
    • The Storage Layer
    • How the server supports storage engines
    • How MySQL uses memory and disk space
    • The MySQL plug-in interface
  • System Administration
    • Choosing between types of MySQL distributions
    • Installing the MySQL Server
    • The MySQL Server installation file structure
    • Starting and stopping the MySQL server
    • Upgrading MySQL
    • Running multiple MySQL servers on a single host
  • Server Configuration
    • MySQL server configuration options
    • System variables
    • SQL Modes
    • Available log files
    • Binary logging
  • Clients and Tools
    • Available clients for administrative tasks
    • MySQL administrative clients
    • The mysql command-line client
    • The mysqladmin command-line client
    • The MySQL Workbench graphical client
    • MySQL tools
    • Available APIs (drivers and connectors)
  • Data Types
    • Major categories of data types
    • Meaning of NULL
    • Column attributes
    • Character set usage with data types
    • Choosing an appropriate data type
  • Obtaining Metadata
    • Available metadata access methods Structure of INFORMATION_SCHEMA
    • Using the available commands to view metadata
    • Differences between SHOW statements and INFORMATION_SCHEMA tables The mysqlshow client program
    • Using INFORMATION_SCHEMA queries to create shell commands and SQL statements
  • Transactions and Locking
    • Using transaction control statement to run multiple SQL statements concurrently
    • The ACID properties of transactions
    • Transaction isolation levels
    • Using locking to protect transactions
  • Storage Engines
    • Storage engines in MySQL
    • InnoDB storage engine
    • InnoDB system and file-per-table tablespaces
    • NoSQL and the Memcached API
    • Configuring tablespaces efficiently
    • Using foreign keys to attain referential integrity
    • InnoDB locking
    • Features of available storage engines
  • Partitioning
    • Partitioning and its use in MySQL
    • Reasons for using partitioning
    • Types of partitioning
    • Creating partitioned tables
    • Subpartitioning
    • Obtaining partition metadata
    • Modifying partitions to improve performance
    • Storage Engine Support of Partitioning
  • User Management
    • Requirements for user authentication
    • Using SHOW PROCESSLIST to show which threads are running
    • Creating, modifying and dropping user accounts
    • Alternative authentication plugins
    • Requirements for user authorization
    • Levels of access privileges for users
    • Types of privileges
    • Granting, modifying and revoking user privileges
  • Security
    • Recognizing common security risks
    • Security risks specific to the MySQL installation
    • Security problems and counter-measures for network, operating system, filesystem and users Protecting your data
    • Using SSL for secure MySQL server connections
    • How SSH enables a secure remote connection to the MySQL server
    • Finding additional information for common security issues
  • Table Maintenance
    • Types of table maintenance operations
    • SQL statements for table maintenance
    • Client and utility programs for table maintenance
    • Maintaining tables for other storage engines
  • Exporting and Importing Data
    • Exporting Data
    • Importing Data
  • Programming Inside MySQL
    • Creating and executing Stored Routines
    • Describing stored routine execution security
    • Creating and executing triggers
    • Creating, altering and dropping events
    • Event execution scheduling
  • MySQL Backup and Recovery
    • Backup basics
    • Types of backup
    • Backup tools and utilities
    • Making binary and text backups
    • Role of log and status files in backups
    • Data Recovery
  • Replication
    • Managing the MySQL Binary Log
    • MySQL replication threads and files
    • Setting up a MySQL Replication Environment
    • Designing Complex Replication Topologies
    • Multi-Master and Circular Replication
    • Performing a Controlled Switchover
    • Monitoring and Troubleshooting MySQL Replication
    • Replication with Global Transaction Identifiers (GTIDs)
  • Introduction to Performance Tuning
    • Using EXPLAIN to Analyze Queries
    • General Table Optimizations
    • Monitoring status variables that affect performance
    • Setting and Interpreting MySQL server Variables
    • Overview of Performance Schema
  • Conclusion
    • Course Overview
    • MySQL Curriculum
    • Course Evaluation
    • Thank You!
    • Q&A Session

Duration: 5 Days

What you will learn

This MySQL for Developers training teaches developers how to develop console and web applications using MySQL with their choice of the PHP, Java, or Python programming languages. Expert Oracle University instructors will guide you through realistic hands-on activities to teach you how to use Connectors to access MySQL databases, query the database effectively, present data in different formats, and use MySQL support for “NoSQL”.

  • Learn To:
    • Program with Connectors.
    • Write console and web applications.
    • Optimize query performance.
    • Write stored routines and triggers.
    • Use the InnoDB memcached plugin for NoSQL access to your data.
    • Store, access, and analyze geospatial data.
  • Benefits to You
    • This course will teach you how to write applications that maximize your investment in MySQL. You will learn best practice techniques for writing programs that store and retrieve MySQL data and present it to users in a way that allows them to gain insight into that data.
  • Audience
    • Application Developers
    • Data Analyst
    • Developer
    • Support Engineer

Related Training

  • Required Prerequisites
    • Knowledgable in Joining tables
    • Knowledgable in executing basic DDL and DML queries using SQL
    • Knowledgable in relational database concepts and MySQL data types
    • Knowledgable in using the mysql command-line client
  • Suggested Prerequisites
    • Basic HTML
    • Developing simple web applications using PHP, Java/JSTL, or
    • Knowledge of JSON (javascript object notation)
    • MySQL Performance Tuning
    • MySQL Performance Tuning Ed 3
    • MySQL for Database Administrators Ed 3.1
  • Course Objectives
    • Secure your connections to the MySQL server
    • Use prepared statements
    • Guard against SQL injection
    • Investigate and handle errors and warnings
    • Create database-driven web applications
    • Enable fast text search
    • Respond to data changes and events
    • Modify table data
    • Use temporal and numeric functions in expressions
    • Write queries that contain nested queries
    • Safeguard concurrent queries by using transactions
    • Find and fix poorly-performing queries
    • Use NoSQL and JSON document stores
    • Write stored programs
    • Create MySQL client programs using Connectors
    • Analyze spatial data
  • Introduction to MySQL
    • MySQL overview
    • MySQL Enterprise Edition
    • MySQL on the Web
    • The MySQL community
  • Connectors and APIs
    • Introducing MySQL Connectors
    • PHP Connectors
    • Connector/J
    • Connector/Python
    • Embedding MySQL in an application
    • Integration with MySQL Fabric
  • Using Connectors
    • PHP, Java, and Python Connectors
    • Reusing connections
    • Dealing with special characters and null values
    • Storing and retrieving connection details
    • Creating secure connections to the MySQL server
  • Prepared Statements
    • Reasons for using prepared statements
    • User-defined variables
    • Preparing, executing, and deallocating prepared statements
    • Using prepared statements with Connectors
  • Handling Errors and Warnings
    • SQL modes
    • Interpreting errors and warnings MySQL diagnostics
    • Error and exception handling with Connectors
  • Building Database-Driven Web Applications
    • Anatomy of a database-driven web application
    • Different methods of displaying query results
    • Web forms and processing requests
    • Using hyperlinks for navigation
    • Displaying query results across multiple pages
    • Dynamic sorting of query results
  • Tables and Views
    • Creating new tables from existing tables
    • Temporary tables
    • Using mysqldump
    • Working with views
  • Working with Strings
    • Overview of working with strings
    • String functions
    • Sorting and comparing strings
    • Matching string patterns with regular expressions
    • Using FULLTEXT search
  • Working with Numeric and Temporal Data
    • Numeric expressions
    • Temporal expressions
    • Interval arithmetic
    • Numeric functions
    • Temporal functions
  • Subqueries
    • Subquery: overview
    • Scalar subqueries
    • Row subqueries
    • Table subqueries
    • Correlated and non-correlated subqueries
    • Subqueries in data modification statements
  • Modifying Table Data
    • Using the INSERT statement
    • Retrieving the ID of the last-inserted record
    • Using the DELETE statement
    • Using the UPDATE and REPLACE statements
    • Using the TRUNCATE statement
  • Transactions
    • Overview of transactions
    • Transactional statements
    • Using transactions within programs
    • Consistency issues
    • Isolation levels Locking
  • Query Optimization
    • Optimization strategies
    • The MySQL Query Optimizer
    • Indexing
    • Query analysis with the EXPLAIN statement
    • Rewriting queries
    • MySQL Enterprise Monitor
  • Stored Routines
    • Creating stored routines
    • Variables in stored routines
    • Parameters in stored routines
    • Managing stored routines in the database
    • Using flow control statements and constructs
    • Using cursors
    • Limitations of stored routines
  • Triggers and Scheduled Events
    • Triggers
    • Scheduled Events
  • Reporting
    • Aggregating data
    • Summary tables
    • Grouping data
    • Crosstab reports
    • Bar charts
    • Decision tables
  • NoSQL
    • NoSQL in MySQL
    • Memcached plugin for InnoDB
    • Memcached application development
    • The JSON native data type
    • JSON functions
    • Generated columns
  • Conclusion
Best viewed in IE 10+, Firefox 20+, Chrome , Safari5+, Opera12+
Cyret Copyright @ 2018. All Rights Reserved
Go Top