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.
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[email protected]
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:
[email protected] -> [email protected]
[email protected] -> [email protected]
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.
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
CodeIgniter is a framework that is known for requiring a minimum amount of customization to get it up and running. This allows those who choose it to work at a good pace. It has been updated many times since its inception in 2006. Now the most recent version is 4.0.3.
https://www.codeigniter.com/
DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL.
It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
https://www.mysql.com/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.