php - Is it possible make dump of mysql database with updated content in codeigniter

Solution:

Strictly speaking, then answer is no, you cannot change the contents of the data this way, since the backup method takes a list of tables and dumps those to a file as they are. Well, this is exactly what you would expect from a backup solution.

What you could do, however, is to create views that incorporate the replacement logic you require. Then you can use the backup method to back the views up, not the tables.

Otherwise, you need to write the logic either in plain queries or implement it in php code and create the dump files yourself.

Solution:

Trying to change the data in-flight like this is going to be some blend of "inadvisable" and "impossible". What I do is first clone the DB to a staging location, and then run data scrub scripts for PII and other sensitive information. Then you can make that available to clone to dev environments.

However, for values like the email address that may be used in relations or otherwise be required to be unique you will want to take extra care, as we found out the hard way, when you change all the emails to example@example.com properly testing anything relating to that information becomes virtually impossible.

For something like this I would suggest deriving a dummy email from the real one, eg:

UPDATE users
SET email = CONCAT(
  SUBSTRING(MD5(email),1,16),
  '@',
  SUBSTRING(MD5(email),17,16),
  '.com'
);

Result:

foo@bar.com -> f3ada405ce890b6f@8204094deb12d8a8.com
bar@foo.com -> dc8a42aba3651b0b@1f088ef928ff3b1d.com

However, as your DB grows cloning like this becomes more and more of a problem, particularly for local development, as the DB size will likely only grow over time. As it stands our prod DB is approaching 300GB and some of our devs' machines cannot fit a copy in addition to all of their other requirements. Plus the cloning and scrubbing takes hours, and now with everyone remote it poses additional logistical problems to get those clones out to devs over already-constrained links.

The best solution, particularly for local dev, is to only dump the schema of the database and fill it with a representative set of mocked data for testing. For full-blown tests we maintain full-copy, scrubbed versions of the DB in our CI/CD pipeline environments.