Accessing foreign tables in your local MySQL or MariaDB

Yesterday I was needing to migrate a website, but it was not just another website. Many of you may know I have a project called EstoyEn. EstoyEn is a service that allows you to reach content with geo restriction; in other words, you may be able to watch Netflix catalogs from another country among other things. One of the techniques I use to archive this is using DNS and reversal HTTP/HTTPS proxies.

To justify the use of this database technique, the DNS uses the database to know how to answer. So, as the DNS is a public IP already know by all users, it can not be moved; I just need to move out the website. So, Ii needed an easy way to have some database tables locally without breaking the website; here it is were federated engine comes.

Federated engine allows accessing a foreign table locally. Both servers communicate each other using the MySQL protocol. So, as you suppose, the MySQL remote server (server A) must have access to the foreign database (server B, where the information is) by having proper username and password. Assuming you have all this, log into your MySQL server A database and type CREATE TABLE local_name ENGINE=FEDERATED CONNECTION='mysql://remote_user:password@server_b_ip:3306/database_name/table_name';

If you get an error that FEDERATED engine is not a valid value, you need to enable it by typing INSTALL PLUGIN federated SONAME 'ha_federatedx.so';

After this, you are done. You can do SELECT, UPDATE, DELETE queries in server A.

Enjoy!