Advanced MySQL Administration and Deployment

Course Outline

Course Objectives Participants attending this training program will learn the following:
  • Advanced MySQL Administration and Deployment.

Recommended Duration 24 hours
(2 days @ 8 hours per day)
Target Audience
  • Database Administrators who can manage and administer MySQL database and perform routine support/maintenance.

NOTE: This training program will largely be discussion and demonstration-oriented along with case-studies. Focus will strictly be on administration/deploymen/management and NOT development. Strong knowledge of MySQL administration amongst the participants is a must.

Pre-requisites
  • Participants attending this training program should be competent in the following:
    • Good knowledge of Linux/UNIX/Windows administration.
      • Good knowledge of Network administration on Linux.
      • In-depth knowledge of MySQL (Concepts, Commands, Administration and Deployment).
    • Fair knowledge of SQL statements and constructs.
Training
  • The Training program will be demonstration oriented.
  • Most of the concepts will be taught by demonstrating code and the participants can practice the same.

Click here to check the schedule for the next batch of this training program.

Click here to register/nominate yourself for this training program.

Course Coverage

Day Description
Day 1 Planning MySQL deployment setup
  • Understanding project requirements and goals.
  • Hardware architecture plan and setup.
  • Determining storage engines for project-specific requirements.

Migration from existing databases

  • Understanding the features/limitation of MySQL.
  • Design-level migration and Data migration.
  • Intricacies involved in data migration from other databases (Oracle, Sybase and PostgreSQL).
  • Creating migration scripts (Perl/PHP).
  • An overview on design-level migration using DBDesigner and MySQL workbench.

MySQL Performance tuning

  • Identifying performance bottlenecks (checklists).
  • Tuning configuration parameters (storage engine specific options in my.cnf file).
  • Tuning/Optimizing queries for performance.
  • Routine database maintenance checklists.

Backup/Recovery and disaster planning

  • Online and Offline backup mechanisms.
  • Scheduling automated database backups.
  • Database replication planning and setup.
  • Common problems and possible work-arounds.
  • Recovering methods for crashed databases.
  • Maintaining and centralizing logs across servers.

Day 2 An overview of MySQL scalability planning
  • Scale-up and Scale-out strategies.
  • Planning database clusters
  • HA clusters Vs. Load-balanced clusters
  • Clustering strategies and planning
    • OS-level clusters
      • Using Heartbeat + DRBD on Linux
        • Load balanced clusters using OpenMOSIX
    • Application clusters
      • Clustering web servers/application tiers
        • Session migration and management strategies
    • Database clustering
      • An overview of NDBCluster
        • Features and Limitations of NDBCluster

Using MySQL Cluster (NDBCluster)

  • Planning Hardware/Network architecture and setup.
  • Setup and configuration of NDBCluster.
    • Configuration of Data nodes.
    • Configuration of SQL nodes.
    • Management server and client.
  • Configuring node groups and partitions.
  • Routine management and maintenance of cluster.
  • Common pitfalls and work-arounds.
  • Configuring cluster parameters and local check-points.
    • Understanding Cluster status and system variables.
  • Q & A

    Feedback