MySQL Basics

History of MySQL

  • MySQL was created by a Swedish company, MySQL AB, founded by David Axmark, Allan Larsson and Michael “Monty” Widenius.
  • The first version of MySQL appeared on 23 May 1995. It was initially created for personal usage from mSQL based on the low-level language ISAM.
  • Sun Microsystems acquired MySQL AB In 2008 .
  • Oracle acquired Sun Microsystems on 27 January 2010.

Why MySQL

1.Scalability and Flexibility

2.High Performance

3.High Availability

4.Robust Transactional Support

5.Web and Data Warehouse Strengths

6.Strong Data Protection

7.Comprehensive Application Development

8.Management Ease

9.Open Source Freedom

10.Lowest Total Cost of Ownership

 

introduction-to-mysql-4-728

MySQL Engines

  • Recommended
  • InnoDB
  • NDB Cluster
  • MyISAM
  • Other Engines
  • Memory
  • Archive
  • Federated
  • Blackhole
  • CSV
  • InfoBright (ICE)

MyISAM Storage Engine

  • The MySQL storage engine that is used the most in Web, data warehousing, and other application environments.
  • MyISAM is supported in all MySQL configurations, and is the default storage engine prior to MySQL 5.5.5.
  • Each MyISAM table is stored on disk in three files.
  • An .frm file stores the table format.
  • The data file has an .MYD (MYData) extension.
  • The index file has an .MYI (MYIndex) extension.
  • MyISAM is non transaction-safe engine.
  • fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used).
  • full text indexing (update: supported in InnoDB from MySQL 5.6).
  • smaller disk footprint.
  • very high table compression (read only).
  • MyISAM has table-level locking, but no row-level locking.
  • No transactions.
  • No automatic crash recovery, but it does offer repair table functionality.
  • No foreign key constraints.
  • MyISAM tables are generally more compact in size on disk when compared to InnoDB tables.
  • MyISAM tables could be further highly reduced in size by compressing with myisampack if needed, but become read-only.
  • MyISAM stores indexes in one file and data in another.

MyISAM uses key buffers for caching indexes and leaves the data caching management to the operating system.

 

Innodb Storage Engine

  • InnoDB is a general-purpose storage engine that balances high reliability and high performance.
  • In MySQL 5.6, InnoDB is the default MySQL storage engine.
  • InnoDB table creation.
  • Its DML operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
  • Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
  • InnoDB tables arrange your data on disk to optimize queries based on primary keys.
  • To maintain data integrity, InnoDB also supports FOREIGN KEY constraints. With foreign keys, inserts, updates, and deletes are checked to ensure they do not result in inconsistencies across different tables.
  • You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.
  • InnoDB has been designed for CPU efficiency and maximum performance when processing large data volumes.
  • The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory.
  • By default, with the innodb_file_per_table setting enabled, each new InnoDB table and its associated indexes are stored in a separate file.
  • When the innodb_file_per_table option is disabled, InnoDB stores all its tables and indexes in the single system tablespace, which may consist of several files (or raw disk partitions).
  • InnoDB tables can handle large quantities of data, even on operating systems where file size is limited to 2GB.

 

How to choose MySQL Storage Engine

  • Concurrency: Some applications have more granular lock requirements (such as row-level locks) than others. Choosing the right locking strategy can reduce overhead and therefore improve overall performance. This area also includes support for capabilities such as multi-version concurrency control or “snapshot” read.

 

  • Transaction Support: Not every application needs transactions, but for those that do, there are very well defined requirements such as ACID compliance and more.

 

  • Referential Integrity: The need to have the server enforce relational database referential integrity through DDL defined foreign keys.

 

  • Physical Storage: This involves everything from the overall page size for tables and indexes as well as the format used for storing data to physical disk.

 

  • Index Support: Different application scenarios tend to benefit from different index strategies. Each storage engine generally has its own indexing methods, although some (such as B-tree indexes) are common to nearly all engines.

 

  • Memory Caches: Different applications respond better to some memory caching strategies than others, so although some memory caches are common to all storage engines (such as those used for user connections or MySQL’s high-speed Query Cache), others are uniquely defined only when a particular storage engine is put in play

 

  • Performance Aids: This includes multiple I/O threads for parallel operations, thread concurrency, database check pointing, bulk insert handling, and more.

 

MySQL Installation

MySQL standard Installations :

  • RPMS Installation
  • Binary Installation
  • Source Installation

Other Installation Methods :

  • MySQL Yum Repository

MySQL Yum repository supports the following Linux Distros:

Red Hat Enterprise Linux 7 / Oracle Linux 7

Red Hat Enterprise Linux 6 / Oracle Linux 6

Red Hat Enterprise Linux 5 / Oracle Linux 5

Fedora 21, 22 and 23

  • MySQL APT Repository

The APT repository supports the following Linux Distros:

Debian – 7

Debian – 8

Ubuntu – 12.04 LTS

Ubuntu – 14.04 LTS

Ubuntu – 14.10

Ubuntu – 15.04

  • MySQL SUSE Repository

The repository at this time supports the following distro versions:

SLES 12 for x86-64

SLES 11.3 for x86-64

  • MySQL on Windows.

 

MySQL User Management

Capture

 

  • Global Privileges

Global privileges are administrative or apply to all databases on a given server.

To assign global privileges, use ON *.* syntax:

aa

  • Column Privileges

Column privileges apply to single columns in a given table. Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses.

ab

  • Stored Routine Privileges

ac

  • Proxy User Privileges

The PROXY privilege enables one user to be a proxy for another.

The proxy user impersonates or takes the identity of the proxy user.

ad

 

 

 

 

 

 

 

 

 

 

 

 

Related Posts:

Leave a Comment