Delving Deeper into MySQL and PostgreSQL

Mastering Performance, High Availability, and Migration

Duration

5 Days (8 hours per day)

Level

Advanced Level

Design and Tailor this course

As per your team needs

Edit Content

This training program is designed for experienced Database Administrators (DBAs) seeking in-depth knowledge and skills in managing MySQL and PostgreSQL databases. The course covers essential aspects of database architecture, administration, patching and upgrades, backup and recovery, performance tuning, high availability, and migration between MySQL/PostgreSQL and other databases like Oracle and SQL Server.

Edit Content

Database Administrators (DBAs) with a background in MySQL and/or PostgreSQL
IT professionals responsible for managing and maintaining database systems.
Individuals looking to enhance their skills in database administration.

Edit Content

Recap Relational Databases

Overview of relational databases and their role in modern applications
Comparison of MySQL and PostgreSQL: features, strengths, and weaknesses
Basic SQL concepts: SELECT, INSERT, UPDATE, DELETE, JOINs, constraints
Differences and similarities between MySQL and PostgreSQL SQL dialects

Advanced SQL for MySQL Databases

Advanced SQL features: subqueries, window functions, transactions, triggers
Optimizing queries for both databases
Working with JSON data in MySQL
Hands-on labs:
- Writing and executing complex SQL queries for both databases
- Optimizing queries for performance
- Working with JSON data in MySQL

Advanced SQL for PostgreSQL Databases

Advanced SQL features: subqueries, window functions, transactions, triggers
Optimizing queries for both databases
Working with JSON data in PostgreSQL
Hands-on labs:
- Writing and executing complex SQL queries for both databases
- Optimizing queries for performance
- Working with JSON data PostgreSQL

MySQL Deep Dive

Database Architecture:
- Physical storage (InnoDB, MyISAM), indexes, data structures
- Internal query processing and optimization techniques
- Replication and high availability options
DB Administration:
- User management and access control.
- Security best practices: authentication, authorization, encryption
- Monitoring and performance analysis tools
- Backup and recovery strategies (full, incremental, point-in-time)
- Patching and upgrade procedures for MySQL versions
Performance Tuning:
- Identifying bottlenecks and slow queries
- Query optimization techniques (EXPLAIN, indexes, tuning parameters)
- Caching and buffering strategies

Hands-on labs:
- Setting up and configuring MySQL server
- Creating and managing databases, tables, and users
- Writing and executing SQL queries
- Implementing backup and recovery procedures
- Performing basic performance tuning

PostgreSQL Deep Dive

Database Architecture:
- Physical storage (TOAST, B-tree, LSM-tree), indexes, data types
- Advanced features: triggers, functions, stored procedures
- Multi-master replication and high availability solutions
DB Administration:
- User management and roles
- Security best practices: authentication, authorization, row-level security
Monitoring and performance analysis tools
Backup and recovery strategies (WAL archiving, point-in-time recovery)
Patching and upgrade procedures for PostgreSQL versions
Performance Tuning:
- Identifying bottlenecks and slow queries using EXPLAIN ANALYZE
- Indexing strategies and materialized views for performance optimization
- Configuration parameters and resource management
Hands-on labs:
- Setting up and configuring PostgreSQL server
- Creating and managing databases, tables, and roles
- Writing and executing SQL queries with advanced features
- Implementing backup and recovery procedures
- Performing basic performance tuning

Database Migration

Strategies and tools for migrating data from/to MySQL/PostgreSQL
Popular migration tools: pglogical, mysqldump, pg_dump, etc
Planning and executing migration projects: schema mapping, data transfer, testing
Migrating data from/to other databases like Oracle, SQL Server, etc

Hands-on labs:
- Planning and executing a simple migration between MySQL and PostgreSQL
- Using migration tools to transfer data
- Writing migration scripts for complex scenarios

Advanced Topics

Security best practices for database applications
High availability and disaster recovery solutions for MySQL and PostgreSQL
Cloud-based database deployments: AWS RDS, Google Cloud SQL, etc
Working with NoSQL databases alongside relational databases
Hands-on labs:
- High availability and disaster recovery solutions for MySQL and PostgreSQL
- Cloud-based database deployments: AWS RDS, Google Cloud SQL, etc
- Working with NoSQL databases alongside relational databases

InnoDB Replication and Clustering

Working with NoSQL databases alongside relational databases
InnoDB Replication Architecture
Asynchronous vs. Synchronous Replication
Configuring Replication Filters
Cascading Replication
MySQL InnoDB Cluster
Setting Up InnoDB Cluster
Configuring MySQL Router for load balancing
Scaling Out with InnoDB Cluster

Hands-on labs:
- Configuring Replication Filters
- Setting Up InnoDB Cluster
- Configuring MySQL Router for load balancing
- Scaling Out with InnoDB Cluster

Edit Content

Basic understanding of relational database concepts.
Basic experience with MySQL and/or PostgreSQL.

FIND YOUR COURSE

Topics

Brands

Delving Deeper into MySQL and PostgreSQL

Duration

Level

Design and Tailor this course

Quick Links

our Offerings

Get in touch

Sign up for DataCouch Communications

Connect

we'd love to have your feedback on your experience so far