pt-table-sync on Amazon RDS?

Is there anyway to get around either of these errors when I try to pt-table-sync two databases within Amazon RDS?

  1. $ pt-table-sync --recursion-method=none etc.
    Failed to /!50108 SET @@binlog_format := ‘STATEMENT’/: DBD::mysql::db do failed: Access denied; you need (at least one of) the SUPER privilege(s) for this operation [for Statement “/!50108 SET @@binlog_format := ‘STATEMENT’/”] at /usr/bin/pt-table-sync line 10828.

This tool requires binlog_format=STATEMENT, but the current binlog_format is set to MIXED and an error occurred while attempting to change it. If running MySQL 5.1.29 or newer, setting binlog_format requires the SUPER privilege. You will need to manually set binlog_format to ‘STATEMENT’ before running this tool.

  1. $ pt-table-sync --no-bin-log --recursion-method=none etc.
    DBD::mysql::db do failed: Access denied; you need (at least one of) the SUPER privilege(s) for this operation [for Statement “/*!32316 SET SQL_LOG_BIN=0 */”] at /usr/bin/pt-table-sync line 10793.

you need to assign SUPER privileges for the user you are using with pt-table-sync. As SUPER privilege is required to change binlog_format.
To check current privileges for the user. Check via following command.

mysql> SHOW GRANTS FOR 'username'@'hostname';

Use following command to assign SUPER privilege.

mysql> GRANT SUPER ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password';

Unfortunately, SUPER privilege is not available on Amazon RDS instances.

Hey guys, It would be awesome if it was possible to use pt-table-sync on AWS RDS. Simply superb. Right now it isn’t possible even though we don’t really care about the binlog format.

I was getting that binlog_format permission error too.

For some reason the --nocheck-privileges switch was removed… Why?

It really put me in a pickle as I have the script in place to sync from a protected database to a local copy etc. The script used to work fine until this switch was removed. The DBA’s are never going to grant super privs to a user account in that database…

Unless that parameter is restored at some point, you have two options, you can find an old version of the toolkit - I think version 2.1.3 still supports this switch… -or- you can modify the pt-table-sync script and comment out the line where the set binlog sql is executed around line 10827 like this - #$dbh->do($sql);

I love it, was solving this just today and found this thread. As if I weren’t here 3 years ago. LOL. The line to comment is 10910 in pt-table-sync version 3.0.2.

have you resolved it?

Unfortunately, RDS doesn’t support binlog_format=statement, which is needed for this tool to work properly.

Regards

Is there a way to use pt-table-sync in comparing the databases between two slave servers without using the master. One of the slave server is insync witht he master and the other slave is missing some data.

Hi,

You can use --no-check-master --no-check-slave

Regards