CODEWITHSUNDEEP
mysqletlremote-servermultiple-databasesmariadb-10.4
Rohit Swamy
Rohit Swamy

Reputation: 11

How I can connect and fetch the data from multiple mysql databases on multiple severs?

I want to fetch data from multiple mysql databases which are on multiple servers. I'm using phpmyadmin (mysql). All the databases will be mysql database (same vendor) which are on multiple servers. First I want to connect to those server databases and then I want to fetch data from them and then put the result in central database.

For example : remote_db_1 on server 1, remote_db_2 on server 2, remote_db_3 on server 3. and I have central database where I want to store the data which comes from different databases.

Query : select count(user) from user where profile !=2; same query will be run for all the databases.

central_db

school_distrct_info_table

id  school_district_id  total_user
1.      2                  50
2.      55                 100
3.      100                200

I've tried federated engine but it doesn't fit to our requirement.What can be done in this situation any tool, any alternative method or anything.

In future no. of databases on different server will be increased. It might 50, 100, maybe more, exporting the tables from source server & then load to central db will be hard task. So I'm also looking for some kind of etl tool which can directly fetch data from multiple source databases and then sending the data to destination database. In central db table, structure,datatypes,columns everything will be different. Sometimes we might need to add extra column to store some data I know it can be achieved through etl tool in the past I've used ssdt which works with SQL Server but here this is mysql.

Upvotes: 1

Views: 1175

Answers (1)

O. Jones
O. Jones

Reputation: 108746

The easiest way to handle this problem is with federated servers. But, you say that won't work for you.

So, your next best way to handle the problem is to export the tables from the source servers and then load them into your central server. But that's much harder. This sort of operation is sometimes called extract / transform / load or ETL.

You'll write a program in the programming language of your choice (Python, php, Java, PERL, nodejs??) to connect to each database separately, then query it, then put the information into a central database.

Getting this working properly is, sad to say, incompatible with really urgent. It's tricky to get working and to test.

May I suggest you write another question explaining why server federation won't meet your needs, and asking for help? Maybe somebody can help you configure it so it does. Then you'll have a chance to finish this project promptly.

Upvotes: 1

Related Questions