MySQL Storage Engines - Its types, InnoDB, installing and disabling storage engines.

 MySQL Storage Engines

The main use of storage engines in DBMS is to manage how data is stored, retrieved, manipulated within the database.

It is a pluggable storage engine architecture ,i.e, you can load and unload the data while the MySQL server is running.

Example of storage engines are : InnoDb, MyISAM, Federated, Memory, CSV, Blackhole etc.

You can view the different types of storage engines in MySQL by executing:

    show engines;





    





As they are plugins, they are installed under specific file path. 
You can find the path of the plugins in your mysql server by executing:

    show variables like 'plugin_dir';











Now, let's visit this file location and check which plugins are inside it.

    ls -ltr /usr/lib64/mysql/plugin/

Important: To use any storage engine in mysql, we need to copy its .so file, drop it to the path location stated above, and then use the install plugin command to access it.


Now let's try some of the storage engines in MySQL:

1. Memory Storage Engine

  • All table are stored in memory, ensuring quick access.
  • Stores temporary data inside the tables, data is lost when MySQL is shut down or crashes.
  • It does not support transactions and and foreign key constraints.
  • You can use this storage engine for lookups.

Lets create one table named continent in world database using engine as memory.

    create table continent(
    c_id int primary key auto_increment,
    c_name varchar(255) not null
    )engine=memory;







Now Populate the table with some data.

INSERT INTO continent (c_name)
    -> VALUES
    -> ('Asia'),
    -> ('Africa'),
    -> ('Australia'),
    -> ('North America'),
    -> ('South America'),
    -> ('Europe'),
    -> ('Antarctica');

Now as we have discussed earlier in our previous blogs that information_schema(default database) contains metadata(data about data).
Therefore we check the properties of our table using information_schema database

     use information_schema;

  select table_schema,table_name,table_type,engine,version,table_rows
  from tables
  where engine = 'MEMORY';



    
     





Now, as it stores temporary data, lets restart the MySQL sever and check whether the data is there or not.

    sudo systemctl stop mysqld

    sudo systemctl start mysqld

We have now restarted the mysql server.

Now check whether data is there or not in the continent table.

    select * from continent;





Note: Only data stored gets lost, the table and its structure remains intact.


2. Blackhole Storage engine

  • You can store as much data as you want, but when you try to retrieve it , it will return an empty set.
  • It also does not support transactions.

Now let's check this storage engine by creating one table and inserting data into it.

I am using the same table structure which I have used in memory storage engine ( table name: continent)

    create table continent(
    c_id int primary key auto_increment,
    c_name varchar(255) not null
    )engine=blackhole;


Now, Populate the table with some data (use the same data that was used in the memory storage engine part)

Now, try to retrieve the data:

    select * from continent;



Well, yes, the data you stored earlier is now resulting in an empty set.



3. CSV Storage engine

  • CSV storage engine is a engine that stores data in comma separated values files.
  • Mainly useful for exchanging data between MySQL and other application that supports .csv files.
  • Does not support transactions.
  • It does not support the uses of indexes and keys.

Now let's check this storage engine by creating one table and inserting data into it.

The syntax for table creation is:

    create table continent(
    c_id int not null,
    c_name varchar(255) not null
    )engine=csv;

(Remember, you cannot use keys or auto_increment while creating tables with csv as your engine type. This goes for many other storage engines as well)

Insert the data into the table:

INSERT INTO continent (c_id, c_name) VALUES
(1, 'Asia'),
(2, 'Africa'),
(3, 'North America'),
(4, 'South America'),
(5, 'Antarctica'),
(6, 'Europe'),
(7, 'Australia');

Now you can check the .csv file in data_dir file path i.e. /var/lib/mysql/database_name

    sudo cat /var/lib/mysql/world/continent.CSV






    





4. Archive Storage Engine

  • Archive storage engine is used to store large amount of un-indexed data in a highly compressed file.
  • Creates a file with the same name as the table using .ARZ extension.
  • ARZ files are binary data files and are called as MySQL Archive Storage Data engine file.
  • The archive storage engine uses .zlib library for compressing rows 
  • Does not support DELETE and UPDATE operations.
  • No support for transactions.

The syntax for table creation is as follows:









Insert some dummy values inside of it.

You can check the .arz file in the following data path: i.e. sudo ls /var/lib/mysql/world







Now, before jumping to the next storage engine, let's take a moment to understand the ACID properties.


ACID --> Atomicity, Consistency, Isolation, Durablity

Atomicity: A transaction must either complete fully or not at all.

Consistency: After any operation, the data should follow all the rules set up for the database like unique keys and constraints.

Isolation: Transactions happening at the same time should not affect each other.

Durability: Once a transaction is complete, its change are permanent even if the system crashes.


5. InnoDB storage engine

  • It is the default storage engine of MySQL, known for its reliance, performance and ACID compliance.
  • Supports ACID properties, transactions and foreign key constraints.
  • Uses row-level locking instead of table-level locking.
  • It has built in crash recovery mechanism.
  • You can also create indexes.

The syntax for table creation is as follows:








As InnoDB is default storage engine, there is no need to specify the engine type explicitly.

Let's see how InnoDB supports transactions.

1. First start the transaction 

        start transaction;

2. Now update one row.

        update continent set c_name = 'NA' where cid = 4;

3. Now verify whether the changes are made or not.

        select * from continent;














4. Now rollback the transaction.

        rollback;

5. Now check the table again to see whether the previous changes have been discarded or not.

        select * from continent;




     








As you can see the changes made to the 'continent' table have been discarded, because the transaction was rolled back before being committed.


To check the status of the storage engine you can execute the following command:


        show engine innodb status\G


To switch the storage engine, you can execute the follwoing command:


        alter table continent engine = 'engine_name';

 
Now to install any storage engine, you need to copy the so file of that storage engine from the plugin directory.

To know the path for the plugin directory:

    show variables like 'plugin_dir';

After you have copied the .so file of the storage engine you want to install, you can log in to MySQL and execute this command:

    install plugin example soname 'ha_example.so';
















 To uninstall the storage engine:


    uninstall plugin example;


Now, how to disable the storage engine ?


To disable the storage engine, you need to make changes in your .cnf file which is present in /etc/my.cnf file path.

    sudo nano /etc/my.cnf










After making these changes in the configuration file, you have to restart the mysql server to apply the changes made to the configuration file.

    sudo systemctl restart mysqld

After restarting the mysql server, log in to mysql and create a table using a storage engine that you have disabled.








Table creation is disallowed because the storage engine 'MEMORY' is disabled.


Great! We have briefly discussed about what is a storage engine, what are its types, how to check the status of storage engines, how to switch between the engines, and how to disable the storage engines.🎉


Comments

Post a Comment

Popular posts from this blog

MySQL Configuration File - Updating and changing the default location of my.cnf

Image
MySQL Option file or Configuration file Most MySQL programs can read startup options from option files (also called as configuration files ). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. File path: /etc/my.cnf The   mysqld --verbose --help   is used to display detailed information about the mysql server's configurations, options, and supported features. To check the contents of the my.cnf file :     sudo nano /etc/my.cnf Let's update our configuration file now. We will remove all the comments and one option of 'server-id', for which we will assign the value as 1. To check for the serer-id in MySQL you execute this command:     show variables like '%server_id%'; As you can see the server_id is 1. Now let's update this in our option file. In our configuration file we will add the following line:     server-id=1 Notice, that our system ...
Read more

MySQL User Administration - Creating new users, logging in with them , and dropping users.

Image
Learn how to create a new user and explore multiple methods to connect to mysql with that user. Additionally, discover the steps to delete a user when no loner needed. Let's look at the steps to create a MySQL user: Step 1: First let's check what options are available when creating a user.     help create user; Step 2: To create user execute this command:     create user if not exists 'user_name' identified by 'password' password expire never account unlock; Step 3 : Now let's grant the permissions to the user. In this case we are granting all the privileges to the user.     grant all privileges on *.* to 'junior' with grant option; Step 4: Flush the privileges.     flush privileges;     (To reload the privileges, ensuring any changes made to the account take effect immediately) Step 5: To confirm whether all privileges are granted:     show grants for user_name; Now the question arises: how do we connect to mysql with the ...
Read more

MySQL config editor , Executing MySQL commands from the terminal, mysqlimport and mysqlcheck

Image
We will log in to MySQL without entering credentials by using MySQL config editor, then execute mysql commands from the terminal, import the data into a mysql table, and check data integrity using mysqlcheck. mysql_config_editor is a command line utility provided by MySQL to securely manage login credentials. It can store credentials like username, password, host and port in an encrypted login file path. To learn more about this command and how to use it you can execute this query:          mysql_config_editor --help Now we will set the credentials by using mysql_config_editor:     mysql_config_editor set --user=root --password -login-path=client To verify whether the credentials are stored and encrypted :     mysql_config_editor print Now let's check whether we can log in to MySQL without providing user and password credentials.     mysql Great! We have successfully stored our credentials with the help of mysql_config_editor and...
Read more