1

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
Rohit Swamy
  • 11
  • 1

1 Answers1

1

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • In future no. of databases on different server will be increased It might 50,100,may be 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. Sometime 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 mssql but here this is mysql. – Rohit Swamy Aug 17 '20 at 13:36
  • This sounds like a sizeable project to me, writing a program to run from cron or the Windows task scheduler, to open connections to each database in turn, retrieve the necessary data, and insert it into the central system. My guess: it will take about the same amount of time to script up an ETL program as it will to write a custom program. I don't envy your task of setting your customer's expectations about this. – O. Jones Aug 17 '20 at 13:55
  • So it can be only done through some scripting language or something like you said Php, Java, Nodejs, Python,Perl etc.. – Rohit Swamy Aug 17 '20 at 14:02
  • Using a programming language is how I would do this. That's my choice because commercial ETL packages are expensive, poorly documented, and hard to configure. But I would not say my way is the *only* way. If you only have a few satellite databases now, you could handle this stuff manually until you get your program working. – O. Jones Aug 17 '20 at 14:08
  • Any suggestion sir which etl tool can be helpful in such situation.. – Rohit Swamy Aug 17 '20 at 14:12