Description

This course aims to get you up to speed on a set of advanced aspects of MySQL administration such as configuring multiple instances on the same machine, replication and MySQL Cluster architecture.

Who is this training for ?

For whom ?

System administrators and database administrators.

Prerequisites

Good knowledge of MySQL administration or knowledge equivalent to that provided by the "MySQL, administration" internship (ref. MSA). Experience required in MySQL database administration.

Training objectives

Install multiple instances of MySQL on the same server

Know best practices for improving storage, such as table compression, defragmentation

Improve security by using SSL to encrypt user connections

Understand the principle of replication and implement in MySQL

Set up a clustered MySQL architecture

Training program

• Reminders
  • MySQL architecture and different storage engines.
  • Storage engines and plug-ins.
  • Storage engines and indexes.
  • Configuration of the MySQL server.
  • Configuration and management of the InnoDB engine.
  • Collection of statistics.
  • MySQL logs.
• Advanced administration functions
  • Managing the startup of several instances (mysqld_multi).
  • Internal structure of MyISAM tables.
  • Compression of MyISAM tables.
  • Defragmentation of the MyISAM table space.
  • Repairing MyISAM tables.
  • Internal structure of InnoDB transactional tables.
  • Organization of the InnoDB table space.
  • Organization of rows in InnoDB tables.
  • Transactional operation of the InnoDB engine.
  • User connections over SSL.
  • Practical work Building and using MySQL instances with mysqld_multi.
  • Compressing MyISAM tables.
  • Defragmenting the MyISAM and InnoDB tablespace.
  • Configuring user connections over SSL.
• Replication
  • Binary log and transactional consistency.
  • Binary logging formats: per instruction, per line, mixed.
  • Simple master-slave replication.
  • Advanced replication strategies.
  • Implementation details.
  • Thread states and relay files.
  • Replication start options.
  • Troubleshooting common problems.
  • Hands-on Replication configuration, troubleshooting.
• MySQL cluster
  • Installation of a MySQL cluster.
  • Architecture.
  • Hardware and system configuration.
  • Partitioning of tables and distribution of data on the cluster nodes.
  • The NDB engine.
  • Processes and files.
  • Management server.
  • Implementation and administration.
  • Backups, restorations.
  • Cluster and replication.
  • Limitations and developments.
  • Practical work Installation, configuration and administration of a MySQL cluster.