Amazon RDS and Pt-Online-Schema-Change
Amazon RDS and Pt-Online-Schema-Change
Here's a workaround to get RDS to accept the pt-online-schema-change tool, which can throw an error based on permissions.
Join the DZone community and get the full member experience.
Join For FreeCompliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper
In this blog post, I discuss some of the insights needed when using Amazon RDS and pt-online-schema-change together.
The pt-online-schema-change tool runs DDL queries (ALTER) online so that the table is not locked for reads and writes. It is a commonly used tool by community users and customers. Using it on Amazon RDS requires knowing about some specific details. First, a high-level explanation of how the tool works.
This is an example from the documentation:
pt-online-schema-change--alter"ADD COLUMN c1 INT"D=sakila,t=actorAs we can see, it is a tool that uses the basic features of MySQL. You can run it on MySQL, Percona Server, MariaDB, Amazon RDS, and so on. But when using Amazon, there is a hidden issue: you don't have SUPER privileges. This means that if you try to run the tool on an RDS with binary logs enabled, you could get the following error:
DBD::mysql::db dofailed:You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)[forStatement"CREATE TRIGGER `pt_osc_db_table_del` AFTER DELETE ON `db`.`table` FOR EACH ROW DELETE IGNORE FROM `db`.`_table_new` WHERE `db`.`_table_new`.`table_id` <=> OLD.`table_id` AND `db`.`_table_new`.`account_id` <=> OLD.`account_id`"]at/usr/bin/pt-online-schema-change line10583.This documentation page explains the reason for this message.
The bottom line is creating triggers on a server with binary logs enabled. This requires a user with SUPER privileges (which is impossible in Amazon RDS). The error message specifies the workaround. We need to enable the variable log_bin_trust_function_creators. Enabling it is like saying to the server:
“I trust regular users' triggers and functions, and that they won't cause problems, so allow my users to create them.”
Since the database functionality won't change, it becomes a matter of trusting your users. log_bin_trust_function_creators is a global variable that can be changed dynamically:
mysql>SET GLOBAL log_bin_trust_function_creators=1;Run the tool again. This time, it will work. After the ALTER is done, you can change the variable to 0 again.
Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper
Like This Article? Read More From DZone
Published at DZone with permission of Miguel Angel Nieto . See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}