Tag: MySQL

Duration

21 hours (usually 3 days including breaks)

Requirements

There are no specific requirements needed to attend this course.

Overview

This bespoke course has been designed specifically against the requirements provided by Now Managed Learning Services

Course Outline

1.            MySQL High Availability

2.            Galera Cluster Architecture

3.            Percona XtraDB cluster

4.            Installation and configuration of Galera Cluster

5.            Operations of Galera Cluster

6.            Backup and Restore

7.            Load Balancing

8.            Performance tuning and monitoring

9.            High Availability and Scalability with Galera Cluster

10.          Security

11.          Galera Cluster MultiDatacenter Replication

12.          Troubleshooting

13.          Integration with ProxySQL (nice to have)

14.          Hands-on based sessions to learn and practice skills

Duration

14 hours (usually 2 days including breaks)

Overview

Percona Server for MySQL is optimized for cloud computing, NoSQL access, containers and modern hardware such as SSD and Flash storage.

• Cloud ready
  Dramatically reduces downtime on servers with slow disks and large memory, such as 4XL EC2 servers on EBS volumes
• SaaS deployable
  Increases flexibility for architectures such as co-located databases with hundreds of thousands of tables and heterogeneous backup and retention policies
• Vertical scalability and server consolidation
  Scales to over 48 CPU cores, with the ability to achieve hundreds of thousands of I/O operations per second on high-end solid-state hardware
• Query, object and user level instrumentation
  Detailed query logging with per-query statistics about locking, I/O, and query plan, as well as performance and access counters per-table, per-index, per-user, and per-host
• Enterprise ready
  Percona Server for MySQL includes advanced, full-enabled external authentication, audit logging and threadpool scalability features that are only available in Oracle’s commercial MySQL Enterprise Edition

Course Outline

Percona Server Installation

• Choosing version
• Downloading installer (YUM, RPM)
• Installation on Linux machine

Percona Server Files and Scripts

• Percona/MySQL Programs
• Percona Server
• Percona Client
• GUI Tools

Percona Server Configuration

• The Server SQL Mode
• Server System Variables
• Dynamic System Variables
• Server Status Variables
• Shutdown Process

Percona Security Issues

• Securing Percona Server Against Attacks
• Security-Related Options
• Security Issues with LOAD DATA LOCAL

Percona Access Privilege System

• Percona Privilege System Overview
• Privileges Provided by Percona
• Connecting to the Percona Server – Stages
• Access Control, Stage 1: Connection Verification
• Access Control, Stage 2: Request Verification
• Access Denied Errors

Percona User Account Management

• Users and Passwords
• Creating New Users
• Deleting User Accounts
• Limiting User Resources
• Changing Passwords

Percona Database Maintenance

• Backup and Recovery – dump vs. XtraBackup
• Point-in-Time Recovery
• Maintenance and Crash Recovery
• Getting Table Information

Percona Log Files

Error Log

• General Query Log
• Update Log
• Binary Log
• Slow Query Log
• Log File Maintenance and Rotation

Percona  Query Cache

• The Concept of Query Cache
• Testing Query Cache with SELECT
• Configuring Query Cache
• Checking Query Cache Status and Maintenance

Installing and starting Percona XtraDB Cluster

Duration

28 hours (usually 4 days including breaks)

Requirements

No specific prerequisites, however good if student has some prior knowledge of databases.

Overview

Audience: Any IT professionals who aspire to become DBAs or database support professionals on MySql Database on linx/windows platforms.

Format: 40% theoretical/lectures, 60%Practical/hands on lab

Course Outline

Introduction

• MySQL Overview, Products, Services
• MySQL Services and Support
• Supported Operating Services
• 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

Q&A Session

Duration

14 hours (usually 2 days including breaks)

Requirements

• Computer literacy
• Knowledge of any operating system

Overview

• How to build a query?
• What is a relational database?
• What is the structure and SQL commands?

Course Outline

Relational database models

• Relational operators
• Characteristics of declarative SQL language
• SQL syntax
• Division language DQL, DML, DDL, DCL

Data Query Language

• SELECT queries.
• Aliases columns of tables
• Service date (DATE types, display functions, formatting)
• Group Features
• Combining internal and external tables (JOIN clause)
• UNION operator
• Nested Subqueries (the WHERE clause, the table name, column name)
• Correlated subqueries

Data Modification Language

• Inserting rows (INSERT clause)
• Inserting rows by request
• Variation of the rows (UPDATE)
• Delete rows (DELETE)

Data Definition Language

• Creating, altering and dropping objects (CREATE, ALTER, DROP)
• Creating tables using subquery (CREATE TABLE …. AS SELECT…)

CONSTRAINTS

• Options NULL and NOT NULL
• CONSTRAINT clause
• ENUM type
• type SET
• PRIMARY KEY condition
• UNIQUE condition
• FOREIGN KEY condition
• DEFAULT clause

Transactions

Duration

7 hours (usually 1 day including breaks)

Requirements

Good SQL knowledge.

Overview

This course has been created for people already acquainted with SQL. The course introduces you into secrets common to all SQL databases as well as MySQL specific syntax, functions and features.

Course Outline

DQL (Data Query Language)

• Correlation in FROM, WHERE, SELECT and HAVING clauses
• Correlation and performance
• Using CASE, IF, COALESCE functions
• Using variables
• Casting and converting
• Dealing with NULL, NULL-safe operators
• Using regular expression with REGEXP operator
• Useful MySQL specific group by functions (GROUP_CONCAT, etc.)
• GROUP BY WITH ROLLUP
• EXISTS, ALL, ANY
• Multitable OUTER JOIN
• Rewriting subqueries as joins

DML (Data Modification Language)

• Multi-row inserts
• INSERT by SELECT
• Using subqueries in DML statements
• Using variables in DML queries
• Locking tables and rows
• Updating data in many tables
• IGNORE clause
• REPLACE clause
• DELETE versus TRUNCATE

DDL (Data Definition Language)

• Creating tables with select
• Temporary tables

Stored Procedures

• Short introduction to MySQL stored procedures

Duration

14 hours (usually 2 days including breaks)

Requirements

• An understanding of big data concepts (HDFS, Hive, etc.)
• An understanding of relational databases (MySQL, etc.)
• Experience with the Linux command line

Overview

Sqoop is an open source software tool for transfering data between Hadoop and relational databases or mainframes. It can be used to import data from a relational database management system (RDBMS) such as MySQL or Oracle or a mainframe into the Hadoop Distributed File System (HDFS). Thereafter, the data can be transformed in Hadoop MapReduce, and then re-exported back into an RDBMS.

In this instructor-led, live training, participants will learn how to use Sqoop to import data from a traditional relational database to Hadoop storage such HDFS or Hive and vice versa.

By the end of this training, participants will be able to:

• Install and configure Sqoop
• Import data from MySQL to HDFS and Hive
• Import data from HDFS and Hive to MySQL

Audience

• System administrators
• Data engineers

Format of the Course

• Part lecture, part discussion, exercises and heavy hands-on practice

Note

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

• Moving data from legacy data stores to Hadoop

Installing and Configuring Sqoop

Overview of Sqoop Features and Architecture

Importing Data from MySQL to HDFS

Importing Data from MySQL to Hive

Transforming Data in Hadoop

Importing Data from HDFS to MySQL

Importing Data from Hive to MySQL

Importing Incrementally with Sqoop Jobs

Troubleshooting

Summary and Conclusion