Merging 4 databases

Is the database structure the same in all 4 databases ?

Yes, they are all the exact same structures

Sorry, I mistakenly clicked 'best answer', now I cannot remove that again.

It's tricky and would most likely involve lots of temporary tables.

However something like this should work

1. Export table 1 from DB1
2. Import it as table_db1
3. Repeat for all tables and DBs
4. Insert table_db1, table_db2, table_db3 & table_db4 into table
5. Use the _db tables to help you get the correct relations on the other tables referencing one or more foreign keys.

It's an idea yes, unfortunately, there are 30 tables and this would be a slow process.

Another option would be to write a custom importer, but that's not a small task either.

To get that custom importer to work will take longer time than doing it manually.

For small databases yes. There are more than 10.000.000 records, so I'm not sure about that.

SELECT TABLE_NAME, AUTO_INCREMENT
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = '__DATABASE__'

This gives me the max ID in each table, I can add that to the next one before import. That might work.

How about you add a temporary old_id (which you clear between database imports) to all your new database tables as well as temporary foreign key fields old_..._id (which you also clear between imports) ? Then you link all your entries together based on those IDs, but you assign the new incremented IDs to the "real" columns.