Vettabase Webinar - Enhancing MySQL Security_ More on Data-at-Rest Encryption

During this webinar, we’ll demonstrate how important MySQL data encryption is using examples close to real world. We’ll dive into a few practical cases of where encryption is required. Additionally, we’ll prepare a git repository with all files and reproducible test cases for your convenience. As a bonus, we’ll show how to backup and restore encrypted data.

Transcript

1. Enhancing MySQL Security: More on Data-at-Rest Encryption Mike Rykmas mykhaylo.rykmas@vettabase.com 27 November 2025
2. WHO AM I? 01> MySQL consultant / DevOps engineer having 10+ years hands-on experience. 02> MySQL blogger: https://vettabase.com/author/mykhaylo-rykmas/ 03> Vettabase Ltd., a young company offering automation and expert services for MariaDB, MySQL, PostgreSQL and Cassandra.
3. Agenda • Welcome & Introduction • Understanding Data-at-Rest Encryption • Keyring Plugin in Focus • Benchmarking MySQL Encryption • Best Practices for MySQL Encryption • Interactive Q&A • Closing Remarks
4. Basics, Benchmarks, and Best Practices
5. Basics, Benchmarks, and Best Practices • Basics ◦ Understand what data-at-rest encryption is and why it’s critical. ◦ Explore MySQL’s built-in encryption features, with a focus on the keyring plugin. • Benchmarks ◦ Measure the performance impact of enabling encryption. ◦ Use real-world scenarios and sysbench to evaluate encryption overhead. • Best Practices ◦ Learn how to configure encryption securely and efficiently. ◦ Tips to maintain performance while improving security.
6. Basics of Data-at-Rest Encryption What is Data-at-Rest Encryption? • Protects stored data from unauthorized access or theft. • Data is encrypted when it is saved to disk and decrypted when read into memory. Why Does It Matter? • Compliance: Meets security regulations like GDPR, HIPAA, or PCI DSS. • Mitigates risks: Protects sensitive information even if the storage medium is compromised.
7. Basics of Data-at-Rest Encryption Encryption Keys: • Secret keys used to encrypt and decrypt data. • Managed securely to avoid breaches. How MySQL Handles It: • Transparent Data Encryption (TDE): Encrypts data stored in InnoDB tablespaces. • Key Management: Handled by plugins like the Keyring Plugin.
8. Keyring Plugin Overview
9. Keyring Plugin Overview What is the Keyring Plugin? • A MySQL plugin for managing encryption keys securely. • Keys are stored outside the database, enhancing security.
10. Keyring Plugin Overview Why Use It? • Enhanced Security: ◦ Prevents keys from being stored in plaintext within the database. • Compliance: ◦ Meets regulatory requirements for secure key management. • Simplicity: ◦ No need for external key management systems. • Integration: ◦ Works seamlessly with Transparent Data Encryption (TDE).
11. Keyring Plugin Overview How Does It Work? • Encrypts data using keys stored in a keyring backend. • Keyring backends can include: ◦ File-based storage. ◦ Cloud providers (AWS, Azure, GCP). ◦ Hardware Security Modules (HSMs).
12. Keyring Plugin Overview Key Features: • Pluggable architecture: Multiple backends supported. • Auto-encryption for InnoDB tablespaces. • Easily configured through MySQL options.
13. Setup
14. Git Repository • Git Repository ◦ URL: https://github.com/Vettabase/webinar-2024-11-enhancing-mysql-security • Create custom sysbench image • Build Stack • Apply Aliases • Check Encryption • Benchmarking
15. Build Custom Sysbench Image
16. Docker-Compose
17. Read non-Encrypted Tablespace
18. Read Encrypted Tablespace
19. Benchmarking
20. Benchmarking Methodology Objectives: • Measure the performance impact of MySQL data-at-rest encryption. • Compare performance with encryption and without encryption. • Analyze key metrics like throughput, latency, and query performance. Database Environment: • MySQL 8.0 with and without encryption enabled. • Tablespaces encrypted using the Keyring Plugin.
21. Benchmarking Methodology Sysbench Configuration: • Workloads: OLTP Read/Write (using oltp_read_write.lua). • Dataset Size: 5 tables, 100,000 rows per table. • Threads: Start with 2 threads, scale up if needed. • Test Duration: Run for 60 seconds for reliable results. Hardware/Resources: • Consistent CPU, memory, and disk for both tests. • Same MySQL configuration except for encryption settings.
22. Benchmarking Methodology Metrics Collected: • Query Performance: Transactions and queries per second. • Throughput: Events per second (EPS). • Latency: Minimum, average, and 95th percentile latency. Execution Steps: • Prepare dataset: Initialize the dataset using sysbench prepare command. • Run benchmark • Analyze results: Compare performance metrics across scenarios.
23. Benchmarking: Steps
24. Benchmarking - Prepare sysbench \ /usr/share/sysbench/oltp_read_write.lua \ --mysql-host=${MYSQL_HOST} \ --mysql-port=${MYSQL_PORT} \ --mysql-user=${MYSQL_USER} \ --mysql-password=${MYSQL_PASSWORD} \ --mysql-db=${MYSQL_DB} \ --tables=5 \ --table-size=100000 \ --threads=4 \ --time=60 \ prepare
25. Benchmarking - Run sysbench \ /usr/share/sysbench/oltp_read_write.lua \ --mysql-host=${MYSQL_HOST} \ --mysql-port=${MYSQL_PORT} \ --mysql-user=${MYSQL_USER} \ --mysql-password=${MYSQL_PASSWORD} \ --mysql-db=${MYSQL_DB} \ --tables=5 \ --table-size=100000 \ --threads=2 \ --time=60 \ run
26. Benchmarking: Results
27. Results & Analysis Host with Encryption Enabled Host without Encryption
28. Benchmarking: Key Observations
29. Benchmarking: Encrypted vs. Non-Encrypted Performance Comparison Metric Encrypted Host Non-Encrypted Host Impact Transactions 72,864 80,774 ~9.8% fewer transactions Transactions/sec 1,214.35 1,346.21 ~10% lower Total Queries 1,457,280 1,615,480 ~11% fewer queries Queries/sec 24,286.90 26,924.18 ~10% lower Events/sec (Throughput) 1,214.35 1,346.21 ~10% reduction Avg Latency (ms) 3.29 2.97 ~10.8% higher Max Latency (ms) 28.39 19.91 Higher under encryption 95th Percentile Latency (ms) 4.91 4.49 Slightly higher Threads Fairness Balanced (stddev: 182.55) Balanced (stddev: 46.10) Consistent across threads
30. Benchmarking: Queries Performed With Encryption: • Total queries: 1,457,280 (Read: 1,020,096, Write: 291,456, Other: 145,728) Without Encryption: • Total queries: 1,615,480 (Read: 1,130,836, Write: 323,096, Other: 161,548) Impact: • Without encryption, 11% more queries were processed due to the absence of encryption overhead.
31. Benchmarking: Transactions • With Encryption: 72,864 transactions (1,214.35 transactions per second) • Without Encryption: 80,774 transactions (1,346.21 transactions per second) • Impact: Encryption reduced transaction throughput by 9.8%, which is consistent with the observed impact on query performance.
32. Benchmarking: Throughput • With Encryption: 1,214.35 events/sec • Without Encryption: 1,346.21 events/sec • Impact: Encryption introduces an approximate 9.8% reduction in throughput, likely due to the additional CPU processing required for encryption and decryption.
33. Benchmarking: Latency With Encryption: • Average latency: 3.29 ms • 95th percentile latency: 4.91 ms • Maximum latency: 28.39 ms Without Encryption: • Average latency: 2.97 ms • 95th percentile latency: 4.49 ms • Maximum latency: 19.91 ms Impact: • Encryption increased average latency by 10.8%. • The maximum latency was higher with encryption, suggesting that certain operations, likely involving writes or I/O-intensive queries, were more affected.
34. Benchmarking: Threads Fairness With Encryption: • Events per thread: 18,216.00 (±182.55) • Execution time per thread: 59.9834 s (stddev = 0.00) Without Encryption: • Events per thread: 20,193.50 (±46.10) • Execution time per thread: 59.9841 s (stddev = 0.00) Impact: • Fairness across threads was consistent in both cases, showing good load distribution. • However, without encryption, each thread handled approximately 11% more events, consistent with the throughput difference.
35. Analysis of Performance Impact
36. Analysis of Performance Impact Encryption Overhead (expected) • Encryption adds computational overhead for every read/write operation, resulting in reduced throughput and increased latency. • The CPU utilization is likely higher with encryption, which can be validated by monitoring system metrics during the test. Query and Transaction Performance • The 11% reduction in query and transaction performance aligns with expectations for encryption overhead in a database environment. • Reads and writes are both affected due to encryption/decryption during disk I/O and memory operations.
37. Analysis of Performance Impact Real-World Implications • For workloads where throughput and latency are critical (e.g., high-frequency trading, real-time analytics), encryption might introduce noticeable performance degradation. • For workloads where security is paramount (e.g., healthcare, finance), the performance impact of encryption is likely an acceptable trade-off.
38. Conclusion With Encryption: • Provides data security at rest but with approximately 10-11% overhead in throughput and latency. Without Encryption: • Achieves higher performance but lacks the security benefits of encrypted data.
39. Best Practises
40. Best Practices Evaluate Encryption Impact • Benchmark your workload with and without encryption before enabling it in production. • Focus on key metrics: throughput, latency, and resource usage. Optimize Hardware • Ensure sufficient CPU capacity and IOPS from storage to handle the additional encryption overhead. Monitor Performance Regularly • Performance Schema: Queries performance • Resource usage (CPU, memory, disk).
41. Best Practices Balance Security and Performance • Encrypt only what’s necessary: ◦ Critical data (e.g., customer information, financial records). ◦ Avoid encrypting less sensitive data to reduce overhead. Test Key Rotation • Regularly rotate encryption keys to meet compliance requirements. • Test key rotation procedures to ensure minimal downtime.
42. Q&A
43. Thank you! Any questions? Mike Rykmas Consultant, Vettabase Ltd. mykhaylo.rykmas@vettabase.com