Overview of MySQL Alternative Storage Engines – Percona Database Performance Blog

For MySQL, MyISAM and InnoDB storage engines are extremely popular. At the moment, we’re principally utilizing InnoDB engines for prime reliability and excessive efficiency. Other than these engines, we even have another different engines and so they have some good options in them. On this weblog, I’m going to elucidate a few of these engines, which I’ve listed under. 

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

FEDERATED Storage Engine

Overview:

  • FEDERATED Storage Engine permits you to entry the information remotely with out replication and cluster applied sciences. 
  • Utilizing the FEDERATED tables, you’ll be able to scale your server load. Queries for the given desk might be despatched over the community to a different MySQL occasion. On this case, to scale the DB, you should use many MySQL cases with out altering the appliance code.
  • FEDERATED tables are a safety concern as a result of you will want to save lots of the host and person data within the desk. It may be seen utilizing SHOW CREATE TABLE command.
  • Question optimization is proscribed and JOINs are sluggish.
  • Doing the majority transaction might crash the native server.

By default, FEDERATED Storage Engine assist is disabled. To allow it, you should manually allow the variable “federated = ON” within the MySQL config file and restart the MySQL service. 

See also  17 Sites to Download Free Background Music for Video Editing

How Does it work?

  • FEDERATED tables must be created on a neighborhood server and the distant desk must be created on a distant server.
  • Just be sure you have the MySQL port and person entry between the native and distant servers.
  • Distant tables could be created as MyISAM or InnoDB storage engines.
  • The FEDERATED desk won’t retailer any knowledge. Information might be saved on the distant server.
  • Each native and distant servers ought to have the identical columns and construction.
  • You possibly can execute the question on each native or distant servers to change or retrieve the information.

Instance

I’ve two servers:

  • 172.28.128.16 (native server)
  • 172.28.128.17 (distant server)

On the native server, I’m creating the FEDERATED desk:

Syntax is:

On the distant server, I’m creating the desk with InnoDB engine: