Overview of MySQL Alternative Storage Engines

MySQL Alternative Storage EnginesFor MySQL, MyISAM and InnoDB storage engines are very popular. Currently, we are mostly using InnoDB engines for high reliability and high performance. Apart from those engines, we also have some other alternative engines and they have some nice features in them. In this blog, I am going to explain some of those engines, which I have listed below. 

  • FEDERATED Storage Engine
  • Merge or MRG_MyISAM Engine
  • Blackhole Engine
  • CSV Engine

FEDERATED Storage Engine

Overview:

  • FEDERATED Storage Engine allows you to access the data remotely without replication and cluster technologies. 
  • Using the FEDERATED tables, you can scale your server load. Queries for the given table will be sent over the network to another MySQL instance. In this case, to scale the DB, you can use many MySQL instances without changing the application code.
  • FEDERATED tables are a security concern because you will need to save the host and user information in the table. It can be viewed using SHOW CREATE TABLE command.
  • Query optimization is limited and JOINs are slow.
  • Doing the bulk transaction may crash the local server.

By default, FEDERATED Storage Engine support is disabled. To enable it, you need to manually enable the variable “federated = ON” in the MySQL config file and restart the MySQL service. 

How Does it work?

  • FEDERATED tables need to be created on a local server and the remote table needs to be created on a remote server.
  • Make sure that you have the MySQL port and user access between the local and remote servers.
  • Remote tables can be created as MyISAM or InnoDB storage engines.
  • The FEDERATED table will not store any data. Data will be stored on the remote server.
  • Both local and remote servers should have the same columns and structure.
  • You can execute the query on both local or remote servers to modify or retrieve the data.

Example

I have two servers:

  • 172.28.128.16 (local server)
  • 172.28.128.17 (remote server)

On the local server, I am creating the FEDERATED table:

Syntax is:

On the remote server, I am creating the table with InnoDB engine:

As I mentioned earlier, the data will be physically stored on the remote server. FEDERATED tables will not store the data. From the below example, you can see the data file (.ibd) was created on the remote server and the local server just has the table structure file ( .frm ).

Local server:

Remote server:

Let’s do this experiment. On the local server, I am inserting the record. 

And on the remote server: