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

pt-online-schema changes for 2 different databases not working

chintoo02018chintoo02018 ContributorCurrent User Role Beginner
Below command only woks for same database name (synctest) on diffrent server.
pt-table-sync --execute  h=xx.xx.xx.xxx -u synctest -p synctest ,D=synctest,t=a h=localhost,D=ptest,t=a

need to sync table from synctest database to ptest dataabase

Comments

  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Use the full DNS for both MySQL instances.
    I set up a local MySQL instance and started the sandbox.
    The I've synced encuestas.personas table using:
    bin/pt-table-sync --execute h=127.1,P=3306,u=root,p=root,D=encuestas,t=personas h=127.1,P=12345,u=msandbox,p=msandbox,D=s1,t=personas
    
  • chintoo02018chintoo02018 Contributor Current User Role Beginner
    Thanks , Its work but below getting error , If we have to give super privilege to local user , or any bypass option
    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 */"]
    

    Also check in log
    48 Query     SELECT @@SQL_MODE
                       48 Query     SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/
                       48 Query     /*!32316 SET SQL_LOG_BIN=0 */
                       48 Query     /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
    

    Why there is need to give SUPER privileges for Source database , where only select command to be execute ?
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    It because it is changing the binlog format and you need SUPER privileges to do that.
    https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html
  • chintoo02018chintoo02018 Contributor Current User Role Beginner
    There 2 issue found

    1 - If SUPER Privileges required for Source then this directly security risk factor .
    2 - It gives EXIT Status =2 , Where shell script shows $?=2 and got failed , where ever there changes in data found .
    # DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
    #      1      0      92    23   Chunk     20:59:07 20:59:30  2       DB.TBL
    

    Can you report the issue for Further improvements
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.