2

This question is related to another I had asked earlier here: Passing passwords more securely to mysql utilities on the command line.

I have recently been trying to use mysqldiff.exe, part of MySQL Utilities group of tools. My goal is to spot any schema differences between database1 and database2, then going by what it says "on the label", I should be able to obtain statements that can transform either of the databases to match the other.

Here's example output I obtained by comparing database1.clients and database2.clients.

# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing database1.clients to database2.clients            [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- database1.clients
+++ database2.clients
@@ -61,4 +61,4 @@
   CONSTRAINT `clients_ibfk_6` FOREIGN KEY (`state_code`) REFERENCES `states` (`state_code`) ON DELETE
SET NULL ON UPDATE CASCADE,
   CONSTRAINT `clients_ibfk_7` FOREIGN KEY (`rel_id`) REFERENCES `religions` (`rel_id`) ON DELETE SET NULL ON
UPDATE CASCADE,
   CONSTRAINT `clients_ibfk_9` FOREIGN KEY (`nationality_id`) REFERENCES `country` (`Code`) ON DELETE SET NULL
 ON UPDATE CASCADE
-) ENGINE=InnoDB AUTO_INCREMENT=8041 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
+) ENGINE=InnoDB AUTO_INCREMENT=691802 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
Compare failed. One or more differences found.

Problem is, I was expecting the output to be usable SQL statements, so I can then run this on the relevant side of the compare. How can I use this tool to perform database schema synchronization as I described?

Improve this question

1 Answer 1

Reset to default
2

As it appears on the manual, use the option --difftype=sql in order to obtain the results as ALTER TABLEs.

You have an example on a recent MySQL Performance Blog post:

$ mysqldiff --force --difftype=sql \
--server1=root:[email protected]:21489 \
--server2=root:[email protected]:21490 \
employees:employees
Improve this answer
2
  • Outch!! And I missed that? Need to change my lenses! Thank you so much, bro!
    – Ifedi Okonkwo
    Commented Apr 20, 2015 at 11:46
  • However, having gone through your suggested percona.com blog. I wonder if my bublbe is burst. It worries me when it says the diff SQL statements is "not perfect", and I "need to visually check the output before applying the changes". How does that sound when you have hundreds or thousands of query lines in the diff output?
    – Ifedi Okonkwo
    Commented Apr 20, 2015 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.