How To Use pt-secure-collect for Capturing Data in a Secure Way From the OS and...
source link: https://www.percona.com/blog/how-to-use-pt-secure-collect-for-capturing-data-in-a-secure-way-from-the-os-and-database-system/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
How To Use pt-secure-collect for Capturing Data in a Secure Way From the OS and Database System
May 2, 2023
Sometimes crucial data sharing is avoided because of compliance rules, organizational policies, or numerous security concerns. The common use cases involve sharing pt-mysql-summary, pt-stalk, and other OS-related details to assist Support Engineers or any other third-party team troubleshoot database-related issues.
In this context, pt-secure-collect is a very important utility from Percona, which helps capture the required information securely and also provides aid in masking the existing information.
Pt-secure-collect helps in collecting, sanitizing, and encrypting data from various sources. By default, this utility collects the output with the help of pt-stalk, pt-summary, and pt-mysql-summary.
Let’s see how this tool works.
Installation
The tool can be installed via the Percona official repositories:
sudo yum install percona-toolkit |
Another option for downloading pt-secure-collect is either via the Percona Toolkit or directly installing the specific tool.
shell> sudo wget https://downloads.percona.com/downloads/percona-toolkit/3.5.2/binary/redhat/7/x86_64/percona-toolkit-3.5.2-2.el7.x86_64.rpm shell> sudo yum install percona-toolkit-3.5.2-2.el7.x86_64.rpm |
shell> sudo wget percona.com/get/pt-secure-collect shell> sudo chmod +x pt-secure-collect |
Now, let’s run our first command to capture the OS/Database-related details from the tool.
shell> ./pt-secure-collect collect --bin-dir=/usr/bin/ --temp-dir=/home/vagrant/pt/ --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=Root@1234 Encryption password |
Output:
<span style="font-weight: 400;">INFO[2023-04-22 06:54:10] Temp directory is "/home/vagrant/pt/" INFO[2023-04-22 06:54:10] Creating output file "/home/vagrant/pt/pt-stalk_2023-04-22_06_54_10.out" INFO[2023-04-22 06:54:10] Running pt-stalk --no-stalk --iterations=2 --sleep=30 --host=localhost --dest=/home/vagrant/pt/ --port=3306 --user=root --password=******** INFO[2023-04-22 06:55:42] Creating output file "/home/vagrant/pt/pt-summary_2023-04-22_06_55_42.out" INFO[2023-04-22 06:55:42] Running pt-summary INFO[2023-04-22 06:55:48] Creating output file "/home/vagrant/pt/pt-mysql-summary_2023-04-22_06_55_48.out" INFO[2023-04-22 06:55:48] Running pt-mysql-summary --host=localhost --port=3306 --user=root --password=******** INFO[2023-04-22 06:56:01] Sanitizing output collected data INFO[2023-04-22 06:56:17] Creating tar file "/home/vagrant/pt/pt.tar.gz" INFO[2023-04-22 06:56:17] Encrypting "/home/vagrant/pt/pt.tar.gz" file into "/home/vagrant/pt/pt.tar.gz.aes" INFO[2023-04-22 06:56:17] Skipping encrypted file "pt.tar.gz.aes" </span> |
So, here the above command collected the data from the “pt*” tools securely. By default, it encrypts the data and asks for the encryption password as well. However, we can skip that part by mentioning this option “ –no-encrypt” option.
Options:-
--bin-dir => Directory having the Percona Toolkit binaries (pt* tools). --temp-dir => Temporary directory used for the data collection. |
Note – In order to run the command successfully all prerequisites binaries of (pt-stalk, pt-summary, and pt-mysql-summary) must be present and included in the command.
Let’s decrypt the file and observe the captured details:
<span style="font-weight: 400;">shell> ./pt-secure-collect decrypt /home/vagrant/pt/pt.tar.gz.aes --outfile=/home/vagrant/pt/pt.tar.gz Encryption password: INFO[2023-04-22 07:01:55] Decrypting file "/home/vagrant/pt/pt.tar.gz.aes" into "/home/vagrant/pt/pt.tar.gz" </span> |
Note – Here, we need to provide the password which we used at the time of encryption.
--outfile => Write the output to this file. If omitted, the output file name will be the same as the input file, adding the .aes extension. |
Now, inside the path, we can see the unencrypted file. Followed by this, we can uncompress the file to see the contents.
shell> /home/vagrant/pt -rw-------. 1 vagrant vagrant 500K Apr 22 07:01 pt.tar.gz |
shell> tar -xzvf pt.tar.gz |
Let’s look at a couple of examples where the sensitive data has been altered or masked.
- With pt-secure-collect:
Hostname | hostname log_error | /var/log/hostname Config File | /etc/hostname pid-file = /var/run/mysqld/hostname log-error = /var/log/hostname socket = /var/lib/mysql/hostname |
- Without pt-secure-collect:
Hostname | localhost.localdomain log_error | /var/log/mysqld.log Config File | /etc/my.cnf pid-file = /var/run/mysqld/mysqld.pid log-error = /var/log/mysqld.log socket = /var/lib/mysql/mysql.sock |
Note – We can clearly see some differences in the both types of outputs. With pt-secure-collection the above information was just replaced with some random value(“hostname”).
Now, let’s see how we can sanitize an existing file “pt-mysql-summary.out” and mask the critical information that ends with the below output section.
shell> ./pt-secure-collect sanitize --input-file=/home/vagrant/pt-mysql-summary.out > /home/vagrant/pt-mysql-summary_sanitize.out |
Output:
Hostname | hostname Pidfile | /var/run/mysqld/hostname (exists) log_error | /var/log/hostname Config File | /etc/hostname pid-file = /var/run/mysqld/hostname log-error = /var/log/hostname socket = /var/lib/mysql/hostname log-error = /var/log/mariadb/hostname pid-file = /var/run/mariadb/hostname |
You may also control the information which you want to skip from masking with settings with option –no-sanitize-hostnames and –no-sanitize-queries.
Here, we see one more example where the critical information, such as “hostname” details inside the OS log file (“/var/log/messages”), is masked/replaced by some other value.
shell> sudo ./pt-secure-collect sanitize --input-file=/var/log/messages > /home/vagrant/messages_sanitize.out |
Output (without pt-secure-collect):
Apr 23 03:37:13 localhost pmm-agent: #033[31mERRO#033[0m[2023-04-23T03:37:13.547+00:00] time="2023-04-23T03:37:13Z" level=error msg="Error opening connection to ProxySQL: dial tcp 127.0.0.1:6032: connect: connection refused" source="exporter.go:169" #033[31magentID#033[0m=/agent_id/04dd6ad8-5c2e-4c52-a624-eb3bc7357651 #033[31mcomponent#033[0m=agent-process #033[31mtype#033[0m=proxysql_exporter |
Output (with pt-secure-collect):
Apr 23 03:37:13 localhost pmm-agent: #033[31mERRO#033[0m[2023-04-23T03:37:13.547+00:00] time="2023-04-23T03:37:13Z" level=error msg="Error opening connection to ProxySQL: dial tcp hostname:6032: connect: connection refused" source="hostname:169" #033[31magentID#033[0m=/agent_id/04dd6ad8-5c2e-4c52-a624-eb3bc7357651 #033[31mcomponent#033[0m=agent-process #033[31mtype#033[0m=proxysql_exporte |
Summary
With the help of this tool, both OS and database-level information/logs can be encrypted or masked with some different values to hide the sensitive data. This tool comes in handy while dealing with critical data troubleshooting with any third-party stakeholders and also maintains security/compliance-related practices.
Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL, MariaDB, MongoDB, and PostgreSQL server and system tasks that are too difficult or complex to perform manually.
Learn more about Percona Toolkit
Share This Post!
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK