Live chat by BoldChat

Training on MySQL/SQL Databases

The MySQL for Database Administrators course is for DBAs and other database professionals who want to install the MySQL Server, set up replication and security, perform database backups and performance tuning, and protect MySQL databases.

Benefits To You:

Database administrators will learn how new enhancements made available in MySQL will increase performance, scalability, reliability and manageability for their Web, Cloud and embedded application requirements. You will be able to install, configure, maintain and tune databases for a wide variety of workloads and environments.

Setting up the Server and Databases

This course teaches you how to install, configure, maintain, and tune databases for a wide variety of workloads and environments. You will install and configure MySQL, and set appropriate configuration options. You will connect to the server, using both graphical and command-line clients, and create and populate databases.

Course Details:

Introduction

  • MySQL Overview, Products, Services
  • MySQL Services and Support
  • Supported Operating Services
  • MySQL Certification Program
  • Training Curriculum Paths
  • MySQL Documentation Resources

MySQL Architecture

  • Describe the client/server model
  • Understand communication protocols
  • Understand how the server supports storage engines
  • Explain the basics of how MySQL uses memory and disk space

System Administration

  • Choose between types of MySQL distributions
  • Install the MySQL Server
  • Describe the MySQL Server installation file structure
  • Start and stop the MySQL server
  • Upgrade MySQL
  • Run multiple MySQL servers on a single host

Server Configuration

  • Set up MySQL server configuration files
  • Explain the purpose of dynamic server variables
  • Review the server status variables available
  • Configure operational characteristics of the MySQL server
  • Describe the available log files
  • Explain binary logging

Clients and Tools

  • Describe the available clients for administrative tasks
  • Use MySQL administrative clients
  • Use the mysql command line clients
  • Use the mysqladmin for administrative tasks
  • Describe available MySQL tools
  • List the available APIs (drivers and connectors)

Data Types

  • Describe the major categories of data types
  • Explain the meaning of NULL
  • Describe column attributes
  • Explain character set usage with data types
  • Choose an appropriate data type

Obtaining Metadata

  • List the various metadata access methods available
  • Recognize the structure of the INFORMATION_SCHEMA database schema
  • Use the available commands to view metadata
  • Describe differences between SHOW statements and INFORMATION_SCHEMA tables
  • Use the mysqlshow client program
  • Use INFORMATION_SCHEMA to create shell commands and SQL statements

Transaction and Locking

  • Use transaction control statement to run multiple SQL statements concurrently
  • Explain the ACID properties
  • Describe the transaction isolation levels
  • Use locking to protect transactions

InnoDB Storage Engine

  • Describe the InnoDB storage engine
  • Set the storage engine to InnoDB
  • Illustrate the InnoDB tablespace storage system
  • Efficiently configure the tablespace
  • Use foreign keys to attain referential integrity
  • Explain InnoDB locking

Other Storage Engines

  • Explain the general purpose of storage engines in MySQL
  • List the storage engines available for MySQL
  • Describe the key features of the MyISAM storage engine
  • Describe the key features of the MEMORY storage engine
  • Give an overview of other storage engines: FEDERATED, ARCHIVE, BLACKHOLE, NDBCLUSTER
  • Choose an appropriate storage engine

Partitioning

  • Define partitioning and its particular use in MySQL
  • Determine server partitioning support
  • List the reasons for using partitioning
  • Explain the types of partitioning
  • Create partitioned tables
  • Describe subpartitioning
  • Obtain partitioning metadata
  • Use partitioning to improve performance

User Management

  • Depict the user connection and query process
  • List requirements for user authentication
  • Use SHOW PROCESSLIST to show which threads are running
  • Create, modify and drop user accounts
  • List requirements for user authorization
  • Describe the levels of access privileges for users
  • List the types of privileges
  • Grant, modify and revoke user privileges

Security

  • Recognize common security risks
  • Describe security risks specific to the MySQL installation
  • List security problems and counter-measures for network, operating system, filesystem and users
  • Protect your data
  • Use SSL for secure MySQL server connections
  • Explain how SSH enables a secure remote connection to the MySQL server,
  • Find additional information for common security issues

Table Maintenance

  • Recognize types of table maintenance operations
  • Execute SQL statements for table maintenance
  • Client and utility programs for table maintenance
  • Maintain tables according to specific storage engines

Exporting and Importing Data

  • Exporting Data using SQL
  • Importing Data using SQL

Programming Inside MySQL

  • Creating and executing Stored Routines
  • Describing stored routine execution security
  • Creating and executing triggers
  • Creating, altering and dropping events
  • Explaining even execution scheduling

Views

  • Defining views
  • Reasons to use views
  • Altering, checking and removing a view
  • Setting privileges for views

MySQL Backup and Recovery

  • Describing backup basics
  • Types of backups
  • Backup tools and utilities
  • Making binary and text backups
  • Role of log and status files in backups
  • Data Recovery

Introduction to Replication

  • Describing MySQL Replication
  • Managing the MySQL Binary Log
  • Explaining MySQL replication threads and files
  • Setting up a MySQL Replication Environment
  • Monitoring MySQL Replication
  • Troubleshooting MySQL Replication

Introduction to Performance Tuning

  • Using EXPLAIN to Analyze Queries
  • General Table Optimizations
  • Monitoring status variables that affect performance
  • Setting and Interpreting MySQL server Variables
Request a Quote

Featured Projects

Technical Expertise

Awards

Our Partners

Customer Talk

  • upSage Pastel has streamlined our financial operations. We have achieved stability with the system now and are satisfied with your support.up

    -Principal
    Institute of Social Works

  • upI would like to thanks and congratulate to you and your team who has performed very nice and swift action to fulfil this project in less time and efficient way.up

    -CEO
    Capital Markets and Securities Authority (CMSA)

  • upI have been impressed with your level of technical ability and the ease with which we can work together. I will certainly be recommending Web Technologies to others who need fast and well delivered applications.up

    -Executive Director
    Tanzania Education Authority (TEA)

  • upWe are very happy with the service Web Technologies provides. They are contactable twenty four hours a day, seven days a week and their response times are excellent, which keeps our down time to a minimum.up

    -Johannes Kahwa
    It Manager, Social Security Regulatory Authority ( SSRA)

  • upWeb Technologies have been overseeing the development of our University Website , Software and networks for over 5 years. Work is always completed to a high standards, on time and to budget. We have been very appreciative of the patient, friendly and reliable service.up

    -Elibariki Mushi (Head of ICT)
    Kampala International University (KIU)

Be Social with us

Terms of Service | Terms of Use | Privacy Policy | Connectivity

Articles | FAQ | Sitemap | Inquiry | Client Speaks | Contact | Blog | Partnership