Obfuscate data using mysqldump with ProxySQL

As an alternative of retention straight from MySQL Server, you can argument mysqldump to ProxySQL that will accomplish the data masking/mystification.

The dump file can then be rummage-sale to providing new presentation database without consuming production data, and track agreement supplies.

This tremendously streamline the "public" method of throwing away unique data and loading it in a not clouded setup load the creative data into a database, and to finish achieving some obfuscating query

Install Mysql Server on ubuntu 16

sudo apt-get install -y mysql-server

Here you need to create the table in MySQL database.

Create table employees in it

Insert data in it

Create the second table salaries

Insert data into the table using insert statements

Install ProxySQL on the same machine using following commands

wget https://github.com/sysown/proxysql/releases/download/v1.4.3/proxysql_1.4.3-clickhouse-ubuntu16_amd64.deb

dpkg -i proxysql_1.4.3-clickhouse-ubuntu16_amd64.deb

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='admin> '

create user in mysql command prompt

CREATE USER 'proxysql'@'%' IDENTIFIED BY 'password';

GRANT ALL on *.* to 'proxysql'@'%';

FLUSH PRIVILEGES;

Now need to run the following queries in ProxySQL command prompt

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',3306);

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='password' WHERE variable_name='mysql-monitor_password';

LOAD MYSQL VARIABLES TO RUNTIME;

SAVE MYSQL VARIABLES TO DISK;

LOAD MYSQL SERVERS TO RUNTIME;

INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('proxysql','password','1');

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL USERS TO DISK;

INSERT INTO mysql_query_rules (rule_id,active,schemaname,match_pattern,replace_pattern) VALUES (1,1,'data_obfuscation','^SELECT /\*!40001 SQL_NO_CACHE \*/ \* FROM `salaries`','SELECT SQL_NO_CACHE emp_no, ROUND(RAND()*100000), from_date, to_date FROM salaries');

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

INSERT INTO mysql_query_rules (rule_id,active,schemaname,match_pattern,replace_pattern) VALUES (2,1,'data_obsfuscation','\* FROM `employees`',"emp_no, CONCAT(LEFT(birth_date,2),FLOOR(RAND()*50)+10,RIGHT(birth_date,6)) birth_date, CONCAT(LEFT(first_name,2),REPEAT('x',LENGTH(first_name)-2)) first_name, CONCAT(LEFT(last_name,3),REPEAT('x',LENGTH(last_name)-3)) last_name, gender, hire_date FROM employees");

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

Now you can use mysqldump command to get the obfuscated data.