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

How do I pt-table-sync all tables from one localhost database to another?

Engineer10Engineer10 EntrantInactive User Role Beginner
I have two databases on localhost: db1 and db2. I want to pt-table-sync all tables on db2 to be exactly like their equivalents on db1. I tried the following command line:
$ pt-table-sync --dry-run h=localhost,u=user,p=pass,d=db1 h=localhost,u=user,p=pass,d=db2

This failed with the following error:
you specified a database but not a table in h=localhost,u=user,p=pass,d=db1.  are you trying to sync only tables in the 'db1' database?  if so, use '--databases db1' instead.

I also tried dropping the D part of both DSNs, and instead specify
--databases=db1,db2

This shows zero differences, even though in reality differences do exist. I'm guessing what it does is compare each database to itself.

Is there a way to do this with pt-table-sync?

Comments

  • volkimdvolkimd Entrant Inactive User Role Beginner
    Hi,

    I have the same topic. I want to Sync all Tables of DB named ABC on Host A to DB CBA on Host B. In this case I want to replicate from a TestDB to ProdDB, no master-slave concept.
    I got the same results as above. Is there another way to realize this, maybe with wildcards or another Tool as pt-table-sync?

    Regards
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Hi,

    Using should work. If there are no differences, you might try using a different checksum algorithm.
    See https://jira.percona.com/browse/PT-187 for a complete explanation.

    Regards
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Answering the second question:
    You can sync to a different database by specifying the source Database and Table in the first DSN, but only the db in the destination DSN.
    Example:

    I have the table test.t1 in the instance running at port 12345 and I want to copy it to the database test2 in the instance running at port 12346
    pt-table-sync h=127.0.0.1,P=12345,u=msandbox,p=msandbox,D=test,t=t1 h=127.0.0.1,P=12346,u=msandbox,p=msandbox,D=test2 --execute
  • volkimdvolkimd Entrant Inactive User Role Beginner
    Hi,

    thank you for your answer.

    I've checked this topics. Pt-table-checksum can't sync across different Hosts if I don't run a Master - Slave approach, which doesn't make sense in this case. Pt-table-sync is working perfect for a defined number of tables, but there is no solution for all tables.

    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.