Not the answer you need?
Register and ask your own question!

pt-table-sync on Amazon RDS?

MikeTitusMikeTitus EntrantInactive User Role Beginner
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.

Comments

  • mirfanmirfan Database Administrator Inactive User Role Beginner
    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';
    
  • MikeTitusMikeTitus Entrant Inactive User Role Beginner
    Unfortunately, SUPER privilege is not available on Amazon RDS instances.
  • twihotwiho Entrant Inactive User Role Beginner
    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.
  • mscroggimscroggi Entrant Inactive User Role Beginner
    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);
  • twihotwiho Entrant Inactive User Role Beginner
    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.
  • tanj13tanj13 Entrant Inactive User Role Beginner
    MikeTitus wrote: »
    Unfortunately, SUPER privilege is not available on Amazon RDS instances.

    have you resolved it?
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Unfortunately, RDS doesn't support binlog_format=statement, which is needed for this tool to work properly.

    Regards
  • ookinaliookinali Entrant Current User Role Beginner
    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.
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Hi,

    You can use --no-check-master --no-check-slave <host1> <host2>

    Regards
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.