Effortless Data Migration Using MySQL Federated Engine

Scenario:

If someone say you Hey, can you transfer one of MySQL data to another MySQL data and we think about SSIS or other Thing if yes then these article made for you to reduce your effort and save your time

Introduction:

In the dynamic landscape of database management, the need to seamlessly access and integrate data from multiple sources has become paramount. Whether it's consolidating information from disparate servers or synchronizing databases for backup and redundancy, MySQL offers a robust solution through its querying capabilities.

In this guide, we delve into the art of fetching data from one MySQL server to another using SQL queries. This method, often overlooked in favor of complex data transfer mechanisms, provides a streamlined approach to data migration, enabling developers and database administrators to efficiently manage their resources.

Through a combination of MySQL's versatile querying language and the innovative use of the FEDERATED storage engine, we'll explore how to establish connections between servers, replicate table structures, and effortlessly transfer data across the network. From setting up the environment to executing queries and troubleshooting common challenges, this tutorial equips you with the knowledge and tools to navigate the intricacies of cross-server data retrieval with ease.

As we know We gonna use FEDERATED feature of MySQL workbench so first we need to check that our workbench support FEDERATED engine or not?

 

Simply open workbench and run below code

show engines;

 

It shows all engines and check our system support FEDERATED OR NOT

 

If your system also not support don't worry we gonna enable it

Open your folder where you save MySQL serve file

In my case it in my C drive

C>ProgramData>MySQL>MySQL Server 8.0>my.ini    open it in notepad++ or preferable software 

 

Insert FEDERATED key word in script like below

 

Now need to restart MySQL

Press Window+R button and paste services.msc press ok> find MySQL and restart it

Now go to workbence and run show engines;  code

 

Now your FEDERATED engine get supported It show like below

 

Now our system Support FEDERATED engine

This same process need to apply on destination side because both server (from source to destination server) need to support FEDERATED engine

Now we make sure to we have permission of access source server for that we need to make user and and give permission of database and tables

 

Below code demonstrate to make user and give permission to user

CREATE USER 'hmysql'@'192.168.1.173' IDENTIFIED BY 'Hardik...';
GRANT ALL PRIVILEGES ON *.* TO 'hmysql'@'192.168.1.173' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 

Now make connection of that user(we make above on source side) on destination server(our system) 

 

Click on plus(+) icon as shown in image and fill all detail

 

Below image is for detail of user connection

 

After filling details our user added like below image

 

Go to user(hardikmysql) and find from which table we want to take data using MySQL query 

 

Here i am taking 'actor' table from 'sakila' database which look like below

 

Now we need to run FEDERATED query on our system(destination server) with url string

 

Our MySQL query like below

CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=FEDERATED
default charset=utf8mb4
CONNECTION='mysql://hmysql:[email protected]:3306/sakila/actor';

 

Here main part is below

ENGINE=FEDERATED
default charset=utf8mb4
CONNECTION='mysql://hmysql:[email protected]:3306/sakila/actor';

 

Here 'mysql' is mandatory for connection string you can not use other word.

'hmysql' is user name

'Hardik...'  is password for user

'192.168.1.173' is server adderess

'3306' is port number

'sakila' is database name

'actor' is table name

 

Now run above table code and you get data in our system(destination server)