Question:- How Can I Synchronize a Table or DB on Two Servers Where Neither is Up-to-date?
Suggestion/Recommendation :- Use MySQl Utilities Scripts(Ref link for introduction :http://myora-dba-notes.blogspot.in/2014/03/introduction-to-mysql-utilities.html)
In this example below, we connected to two servers and compare the database named mydb.
We enabled the transformation statements using a combination of options as follows.
1.The --difftype=SQL option instructs the utility to generate the SQL statements.
2.The --show-reverse option instructs the utility to generate the differences in both direction.
That is, from the perspective of server1 as compared to server2 and server2 as compared to server1.
3.Lastly, the --quiet option simply turns off the verbosity of print statements that normally occur for communicating progress.
Before proceeding let discuss the environment in which we are explaining this case to be tested , There is one db on both server i.e mydb and on server 1 we have 4 table out of which 3 are same as we have in server 2 but one on both server is different table and out of 3 common table we have one table which is out of sync or having different records updated differently on both servers ,So in order to synchronize both server database ,Please follow how can we found the SQL statements that needed to bring the tables into synch
Note:-
We can also compare different db with command
cmd:-mysqldbcompare --server1=root:root@host1:3310 --server2=root:rrrr@host2:3310 mydbserver1:mydbserver2 -a --difftype=SQL --show-reverse --quiet
Server 1:-
mysql> use mydb
mysql> show tables;
+----------------+
| Tables_in_mydb |+----------------+
| mydbtb2 |
| mydbtb3 |
| mydbtb5 |
| mydbtbl |
+----------------+
4 rows in set (0.00 sec
mysql> select * from mydbtbl;
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
| id | snap_id | host_id | rd_ios_s | wr_ios_s | rkbs | wkbs | queue | wait | svc_t | busy | snap_time |
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
| 22809 | 2 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:26:35 |
| 22811 | 3 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:27:24 |
| 22815 | 5 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:28:15 |
| 22829 | 7 | 243 | 10.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:35:02 |
| 22839 | 12 | 243 | 10.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:40:02 |
| 22853 | 19 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:47:02 |
| 22893 | 39 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 18:07:02 |
| 22901 | 43 | 243 | 0.00 | 200.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 18:11:02 |
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
8 rows in set (0.00 sec)
Server 2:-
mysql> use mydb
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| mydbtb2 |
| mydbtb3 |
| mydbtb4 |
| mydbtbl |
+----------------+
4 rows in set (0.00 sec)
mysql> select * from mydbtbl;
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
| id | snap_id | host_id | rd_ios_s | wr_ios_s | rkbs | wkbs | queue | wait | svc_t | busy | snap_time |
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
| 22811 | 3 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:27:24 |
| 22815 | 5 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:28:15 |
| 22829 | 7 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:35:02 |
| 22839 | 12 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:40:02 |
| 22845 | 15 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:43:02 |
| 22853 | 19 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:47:02 |
| 22893 | 39 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 18:07:02 |
| 22901 | 43 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 18:11:02 |
| 22905 | 45 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 18:13:02 |
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
9 rows in set (0.00 sec)
Solution :-
Example 1:-
[root@localhost ~]# mysqldbcompare --server1=root:root@host1:3310 --server2=root:rrrr@host2:3310 mydb -a --difftype=SQL --show-reverse
# server1 on host1: ... connected.
# server2 on host2: ... connected.
# Checking databases mydb on server1 and mydb on server2
#
# WARNING: Objects in server1.mydb but not in server2.mydb:
# TABLE: mydbtb5
#
# WARNING: Objects in server2.mydb but not in server1.mydb:
# TABLE: mydbtb4
#
# Defn Row Data
# Type Object Name Diff Count Check
# ---------------------------------------------------------------
# TABLE mydbtb2 pass pass pass
# TABLE mydbtb3 pass pass pass
# TABLE mydbtbl pass FAIL FAIL
#
# Row counts are not the same among `mydb`.`mydbtbl` and `mydb`.`mydbtbl`.
#
# Transformation for --changes-for=server1:
#
# Data differences found among rows:
UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '0.00' WHERE `id` = '22829'`snap_time` = '2013-10-04 17:35:02';
UPDATE `mydb`.`mydbtbl` SET `wr_ios_s` = '0.00' WHERE `id` = '22901'`snap_time` = '2013-10-04 18:11:02';
UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '0.00' WHERE `id` = '22839'`snap_time` = '2013-10-04 17:40:02';
DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22809'`snap_time` = '2013-10-04 17:26:35';
INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22845', '15', '243', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 17:43:02');
INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22905', '45', '243', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 18:13:02');
#
# Transformation for reverse changes (--changes-for=server2):
#
# # Data differences found among rows:
# UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '10.00' WHERE `id` = '22829'`snap_time` = '2013-10-04 17:35:02';
# UPDATE `mydb`.`mydbtbl` SET `wr_ios_s` = '200.00' WHERE `id` = '22901'`snap_time` = '2013-10-04 18:11:02';
# UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '10.00' WHERE `id` = '22839'`snap_time` = '2013-10-04 17:40:02';
# DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22845'`snap_time` = '2013-10-04 17:43:02';
# DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22905'`snap_time` = '2013-10-04 18:13:02';
# INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22809', '2', '6', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 17:26:35');
#
# Database consistency check failed.
#
# ...done
[root@localhost ~]#
Example 2:-
[root@localhost ~]# mysqldbcompare --server1=root:root@host1:3310 --server2=root:rrrr@host2:3310 mydb -a --difftype=SQL --show-reverse --quiet# Checking databases mydb on server1 and mydb on server2
#
# WARNING: Objects in server1.mydb but not in server2.mydb:
# TABLE: mydbtb5
#
# WARNING: Objects in server2.mydb but not in server1.mydb:
# TABLE: mydbtb4
#
#
# Row counts are not the same among `mydb`.`mydbtbl` and `mydb`.`mydbtbl`.
#
# Transformation for --changes-for=server1:
#
# Data differences found among rows:
UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '0.00' WHERE `id` = '22829'`snap_time` = '2013-10-04 17:35:02';
UPDATE `mydb`.`mydbtbl` SET `wr_ios_s` = '0.00' WHERE `id` = '22901'`snap_time` = '2013-10-04 18:11:02';
UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '0.00' WHERE `id` = '22839'`snap_time` = '2013-10-04 17:40:02';
DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22809'`snap_time` = '2013-10-04 17:26:35';
INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22845', '15', '243', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 17:43:02');
INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22905', '45', '243', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 18:13:02');
#
# Transformation for reverse changes (--changes-for=server2):
#
# # Data differences found among rows:
# UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '10.00' WHERE `id` = '22829'`snap_time` = '2013-10-04 17:35:02';
# UPDATE `mydb`.`mydbtbl` SET `wr_ios_s` = '200.00' WHERE `id` = '22901'`snap_time` = '2013-10-04 18:11:02';
# UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '10.00' WHERE `id` = '22839'`snap_time` = '2013-10-04 17:40:02';
# DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22845'`snap_time` = '2013-10-04 17:43:02';
# DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22905'`snap_time` = '2013-10-04 18:13:02';
# INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22809', '2', '6', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 17:26:35');
#
Example 3:-
[root@localhost ~]# mysqldbcompare --server1=root:root@host1:3310 --server2=root:rrrr@host2:3310 mydb -a --quiet
# Checking databases mydb on server1 and mydb on server2
#
# WARNING: Objects in server1.mydb but not in server2.mydb:
# TABLE: mydbtb5
#
# WARNING: Objects in server2.mydb but not in server1.mydb:
# TABLE: mydbtb4
#
#
# Row counts are not the same among `mydb`.`mydbtbl` and `mydb`.`mydbtbl`.
#
# Data differences found among rows:
--- `mydb`.`mydbtbl`
+++ `mydb`.`mydbtbl`
@@ -1,7 +1,7 @@
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
| id | snap_id | host_id | rd_ios_s | wr_ios_s | rkbs | wkbs | queue | wait | svc_t | busy | snap_time |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
-| 22829 | 7 | 243 | 10.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:35:02 |
-| 22901 | 43 | 243 | 0.00 | 200.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 18:11:02 |
-| 22839 | 12 | 243 | 10.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:40:02 |
+| 22829 | 7 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:35:02 |
+| 22901 | 43 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 18:11:02 |
+| 22839 | 12 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:40:02 |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
# Rows in `mydb`.`mydbtbl` not in `mydb`.`mydbtbl`
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
| id | snap_id | host_id | rd_ios_s | wr_ios_s | rkbs | wkbs | queue | wait | svc_t | busy | snap_time |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
| 22809 | 2 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:26:35 |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
# Rows in `mydb`.`mydbtbl` not in `mydb`.`mydbtbl`
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
| id | snap_id | host_id | rd_ios_s | wr_ios_s | rkbs | wkbs | queue | wait | svc_t | busy | snap_time |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
| 22845 | 15 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 17:43:02 |
| 22905 | 45 | 243 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2013-10-04 18:13:02 |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
No comments:
Post a Comment