RE: Masquerade Your Backups To Build QA/Testing Environments With MyDumper

[matthewlenz]

David, I read your blog post earlier this month and I was curious if this had been added to the most recent release? I was also curious if it is possible to use this to set a column to static value? I have a need to set a specific json column to NULL on export. Right now the way I'm doing this is dumping using --load-data and then manipulating that file directly. It would be nice to skip this step. Another reason I use --load-data is because I need to re-encrypt a specific column using a different key. The following would be super useful:

I understand that some of this functionality means that mydumper would no longer be able to just do a simple select * from ... when dumping but this would be AWESOME. Maybe that's already the case with your new masquerade functionality.

List which columns to dump.

cols-include = col1,col2,col4,col5
or
cols-exclude = col3

Allow specifying a static value.

col1 = NULL

Allow application of db functions or keywords to the output of a field.

col2 = AES_ENCRYPT(AES_DECRYPT(col2, 'myoldkeystring'), 'mynewkeystring')
col2 = DEFAULT

[davidducos]

Hi @matthewlenz,
I will try to answer all your questions!

Yes, it has been added to the latest releases.

You can use a constant value like this:
random_format "NULL"
But we could develop a function to return constant values and avoid using random_format. I will be creating a feature request for this.

I understand that some of this functionality means that mydumper would no longer be able to just do a simple select * from ... when dumping but this would be AWESOME. Maybe that's already the case with your new masquerade functionality.

No NO NOOOO!! mydumper will continue doing SELECT *, but you could use views and then you will be able to use --views-as-tables to accomplish your goal. This is an example of what you could do:

mysql> create table table_1105(id int auto_increment primary key, col2 varchar(255));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into table_1105 (col2) values (AES_ENCRYPT('MyDumper', 'myoldkeystring'));
Query OK, 1 row affected (0.01 sec)

mysql> select  AES_DECRYPT(col2, 'myoldkeystring') from table_1105;
+-------------------------------------+
| AES_DECRYPT(col2, 'myoldkeystring') |
+-------------------------------------+
| MyDumper                            |
+-------------------------------------+
1 row in set (0.01 sec)

mysql> create view table_1105_v as select id, AES_ENCRYPT(AES_DECRYPT(col2, 'myoldkeystring'), 'mynewkeystring') as col2 from table_1105;
Query OK, 0 rows affected (0.01 sec)

mysql> select AES_DECRYPT(col2, 'mynewkeystring') from table_1105_v;
+-------------------------------------+
| AES_DECRYPT(col2, 'mynewkeystring') |
+-------------------------------------+
| MyDumper                            |
+-------------------------------------+
1 row in set (0.00 sec)

[root@mydumper1 ~]# mydumper -o test -T test.table_1105_v,test.table_1105 --views-as-tables
[root@mydumper1 ~]# cat test/test.table_1105.00000.sql
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40103 SET TIME_ZONE='+00:00' */;
INSERT INTO `table_1105` VALUES(1,"D\n4L����
                                            `Ct1��O")
;
[root@mydumper1 ~]# cat test/test.table_1105_v.00000.sql
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40103 SET TIME_ZONE='+00:00' */;
INSERT INTO `table_1105_v` VALUES(1,"9�7
                                        �|M\n��\n��ԡ")
;

What you must do, is edit/create the -schema files for the table creation, as mydumper can't build that for you.

I don't think that I will be implementing something like cols-include or cols-exclude.

[matthewlenz]

Shame. Having the ability to modify the columns in the select query would be incredibly powerful. Thanks for the example of using a view. It wouldn't work for my use case unfortunately.

[davidducos]

@matthewlenz, what we could do is add 2 fields that could be configure like:

columns_on_select = col1, AES_ENCRYPT(AES_DECRYPT(col2, 'myoldkeystring'), 'mynewkeystring'), col4, NULL
columns_on_insert = col1,col2,col4,col5

What we might need to take into account is that this new approach will add some load to the server as it will need to execute those functions during the execution of the SELECT.
On the other hand, the masquerade solution implemented on mydumper adds load to the server where mydumper is executed.
Finally, we are not going to be modifying the -schema files.

This implementation will be simple as I already leave the code open to this kind of modifications. As I previously stated, having cols-include or cols-exclude which are filter by column name will be hard to implement, as we need to modify the code on several places to support it.

[matthewlenz]

This would definitely be helpful and powerful. Understood about modifying the schema itself. Thanks for considering this.

[yyevgenii]

You can use a constant value like this:
random_format "NULL"

@davidducos this makes mydumper to replace original value with "NULL", which is a string containing the word "NULL":

INSERT INTO `mytable` VALUES(1,"NULL");

I tried to use random_format NULL, but it gives the same result. Could you please advise how to replace some some value (varchar in my case) with NULL? Thank you in advance!