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
- 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 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 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
- 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
- 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
- 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
- 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
- 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.
