pt-table-sync two databases on the same mysql server?

Polyglot Projects Percona Toolkit
1

Hi,

is it possible with pt-table-sync to sync one table between two databases (databases have same structure) which are on the same localhost?

Example: I have first database “data1” with table “tbl1” and second database “data2” with table “tbl2” on the same mysql server and I want to have “tbl2” to be equal to “tbl1” if I make some changes to “tbl1”.

Best regards,
Chris

2

Hi,

You can try with --bidirectional option. Check here for more details. [url]pt-table-sync — Percona Toolkit Documentation

With that option, you can give 2 individual hosts details for sync. i.e

mysql> select * from data1.tbl1;
±—±----------+
| id | name |
±—±----------+
| 1 | nilnandan |
| 2 | joshi |
| 3 | ramesh |
±—±----------+
3 rows in set (0.00 sec)

mysql> select * from data2.tbl2;
±—±----------+
| id | name |
±—±----------+
| 1 | nilnandan |
| 2 | joshi |
±—±----------+
2 rows in set (0.00 sec)

shell$ pt-table-sync --bidirectional --print --conflict-column=“id” --conflict-comparison=“newest” h=localhost,u=root,p=root,D=data1,t=tbl1 h=localhost,u=root,p=root,D=data2,t=tbl2
/localhost/ INSERT INTO data2.tbl2(id, name) VALUES (‘3’, ‘ramesh’);
shell$

Here, 1st host will be reliable server and 2nd which is lacking data. You can also do vice-versa. I would suggest to check first with --print option on some stage servers and then try with prod dbs.