• TestingDocs
TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

MySQL

MySQL InnoDB Storage Engine

Overview

In this tutorial, we will learn about InnoDB Storage Engine in MySQL. The support for the InnoDB storage engine is a standard feature in MYSQL database.

InnoDB Features

The InnoDB storage engine manages tables that have the following characteristics:

The InnoDB table is represented on disk by an *.ibd file format in the database directory using InnoDB tablespace. A tablespace is a logical single storage area made up of one or more files or partitions on the disk. By default, it uses a single tablespace that is shared by all database tables.  Tablespace storage format is portable.

We can configure InnoDB to create each table with its own tablespace.

mysql> SHOW VARIABLES LIKE ‘%innodb%’;

MySQL InnoDB Variables

InnoDB storage engine supports foreign keys and referential integrity, including cascaded deletes and updates,multi-versioning concurrency control (MVCC) and Row-level locking.

This InnoDB storage engine supports transactions, with COMMIT and ROLLBACK. It also provides full ACID compliance. This storage engine manages a set of InnoDB-specific log files that contain information about ongoing transactions. As a client performs a transaction, the changes that it makes are held in the  log file. The more recent log contents are cached in memory. Normally, the log buffer is written and flushed to log files on disk at transaction commit time.  We can also configure this to take place periodically.

It also provides auto-recovery after a crash of the MySQL server or the host on which the server runs. If a crash occurs while the tables are being modified, the log files are used for auto-recovery: when the MySQL server restarts, it reapplies the changes recorded in the redo logs, to ensure that the tables reflects all committed transactions.

ENGINE=InnoDB option

When we create a table, we can choose what storage engine to use. We can use the ENGINE=InnoDB option in the CREATE TABLE statement to create table using the InnoDB engine.

For example, the following statement creates table using the option:

mysql> CREATE TABLE demotable (
-> id INT NOT NULL,
-> name CHAR(40) NOT NULL
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

 

ENGINE Option InnoDB Table

When we create a table WITHOUT using the ENGINE option to specify a storage engine,
the MySQL server creates the table using the default storage engine.

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/

For more information on MySQL Database:

https://www.mysql.com/

Related Posts

MySQL /

How to secure your SQL Database: Tips and Tricks

DataGrip Desktop Shortcut

MySQL /

Launch DataGrip on Windows

DataGrip Download

MySQL /

Install DataGrip IDE on Windows 11

MySQL Workbench Windows 11

MySQL /

New MySQL Connection in Workbench

MySQL Command-line Client

MySQL /

Command-Line MySQL Client

‹ MySQL SCHEMATA Table› MySQL MyISAM Storage Engine

Recent Posts

  • How to secure your SQL Database: Tips and Tricks
  • Shaping the Future of Development: Exploring Key Trends in Software Engineering
  • Improving Java Performance with Multithreading
  • Difference between PHP and JavaScript?
  • Bing Conversation Styles
  • ChatGPT Introduction
  • Open Source AI Frameworks
  • Artificial Intelligence Tools
  • Top AI Music Applications
  • Top AI Website Design Tools

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com