How To Use pt-secure-collect for Capturing Data in a Secure Way From the OS and Database System

How To Use pt-secure-collect

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.


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 
shell> sudo yum install percona-toolkit-3.5.2-2.el7.x86_64.rpm


shell> sudo wget 
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


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"   

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. 


--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:

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" 

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/ 
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


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 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



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


Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL

Encryption at rest MariaDB MySQL Percona Server

Encryption at rest MariaDB MySQL Percona ServerProtecting the data stored in your database may have been at the top of your priorities recently, especially with the changes that were introduced earlier this year with GDPR.

There are a number of ways to protect this data, which until not so long ago would have meant either using an encrypted filesystem (e.g. LUKS), or encrypting the data before it is stored in the database (e.g. AES_ENCRYPT or other abstraction within the application). A few years ago, the options started to change, as Alexander Rubin discussed in MySQL Data at Rest Encryption, and now MariaDB®, MySQL®, and Percona Server for MySQL all support encryption at-rest. However, the options that you have—and, indeed, the variable names—vary depending upon which database version you are using.

In this blog post we will take a look at what constitutes the maximum level of at-rest encryption that can be achieved with each of the latest major GA releases from each provider. To allow a fair comparison across the three, we will focus on the file-based key management; keyring_file plugin for MySQL and Percona Server for MySQL along with file_key_management plugin for MariaDB.

MariaDB 10.3

The MariaDB team take the credit for leading the way with at-rest encryption, as most of their features have been present since the 10.1 release (most notably the beta release of 10.1.3 in March 2015). Google donated the tablespace encryption, and eperi donated per-table encryption and key identifier support.

The current feature set for MariaDB 10.3 comprises of the following variables:

Maximising at-rest encryption with MariaDB 10.3

Using the following configuration would give you maximum at-rest encryption with MariaDB 10.3:

plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/keys.enc
file_key_management_filekey = FILE:/etc/mysql/.key
file_key_management_encryption_algorithm = aes_cbc
innodb_encrypt_log = ON
innodb_encrypt_tables = FORCE
Innodb_encrypt_threads = 4
encrypt_binlog = ON
encrypt_tmp_disk_tables = ON
encrypt_tmp_files = ON
aria_encrypt_tables = ON

This configuration would provide the following at-rest protection:

  • automatic and enforced InnoDB tablespace encryption
  • automatic encryption of existing tables that have not been marked with
  • 4 parallel encryption threads
  • encryption of temporary files and tables
  • encryption of Aria tables
  • binary log encryption
  • an encrypted file that contains the main encryption key

You can read more about preparing the keys, as well as the other key management plugins in the Encryption Key Management docs.

There is an existing bug related to encrypt_tmp_files (MDEV-14884), which causes the use of

mysqld --help --verbose

 to fail, which if you are using the official MariaDB Docker container for 10.3 will cause you to be unable to keep mysqld up and running. Messages similar to these would be visible in the Docker logs:

ERROR: mysqld failed while attempting to check config
command was: "mysqld --verbose --help --log-bin-index=/tmp/tmp.HDiERM4SPx"
2018-08-15 13:38:15 0 [Note] Plugin 'FEEDBACK' is disabled.
2018-08-15 13:38:15 0 [ERROR] Failed to enable encryption of temporary files
2018-08-15 13:38:15 0 [ERROR] Aborting

N.B. you should be aware of the limitations for the implementation, most notably log tables and files are not encrypted and may contain data along with any query text.

One of the key features supported by MariaDB that is not yet supported by the other providers is the automatic, parallel encryption of tables that will occur when simply enabling


 . This avoids the need to mark the existing tables for encryption using


 , although at the same time it also does not automatically add the comment and so you would not see this information. Instead, to check for encrypted InnoDB tables in MariaDB you should check


 , an example query being:

mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
   ->   SUBSTRING_INDEX(name, '/', -1) AS db_table,
   -> FROM information_schema.INNODB_SYS_TABLESPACES
| db_name | db_table             | encrypted |
| mysql   | innodb_table_stats   |      1    |
| mysql   | innodb_index_stats   |      0    |
| mysql   | transaction_registry |      0    |
| mysql   | gtid_slave_pos       |      0    |

As can be inferred from this query, the system tables in MariaDB 10.3 are still predominantly MyISAM and as such cannot be encrypted.


Whilst the enterprise version of MySQL has support for a number of data at-rest encryption features as of 5.7, most of them are not available to the community edition. The latest major release of the community version sees the main feature set comprise of:

The enterprise edition adds the following extra support:

Maximising at-rest encryption with MySQL 8.0

Using the following configuration would give you maximum at-rest encryption with MySQL 8.0:

This configuration would provide the following at-rest protection:

  • optional InnoDB tablespace encryption
  • redo and undo log encryption

You would need to create new, or alter existing tables with the


 option, which would then be visible by examining


 , an example query being:

mysql> SELECT TABLE_SCHEMA AS db_name,
   ->    TABLE_NAME AS db_table,
   ->    CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%' AS encrypted
   -> FROM information_schema.INNODB_TABLESPACES ts
   -> INNER JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = SUBSTRING_INDEX(, '/', 1)
   ->                                        AND t.TABLE_NAME = SUBSTRING_INDEX(, '/', -1);
| db_name | db_table        | encrypted |
| sys     | sys_config      |     1     |

N.B. You are able to encrypt the tablespaces in 5.7, in which case you should use


 as the internal system views on the data dictionary were renamed (InnoDB Changes).

Unfortunately, whilst all of the tables in the mysql schema use the InnoDB engine (except for the log tables), you cannot encrypt them and instead get the following error:

ERROR 3183 (HY000): This tablespace can't be encrypted.

Interestingly, you are led to believe that you can indeed encrypt the




 tables, but this is in fact a bug (#91791).

Percona Server for MySQL

Last, but not least we have Percona Server for MySQL, which, whilst not completely matching MariaDB for features, is getting very close. As we shall see shortly, it does in fact have some interesting differences to both MySQL and MariaDB.

The current feature set for 5.7, which does indeed exceed the features provided by MySQL 5.7 and for the most part 8.0, is as follows:

Maximising at-rest encryption with Percona Server for MySQL 5.7

Using the following configuration would give you maximum at-rest encryption with Percona Server 5.7:

This configuration would provide the following at-rest protection:

  • automatic and enforced InnoDB tablespace encryption
  • encryption of temporary files and tables
  • binary log encryption
  • encryption when performing online DDL

There are some additional features that are due for release in the near future:

  • Encryption of the doublewrite buffer
  • Automatic key rotation
  • Undo log and redo log encryption
  • InnoDB system tablespace encryption
  • InnoDB tablespace and redo log scrubbing
  • Amazon KMS keyring plugin

Just like MySQL, encryption of any existing tables needs to be specified via


 via an


, however new tables are automatically encrypted. Another difference is that in order to check which tables are encrypted you can should the flag set against the tablespace in


, an example query being:

mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
   ->    SUBSTRING_INDEX(name, '/', -1) AS db_table,
   ->    (flag & 8192) != 0 AS encrypted
   -> FROM information_schema.INNODB_SYS_TABLESPACES;
| db_name | db_table                  | encrypted |
| sys     | sys_config                |      1    |
| mysql   | engine_cost               |      1    |
| mysql   | help_category             |      1    |
| mysql   | help_keyword              |      1    |
| mysql   | help_relation             |      1    |
| mysql   | help_topic                |      1    |
| mysql   | innodb_index_stats        |      1    |
| mysql   | innodb_table_stats        |      1    |
| mysql   | plugin                    |      1    |
| mysql   | servers                   |      1    |
| mysql   | server_cost               |      1    |
| mysql   | slave_master_info         |      1    |
| mysql   | slave_relay_log_info      |      1    |
| mysql   | slave_worker_info         |      1    |
| mysql   | time_zone                 |      1    |
| mysql   | time_zone_leap_second     |      1    |
| mysql   | time_zone_name            |      1    |
| mysql   | time_zone_transition      |      1    |
| mysql   | time_zone_transition_type |      1    |
| mysql   | gtid_executed             |      0    |

Here you will see something interesting! We are able to encrypt most of the system tables, including two that are of significance, as they can contain plain text passwords:

| db_name | db_table          | encrypted |
| mysql   | servers           |      1    |
| mysql   | slave_master_info |      1    |

In addition to the above, Percona Server for MySQL also supports using the opensource HashiCorp Vault to host the keyring decryption information using the keyring_vault plugin; utilizing this setup (provided Vault is not on the same device as your mysql service, and is configured correctly) gains you an additional layer of security.

You may also be interested in my earlier blog post on using Vault with MySQL, showing you how to store your credentials in a central location and use them to access your database, including the setup and configuration of Vault with Let’s Encrypt certificates.


There are significant differences both in terms of features and indeed variable names, but all of them are able to provide encryption of the InnoDB tablespaces that will be containing your persistent, sensitive data. The temporary tablespaces, InnoDB logs and temporary files contain transient data, so whilst they should ideally be encrypted, only a small section of data would exist in them for a finite amount of time which is less of a risk, albeit a risk nonetheless.

Here are the highlights:

MariaDB 10.3 MySQL 8.0 Percona Server 5.7
encrypted InnoDB data Y Y Y
encrypted non-InnoDB data Aria-only N N
encrypted InnoDB logs Y Y TBA
automatic encryption Y N Y
enforced encryption Y N Y
automatic key rotation Y N TBA
encrypted binary logs Y N Y
encrypted online DDL ? N Y
encrypted keyring Y Enterprise-only N
mysql.slave_master_info N N Y
mysql.servers N N Y
Hashicorp Vault N N Y
AWS KMS Y Enterprise-only TBA


Extra reading:


The post Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL appeared first on Percona Database Performance Blog.


MySQL Security Webinar: Follow-up Q&A

MySQL Security Webinar: Follow-up Q&AThanks to everyone who attended last week’s webinar on MySQL security; hopefully you’ve all gone out and set SELinux to enforcing mode if you weren’t already running that way. If you weren’t able to attend, the recording and slides are available for viewing/download. But now, without further ado, here are the questions which we didn’t have time to cover during the presentation.

Q: Do you have a favorite software firewall you recommend that I can run on an EC2 instance in front of my MySQL server?
A: I’d probably just do this with iptables. Any of the other Linux-based software firewall packages are all going to be wrappers around iptables anyway. However, if your MySQL server is already in EC2, you’re going to be better served by Amazon security groups as your front-line firewall. You can also run inside a VPC for some additional isolation.

Q: What do you use for disk encryption in the cloud environment?
A: I might use GPG if I need to encrypt specific files, or just encrypt data in the database via the MySQL functions or in my application, and there’s also no reason why you can’t use dm-crypt/LUKS in the cloud if you’re willing to accept that mounting the device will require some manual intervention, but for the most part, I think disk encryption for servers is pretty useless, because once you enter the key to unlock and mount the volume, anyone that can get access to that machine has full access to the data. I’ve heard of Gazzang being a possible solution here, but I’ve not personally looked at it, so I can’t speak to its performance or suitability.

Q: How much overhead does SSL put on a heavily-loaded MySQL box?
A: That depends on a lot of factors, such as the cipher in use and the kinds of load you’re running. The slowest part of SSL is the connection setup, so you’re going to see a lot less overhead for replication, connection pooling, or long-running transactions than you would from an application with rapidly connects to the database and then disconnects. There’s a graph on the yaSSL site which shows roughly a 25% penalty at 32 threads, but their benchmarks are from 2011 and they were run on someone’s laptop, so I have my doubts as to how well that translates to modern server-grade hardware.

Q: Can MySQL do LDAP/Kerberos login?
A: You can use the PAM authentication plugin (either the commercial one from Oracle or Percona’s open source version) to authenticate against an LDAP database.

Thanks again for attending and submitting your questions; security is one of those massive topics where it’s only possible to scratch the surface in a one-hour webinar. Later this year I may do a follow-on presentation wherein we skip over all of the system/network/application details and do a deeper dive only on MySQL security-related tweaks and best/worst practices, so stay tuned!

The post MySQL Security Webinar: Follow-up Q&A appeared first on MySQL Performance Blog.


MySQL 5.6 security vs ease of use

MySQL 5.6MySQL 5.6 surely changes the game when it comes to security vs ease of use. Before MySQL 5.6 we would get default MySQL installation being pretty insecure – the user “root” will be created with no password as well as anonymous user with limited access from local host (though still enough to cause DOS attack or crash MySQL Server.

There were some exception to this rule – such as Debian/Ubuntu install scripts would interactively suggest you to set password for root user if it was not set. Still most users would get MySQL install with root account and no password.

This is not the case with MySQL 5.6 when you’re doing fresh MySQL install! Installing official RPM on CentOS6 I’m getting this:

You will find that password in '/root/.mysql_secret'.
You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.
Also, the account for the anonymous user has been removed.
In addition, you can run:
which will also give you the option of removing the test database.
This is strongly recommended for production servers.

So we’re getting random password for the root account by default instead of empty one. Furthermore it is not stored in the root directory my.cnf but separate .mysql_secret file so you need to enter it explicitly to connect to the server for a first time – and it is for a good reason as this is temporary password only. You can’t really use MySQL Server until you change it:

[root@centos6 ~]# mysql -u root -p8AkXyPUs
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.13
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show processlist
    -> ;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

As Such MySQL will refuse any statements even ones which do not cause any database contents access until you change password with SET PASSWORD command.

If you’re looking to keep password you can run:

mysql> set password=password('MySecurePassword');
Query OK, 0 rows affected (0.00 sec)

You also have an option to go back to the old behavior and remove the password for account (this is what I do on MySQL running on VirtualBox on my Laptop as I keep it for testing only)

mysql> set password='';
Query OK, 0 rows affected (0.00 sec)

So at least with RPM Install MySQL 5.6 is getting more secure, but adding a little more effort after installation is worthwhile. I hope this change will make things more secure and will not discourage a lot of users by complicating the install process.

The post MySQL 5.6 security vs ease of use appeared first on MySQL Performance Blog.


MySQL Security: Armoring Your Dolphin

MySQL Security: Armoring Your DolphinMy colleague and teammate Ernie Souhrada will be presenting a webinar on Wednesday, August 21, 2013 at 10 a.m. PDT titled “MySQL Security: Armoring Your Dolphin.”

This is a popular topic with news breaking routinely that yet another Internet company has leaked private data of one form or another. Ernie’s webinar will be a great overview of security MySQL from top to bottom, including changes related to security in the 5.6 release.

Topics to be covered include:

  • Basic security concepts
  • Security above the MySQL layer (network, hardware, OS, etc.)
  • Tips for application design
  • A more secure MySQL configuration
  • Security-related changes in MySQL 5.6

Attendees will leave this presentation knowing where to start when identifying vulnerability in their systems.

Be sure to register for the webinar in advance!

The post MySQL Security: Armoring Your Dolphin appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by