pt-table-sync unable to modify binlog_format

Question

I have a production percona master-master cluster. The secondary master has a replication error and using pt-table-sync, I am trying to sync the masters. Looks like binlog_format="STATEMENT" is necessary for any sync operation. Below is the pt-table-sync error

pt-table-sync --verbose --dry-run --sync-to-master h=,u=root,p=,D=,t=

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/local/bin/pt-table-sync line 10827.

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. Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle empowern_aws;host=...;mysql_read_default_group=client at /usr/local/bin/pt-table-sync line 10830.

As this is production cluster, is there a way to make pt-table-scan work without braking replication completely? Will manually updating binlog_format on both the masters effect the replication on corresponding slaves?

Appreciate the help.

Answer 1

As this is production cluster, is there a way to make pt-table-scan work without braking replication completely?

I take it you mean pt-table-sync, and yes you will be able to make it work (if you haven't already). I wouldn't use the root user and I normally create a temporary user to perform the table sync operations:

GRANT ALL ON *.* TO 'tablesync'@'%' IDENTIFIED BY 'tablesync';

This user should be present on both master servers and will allow pt-table-sync to set binlog_format=STATEMENT (for just that session, not globally) before proceeding.

Will manually updating binlog_format on both the masters effect the replication on corresponding slaves?

See - https://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. (When using STATEMENT mode, the binlog_format system variable is not replicated; when using MIXED or ROW logging mode, it is replicated but is ignored by the slave.) Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can cause replication to fail with errors such as Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

In your case if the slaves/replicas have binlog_format=MIXED and you've changed the masters to binlog_format=STATEMENT the slaves/replicas should be able to handle any STATEMENT based replication.

EDIT: Don't forget to remove the temporary user when you are done!

DROP USER 'tablesync'@'%';