CODEWITHSUNDEEP
mysqlsynchronization
alditis
alditis

Reputation: 4817

Synchronization bidirectional with pt-table-sync

I'm trying to bidirectionally synchronize two databases (one local and one remote) using the command:

pt-table-sync --print --bidirectional --conflict-column * 
--conflict-comparison newest --databases my_db 
h=localhost,u=root,p=my_pass, h=ip_remote_server

Displays the error:

DBI connect(';host=Aplicaciones;mysql_read_default_group=client','',...) 
failed: Unknown MySQL server host 'Aplicaciones' (2) at 
/usr/bin/pt-table-sync line 2208

They have both the same username and password for the database as it says here?

I do not understand properly the documentation.

I hope you can help me. Thanks.

Upvotes: 0

Views: 1567

Answers (1)

alditis
alditis

Reputation: 4817

Finally I succeeded.

1.- The username, password and the name of the database must be the same.

2.- Give permission to the user to connect remotely to another server. You can follow this guide.

3.- Run the following scripts:

Server Local:

GRANT SELECT, REPLICATION CLIENT, SHOW DATABASES, SUPER, PROCESS ON *.* TO 'your_user'@'localhost' IDENTIFIED BY 'your_password';

Server Remote:

GRANT SELECT, REPLICATION CLIENT, SHOW DATABASES, SUPER, PROCESS ON *.* TO 'your_user'@'your_real_ip_local' IDENTIFIED BY 'your_password';

4.- Case for testing: Table 'color'

Server Local:

+----------+--------+--------+---------+---------------------+
| id_color | name   | status | deleted | date_register       |
+----------+--------+--------+---------+---------------------+
|        1 | Negro  |      1 |       0 | 2012-01-26 00:35:19 |
|        2 | Blue   |      1 |       0 | 2012-01-26 00:35:19 |
|        3 | Gray   |      1 |       0 | 2012-01-26 00:35:19 |
|        4 | Silver |      1 |       0 | 2012-01-26 00:35:19 |
|        5 | Tan    |      1 |       0 | 2012-01-26 00:35:19 |
|        6 | White  |      1 |       0 | 2012-04-05 14:14:37 |
+----------+--------+--------+---------+---------------------+

Server Remote:

+----------+----------+--------+---------+---------------------+
| id_color | name     | status | deleted | date_register       |
+----------+----------+--------+---------+---------------------+
|        1 | Black    |      1 |       0 | 2012-01-26 00:35:19 |
|        2 | Blue     |      1 |       0 | 2012-01-26 00:35:19 |
|        3 | Gray     |      1 |       0 | 2012-01-26 00:35:19 |
|        4 | Silver   |      1 |       0 | 2012-01-26 00:35:19 |
|        5 | Tan      |      1 |       0 | 2012-01-26 00:35:19 |
|        6 | White    |      1 |       0 | 2012-04-05 14:14:37 |
|        7 | Amarillo |      1 |       0 | 2013-10-19 01:25:08 |
+----------+----------+--------+---------+---------------------+

5.- Running the command:

$ pt-table-sync --print --bidirectional --conflict-column 'name' --conflict-comparison newest h=ip_your_server_remote,u=your_user,p=your_password,D=your_db,t=color h=localhost

6.- Results:

/*ip_your_server_remote*/ UPDATE `your_db`.`color` SET `name`='Negro', `status`='1', `deleted`='0', `date_register`='2012-01-26 00:35:19' WHERE `id_color`='1' LIMIT 1;
/*localhost*/ INSERT INTO `your_db`.`color`(`id_color`, `name`, `status`, `deleted`, `date_register`) VALUES ('7', 'Amarillo', '1', '0', '2013-10-19 01:25:08');

To execute directly the result, you can change the option --print by the option --execute.

I hope it's useful for someone.

Upvotes: 2

Related Questions