Introduction to MySQL Database Administration.
MySQL is a database software which is used to manage and maintain the database in an organized manner and it is a relational database, there are many relational databases but
MySQL has its own identity like
- It supports both small and large applications
- It is fast, reliable and easy to use
- It uses standard sql commands
- It supports various platforms
The data in a MySQL database is stored in tables and a table is a collection of related data and it consists of columns and rows.
History of MySQL :
MySQL was first launched and developed by an organization called MySQL AB in the year 1994 and later in the year 2010 Oracle Corporation has acquired MySQL and started developing distributing and supporting.
Products of MySQL :
MySQL products are comprised into two types:
- Enterprise Edition
- Community Edition
In the enterprise edition the cost will be based on the subscriptions which we use accordingly the charges applies and in the community edition its free of cost and you can download and use it for free and it is also called as open source database.
Components Of MySQL :
The MySQL Database also follows the client and server model architecture and following are the components of MySQL Server
- MySQL Server
- Client Programs
- Non Client Programs
The server is a database component where all the applications related data is stored and it is served for multiple clients who access that data from a particular database server and under every MySQL Server there is a program running called mysqld which takes the request from multiple application clients and process the data if the mysqld program is not running then client cannot access the data from server and it is a crucial program in the database server which directly gets the data from the storage.
The mysql is a client program which runs at the client side which is used to access the data behalf of client user and there are two types of client programs
- Client Programs
- Non Client Programs
Client Programs are those which runs when the MySQL Server is running if not the client programs will not work because these programs does not have direct access to the file system then the client program requests the mysqld program which runs under MySQL Server which is responsible to access the data from MySQL Server.
Non Client Programs are the programs which can run even if the MySQL Server is not running because these programs have direct access towards the File system of the server.
Mostly we use MySQL Workbench for administering and manipulating the changes in the database It is a gui tool for monitoring the MySQL Server and there also many client and non client programs for accessing and managing MySQL Server.
Binding IP Address in MySQL
There is one configuration change that need to do in MySQL Server for connecting to MySQL Database from the remote clients and the change need to do in /etc/mysql/my.cnf file it is crucial for MySQL Database
In the my.cnf file there are so many attributes in which you need modify a change and below screen shot will give you how to modify it.
The binding of IP Address means allowing the remote client to access the MySQL database Serverbecause the IP Address shown in the picture allows only the users who are connecting from the Server on which the MySQL software is installed and in order to allow remote client connections we need to comment the attribute bind-address so all the client connections can now access the MySQL Database which is resided on the different sever.
After doing the necessary modifications in the my.cnf file we need to restart the MySQL Server for the impact.
The MySQL Server is a client Server Architecture so to establish the communication between the client and server MySQL relies upon network protocols and we all know the TCP/IP is the standard protocol in the industry which used by two systems to communicate with each other for data transmission and data loading between the Database Servers.
MySQL DATABASE ARCHITECTURE
The MySQL Database is a combination of Logical layer and physical layer the logical layer contains the memory structures and the physical layer contains the storage engines.
Query Parsing: It is the validation of the sql query to find out are there any syntactical mistakes in the sql statement.
Optimization: The optimization means executing the query with the best execution plan to access the data faster.
Executing: The execution means following the optimized plan and query parsing for the better execution.
Query Cache: The query cache is a memory structure which holds the data and returns very quick passed by a select query.
A storage engine is a software module that a database management system uses to create, read, update data from a database. There are two types of storage engines in MySQL: transactional and non-transactional
Non –Transactional:The non transactional storage engine is used for storing the historic data where a particular storage engine cannot support all kind of data that we store in a storage engine
- Customer: Name, Preferences
- Product: Name, Hierarchy
- Site/Location: Addresses
- Account: Contracts Detail
Transactional: It is a storage engine which stores only the transactional data based on this the storage engine is used for the better performance
- Financial: orders, invoices, payments
Every storage engine will not be suitable for all the data so depending on the type of data we maintain the different type of storage engines
The most popular storage engines are
The InnoDB storage engine is a default storage engine in MySQL and it has full control in the usage of storage which can be defined according to the requirement we can restrict the storage space for different kinds of files.
The Features of InnoDB :
- Transactional support provided by MVCC (Multi Version Concurrency Control)
- Row-level locking
- Foreign key support
- Indexing using clustered B-tree indexes
- Configurable buffer caching of both data and indexes
- Online non-blocking backup through separate commercial backup program
The MyISAM is a storage engine which is used for the fast data retrieval and storage but it is not used for the transactions storage because it is not safe.
The Features of MyISAM:
- No foreign key support
- No data cache
- Index caches can be specified by name
- Table level locking
- Very fast read activity, suitable for data warehouses
- Compressed data (with myisampack)
- Online backup with mysqlhotcopy
- Maximum of 64 indexes per table