Mysql database administration tutorial – Introduction

MySQL, an open source software is a relational database management system.SQL is the most common standardized language used to access databases and is defined by the ANSI/ISO SQL Standard. Current version of the standard is “SQL:2003”

Main features of Mysql are

  • Written in C and C++.
  • Tested with a broad range of different compilers.
  • Works on many different platforms.
  • Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available
  • Provides transactional and nontransactional storage engines.
  • Implements in-memory hash tables, which are used as temporary tables.
  • Works in cross platform.
  • supports stored procedure, functions, triggers, views, subqueries and partitioning.
  • Supports replication and clustering for High Availability.

Architecture

Mysql architecture is divided into two levels

1. Application layer
2. Logical Layer

Application layer

This is the layer where users, clients connect to Mysql server to query data. there are three components in this layer

  1. admin interface Like mysqladmin, isamchk etc
  2. Client Interfaces . For eg various Mysql APIs like PHP API
  3. Query interface for users to interact with server using ‘mysql’

Logical Layer

Logical layer is further divided into subsystems.

Query processor
All the D*L statements sent from application layer are parsed and optimized in this Query processing layer
Transaction management
This is where transaction management is sone in mysql .A transaction is a single unit of work that has one or more MySQL commands in it. The transaction manager is responsible for making sure that the transaction is logged and executed atomically.It also executes commit and rollback to revert the transactions

  • Ensures Atomicity
  • Avoids Deadlocks
  • Responsible for Commit & Rollback

Recovery management
Recovery management is to retrive and rollback database to its previous state if necessary. It is done by using logs of system. There are different variables one can tune in configuration settings to configure logs and use them to recover to old state
Storage management
Ultimately storage of data is physically done on some secondary storage like hard disks. But dynamic access of medium is done by mainating some buffers in the main memory. These buffers reciding in main and virtual memory is maintained by buffer manager , which is part of storage manager.