MySQL: Become A MySQL Admin
4.1 (107 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
550 students enrolled

MySQL: Become A MySQL Admin

Store, retrieve, and manipulate your data and design efficient database solutions with MySQL 8
4.1 (107 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
550 students enrolled
Created by Packt Publishing
Last updated 6/2018
English [Auto]
Current price: $139.99 Original price: $199.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 8.5 hours on-demand video
  • 1 downloadable resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Handle MySQL user management
  • Set up and operate MySQL replication, including moving to GTID based replication
  • Hands on experience of operations carried out on MySQL instances, running sysbench against them, and producing a workload
  • Perform administration and performance-tuning tasks
  • Master the new querying features in MySQL 8
  • Access databases with PHP, Python, or Java
Course content
Expand all 56 lectures 08:27:11
+ MySQL 8 for Administrators
30 lectures 05:25:07

This video provides an overview of the entire course.

Preview 03:48

This video describes how to use the labs in the course.

  • Explore the required software
  • Understand how to use the labs
A Lab About the Labs

This video shows MySQL installation on rpm based distributions such as RHEL, CentOS and Fedora.

  • Learn about various installation methods
  • Learn about rpm based installation
  • Install MySQL from rpm in the lab
MySQL 8 Installation from Rpm Packages

This video shows MySQL installation on deb based distributions such as Debian and Ubuntu.

  • Learn about deb based installation
  • Install MySQL from deb in the lab
MySQL 8 Installation from Deb Packages

This video shows manual MySQL Installation from binary tarball.

  • Learn about manual installation method
  • Install MySQL from binary tarball in the lab
Set Up MySQL 8 from Binary Tarball

This video describes MySQL configuration file structure: my.cnf.

  • Learn about MySQL variables
  • Learn about my.cnf structure and includes
  • Configure MySQL in the lab
Preview 07:10

This video describes changing MySQL configuration parameters without restarting MySQL.

  • Learn about static and dynamic variables
  • Try modifying dynamic variables in the lab
Changing MySQL Configuration Parameters Online

This video describes how to persist configuration parameters set with SET GLOBAL.

  • Learn about making a dynamic variable change permanent
  • Learn about information_schema, sys schema and performance_schema
  • Try persisting variables in the lab
New in MySQL 8: Persisting Configuration Parameters

This video describes some of the most important MySQL configuration parameters.

  • Learn about filesystem related parameters
  • Learn about session and global scope of parameters
  • Learn about storage engines
Essential MySQL Configuration Parameters

In this video, we will cover essential innodb parameters.

  • Learn about the few most important innodb parameters to set
  • Locate MySQL related files on the filesystem in the lab
Essential InnoDB

This video describes how to create access to applications like wordpress or Drupal.

  • Learn how to create a user
  • Learn how to check existing users
  • Try user management in the lab
Creating Databases and Users for Applications

This video is a slight detour on getting comfortable with MySQL, even with not that much experience.

  • Learn about sakila, a sample database for MySQL
  • Try sakila in the lab
Detour: MySQL Usage Basics

This video describes a new MySQL 8 feature: roles.

  • Learn about roles
  • Try roles in the lab
New in MySQL 8: Roles

This video describes using sysbench, MySQL's de factor standard for benchmarks.

  • Learn about sysbench, how is it used in the course to simulate application traffic
  • Try sysbench in the lab
Using sysbench

This video describes changes around authentication in MySQL 8.

  • Learn about what changed in authentication in MySQL 8
  • Create users with old and new authentication methods in the lab
Authentication Changes

This video describes MySQL's binary logs and their role in replication.

  • Learn about statement and row based binary logs
  • Examine binary logs in the lab
The Binary Log

This video is about setting up a slave replica.

  • Learn about replication: IO and SQL threads
  • Learn about monitoring replication
  • Try setting up a slave in the lab
Setting Up a Replication Slave

This video describes how to use global transaction identifiers for identifying transactions in a replication slave.

  • Learn about GTIDs
  • Learn about monitoring GTID replication
  • Try GTID replication in the lab
Introduction to GTID Replication

This video shows how to move a traditional filename and position based replication setup to GTID.

  • Learn about configuration options used to migrate to GTID replication
  • Learn about the process of migration
  • Migrate a replication domain to GTID replication in the lab
Migrating to GTID Replication

This video describes the raw monitoring tools we have: monitoring data, query from the database directly and command line tools that can be used.

  • Learn about the monitoring tools available
  • Try them in the lab
Command Line Monitoring

This video describes external monitoring systems that can use the raw data and present them in a nice way.

  • Learn about the external monitoring system available
  • Learn about Percona Monitoring and Management
  • Try Percona Monitoring and Management in the lab
External Monitoring Systems

This video talks about backing up data in a representation that can be interpreted by a 3rd party.

  • Learn about logical backup tools
  • Learn about transaction isolation
  • Try logical backups in the lab
Logical Backups

This video talks about backing up data in the database's representation.

  • Learn about various physical backups methods
  • Learn about Percona Xtrabackup, and enough innoDB internals to know how it works
  • Try Xtrabackup in the lab
Physical Backups

This video is about point in time recovery: recovery transactions that happened after the backups using the binary logs.

  • Learn about point in time recovery in general
  • Learn about mysqlbinlog utility
  • Try point in time recovery in the lab
Point in Time Recovery

This video talks about how to promote a slave to master in case of a master maintenance or failure.

  • Learn about the tools available for switchovers
  • Learn about orchestrator
  • Try orchestrator in the lab

This video talks about advanced replication features like semi-sync and parallel replication.

  • Learn about semi-sync and parallel replication
  • Learn about Galera/Group replication (very-very introductory)
  • Try parallel replication in the lab
Advanced Replication

This video is about checking replication consistency: making sure that a master and the slave has the same data.

  • Learn about pt-table-checksum and pt-table-sync
  • Try pt-table-checksum and pt-table-sync in the lab
Checking Replication Consistency

This video talks about tests to do before migrating to MySQL 5.7 and the migration method.

  • Learn about replication based migration method
  • Learn about consistency check at migration time
  • Learn about query behavior check at migration time
Migration Consideration

This video talks about replication consistency check to do before a migration.

  • Learn about specifics of running pt-table-checksum before a migration
  • Check migration time replication consistency in the lab
Replication Consistency Before Migration

This video is about checking if the queries are executing the same way on the old version and the new version.

  • Learn about pt-upgrade
  • Try pt-upgrade in the lab
Verifying Query Behavior Before Migration
+ MySQL 8 Recipes
26 lectures 03:02:04

This video provides an overview of the entire course.

Preview 02:59

Get to know the MySQL database by learning about other databases and comparing them to MySQL. Also visit the MySQL website to find the documentation and downloads.

  • Learn about the strengths of MySQL
  • Visit the documentation
  • Download the MySQL software
Learning MySQL 8

When working with MySQL, you should quickly be able to locate a problem within the MySQL software. This video will give you an overview on how MySQL is built and what components there are.

  • Get an overview over the MySQL architecture
  • Visit the components of the database
  • Learn about the InnoDB storage engine
Getting to Know the MySQL 8 Architecture

As the easiest way to install a database, learn how to install a new MySQL database using the existing packages in your Linux system.

  • Use the package manager to download the package
  • Install the database using the package
  • Perform post-installation tasks
Installation via Package Manager

This video covers more advanced or more customized installations, install a new database using the generic method provided by MySQL.

  • Find and download the software
  • Install the software
  • Perform post-installation tasks
Installation via Manual Package

In this video, we will upgrade an existing database to the latest version of MySQL, for that, you need to perform a few steps. This video will show you what these steps are and how you can upgrade successfully.

  • Preparation for upgrades
  • Performing the upgrade
  • Post-upgrade tasks
Upgrading an Existing Database to MySQL 8

Access your database using different tools provided by MySQL. We will explore both tools for the command-line and tools with a graphical user interface.

  • See the usage of the MySQL command-line
  • Explore and develop scripts with MySQL Workbench
  • Get to know third-party tools to access your database
Using Tools to Access the Database

Learn how you can list databases inside your server and how you can navigate between the different databases.

  • Get to know the structure of the MySQL server
  • Learn common queries to describe the database
  • See examples of how to use the MySQL command-line
Getting to Know Your Database

Configuring a database is one of the typical tasks that a database administrator has to perform. Learn how you can see how your database is configured and how you can change the configuration.

  • Display the current configuration of your database
  • Learn how the configuration is saved in MySQL
  • Change the most important variables
Performing Initial Database Configuration

The data dictionary is a big new feature in MySQL 8. This video shows you some of the new views and how you can use the new feature to get more information about your database.

  • Learn about the new data dictionary in MySQL 8
  • Find out about data dictionary views
  • See examples of data dictionary queries
Accessing the Data Dictionary

Learn how to create, modify and delete users in your database. We will also explore how you can set the privileges for existing or new users.

  • Create, modify and delete users in your database
  • Assign privileges to users
  • Use the new MySQL 8 feature of Roles
Managing Users

When operating a database, you will certainly encounter some problems. This video helps you to perform basic troubleshooting on your database and find common problems.

  • Learn about the MySQL Error Log
  • Troubleshoot connection problems
  • Learn how you can reset the root password
Performing Basic Troubleshooting

Because the data in your database is often important, we need to make sure your data stays safe. This video talks about security issues and how you can secure your database.

  • Be informed about general security issues
  • See how users and privileges can be used
  • Learn about SQL injections attacks and to avoid them
Configuring Security

Keeping your data safe from data corruption and hardware failures is an important task when running a database. This video will show you simple ways to backup and recovery your data.

  • Understand why database backups are important
  • Perform database backup
  • Perform database recovery
Performing Backup and Recovery of Your Database

For your application, it is important that the data returned from the database is returned as quickly as possible. This video explains how you can improve the performance of your query by using Query Execution Plans.

  • Understand SQL and the MySQL Optimizer
  • Learn about Query Execution Plans
  • Improve performance of your database by configuring the right variables
Optimizing Performance with Query Execution Plans

Querying data is maybe the most important task for any database developer. This video explores simple and more advanced usages of the SELECT command.

  • Learn the basics about SQL
  • Use SELECT to query tables
  • Filter, sort, group and aggregate data with SELECT
Learning SQL: Querying Data

To modify data, SQL uses three different commands. This video will help you to insert and modify data in your database.

  • Use INSERT to create new records
  • Modify existing data using UPDATE
  • Delete records with the DELETE statement
Learning SQL: Inserting and Modifying Data

In this video, we will learn about creating new tables and will learn about the most common data types in MySQL.

  • Learn about the CREATE TABLE statement
  • Explore the most common data types in MySQL
  • Learn best practices for defining tables
Learning SQL: Design Databases

Writing good queries may sound hard, but with a few tips you will be writing better queries in an instant.

  • Avoid the most common mistakes developers make
  • Learn about the best practices for writing queries
  • Write better queries by following tips
Learning Best Practices for Writing Queries

MySQL 8 brings a few new features for developers to make querying the database easier. This video shows you what new features there are and takes a closer look at Common Table Expressions.

  • Learn about new querying features in MySQL 8
  • Learn how the Optimizer has gotten better
  • Have a closer look at Common Table Expressions
Using the New Querying Features in MySQL 8

Aside from tables, indexes are an important tool to make your queries faster. Learn about different types of indexes and also learn how indexes can have disadvantages.

  • Understand how indexes work
  • Learn about different type of indexes
  • Avoid the common disadvantages of indexes
Working with Indexes

Managing big tables with a lot of rows is no easy task. Thankfully, with MySQL 8, there are new features to manage partitions. We will also see the different available partitioning types.

  • Understand what partitioning is
  • Explore the different partitioning types
  • See the new features of MySQL 8 for partitioning
Managing Big Databases Using Partitioning

On the web, PHP is one of the most popular languages. So for building your web application, you can use different modules to access a database backend. In this video, we will learn about the mysqli module and how we can use this module to access the database. This video also includes PHP example code.

  • Learn about different PHP modules for database access
  • Understand the main mysqli methods
  • See example PHP code
Programming with PHP

When working with PHP and objects, a database abstraction layer can help you to write easier code. In this video, we will learn about using PDO and how we can access the database using the PDO methods.

  • Understand the advantages of a database abstraction layer
  • Leverage the object-oriented methods of PDO
  • See example PHP code
Using a PHP Database Abstraction Layer

Writing code in Python is very easy. This video shows you how you can also easily access a MySQL database using the MySQLdb Python module. This video also includes Python example code.

  • Learn about the different Python modules
  • Explore the MySQLdb module methods
  • See an example Python script
Programming with Python

Java is one of the most common programming languages today. Accessing the database in Java always uses a JDBC driver. This video will show you how JDBC works and what methods you can use to access the database. Also, this video contains example code for accessing the database with Java.

  • Learn about JDBC
  • Find out about the JDBC methods
  • See Java example code
Programming with Java
  • Basic understanding of database concepts is all you need

MySQL is the most popular and widely used relational databases in the World today.

The main purpose of this comprehensive course is to assist you on setting up MySQL 8 and running with replication, learn about database administration topics like backups, replication, monitoring, and point in time recovery, switchovers, user management and version upgrades. It is packed with step-by-step instructions and working examples to solve any problem you might come across in MySQL querying and administration

Contents and Overview

This training program includes 2 complete courses, carefully chosen to give you the most comprehensive training possible.

The first course, MySQL 8 for Administrators, covers the differences between various MySQL versions. It starts off with the basics: Installing MySQL and creating users. After that, we will get sysbench up and running. Sysbench, responsible for issuing queries against databases, is the de-facto benchmarking tool for MySQL. With that hands-on lab environment at hand, we will learn about various database administration topics like backups, replication, monitoring, and point in time recovery, switchovers, user management and version upgrades.

The second course, MySQL 8 Recipes, contains practical recipes on efficient MySQL administration, with tips on effective user management, security, database monitoring, troubleshooting, and more. Supplying quick solutions to common problems you might encounter while working with MySQL, the tutorial contains practical tips and tricks to give you the edge over others while designing, developing, and administering your MySQL solution.

By the end of this training program, you will be well versed with high-performance querying and database administration using MySQL.

About the Authors

Peter Boros has more than 10 years of MySQL administration experience. Peter has also taught Oracle University courses about MySQL across EMEA, later he was a Principal Architect at Percona, a company specializing in MySQL consulting. Apart from this, he has worked with a few of the world’s largest MySQL deployments, belonging to prestigious web properties. This course is a product of Peter’s immense work experience and his expertise on the subject.

Simon Krenger is a Database Engineer for the Swiss Federal Railways (SBB) in Bern, Switzerland. He has worked with a wide variety of technologies, including Oracle databases (OCP), Linux, and automation software. He focuses on Information Security and has gained experience with common agile software development practices.

Who this course is for:
  • New and returning MySQL DBAs, who are just getting to know MySQL, or who knew earlier versions and are curious about the latest administration differences in MySQL 8