How to make a copy of database to itself in case of multi-tenancy

Question

I have multi-tenant systems wtih mysql database. There is discriminator-based multi-tenancy. Each table contains column tenantId. I have default tenant with tenantId = -1. For this default tenant database contains initial data for all new tenants.

How can I copy all data with tenantId = -1 for a new tenantId in same database? There are a lot of tables in database, so it is very hard to write such script manually.

Main Problem: If I will make dump of db then I can not load this dump to the same db, because primary keys and foreign keys should be new.

Answer 1

You'll want to use the -w (or --where) option in mysqldump. Since all your tables have the tenantId this should work:

mysqldump myDB --where='tenandId=-1'