Announcement

Announcement Module
Collapse
No announcement yet.

pt-table-sync on Amazon RDS?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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


    2) $ 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.

  • #2
    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.

    Code:
    mysql> SHOW GRANTS FOR 'username'@'hostname';
    Use following command to assign SUPER privilege.
    Code:
    mysql> GRANT SUPER ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password';

    Comment


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

      Comment


      • #4
        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.

        Comment


        • #5
          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);
          Last edited by mscroggi; 03-05-2014, 12:50 PM.

          Comment

          Working...
          X