Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents Security and Encryption in the MySQL World on Thursday, July 6, 2017, at 7:00 am PDT / 10:00 am EDT (UTC-7).Register Now
MySQL and MariaDB Server provide many new features that help with security and encryption, both of which are extremely important in today’s world. Learn how to use these features, from roles to at-rest-encryption, to increase security. At the end of the webinar, you should understand how to have a securely configured MySQL instance!Dimitri Vanoverbeke, Solutions Engineer
At the age of 7, Dimitri received his first computer. Since then, he’s addicted to anything with a digital pulse. Dimitri has been active in IT professionally since 2003, when he took various roles from internal system engineering to consulting. Before joining Percona, Dimitri worked as an open source consultant for a leading open source software consulting firm in Belgium. During his career, Dimitri became familiar with a broad range of open source solutions and with the devops philosophy. When not glued to his computer screen, he enjoys traveling, cultural activities, basketball and the great outdoors.
Join Percona’s Senior Architect, Matthew Boehm, as he presents Indexes – What You Need to Know to Get the Most Out of Them on Wednesday, July 5, 2017 at 8:00 am PDT / 11:00 am EDT (UTC-7).Register Now
Proper indexing is key to database performance. Find out how MySQL uses indexes for query execution, and then how to come up with an optimal index strategy. In this session, you’ll also learn how to know when you need an index, and also how to get rid of indexes that you don’t need to speed up queries.Matthew Boehm, Architect
Matthew joined Percona in the fall of 2012 as a MySQL consultant. He quickly rose to Architect and became one of Percona’s training instructors. His areas of knowledge include the traditional Linux/Apache/MySQL/PHP stack, memcached, MySQL Cluster (NDB), massive sharding topologies, PHP development and a bit of MySQL-C-API development.
We recently garnered a Bronze Stevie Award “Company of the Year – Computer Software – Medium.” Stevies are some of the most coveted awards in the tech industry. Each year, more than 10,000 organizations in more than 60 nations compete for Stevies. It’s a huge honor to come away with a win.
Percona was also included in Database Trends and Applications’ (DBTA) annual DBTA 100 list of the companies that matter most in data. The directory was compiled by the editorial staff of DBTA and spotlights the companies that deal with evolving market demands through innovation in software, services, and hardware.
Zippia, a new website dedicated to helping recent grads with their career choices, ranked Percona in the top 10 of the “20 Best Companies To Work For In Raleigh, NC.” To compile the ranking, Zippia chose companies in a 25-mile radius that consistently get high reviews from career resource websites.
Finally, Percona co-founder and CEO Peter Zaitsev was honored as one of the Top Rated CEOs in Raleigh, North Carolina, by Owler. Owler looked at 167,000 leaders across 50 cities and 25 industries worldwide and identified the top 1,000. This is a huge honor for Peter!
Driven by Peter’s vision, all of us at Percona remain committed to building an excellent organization that is the champion of unbiased open source database solutions. Our continued success, growth and recognition from organizations like the above lead us to believe that the best is yet to come. Stay tuned!
The inspiration for this blog post was a recent conversation with a prospective client. They were exploring open source software solutions and professional services for the first time. This is a fairly common and recurring conversation with individuals and enterprises exploring open source for the first time. They generally find Percona through recommendations from colleagues, or organic Google searches. One of the most common questions Percona gets when engaging at this level is, “How does Percona’s software compare with << Insert Closed-source Enterprise DB Here >>?”
We’re glad you asked. Percona’s position on this question is to explain the value of Percona in the open source space. Percona does not push a particular flavor of open source software. We recommend an appropriate solution given each of our client’s unique business and application requirements. A client coming from this space is likely to have a lengthy list of compliance, performance and scalability requirements. We love these conversations. Our focus is always on providing our customers with the best services, support, and open-source technology solutions possible.
If there were a silver bullet for every database, Percona would not have been as successful over the past 10 (almost 11!) years. We know that MongoDB, MariaDB, MySQL Enterprise, MySQL Community, Percona Server for MySQL, etc., is never the right answer 100% of the time. Our competitors in this space will most likely disagree with this assessment – because they have an incentive to sell their own software. The quality of all of these offerings is extremely high, and our customers use each of them to great success. Using the wrong database software for a particular use-case is like using a butter knife to cut a steak. You can probably make it work, but it will be a frustrating and unrewarding experience.
Usually, there are more efficient alternatives.
There is a better question to ask instead of software vs. software, which basically turns into an apples vs. oranges conversation. It’s: “Why should we partner with Percona as our business adopts more open-source software?” This is where Percona’s experience and expertise in the open-source database space shine.
Percona is invested in ensuring you are making the right decisions based on your needs. Choosing the wrong database solution or architecture can be catastrophic. A recent Ponemon study (https://planetaklimata.com.ua/instr/Liebert_Hiross/Cost_of_Data_Center_Outages_2016_Eng.pdf) estimates the average cost of downtime can be up to $8,800 per minute. Not only is downtime costly, but re-architecting your environment due to an errant choice in database solutions can be costly as well. Uber experienced these pains when using Postgres as their database solution and wrote an interesting blog post about their decision to switch back to MySQL (https://eng.uber.com/mysql-migration/). Postgres has, in turn, responded to Uber recently (http://thebuild.com/presentations/uber-perconalive-2017.pdf). The point of bringing up this high-profile switch is not to take sides, but to highlight the breadth of knowledge necessary to navigate the open source database environment. Given its complexity, the necessity of a trusted business partner with unbiased recommendations should be apparent.
This is where Percona’s experience and expertise in the open source database space shines. Percona’s only investors are its customers, and our only customers are those using our services. Because of this, our core values are intertwined with customer success and satisfaction. This strategy trickles down through support, to professional services and all the way to the engineering team, where efforts are focused on developing software and features that provide the most value to customers as a whole.
Focusing on a business partner in the open source space is a much more important and worthwhile effort than focusing on a software provider. Percona has hands-on, practical experience with a varying array of open-source technologies in the MySQL, MariaDB, and MongoDB ecosystems. We’ve tracked our ability to assist our customer’s efficiencies in their databases and see a 30% efficiency improvement on average with some cases seeing a 10x boost in performance of their database. (https://www.percona.com/blog/2016/07/20/value-database-support/). The open source software itself has proven itself time and again to be the choice of billion dollar industries. Again, there is not one universal choice among companies. There are success stories that run the gamut of open source software solutions.
To summarize, there’s a reason we redirect the software vs. software question. It’s not because we don’t like talking about how great our software is (it is great, and holds it own with other open source offerings). It’s because the question does not highlight Percona’s value. Percona doesn’t profit from its software. Supporting the open-source ecosystem is our goal. We are heavily invested in providing our customers with the right open source software solution for their situation regardless of whether it is ours or not. Our experience and expertise in this space make us the ideal partner for businesses adopting more open source technology in place of enterprise-licensed models.
At Percona, we work with a number of clients that require strong security measures for PCI, HIPAA and PHI compliance, where data managed by MySQL needs to be encrypted “at rest.” As with all things open source, there several options for meeting the MySQL encryption at rest requirement. In this three-part series, we cover several popular options of encrypting data and present the various pros and cons to each solution. You may want to evaluate which parts of these tutorials work best for your situation before using them in production.
Part one of this series covered implementing disk-level encryption using crypt+LUKS.
In this post, we discuss InnoDB tablespace encryption (TE). You can choose to use just LUKS, just InnoDB TE, or you can use both. It depends on your compliance needs and your configuration. For example, say you put your binary logs and InnoDB redo logs on partition X, and your InnoDB tablespaces (i.e., your .ibd files) on partition Y. InnoDB TE only handles encrypting the tablespaces. You would need to use LUKS on partition X to encrypt those files.InnoDB Tablespace Encryption Tutorial
The manual describes InnoDB encryption as follows:
InnoDB tablespace encryption uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDB table is encrypted, a tablespace key is encrypted and stored in the tablespace header. When an application or authenticated user wants to access encrypted tablespace data, InnoDB uses a master encryption key to decrypt the tablespace key. The decrypted version of a tablespace key never changes, but you can change the master encryption key as required. This action is referred to as master key rotation.
As a real-world example, picture a high-school janitor’s key-chain. Each key is different; each key works for only one door (or in MySQL’s case, table). The keys themselves never change, but the label on each key can change based on how the janitor wants to note which key works on which door. At any time, the janitor can re-label the keys with special names, thwarting any hooligans from opening doors they shouldn’t be opening.Configuration Changes
As noted above, InnoDB uses a keyring to manage encryption keys on a per-table basis. But you must load the keyring plugin before InnoDB. Add the following lines to your my.cnf under the [mysqld] section. You may substitute a different path for the keyring file, but be sure that mysqld has permissions to this path:early-plugin-load = keyring_file.so keyring_file_data = /var/lib/mysql-keyring/keyring
You do not need to restart MySQL at this time, but you can if you wish. These settings are for when you restart MySQL in the future.Install the Keyring Plugin in MySQL
If you don’t wish to restart MySQL at this time, you can still continue. Run the following SQL statements within MySQL to load the plugin and configure the keyring file:mysql> INSTALL PLUGIN keyring_file SONAME 'keyring_file.so'; mysql> SET GLOBAL keyring_file_data = '/var/lib/mysql-keyring/keyring';
You can now CREATE encrypted tables or ALTER existing tables to encrypt them with a simple command:mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y'; Query OK, 0 rows affected (0.04 sec) mysql> ALTER TABLE users ENCRYPTION='Y'; Query OK, 1 row affected (0.04 sec)
Be careful with the above ALTER: It will most likely block all other transactions during this conversion.Encrypting Tables Using pt-online-schema-change
Using pt-online-schema-change is fairly straightforward. While it has many options, the most basic usage to encrypt a single table is the following:pt-online-schema-change --execute --alter "engine=innodb encryption='Y'" D=mydb,t=mytab
pt-online-schema-change creates an encrypted, shadow copy of the table mytab. Once the table is populated with the original data, it drop-swaps the table and replaces the original table with the new one.
It is always recommended to first run pt-online-schema-change with the --dry-run flag first, in order to validate the command and the change.Encrypting All Tables in a Database
To encrypt all the tables in a database, we can simply take the above command and wrap it in a shell loop. The loop is fed a list of tables in the database from MySQL. You just need to change the dbname variable:# dbname=mydb # mysql -B -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$dbname' and TABLE_TYPE='BASE TABLE'" | while read line; do pt-online-schema-change --execute --alter "engine=innodb encryption='Y'" D=$dbname,t=$line; done
Be careful with the above command, as it will take off and blast through all the changes!Rotating InnoDB Encryption Keys
Your compliance policy dictates how often you need to rotate the encryption keys within InnoDB:mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;
This command creates a new key, decrypts all encrypted tablespace headers (where you store the per-table key) using the previous key, and then re-encrypts the headers with the new key. Notice that the table itself is not re-encrypted. This would be an extremely intrusive and potentially dangerous operation. Recall above that each tables’ encryption key never changes (unless the entire table gets decrypted). What can change is the key used to encrypt that key within each tables’ header.The keyring UDFs in MySQL
The keyring plugin can also be used to store sensitive data within MySQL for later retrieval. This functionality is beyond the scope of this blog as it doesn’t deal directly with InnoDB TE. You can read more about it in the MySQL manual.Conclusion
InnoDB Tablespace Encryption makes it easy to use MySQL encryption at rest. Keep in mind the downsides of using only TE: InnoDB tablespace encryption doesn’t cover undo logs, redo logs or the main ibdata1 tablespace. Additionally, InnoDB encryption doesn’t cover binary-logs and slow-query-logs.
Recently I was working on an SSL implementation with MySQL 5.7, and I made some interesting discoveries. I realized I could connect to the MySQL server without specifying the SSL keys on the client side, and the connection is still secured by SSL. I was confused and I did not understand what was happening.
In this blog post, I am going to show you why SSL works in MySQL 5.7, and it worked previously in MySQL 5.6.
Let’s start with an introduction of how SSL worked in 5.6.SSL in MySQL 5.6
The documentation for SSL in MySQL 5.6 is quite detailed, and it explains how SSL works. But first let’s make one thing clear: MySQL supports secure (encrypted) connections between clients and the server using the TLS (Transport Layer Security) protocol. TLS is sometimes referred to as SSL (Secure Sockets Layer), but MySQL doesn’t actually use the SSL protocol for secure connections because it provides weak encryption.
So when we/someone says MySQL is using SSL, it really means that it is using TLS. You can check which protocol you use:show status like 'Ssl_version'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Ssl_version | TLSv1.2 | +---------------+---------+
So how does it work? Let me quote a few lines from the MySQL documentation:
TLS uses encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect data change, loss, or replay. TLS also incorporates algorithms that provide identity verification using the X509 standard. X509 makes it possible to identify someone on the Internet. In basic terms, there should be some entity called a “Certificate Authority” (or CA) that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can present the certificate to another party as proof of identity. A certificate consists of its owner’s public key. Any data encrypted using this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.
It works with key pairs (private and public): the server has the private keys and the client has the public keys. Here is a link showing how we can generate these keys.MySQL 5.6 Client
When the server is configured with SSL, the client has to have the client certificates. Once it gets those, it can connect to the server using SSL:mysql -utestuser -p -h192.168.0.1 -P3306 --ssl-key=/root/mysql-ssl/client-key.pem --ssl-cert=/root/mysql-ssl/client-cert.pem
We have to specify the key and the certificate. Otherwise, we cannot connect to the server with SSL. So far so good, everything works as the documentation says. But how does it work in MySQL 5.7?SSL in MySQL 5.7
The documentation was very confusing to me, and did not help much. It described how to create the SSL keys the same way (and even the server and client configuration) as in MySQL 5.6. So if everything is the same, why I can connect without specifying the client keys? I did not have an answer for that. One of my colleagues (Alexey Poritskiy) found the answer after digging through the manuals for a while, and it finally clearly described this behavior:
As of MySQL 5.7.3, a client need specify only the --ssl option to obtain an encrypted connection. The connection attempt fails if an encrypted connection cannot be established. Before MySQL 5.7.3, the client must specify either the --ssl-ca option, or all three of the --ssl-ca, --ssl-key, and --ssl-cert options.
These lines are in the “CREATE USER” syntax manual.
After this, I re-read the SSL documentation and found the following:
5.7.3: On the client side, an explicit --ssl option is no longer advisory but prescriptive. Given a server enabled to support secure connections, a client program can require a secure conection by specifying only the --ssl option. The connection attempt fails if a secure connection cannot be established. Other --ssl-xxx options on the client side mean that a secure connection is advisory (the connection attempt falls back to an unencrypted connection if a secure connection cannot be established).
I still think the SSL manual could be more expressive and detailed.
Before MySQL 5.7.3, it used key pairs. After that, it works a bit similar to websites (HTTPS): the client does not need the keys and the connection still can be secure. (I would still like to see more detailed documentation how it really works.)Is This Good for Us?
In my opinion, this is a really good feature, but it didn’t get a lot of publicity. Prior to 5.7.3, if we wanted to use SSL we had to create the keys and use the client keys in every client application. It’s doable, but it is just a pain — especially if we are using different keys for every server with many users.
With this feature we can have a different key for every server, but on the client side we only have to enable the SSL connection. Most of the clients use it as the default if SSL is available.
I am pretty sure if people knew about this feature they would use it more often.Limitations
I tested it with many client applications, and all worked without specifying the client keys. I only had issues with the MySQL 5.6 client, even though the server was 5.7. In that case, I had to specify the client keys. Without them, I couldn’t connect to the server.
It is possible some older applications won’t support this feature.Conclusion
This is a great feature, easy to use it and it makes SSL implementations much easier. But I think the documentation should be clearer and more precise in describing how this new feature actually works. I already submitted a request to update the documentation.
Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017, at 11:00 am PDT / 2:00 pm EDT (UTC-7).Register Now
After 12 years of working as a PHP/JS developer for local and remote firms, Marcos decided to pursue his true love and become a full-time DBA. Since then, he has provided MySQL Support for Percona for the past six. He helps leading web properties with advice on anything-MySQL, as well as in-depth system performance diagnostic help.
Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017, at 7:00 am PDT / 10:00 am EDT (UTC-7).Register Now
The new MariaDB Server 10.2 release is out. It has some interesting new features, but beyond just a list of features we need to understand how to use them. This talk will go over everything new that MariaDB 10.2 has to offer.
In this webinar, we’ll learn about Window functions, common table expressions, finer-grained CREATE USER statements, and more – including getting mysqlbinlog up to parity with MySQL.
There are also unique MariaDB features that don’t exist in MySQL like encryption at rest, integrated Galera Cluster, threadpool, InnoDB defragmentation, roles, extended REGEXP, etc.
The webinar describes all the new features, both MySQL compatible and MariaDB-only ones, and show usage examples and practical use cases. We’ll also review the feature roadmap for MariaDB Server 10.3.Colin Charles, Chief Evangelist
Colin Charles is the Chief Evangelist at Percona. He was previously part of the founding team of MariaDB Server (2009), worked at MySQL since 2005, and has been a MySQL user since 2000. Before joining MySQL, he actively worked on the Fedora and OpenOffice.org projects. He’s well known within open source communities in APAC and has spoken at many conferences.
For general inquiries, please send us your question and someone will contact you.