Approach in order to spread database - Architecture

MySQL & MariaDB Other MySQL® Questions
1

Hello Gurus

I have got a customer with a large database handling a business for a lot other customers, everything into just one database .

There is a large table with FKs with over 300 billions lines and also as mention before the main idea is divide the database for business branchs with minimum downtime in order to reduce outage risk and maintanance as well .

Do you have any approach or tool in order to set a continuous migration ( CDC ) by customer ?

Thanks a lot
Andre

2

Hello @Andre1976,
You won’t really be able to do this without some downtime. What you have is a multi-tenant, or SaaS type database. You create 1 database per customer, and have the same tables in each database, but with only the data for that customer.
In order to accomplish this, you’ll need to create the database, create the tables, then do INSERT INT O… SELECT … FROM the main tables to copy the data for each customer. Then you need to update your code to tell it to select the data for customer X out of database X. Good luck!

3

Hello @matthewb

Thanks a lot for your feedback! Otherwise, this database is extremly critical for business and I need to think in something like continuos replication.

I totall agree with you about some approachs like ( select * into outfile /mysqldump/table.csv from table + LOAD DATA LOCAL INFILE /mysqldump/table.csv into table , however the questions is, how can I set something likewise Data Migration Service CDC for specific customer ( ids ). I have never worked with any tool to do it this way

Thanks in advance

Andre Rodrigues

4

You would need to write this tool. There is nothing that exists built-in/bundled with MySQL to move data from one table to another. (Ehh, you might be able to use pt-archiver but you would have to run 1 instance of it per table, per customer id)

You could also set up X MySQL replica servers, replicating from this single source. When the time comes, break replication on all X, then delete from replica 1 all the data that does not need to be there. Repeat for remaining replicas.

1 Like
5

Thanks a lot you @matthewb .

The replication + Discarding all unneeded data solution I believe to be more feasible and less risk .
Best Regards
Andre Rodrigues