Mysql – Move Oracle data to and from MySQL with the help of PHP

From your question, it doesn't sound like you need true transactional semantics, and that asynchronous replication would be fine.

If that's the case, it's fairly trivial to build a replication system using triggers within Oracle to track data changes, and then replaying those changes into MySQL, using something like perl (or other scripting languages) as the glue code to talk between both systems using SQL.

This might sound like it would be a lot of overhead, but we've built numerous systems like this for our clients, and the overhead from Oracle's standpoint is pretty low; we've run it on some OLTP systems powering top 1000 websites that had hefty transactional workloads, and Oracle handled it like a champ.

Setup:

1. Take a mysqldump of the 'default' data.
2. Put the output into a Stored Procedure.
3. Modify the stored procedure to also CREATE DATABASE and USE that database. The database name would be dynamic -- probably the user id, with something prepended to avoid naming conflicts. Also surround with backtics.

User comes in:

1. perform that stored procedure.

User leaves. This could be tricky -- what if he stays logged in? what if some server dies without getting to this code? Etc. There may need to be something that runs around periodically to clean up orphaned databases.

1. Drop his database.

Note: Your question implied that everyone is using the same database. I switched to having everyone use a different database.