MySQL/MariaDB Administration

Seminarziel

Participants will gain a comprehensive understanding of MySQL and MariaDB administration, including installation, configuration, and management of database servers. They will learn how to design efficient database schemas, implement indexing strategies, and optimize queries for enhanced performance. The seminar will cover essential tasks such as user and permission management, backup and restoration procedures, and monitoring database health. Additionally, attendees will explore replication and high availability configurations to ensure data availability and disaster recovery. By the end of the seminar, participants will be capable of handling routine database administration tasks, optimizing database performance, and ensuring the security and reliability of the database systems.

Inhalt

• Introduction to MySQL/MariaDB
  • Overview of MySQL vs. MariaDB
  • Key features and differences
  • Installation and initial setup
• Database Design and Architecture
  • Principles of database design
  • Normalization and denormalization
  • Indexing strategies for performance
• User and Permission Management
  • Creating and managing users
  • Assigning roles and privileges
  • Securing database access
• Data Manipulation and Query Optimization
  • Writing efficient SQL queries
  • Understanding query execution plans
  • Optimizing queries for performance
• Backup and Recovery
  • Backup strategies and tools
  • Performing backups and restorations
  • Automating backup processes
• Replication and High Availability
  • Setting up master-slave replication
  • Configuring master-master replication
  • Implementing high availability solutions
• Performance Monitoring and Tuning
  • Monitoring database performance
  • Identifying and resolving bottlenecks
  • Tuning server and query performance
• Security Best Practices
  • Securing MySQL/MariaDB servers
  • Implementing encryption and SSL
  • Auditing and compliance
• Advanced Administration Tasks
  • Managing stored procedures and triggers
  • Using scripting for automation
  • Data migration techniques
• Troubleshooting and Maintenance
  • Common issues and solutions
  • Maintenance tasks and scheduling
  • Using tools for troubleshooting
• Integration with Applications
  • Connecting databases to applications
  • Using connectors and APIs
  • Managing data access in web applications
• Exercises
  • Exercise 1: Setting Up Replication
    • Description: Configure master-slave replication between two MySQL/MariaDB servers, ensuring data consistency and replication lag monitoring.
    • Result: Participants will have a functional replication setup, demonstrating their ability to implement data replication for high availability and redundancy.
  • Exercise 2: Query Optimization
    • Description: Analyze and optimize a set of slow-performing queries using indexing and query restructuring techniques.
    • Result: Attendees will improve the performance of the given queries, showcasing their ability to enhance database performance through optimization strategies.