mysql Tutorial

mysql is a standard language for accessing and manipulation database.


what is mysql ?

mysql, the most popular open source database management system.it is a multi-user, multithreaded database management system.

source code of mysql is available under gnu general public license.

some quick points about mysql.

  • mysql is a database management system.
  • mysql databases are relational.
  • mysql software is open source.
  • mysql database server is very fast, reliable, scalable, and easy to use.
  • mysql server works in client/server or embedded systems.

what can mysql do ?

  • mysql can create new databases
  • mysql can create new tables in a database
  • mysql can create stored procedures in a database
  • mysql can retrieve data from a database
  • mysql can insert records in a database
  • mysql can update records in a database
  • mysql can delete records from a database
  • mysql can execute queries against a database
  • mysql can set permissions on tables, procedures, and views.

what is database ?

the database is an organized collection of data.database management systems(dbms) are specially designed applications that interact with the user, other applications, and the database itself to capture and analyze data.each database has one or more distinct apis for creating, accessing, managing, searching, and replicating the data it holds.


rdbms

rdbms stands for relational database management system.

the data in rdbms is stored in database objects called tables.it consists of columns and rows.

examples of an rdbms:oracle database, mysql, microsoft sql server and ibm db2.


features of mysql

  • it designed to be fully multi-threaded using kernel threads, to easily use multiple cpus if    they are available.
  • uses a very fast thread-based memory allocation system.
  • supports server secure layer (ssl)
  • cross-platform support
  • full-text indexing and searching using myisam engine
  • embedded database library

  security

  • a privilege and password system that is very flexible and secure, and that enables host-based verification.

  scalability and limits

  • mysql support for large database that contain 50 million records.
  • mysql can handle extremely large and complex databases without too much of a performance drop.

portability- mysql ia available for both unix and non-unix operating system, including linux, solaris, macos, window 2000, xp, vista.mysql server can run on a range of architectures, including intel x86, alpha, powerpc.

mysql language is a fully multi-threaded architecture, which allows multiple concurrent accesses to the database.which means multiple clients to read the same database simultaneously and providing a substantial performance gain.the Mysql code is also structured in a modular, multi-layerd manner, with minimum redundancies and special optimizers for such complex tasks a joins and indexing

MySQL database management language is a full multi-user system, which means that multiple clients can access and use one MySQL database simultaneously,which are required to support simultaneous connections by multiple remote clients.

application support - MySQL server support all high level languages like C, C++, Java, PHP, Perl, Python.


MySQL Relational Database Management System consists of the following two components:

  • client side tools - include a command-line MySQL client, tools to manage mysql user permissions, and utilities to import and export MySQL databases.using this tool you can view tables, maintain tables, retrieve server status information.
  • server side tools - include the mysql database server which is the core software engine responsible for creating and managing databases, executing queries and returning query results, this tools more powerfull to repair MySQL tables, create bug reports.

MySQL Architecture

In the MySQL tutorial the MySQL is based on a tiered architecture, consisting of both primary subsystem and support components that ineract with each other to parser, read and execute queries and to cache and return query results.

primary subsystems

the MySQL language architecture consists of five primary subsystems that work together to respond to a request made to the mysql database server.

  • The Mysql storage manager
  • the mysql query engine
  • the mysql buffer manager
  • the mysql transaction manager
  • the mysql recovery manager

The Query Engine

MySQL Tutorial - This Query Engine contains three interrelated components:

  • Syntax Parser
  • Query Optimizer
  • Execution Component

The Syntax Parser decomposes the sql commands it receives from calling programs into a form that can be understood by the MySQL engine.The Objects that will be used are identified along with the correctness of the syntax.The Syntax Parser also checks the object being referenced to ensure that the privilege level of the calling program allows it to use them.

MySQL tutorial for query optimizer then streamlines the syntax for use by the execution component, which then prepares the most efficient plan of query execution. The query optimizer checks to see which index should be used to retrieve the data as quickly and efficiently as possible.

MySQL tutorial for the Execution component then interprets the execution plan and based on the information it has received makes requests of the other components to retrieve the records.

storage Manager- MySQL Tutorial

The storage Manager interfaces with the os to write data to the disk efficiently. because the storage functions reside in a separate subsystem, the mysql engine operates at a level of abstraction away from the operating system.This means that if you port to a different operating system that uses a different storage mechanism for example, you can rewrite only the storage portion of the code while leaving the rest of the engine as is.The Storage Manager writes to disk all of the data in the user tables, indexes and logs as well as the internal system data.

Query Cache

MySQL Tutorial for query cache if a query returns a given set of records repeating the same query should return the same set of records unless the underlying data has somehow changed.As obvious as this sounds, few of the other major relational database management system vendors provide features that take advantage of this principle.Other database products are efficient in storing optimized access plans that detail the process by which data is retrieved such plans allow queries similar to those that have been issued previously to bypass the process of analyzing indexes yet again to get to the data.The MySQL engine uses an extremely efficient result set caching mechanism known as the query cache that dramatically enhances response times for queries that the called upon to retrieve the exact same data as a previous query.This mechanism is so efficient that a major computing publication declared MySql Queries to be faster than those of oracle and sql server.

buffer Manager

MySQL Tutorial- the buffer manager handles all memory management issues between requests for data by the query engine and the storage manager.MySQL makes aggressive use of memory to cache result sets that can be returned as is rather than making duplicate requests to the storage manager this cache is maintained in the buffer manager.This is also the area where new records can be cached while waiting for availability of targeted tables and indexes.If any new data is needed its requested from the storage manager and placed in the buffer before then being sent to the query engine.

Transaction Manager

Mysql Tutorial - for transaction manager its provide facilitate concurrency in data access.this subsystem provides a locking facility to ensure that multiple simultaneous users access the data in a consistent way without corrupting or damaging the data in any way transaction control takes place via the lock manager subcomponent, which places and releases locks on various objects being used in transactions.Each transactional table handler implements its own transaction manager to handle all locking and concurrenc needs.

recover Manager

Mysql Tutorial - for recovery manager job is to keep copies of data for retrieval later in case of a loss of data.It also logs commands that modify the data and other significant events inside database.Only the InnoDB and BDB table handlers provide recovery management the MyISAM handler doesn't doesn't have transactional recovery procedures, but it does provide mechanisms that apply certain recovery features in case of a server outage, these features fix any internal inconsistencies that might occur as the result of such a crach.

Process Manager

Mysql Tutorial - for process manager performs two functions in the system.First if manager user connections via modules for network connection management with clients.Second if synchronizes competing tasks and process via modules for multi-threading, thread locking and performing thread-safe operations.

Function Libraries

Mysql tutorial - this libraries contains general-purpose routines that are used by all the other subsystems.It includess routines for string manipulation sorting operations and operating-system specific functions as memory management and file I/O.

The MySQL is multiprocessor architecture,Mysql is built using a multi-threaded design which allows threads to be allocated between processors to achieve a higher degree of parallelism. this is important to know not only for the database administrator who needs to understand how mysql takes best advantage of processing power but also for developers who can extend mysql with custom functions.all custom functions must be thread safe that is that they must not interfere with the workings of other threads in the same process as MySQL.

The Mysql is a threaded application it is able to let the operating system take over the task of coordinating the allocation of threads to balance the workload across multiple processors.MySQl uses these threads to do the following

  • a global connection thread handles all connection requests and creates a new dedicated thread to handle authentication and SQL query processing for each connection.this type of thread works on both TCP/IP and named pipe connections.
  • internal semaphores and alarm listening functions are handled by a separate thread
  • when requested a dedicated thread is created to flush all tables at the specified interval.
  • every single table on which a delayed Insert is being used gets its own thread.
  • In replication master host synchronization is handled by separate threads

mysql statements can be divided into three broad categories each concerned with a different aspect of database management:

  • statements used to define the structure of a database these statements define the relationships among different pieces of data definitions for database table and column types and database indices.
  • statements used to manipulate data these statements control adding and removing records querying and joining tables and verifying data integrity.
  • statements used to control the permissions and access level to different pieces of data - these statements define the access levels and security privileges for databases, tables and fields which may be specified on a preuser and or per host basis.

components of the MySQL API

  • connection management - before an application can begin interacting with a database and executing queries on the data stored within it it must first establish a connection to the database server.The Mysql api includes a series of functions designed specifically for application to connect to a MySql database server provide all requested credentials and open a channel for subsequent communication.Once all communication is completed the APi also provides functions to terminate the connection and clean up things by freeing used memory.
  • Query execution and result processing - once a connection has been extablished an application can begin sending queries to the server and retrieving the result. mysql api provide numerous functions to facilitate this process.
  • error reporting - the mysql api also provides basic error-reporting capabilities passing error codes and messages onward to the caller in case of a problam with a connection or query.in addition the api also enables callers to access detailed debugging and housekeeping information including the current servers state and a list of all active processes.